0.はじめに
対象者
- 普段SQLでSELECT句を使っている人
- 開発環境はPostgreSQL9.4以上
前提知識
- SELECT句
- GROUP BY句
- サブクエリ
伝えたいこと
- SQLのWindows関数を使うと、SELECT句の幅が広がる。そしてサブクエリより高速になる場合がある。
参考図書
特に指定がない限り、この本を引用しています。
目次
- SQL Window関数の概要
- サブクエリをウィンドウ関数に置き換える
1. ウィンドウ関数の概要
ウィンドウ関数とは
ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。 これは集約関数により行われる計算の形式と似たようなものです。 とは言っても、通常の集約関数とは異なり、ウィンドウ関数の使用は単一出力行に行をグループ化しません。 行はそれぞれ個別の身元を維持します。
PostgreSQL ウィンドウ関数 より引用
一言で言うと「集約機能を省いたGROUP BY句」。
実際に手を動かして確認していきましょう。
ハンズオン用のテーブル作成
-- 住所テーブルの作成
CREATE TABLE Address
(name VARCHAR(32) NOT NULL,
phone_nbr VARCHAR(32) ,
address VARCHAR(32) NOT NULL,
sex CHAR(4) NOT NULL,
age INTEGER NOT NULL,
PRIMARY KEY (name));
INSERT INTO Address VALUES('小川', '080-3333-XXXX', '東京都', '男', 30);
INSERT INTO Address VALUES('前田', '090-0000-XXXX', '東京都', '女', 21);
INSERT INTO Address VALUES('森', '090-2984-XXXX', '東京都', '男', 45);
INSERT INTO Address VALUES('林', '080-3333-XXXX', '福島県', '男', 32);
INSERT INTO Address VALUES('井上', NULL, '福島県', '女', 55);
INSERT INTO Address VALUES('佐々木', '080-5848-XXXX', '千葉県', '女', 19);
INSERT INTO Address VALUES('松本', NULL, '千葉県', '女', 20);
INSERT INTO Address VALUES('佐藤', '090-1922-XXXX', '三重県', '女', 25);
INSERT INTO Address VALUES('鈴木', '090-0001-XXXX', '和歌山県', '男', 32);
「SQL 実践入門」図2.1参照。
GROUP BY句とPARTION BY句の違い
-- GROUP BYで住所別人数を調べる
SELECT address, COUNT(*)
FROM Address
GROUP BY address
-- ウィンドウ関数で住所別人数を調べる
SELECT address,
COUNT(*) OVER (PARTITION BY address)
FROM Address;
「SQL 実践入門」リスト2.26, 2.27引用。
ウィンドウ関数の構文の説明
- OVER句:
COUNT()
は「ウィンドウ関数だよ」という印。 - PARTITION BY句: カット対象の項目を指定
- COUNT, SUM, MIN, MAXなどの集約関数を、ウィンドウ関数として利用できる
GROUP BY句とPARTIN BY句のまとめ
- GROUP BY句は住所ごとにカットして、集約している(行数が少なくなる)
- PARTIN BY句は、住所ごとにカットするだけで、集約していない(行数は変わらない)
ウィンドウ関数専用の関数
-- ウィンドウ関数でランキング
SELECT name,
age,
RANK() OVER(ORDER BY age DESC) AS rnk
FROM Address;
-- ウィンドウ関数でランキング(抜け番なし)
SELECT name,
age,
DENSE_RANK() OVER(ORDER BY age DESC) AS dense_rnk
FROM Address;
-- ウィンドウ関数で連番
SELECT name,
age,
ROW_NUMBER() OVER(ORDER BY age DESC) AS dense_rnk
FROM Address;
「SQL 実践入門」リスト2.28, 2.29引用。
-
RANK()
:同値があった場合は同位 -
DENSE_RANK()
:抜け番を作らないRANK関数 -
ROW_NUMBER()
:行番号
PostgreSQL ウィンドウ関数
ROW_NUMBER,RANK,DENSE_RANKの違い
2.サブクエリをウィンドウ関数に置き換える
ハンズオン用のテーブルを作成
-- 購入明細テーブルを作成
CREATE TABLE Receipts
(cust_id CHAR(1) NOT NULL, -- 顧客ID
seq INTEGER NOT NULL, -- 連番
price INTEGER NOT NULL, -- 購入額
PRIMARY KEY (cust_id, seq));
INSERT INTO Receipts VALUES ('A', 1 ,500 );
INSERT INTO Receipts VALUES ('A', 2 ,1000 );
INSERT INTO Receipts VALUES ('A', 3 ,700 );
INSERT INTO Receipts VALUES ('B', 5 ,100 );
INSERT INTO Receipts VALUES ('B', 6 ,5000 );
INSERT INTO Receipts VALUES ('B', 7 ,300 );
INSERT INTO Receipts VALUES ('B', 9 ,200 );
INSERT INTO Receipts VALUES ('B', 12 ,1000 );
INSERT INTO Receipts VALUES ('C', 10 ,600 );
INSERT INTO Receipts VALUES ('C', 20 ,100 );
INSERT INTO Receipts VALUES ('C', 45 ,200 );
INSERT INTO Receipts VALUES ('C', 70 ,50 );
INSERT INTO Receipts VALUES ('D', 3 ,2000 );
「SQL 実践入門」リスト7.1 引用
最小の連番の金額を求めるには?
購入明細テーブルから、「顧客ごとに最小の連番の金額」を求めたい。
連番の最小値が不確定なので、seq = 1
という条件が使えない。
サブクエリを使った解
顧客ごとに最小の連番を保持するサブクエリ(R2)を作成して、本体のReceipts
テーブルと結合する。
SELECT R1.cust_id, R1.seq, R1.price
FROM Receipts R1
INNER JOIN
(SELECT cust_id, MIN(seq) AS min_seq
FROM Receipts
GROUP BY cust_id) R2
ON R1.cust_id = R2.cust_id
AND R1.seq = R2.min_seq
「SQL 実践入門」リスト7.2引用。
[Try] まずは、サブクエリの中身を確認しよう!
サブクエリの問題
- 一時的な領域に確保されるため、オーバヘッドが生じる
- インデックスや制約の情報を持っていないので、最適化が受けられない
- 結合を必要とするためコストが高い、かつ実効計画変動のリスクが発生する
- Receiptsテーブルへのスキャンが2回必要になる
SQLチューニングの要諦は、1にI/O,2にI/O, 3,4がなくて5にI/Oです。
ウィンドウ関数を使った解
ROW_NUMBER
で行に連番を振り、最小値が常に1
となる項目(row_seq
)を用意した。
SELECT cust_id, seq, price
FROM (
SELECT cust_id, seq, price,
ROW_NUMBER()
OVER (PARTITION BY cust_id ORDER BY seq) AS row_seq
FROM Receipts ) AS WORK
WHERE WORK.row_seq = 1;
「SQL 実践入門」リスト7.4引用。
[Try] まずは、サブクエリ(WORK)の中身を確認しよう!
[補足] DISTINCT ON句(PostgreSQL独自)
SELECT DISTINCT ON ( expression [, ...] )は指定した式が等しいと評価した各行集合の中で、最初の行のみを保持します。
https://www.postgresql.jp/document/9.6/html/sql-select.html 引用。
PostgreSQLならば、DISTINCT ON句を用いても書ける。
-- cust_idでまとめる際、seqが最も小さい値(ORDER BY句で指定)を採用する
SELECT DISTINCT ON(cust_id) cust_id, seq, price
FROM Receipts
ORDER BY cust_id, seq
参考サイト:http://www.odin.hyork.net/write/write0228.html
EXPALIN句で実行計画を確認
PostgreSQL 9.6.2で確認
- サブクエリ
Nested Loop (cost=37.90..126.25 rows=9 width=16)
-> HashAggregate (cost=37.75..39.75 rows=200 width=12)
Group Key: receipts.cust_id
-> Seq Scan on receipts (cost=0.00..28.50 rows=1850 width=12)
-> Index Scan using receipts_pkey on receipts r1 (cost=0.15..0.41 rows=1 width=16)
Index Cond: ((cust_id = receipts.cust_id) AND (seq = (min(receipts.seq))))
- ウィンドウ関数
Subquery Scan on work (cost=0.15..131.40 rows=9 width=16)
Filter: (work.row_seq = 1)
-> WindowAgg (cost=0.15..108.28 rows=1850 width=24)
-> Index Scan using receipts_pkey on receipts (cost=0.15..75.90 rows=1850 width=16)
- DISTINCT ON句
Unique (cost=0.15..80.53 rows=200 width=16)
-> Index Scan using receipts_pkey on receipts (cost=0.15..75.90 rows=1850 width=16)
まとめ
- 「サブクエリ」は、Receiptsテーブルに2回アクセスしている。
- 「ウィンドウ関数」、「DISTINCT ON句」は、Receiptsテーブルに1回アクセスしている。
- 「サブクエリ」より「ウィンドウ関数」、「DISTINCT ON句」の方が、「cost」が低い。
付録
比率を算出するのに、ウィンドウ関数は便利
「顧客ごとの各明細の比率」は、下記のSELECT文で算出できる。
SELECT
cust_id, seq, price
, CASE WHEN
price = 0 THEN 0 /* ゼロ除算防止対応 */
ELSE
ROUND(price::NUMERIC / SUM(price) OVER (PARTITION BY cust_id),2) -- 少数2桁表示
END AS ratio
FROM Receipts
参考サイト:http://qiita.com/yuji38kwmt/items/da19261a2dbcff141e87
ウィンドウ関数の補足
- ウィンドウ関数は「SQL:2003」以降の標準SQLで規定されている。
- ウィンドウ関数の名前の由来は何?