はじめに
PostgreSQLをメインに使うようになってからなんか書ける方法でしか書けないのでちゃんと公式のドキュメントを読みました。
日本語のドキュメントが整っているのはありがたいことです。
小並な感想を述べると大変勉強になりました。
- 書く量が減った
- 他でゴニョゴニョせずSQLだけで完結するようになった
- なんか楽しくなった
その中でよく使うシリーズを下記に記します。
日付ごとに集計したい時
SELECT generate_series::date FROM generate_series(
'2017-08-21'::date,
'2017-10-09'::date, '1 day')
これを普通に日付ごとにgroup by するやnつとleft joinすれば
無いレコードも含めた集計がしやすい。
いろんな条件の件数とかみる
select count(id) ,
count(id) filter (where 条件1) -- 条件1に当てはまる物の数,
count(id) filter (where 条件2) -- 条件2に当てはまる物の数,
...
時間帯ごととかの集計
タイムスタンプから時間だけとか、日付だけとか抽出する
extract(hour from created_at) as hour
JOINしなくても良い時
マスターデータとかをJOINしていたところは存在しないkeyとかが無いので下記の書き方のほうがシンプル
SELECT *
FROM data d, mst_table m
WHERE d.mst_id = m.id;
平均値とか出す時
PARTITION BY
で指定したColumnでグルーピングした時の値の平均値を出せる。
もちろん他にも合計とか別の集約関数をあててあげれば別の集計ができる。
SELECT date, value,
avg(value) OVER (PARTITION BY date)
FROM empsalary;
グループセット
例えばこんなテーブルがあったとします。
SELECT * FROM items_sold;
brand | size | sales |
---|---|---|
Foo | L | 10 |
Foo | M | 20 |
Bar | M | 15 |
Bar | L | 5 |
カラムごとの集計結果をパット見たい
- 日付ごとのグルーピング、時間帯ごとのグルーピングの結果みたいな〜とか
- 性別、年齢それぞれのグルーピングの結果みたいな~とか
SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
-- ()のなかみなしで全てをグルーピング
brand | size | sum |
---|---|---|
Foo | 30 | |
Bar | 20 | |
L | 15 | |
M | 35 | |
50 |
人数ごと、期間ごとの集計を一括でできます。
それぞれのサブセットをつくって集計
性別、年齢ごと、あとは男性かつ20代とか細かくもみたいな〜のようなとき
CUBE
を使うとサブセットを展開してgrouping sets
を用意してくれます
SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS (CUBE(brand, size));
-- 等価
SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand, size),(brand), (size), ());
brand | size | sum |
---|---|---|
Foo | 30 | |
Foo | L | 10 |
Foo | M | 20 |
Bar | 20 | |
Bar | M | 15 |
Bar | L | 5 |
L | 15 | |
M | 35 | |
50 |
(おまけ)そこまでやらんでも〜なやつ
休・祝日抜いてくる条件
WHERE EXTRACT(DOW FROM date) IN (0, 6) -- 土日
OR EXTRACT(MONTH FROM date) * 100 + EXTRACT(DAY FROM date) IN (101, 211, 429, 503, 504, 505, 811, 1103, 1123, 1223) -- 固定の祝日
OR (EXTRACT(DOW FROM date) = 1 AND EXTRACT(DAY FROM date) > 14 AND EXTRACT(DAY FROM date) < 22 AND EXTRACT(MONTH FROM date) IN (7, 9)) -- 海の日、敬老の日
OR (EXTRACT(DOW FROM date) = 1 AND EXTRACT(DAY FROM date) > 7 AND EXTRACT(DAY FROM date) < 15 AND EXTRACT(MONTH FROM date) IN (1, 10)) -- 成人の日、体育の日
OR (EXTRACT(MONTH FROM date) = 3 AND EXTRACT(DAY FROM date) = FLOOR(20.8431 + 0.242194 * (EXTRACT(YEAR FROM date) - 1980)) - FLOOR((EXTRACT(YEAR FROM date) - 1980) / 4)) -- 春分
OR (EXTRACT(MONTH FROM date) = 9 AND EXTRACT(DAY FROM date) = FLOOR(23.2488 + 0.242194 * (EXTRACT(YEAR FROM date) - 1980)) - FLOOR((EXTRACT(YEAR FROM date) - 1980) / 4)) -- 秋分
おわりに
SQLは楽しい。
これらはパフォーマンスを意識していないため(filterはあかんとかきくから)そのへんはご注意ください。