LoginSignup
3
5

More than 5 years have passed since last update.

@yuji38kwmtin有限会社来栖川電算

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

Last updated at Posted at 2017-06-28
1 / 23

0.はじめに

対象者

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

前提知識

  • SELECT句
  • GROUP BY句
  • サブクエリ

伝えたいこと

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

参考図書

SQL実践入門

SQL実践入門.jpg

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

サンプルコード

目次

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

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

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
Sign upLogin
3
5