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

mysql

合計も同時に出したいだけであれば、GROUP BYと共にWITH ROLLUPを使うことでできます
http://dev.mysql.com/doc/refman/4.1/ja/group-by-modifiers.html


しかし、同時にカラムによって合計/平均を出したいなど複雑な条件の場合はできません。その場合ユーザー変数を使うことで1クエリで処理することができます。

サンプルとして以下のようなアフィリエイトのレポート画面用のテーブルを使って説明します。

CREATE TABLE `report` (
  `report_id`   BIGINT       NOT NULL AUTO_INCREMENT,
  `report_date` DATE         NOT NULL ,
  `imp`         INT          NOT NULL ,
  `click`       INT          NOT NULL ,
  `action`      INT          NOT NULL ,
  `revenue`     INT          NOT NULL ,
PRIMARY KEY (  `report_id`  ),
UNIQUE      (  `report_date`)
) ENGINE = INNODB;

INSERT INTO `report` (
`report_date` ,
`imp` ,
`click` ,
`action` ,
`revenue`
)
VALUES 
('2011/12/01',  '12125',  '315',  '15', '200'),
('2011/12/02',  '32132',  '615',  '40', '612'),
('2011/12/03',  '22545',  '345',  '11', '315'),
('2011/12/04',  '28012',  '451',  '30', '425'),
('2011/12/05',  '17122',  '315',  '17', '375'),
('2011/12/06',  '16212',  '275',  '11', '285'),
('2011/12/07',  '21454',  '487',  '33', '448'),
('2011/12/08',  '41212',  '811',  '64', '777'),
('2011/12/09',  '12151',  '301',  '11', '285'),
('2011/12/10',  '21451',  '412',  '22', '385'),
('2011/12/11',  '17151',  '260',  '14', '275'),
('2011/12/12',  '23712',  '612',  '39', '555'),
('2011/12/13',  '37121',  '701',  '40', '657'),
('2011/12/14',  '41212',  '744',  '74', '800'),
('2011/12/15',  '12141',  '160',  '10', '125'),
('2011/12/16',  '15101',  '201',  '10', '175'),
('2011/12/17',  '24121',  '317',  '38', '488'),
('2011/12/18',  '32128',  '815',  '66', '715'),
('2011/12/19',  '51215',  '888',  '89', '981'),
('2011/12/20',  '36454',  '511',  '41', '488'),
('2011/12/21',  '12155',  '304',  '25', '285')
report_id report_date imp click action revenue
1 2011-12-01 12125 315 15 200
2 2011-12-02 32132 615 40 612
3 2011-12-03 22545 345 11 315
4 2011-12-04 28012 451 30 425
5 2011-12-05 17122 315 17 375
6 2011-12-06 16212 275 11 285
7 2011-12-07 21454 487 33 448
8 2011-12-08 41212 811 64 777
9 2011-12-09 12151 301 11 285
10 2011-12-10 21451 412 22 385
11 2011-12-11 17151 260 14 275
12 2011-12-12 23712 612 39 555
13 2011-12-13 37121 701 40 657
14 2011-12-14 41212 744 74 800
15 2011-12-15 12141 160 10 125
16 2011-12-16 15101 201 10 175
17 2011-12-17 24121 317 38 488
18 2011-12-18 32128 815 66 715
19 2011-12-19 51215 888 89 981
20 2011-12-20 36454 511 41 488
21 2011-12-21 12155 304 25 285


WITH ROLLUPで出す場合、今回のサンプルでは無駄に合計したい対象のカラムにsum関数と元から一意なreport_dateにGROUOP BYをかけてあげる必要があります。

SELECT
  report_id,
  report_date,
  sum(imp)     imp   ,
  sum(click)   click ,
  sum(action)  action,
  sum(revenue) revenue
FROM
  report 
GROUP BY
 report_date 
WITH ROLLUP;
report_id report_date imp click action revenue
1 2011-12-01 12125 315 15 200
2 2011-12-02 32132 615 40 612
3 2011-12-03 22545 345 11 315
4 2011-12-04 28012 451 30 425
5 2011-12-05 17122 315 17 375
6 2011-12-06 16212 275 11 285
7 2011-12-07 21454 487 33 448
8 2011-12-08 41212 811 64 777
9 2011-12-09 12151 301 11 285
10 2011-12-10 21451 412 22 385
11 2011-12-11 17151 260 14 275
12 2011-12-12 23712 612 39 555
13 2011-12-13 37121 701 40 657
14 2011-12-14 41212 744 74 800
15 2011-12-15 12141 160 10 125
16 2011-12-16 15101 201 10 175
17 2011-12-17 24121 317 38 488
18 2011-12-18 32128 815 66 715
19 2011-12-19 51215 888 89 981
20 2011-12-20 36454 511 41 488
21 2011-12-21 12155 304 25 285
21 NULL 526927 9840 700 9651