0.はじめに

対象者

普段SQLでSELECT句を使っている人
開発環境はPostgreSQL9.4以上

前提知識

SELECT句
GROUP BY句
サブクエリ

伝えたいこと

SQLのWindows関数を使うと、SELECT句の幅が広がる。そしてサブクエリより高速になる場合がある。

参考図書

SQL実践入門

特に指定がない限り、この本を引用しています。

サンプルコード

目次

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テーブルに１回アクセスしている。
「サブクエリ」より「ウィンドウ関数」、「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で規定されている。
ウィンドウ関数の名前の由来は何？