PostgreSQLのSQLの特徴
第3章では,
そこでまずは,
そのあとの節では,
PostgreSQLは標準SQLを重視する
PostgreSQLは,
結果として,
標準SQLに準拠している機能
標準SQLに準拠している機能の一覧は,
標準SQLに準拠していない機能
残念ながら,
PostgreSQLは今後も標準SQLに準拠していくための機能追加を進めていく予定です。まだサポートされていない標準SQLの一覧を眺めると,
PostgreSQL独自のSQLと機能
PostgreSQLは標準SQLを重視していますが,
UPSERT──追加と更新を同時に行う
データの挿入時に該当のキーにデータがなければINSERT,
これは,INSERT ... ON DUPLICATE KEY UPDATE
の構文でサポートしています。PostgreSQLは,INSERT ... ON CONFLICT DO UPDATE
の構文をサポートしました。
PostgreSQLのUPSERTは,
待望の機能としてリリースされたUPSERTは,
独自の関数
RDBMSで独自に提供されている機能の筆頭と言えば関数で,
Window関数──集計の味方
標準SQLにはWindow関数が定義されており,
Window関数の役割
まず,
ここでは,
図1のSQLを使って売上表を作成します。図1では,\x
を利用して,
図1 データの準備
テーブルの用意 demo=# CREATE TABLE public."売上" (id serial NOT NULL, "売上日時" timestamp NOT NULL DEFAULT now(), "商品名" character varying(64) NOT NULL, "価格" numeric NOT NULL DEFAULT 0, "ジャンル" text NOT NULL); バルクINSERTでテストデータを作る demo=# INSERT INTO "売上" ("売上日時","商品名","価格 ","ジャンル") VALUES (now()-((random()*10000)::int%365)*interval '1 day', 'WEB+DB PRESS Vol.107',1480,'紙'), (now()-((random()*10000)::int%365)*interval '1 day', 'WEB+DB PRESS Vol.106',1480,'電子'), (now()-((random()*10000)::int%365)*interval '1 day', 'WEB+DB PRESS Vol.105',1480,'紙'), (now()-((random()*10000)::int%365)*interval '1 day', 'WEB+DB PRESS Vol.104',1480,'電子'); demo=# \x Expanded display is on. データの中身の確認 demo=# SELECT * FROM "売上" WHERE "商品名" = 'WEB+DB PRESS Vol.105' LIMIT 1; -[ RECORD 1 ]------------------------------- id | 22251 売上日時 | 2018-09-08 07:42:00.067831 商品名 | WEB+DB PRESS Vol.105 価格 | 1480 ジャンル | 紙
この売上表の8月から10月の各月における,
Window関数を利用しない場合
まずは,
図2 Window関数を使わない集計
8~10月の各月の日別の売上件数の上位3件を表示
demo=# SELECT "売上日", "件数" FROM
(SELECT "売上日時"::date AS "売上日",
COUNT(*) AS "件数" FROM "売上"
WHERE "売上日時" >= '2018/10/01 00:00:00'
AND "売上日時" <= '2018/10/31 23:59:59'
GROUP BY "売上日"
ORDER BY "件数" DESC,売上日 LIMIT 3) AS oct
UNION ALL SELECT * FROM
(SELECT "売上日時"::date AS "売上日",
COUNT(*) AS "件数" FROM "売上"
WHERE "売上日時" >= '2018/09/01 00:00:00'
AND "売上日時" <= '2018/09/30 23:59:59'
GROUP BY "売上日"
ORDER BY "件数" DESC,売上日 LIMIT 3) AS Sep
UNION ALL SELECT * FROM
(SELECT "売上日時"::date AS "売上日",
COUNT(*) AS "件数" FROM "売上"
WHERE "売上日時" >= '2018/08/01 00:00:00'
AND "売上日時" <= '2018/08/31 23:59:59'
GROUP BY "売上日"
ORDER BY "件数" DESC,売上日 LIMIT 3) AS Aug;
売上日 | 件数
------------+------
2018-10-13 | 101
2018-10-01 | 99
2018-10-05 | 97
2018-09-04 | 104
2018-09-10 | 100
2018-09-26 | 99
2018-08-25 | 108
2018-08-19 | 102
2018-08-24 | 100
(9 rows)
Window関数を利用した場合
Window関数を利用すると,
Window関数を利用した例が図3です。Window関数は,OVER()
で対象を指定します。PARTITION BY句は,
図3 Window関数を使った集計
8~10月の各月の日別の売上件数の上位3件を表示
demo=# SELECT "売上日", "件数", "順位"
FROM (
SELECT *, dense_rank() OVER (PARTITION BY "売上月"
ORDER BY "件数" DESC, 売上日) AS "順位"
FROM (
SELECT
extract(month from "売上日時") AS "売上月",
"売上日時"::date AS "売上日",
COUNT(*) AS "件数"
FROM "売上"
WHERE "売上日時" >= '2018/08/01 00:00:00'
AND "売上日時" <= '2018/10/31 23:59:59'
GROUP BY "売上日", "売上月"
) AS "集計"
) AS "集計ランキング"
WHERE "順位" <= 3
ORDER BY "売上月" DESC, "順位";
売上日 | 件数 | 順位
------------+------+------
2018-10-13 | 101 | 1
2018-10-01 | 99 | 2
2018-10-05 | 97 | 3
2018-09-04 | 104 | 1
2018-09-10 | 100 | 2
2018-09-26 | 99 | 3
2018-08-25 | 108 | 1
2018-08-19 | 102 | 2
2018-08-24 | 100 | 3
(9 rows)
これが,
主なWindow関数を表1にまとめました。使いこなしてデータ分析や集計を効率化していきましょう。
表1 主なWindow関数
関数名 | 説明 |
---|---|
row_ | 行番号を出力する |
rank() | 同率の番号を飛ばしてランキングを出力する |
dense_ | 同率の番号を飛ばさずにランキングを出力する |
percent_ | ランキングを%で出力する。計算方法は(ランキング - 1) / (全行数 - 1) |
cume_ | percent_ |
ntile(N) | ランキングを1.. |
lag(value, offset, default) | ウィンドウをソートした状態での前の行の値を出力する |
lead(value, offset, default) | ウィンドウをソートした状態でのあとの行の値を出力する |
first_ | 最初の値を出力する |
last_ | 最後の値を出力する |
nth_ | 1から数えたN番目の値を出力する |