【社内勉強会】SQLの応用(Window関数) (2017/06/29) 


# 0.はじめに

------------------
## 対象者
* 普段SQLでSELECT句を使っている人
* 開発環境はPostgreSQL9.4以上

### 前提知識
 * SELECT句
 * GROUP BY句
 * サブクエリ


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

------------------
## 参考図書

[SQL実践入門](http://gihyo.jp/book/2015/978-4-7741-7301-6)

![SQL実践入門.jpg](https://qiita-image-store.s3.amazonaws.com/0/37903/20592c22-7242-f9c7-b80f-7a2417b50c1d.jpeg)

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

[サンプルコード](http://image.gihyo.co.jp/assets/files/book/2015/978-4-7741-7301-6/download/SQL_practical_guide_samplecode.zip)

------------------
## 目次

1. SQL Window関数の概要
2. サブクエリをウィンドウ関数に置き換える

------------------
# 1. ウィンドウ関数の概要

------------------
## ウィンドウ関数とは

>ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。 これは集約関数により行われる計算の形式と似たようなものです。 とは言っても、通常の集約関数とは異なり、ウィンドウ関数の使用は単一出力行に行をグループ化しません。 行はそれぞれ個別の身元を維持します。 

[PostgreSQL ウィンドウ関数](https://www.postgresql.jp/document/9.6/html/tutorial-window.html) より引用

一言で言うと「集約機能を省いたGROUP BY句」。

実際に手を動かして確認していきましょう。



-------------------
## ハンズオン用のテーブル作成


```sql
-- 住所テーブルの作成
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句の違い

```sql
-- 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句は、住所ごとにカットするだけで、集約していない（行数は変わらない）



-------------------
## ウィンドウ関数専用の関数

```sql
-- ウィンドウ関数でランキング
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 ウィンドウ関数](https://www.postgresql.jp/document/9.6/html/functions-window.html)
[ROW_NUMBER,RANK,DENSE_RANKの違い](http://itref.fc2web.com/oracle/function/row_number.html)


------------------
# 2.サブクエリをウィンドウ関数に置き換える

------------------
## ハンズオン用のテーブルを作成


```sql
-- 購入明細テーブルを作成
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`テーブルと結合する。

```sql
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`)を用意した。

```sql
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句を用いても書ける。

```sql
-- 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文で算出できる。

```sql
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で規定されている。
* ウィンドウ関数の名前の由来は何？