Help us understand the problem. What is going on with this article?

PostgreSQLをちゃんと勉強したら捗ったシリーズ

More than 3 years have passed since last update.

はじめに

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はあかんとかきくから)そのへんはご注意ください。

increments
「エンジニアを最高に幸せにする」ために Qiita、Qiita Team、Qiita Jobs を開発・運営しています。
https://increments.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away