8章 | Notion

ウィンドウ関数（OLAP関数）

データベースを使ってリアルタイムにデータ分析を行なう処理のこと。MySQLでは使えない。

使える関数

集約関数

RANK、DENSE_RANK、ROW_NUMBERなどのウィンドウ専用関数

RANK：同順位があったら、後続の順位が飛ぶ。

SELECT shohin_mei, shohin_bunrui, hanbai_tanka,
RANK () OVER (ORDER BY hanbai_tanka) AS ranking
FROM Shohin;
   shohin_mei   | shohin_bunrui | hanbai_tanka | ranking 
----------------+---------------+--------------+---------
 ボールペン     | 事務用品      |          100 |       1
 フォーク       | キッチン用品  |          500 |       2
 穴あけパンチ   | 事務用品      |          500 |       2
 おろしがね     | キッチン用品  |          880 |       4
 Tシャツ        | 衣服          |         1000 |       5
 包丁           | キッチン用品  |         3000 |       6
 カッターシャツ | 衣服          |         4000 |       7
 圧力鍋         | キッチン用品  |         6800 |       8
(8 rows)

DENSE_RANK：同順位があっても、後続の順位は飛ばない。

SELECT shohin_mei, shohin_bunrui, hanbai_tanka,
DENSE_RANK () OVER (ORDER BY hanbai_tanka) AS ranking
FROM Shohin;
   shohin_mei   | shohin_bunrui | hanbai_tanka | ranking 
----------------+---------------+--------------+---------
 ボールペン     | 事務用品      |          100 |       1
 フォーク       | キッチン用品  |          500 |       2
 穴あけパンチ   | 事務用品      |          500 |       2
 おろしがね     | キッチン用品  |          880 |       3
 Tシャツ        | 衣服          |         1000 |       4
 包丁           | キッチン用品  |         3000 |       5
 カッターシャツ | 衣服          |         4000 |       6
 圧力鍋         | キッチン用品  |         6800 |       7
(8 rows)

ROW_NUMBER：一意な連番を付与する。同順位でも関係ない。

SELECT shohin_mei, shohin_bunrui, hanbai_tanka,
ROW_NUMBER () OVER (ORDER BY hanbai_tanka) AS ranking
FROM Shohin;
   shohin_mei   | shohin_bunrui | hanbai_tanka | ranking 
----------------+---------------+--------------+---------
 ボールペン     | 事務用品      |          100 |       1
 フォーク       | キッチン用品  |          500 |       2
 穴あけパンチ   | 事務用品      |          500 |       3
 おろしがね     | キッチン用品  |          880 |       4
 Tシャツ        | 衣服          |         1000 |       5
 包丁           | キッチン用品  |         3000 |       6
 カッターシャツ | 衣服          |         4000 |       7
 圧力鍋         | キッチン用品  |         6800 |       8
(8 rows)

実際にshohin_bunruiごとにhanbai_tankaの安い順にランキングをつけてみた。

postgres=# SELECT shohin_mei, shohin_bunrui, hanbai_tanka,
postgres-# RANK () OVER (PARTITION BY shohin_bunrui ORDER BY hanbai_tanka) AS ranking
postgres-# FROM Shohin;
   shohin_mei   | shohin_bunrui | hanbai_tanka | ranking 
----------------+---------------+--------------+---------
 フォーク       | キッチン用品  |          500 |       1
 おろしがね     | キッチン用品  |          880 |       2
 包丁           | キッチン用品  |         3000 |       3
 圧力鍋         | キッチン用品  |         6800 |       4
 ボールペン     | 事務用品      |          100 |       1
 穴あけパンチ   | 事務用品      |          500 |       2
 Tシャツ        | 衣服          |         1000 |       1
 カッターシャツ | 衣服          |         4000 |       2
(8 rows)

PARTITION BYは順位をつける範囲、ここでは商品分類を指定。（省略可） ORDER BYでどんな順序で順位をつけるかを指定。

ウィンドウ関数は原則としてSELECT句で使う。

集約関数をウィンドウ関数として使ってみる。自分よりも上のレコードを対象に足していくので、累計をすることができるようになった。

SELECT shohin_id, shohin_mei, hanbai_tanka,
SUM (hanbai_tanka) OVER (ORDER BY shohin_id) AS current_sum
FROM Shohin;
 shohin_id |   shohin_mei   | hanbai_tanka | current_sum 
-----------+----------------+--------------+-------------
 0001      | Tシャツ        |         1000 |        1000
 0002      | 穴あけパンチ   |          500 |        1500
 0003      | カッターシャツ |         4000 |        5500
 0004      | 包丁           |         3000 |        8500
 0005      | 圧力鍋         |         6800 |       15300
 0006      | フォーク       |          500 |       15800
 0007      | おろしがね     |          880 |       16680
 0008      | ボールペン     |          100 |       16780
(8 rows)

集約範囲を指定することもできる。自分のいるレコードの前2つ分の平均を求めている。PRECEDINGをFOLLOWINGに変えると反対の意味で後ろになる。

SELECT shohin_id, shohin_mei, hanbai_tanka,
AVG (hanbai_tanka) OVER (ORDER BY shohin_id ROWS 2 PRECEDING) AS current_sum
FROM Shohin;
 shohin_id |   shohin_mei   | hanbai_tanka |      current_sum      
-----------+----------------+--------------+-----------------------
 0001      | Tシャツ        |         1000 | 1000.0000000000000000
 0002      | 穴あけパンチ   |          500 |  750.0000000000000000
 0003      | カッターシャツ |         4000 | 1833.3333333333333333
 0004      | 包丁           |         3000 | 2500.0000000000000000
 0005      | 圧力鍋         |         6800 | 4600.0000000000000000
 0006      | フォーク       |          500 | 3433.3333333333333333
 0007      | おろしがね     |          880 | 2726.6666666666666667
 0008      | ボールペン     |          100 |  493.3333333333333333
(8 rows)

※ FOLLOWINGとPRECEDINGをBETWEENで結ぶと前後どちらも指定できる。