この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
分析系のSQLを書くことが多くなってきたので学んだことを書いていきます。
まずそもそもクロス集計とはなんなのか?
クロス集計とは、与えられたデータの中から複数個の項目を抽出してデータ分析や集計を行う手法のことである。
Weblio辞書
2つ以上の項目を掛け合わせて、その項目の相関関係を表にして視覚的にわかりやすく表現しているものだそうです。
3個以上の項目を使う多重クロス集計というのもあるそうですが、今回SQLで実現するのは2個の項目を使用したクロス集計になります。
環境
PostgreSQL 9.5.4
求める結果
今回はサンプルのデータベースのpaymentテーブルを使って説明します。
手を動かしながら書きたい方はこちらを参考にしてサンプルをご用意ください。
スタッフごとの直近3カ月の売り上げの合計を表示する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 | 2月 | 3月 | 4月 | 5月
----------+---------+----------+----------+--------
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 "2月"
,sum(case date_trunc('month',payment_date) when '2007-03-01' then amount else 0 end) as "3月"
,sum(case date_trunc('month',payment_date) when '2007-04-01' then amount else 0 end) as "4月"
,sum(case date_trunc('month',payment_date) when '2007-05-01' then amount else 0 end) as "5月"
from
payment
group by
staff_id
order by
staff_id asc;
まず9行目でGroup By句でstaff_idごとにグループ化しています。
ポイントは3-7行目のcase式です。
date_trunc()は非常に便利で、第1引数で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でも2月の値であれば全て同じ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 "2月"
1行目 sum()関数の引数に
2行目 date_trunc()関数の結果が
3行目 '2007-02-01'のときは
4行目 amountを渡す
5行目 '2007-02-01'と等しくない時は0を渡す
6行目 このカラムの値は'2月'とする
このCASE式によりGroup by句で分けられたグループごとに、③の条件が合った時だけamountが足されていくことになり、2月だけの値を縦で表示することができまます。
同じことを3、4、5月にもしています。
Grouping演算子で合計を表示
select
staff_id
,sum(case date_trunc('month',payment_date) when '2007-02-01' then amount else 0 end) as "2月"
,sum(case date_trunc('month',payment_date) when '2007-03-01' then amount else 0 end) as "3月"
,sum(case date_trunc('month',payment_date) when '2007-04-01' then amount else 0 end) as "4月"
,sum(case date_trunc('month',payment_date) when '2007-05-01' then amount else 0 end) as "5月"
from
payment
group by
rollup(staff_id)
order by
staff_id asc;
staff_id | 2月 | 3月 | 4月 | 5月
----------+---------+----------+----------+--------
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関数を使い合計を出すこともできます。
最後に
データ分析に使うSQLは楽しいですね。今後も掘り下げて記事にしてみようと思います。