裏MySQL ユーザー変数を使って合計と平均も同時に計算する その2

myql

ユーザー変数を使って、合計と平均を同時に出す場合は以下のとおり

SELECT 
 '00000/00/00' `report_date`,
 0             `imp`,
 0             `click`,
 0             `action`,
 0.00          `ctr`,
 0.00          `cvr`,
 0             `revenue` 
FROM DUAL 
WHERE @imp:=0+@click:=0+@action:=0+@revenue:=0 
UNION ALL 
SELECT 
`report_date` ,
`imp`     +(@imp    :=@imp     + `imp`   )*0 ,
`click`   +(@click  :=@click   + `click` )*0 ,
`action`  +(@action :=@action  + `action`)*0 ,
cast(click  * 100/imp as decimal(3,2)),
cast(action * 100/imp as decimal(3,2)),
`revenue`+(@revenue:=@revenue + `revenue`)*0 
FROM 
 report
UNION ALL 
SELECT 
'合計/平均',
cast(@imp     as unsigned),
cast(@click   as unsigned),
cast(@action  as unsigned),
cast(@click  * 100/@imp as decimal(3,2)),
cast(@action * 100/@imp as decimal(3,2)),
cast(@revenue as unsigned)
report_date imp click action ctr cvr revenue
2011-12-01 12125 315 15 2.60 0.12 200
2011-12-02 32132 615 40 1.91 0.12 612
2011-12-03 22545 345 11 1.53 0.05 315
2011-12-04 28012 451 30 1.61 0.11 425
2011-12-05 17122 315 17 1.84 0.10 375
2011-12-06 16212 275 11 1.70 0.07 285
2011-12-07 21454 487 33 2.27 0.15 448
2011-12-08 41212 811 64 1.97 0.16 777
2011-12-09 12151 301 11 2.48 0.09 285
2011-12-10 21451 412 22 1.92 0.10 385
2011-12-11 17151 260 14 1.52 0.08 275
2011-12-12 23712 612 39 2.58 0.16 555
2011-12-13 37121 701 40 1.89 0.11 657
2011-12-14 41212 744 74 1.81 0.18 800
2011-12-15 12141 160 10 1.32 0.08 125
2011-12-16 15101 201 10 1.33 0.07 175
2011-12-17 24121 317 38 1.31 0.16 488
2011-12-18 32128 815 66 2.54 0.21 715
2011-12-19 51215 888 89 1.73 0.17 981
2011-12-20 36454 511 41 1.40 0.11 488
2011-12-21 12155 304 25 2.50 0.21 285
合計/平均 526927 9840 700 1.87 0.13 9651

ざっくり説明すると、UNION ALL で3つの部分に分かれています。はじめは、ユーザー変数の初期化とカラム名の初期化、次にメインのSQL、最後に合計と平均です。

はじめのSQLでWHERE句にユーザ変数の初期化が書かれているのはWHERE 0として行を表示させないようにさせている為です。つまり、WHERE 0とするとカラムに処理が移らないため、カラムの方にユーザ変数の初期化を書くと処理されないからです。つまり、UNION ALLされていますが、WHEREが0になるので、ユーザー変数の初期化とカラム名の設定だけで全く表示されない行になります。表示されないので、カラムの値はダミーで、カラムの型さえあっていれば、なんでも構いません。カラムの数と型が合っていて、カラム名が定義できればよいということです。

メインのSQLでそれぞれのカラム用に用意したユーザー変数に足しこみを行っています。

最後は、ユーザー変数を使った合計や平均の表示部分ですが、SQL中で変数を初期化した場合ユーザー変数の型がBINARYとなってしまうため、適切にCASTをしてあげる必要があります。

最後にフォーマットを整形したものは以下のとおりです。

SELECT 
 '00000/00/00' `日付`,
 0             `インプレッション数`,
 0             `クリック数`,
 0             `アクション数`,
 0.00          `クリック率`,
 0.00          `コンバージョン率`,
 0             `収益` 
FROM DUAL 
WHERE @imp:=0+@click:=0+@action:=0+@revenue:=0 
UNION ALL 
SELECT 
date_format(`report_date`,'%Y年%m月%d日') ,
concat(`imp`    ,' 回'   ),
concat(`click`  ,' click' ),
concat(`action` ,' action'),
concat(cast(click  * 100/imp as decimal(3,2)),' %'),
concat(cast(action * 100/imp as decimal(3,2)),' %'),
concat(`revenue`+
(@imp    :=@imp     + `imp`   )*0 +
(@click  :=@click   + `click` )*0 +
(@action :=@action  + `action`)*0 +
(@revenue:=@revenue + `revenue`)*0 ,
' 円') 
FROM 
 report 
UNION ALL 
SELECT 
'合計/平均',
concat(cast(@imp     as unsigned),' 回'    ),
concat(cast(@click   as unsigned),' click' ),
concat(cast(@action  as unsigned),' action'),
concat(cast(@click  * 100/@imp as decimal(3,2)),' %'),
concat(cast(@action * 100/@imp as decimal(3,2)),' %'),
concat(cast(@revenue as unsigned)              , '円')
日付 インプレッション数 クリック数 アクション数 クリック率 コンバージョン率 収益
2011年12月01日 12125 回 315 click 15 action 2.60 % 0.12 % 200 円
2011年12月02日 32132 回 615 click 40 action 1.91 % 0.12 % 612 円
2011年12月03日 22545 回 345 click 11 action 1.53 % 0.05 % 315 円
2011年12月04日 28012 回 451 click 30 action 1.61 % 0.11 % 425 円
2011年12月05日 17122 回 315 click 17 action 1.84 % 0.10 % 375 円
2011年12月06日 16212 回 275 click 11 action 1.70 % 0.07 % 285 円
2011年12月07日 21454 回 487 click 33 action 2.27 % 0.15 % 448 円
2011年12月08日 41212 回 811 click 64 action 1.97 % 0.16 % 777 円
2011年12月09日 12151 回 301 click 11 action 2.48 % 0.09 % 285 円
2011年12月10日 21451 回 412 click 22 action 1.92 % 0.10 % 385 円
2011年12月11日 17151 回 260 click 14 action 1.52 % 0.08 % 275 円
2011年12月12日 23712 回 612 click 39 action 2.58 % 0.16 % 555 円
2011年12月13日 37121 回 701 click 40 action 1.89 % 0.11 % 657 円
2011年12月14日 41212 回 744 click 74 action 1.81 % 0.18 % 800 円
2011年12月15日 12141 回 160 click 10 action 1.32 % 0.08 % 125 円
2011年12月16日 15101 回 201 click 10 action 1.33 % 0.07 % 175 円
2011年12月17日 24121 回 317 click 38 action 1.31 % 0.16 % 488 円
2011年12月18日 32128 回 815 click 66 action 2.54 % 0.21 % 715 円
2011年12月19日 51215 回 888 click 89 action 1.73 % 0.17 % 981 円
2011年12月20日 36454 回 511 click 41 action 1.40 % 0.11 % 488 円
2011年12月21日 12155 回 304 click 25 action 2.50 % 0.21 % 285 円
合計/平均 526927 回 9840 click 700 action 1.87 % 0.13 % 9651円

参考