[SQL] CASE式とGROUPING関数を使ってクロス集計をする

宇都宮洋

2016.08.27

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

分析系のSQLを書くことが多くなってきたので学んだことを書いていきます。　　

まずそもそもクロス集計とはなんなのか？
クロス集計とは、与えられたデータの中から複数個の項目を抽出してデータ分析や集計を行う手法のことである。 Weblio辞書２つ以上の項目を掛け合わせて、その項目の相関関係を表にして視覚的にわかりやすく表現しているものだそうです。

３個以上の項目を使う多重クロス集計というのもあるそうですが、今回SQLで実現するのは２個の項目を使用したクロス集計になります。

環境

PostgreSQL 9.5.4

求める結果

今回はサンプルのデータベースのpaymentテーブルを使って説明します。
手を動かしながら書きたい方はこちらを参考にしてサンプルをご用意ください。

スタッフごとの直近３カ月の売り上げの合計を表示するSQLを書いてみます。

select * from payment;

 payment_id | customer_id | staff_id | rental_id | amount |        payment_date        
------------+-------------+----------+-----------+--------+----------------------------
      17503 |         341 |        2 |      1520 |   7.99 | 2007-02-15 22:25:46.996577
      17504 |         341 |        1 |      1778 |   1.99 | 2007-02-16 17:23:14.996577
      17505 |         341 |        1 |      1849 |   7.99 | 2007-02-16 22:41:45.996577
      17506 |         341 |        2 |      2829 |   2.99 | 2007-02-19 19:39:56.996577
      17507 |         341 |        2 |      3130 |   7.99 | 2007-02-20 17:31:48.996577
      17508 |         341 |        1 |      3382 |   5.99 | 2007-02-21 12:33:49.996577
      17509 |         342 |        2 |      2190 |   5.99 | 2007-02-17 23:58:17.996577
      17510 |         342 |        1 |      2914 |   5.99 | 2007-02-20 02:11:44.996577
      17511 |         342 |        1 |      3081 |   2.99 | 2007-02-20 13:57:39.996577
      17512 |         343 |        2 |      1547 |   4.99 | 2007-02-16 00:10:50.996577
      17513 |         343 |        1 |      1564 |   6.99 | 2007-02-16 01:15:33.996577
      17514 |         343 |        2 |      1879 |   0.99 | 2007-02-17 01:26:00.996577
      17515 |         343 |        2 |      1922 |   0.99 | 2007-02-17 04:32:51.996577
      17516 |         343 |        2 |      2461 |   6.99 | 2007-02-18 18:26:38.996577
      17517 |         343 |        1 |      2980 |   8.99 | 2007-02-20 07:03:29.996577
      17518 |         343 |        1 |      3407 |   0.99 | 2007-02-21 14:42:28.996577
      17519 |         344 |        1 |      1341 |   3.99 | 2007-02-15 10:54:44.996577
      17520 |         344 |        2 |      1475 |   4.99 | 2007-02-15 19:36:27.996577
      17521 |         344 |        1 |      1731 |   0.99 | 2007-02-16 14:00:38.996577
      17522 |         345 |        2 |      1210 |   0.99 | 2007-02-15 01:26:17.996577
      17523 |         345 |        1 |      1457 |   4.99 | 2007-02-15 18:34:15.996577
以下省略 14596 row

上のデータを

 staff_id |  ２月   |   ３月   |   ４月   |  ５月  
----------+---------+----------+----------+--------
        1 | 4160.84 | 11776.83 | 14080.36 | 234.09
        2 | 4191.00 | 12109.73 | 14479.10 | 280.09

SQLでこのようにしたいと思います。

CASE式を使ってデータを縦持ちにする

sele　ct
 staff_id
,sum(case date_trunc('month',payment_date) when '2007-02-01' then amount else 0 end) as "２月"
,sum(case date_trunc('month',payment_date) when '2007-03-01' then amount else 0 end) as "３月"
,sum(case date_trunc('month',payment_date) when '2007-04-01' then amount else 0 end) as "４月"
,sum(case date_trunc('month',payment_date) when '2007-05-01' then amount else 0 end) as "５月" 
from
 payment 
group by
 staff_id 
order by
 staff_id asc;

まず９行目でGroup By句でstaff_idごとにグループ化しています。

ポイントは3-7行目のcase式です。
date_trunc()は非常に便利で、第１引数でmonthと指定するとそれ以下の数字をdayと指定すればそれ以下を切り捨ててくれます。
※truncはtruncateの略で端数を切り捨てる、以下の桁を切り捨てると言う意味
例）
- date_trunc('month',payment_date) →　 2007-02-01 00:00:00
- date_trunc('day',payment_date) →　 2007-02-15 00:00:00
- date_trunc('hour',payment_date) →　 2007-02-15 22:00:00

date_trunc('month',payment_date)とすれば、 2007-02-02 00:00:00でも2007-02-28 00:00:00でも２月の値であれば全て同じ2007-02-01 00:00:00になるのでその後の条件式when '2007-02-01'が楽に書けるようになります。

横のままだとわかりにくいかもしれないので

sum(
  case date_trunc('month',payment_date)　　
   when '2007-02-01'
    then amount 
   else 0
  end) as "２月"

１行目 sum()関数の引数に
2行目 date_trunc()関数の結果が
3行目 '2007-02-01'のときは
4行目 amountを渡す
5行目 '2007-02-01'と等しくない時は0を渡す
6行目このカラムの値は'２月'とする
このCASE式によりGroup by句で分けられたグループごとに、③の条件が合った時だけamountが足されていくことになり、２月だけの値を縦で表示することができまます。
同じことを３、４、５月にもしています。

Grouping演算子で合計を表示

select
 staff_id
,sum(case date_trunc('month',payment_date) when '2007-02-01' then amount else 0 end) as "２月"
,sum(case date_trunc('month',payment_date) when '2007-03-01' then amount else 0 end) as "３月"
,sum(case date_trunc('month',payment_date) when '2007-04-01' then amount else 0 end) as "４月"
,sum(case date_trunc('month',payment_date) when '2007-05-01' then amount else 0 end) as "５月" 
from
 payment 
group by
 rollup(staff_id) 
order by
 staff_id asc;

 staff_id |  ２月   |   ３月   |   ４月   |  ５月  
----------+---------+----------+----------+--------
        1 | 4160.84 | 11776.83 | 14080.36 | 234.09
        2 | 4191.00 | 12109.73 | 14479.10 | 280.09
          | 8351.84 | 23886.56 | 28559.46 | 514.18

他のDBではすでにある機能のようですが、postgreSQLでも9.5以降ならrollup関数を使い合計を出すこともできます。