19
11

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

Ateam Brides Inc.Advent Calendar 2017

Day 22

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

Last updated at Posted at 2017-12-25

はじめに

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

19
11
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
19
11

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?