ウィンドウ関数に触ってみる

下準備

http://sqlfiddle.com/ の Postgress SQL 9.6 でテスト。

CREATE TABLE books (
  id int,
  name varchar,
  author varchar,
  price int
);

INSERT INTO books
VALUES
(
  1,
  'Introduciton For Dog',
  'Pochi',
  200
);

INSERT INTO books
VALUES
(
  2,
  'High Performance For Dog',
  'Pochi',
  350
);

INSERT INTO books
VALUES
(
  3,
  'Master Of Dog',
  'Pochi',
  1000
);

INSERT INTO books
VALUES
(
  4,
  'Introduciton For Cat',
  'Tama',
  500
);

INSERT INTO books
VALUES
(
  5,
  'High Performance For Cat',
  'Tama',
  700
);

まずは集約関数

SELECT author, COUNT(*) 
FROM books 
GROUP BY(author);

// 結果
author  count
Tama    2
Pochi   3

はじめてのウィンドウ関数

// 関数をかける列を指定するイメージ(関数で使う列の値で行を並び替えて...みたいなイメージ)はGROUP BYに似ているが
// WINDOW関数の基本的な構文の場合、SELECT句にOVER(PARTITION BY 列) と書く
SELECT author, COUNT(*) OVER(PARTITION BY author) 
FROM books;

author   count
Pochi   3 // countで出ている数はGROUP BYの場合と同じだが、
Pochi   3 // ウィンドウ関数の場合は集約処理がなされていないので
Pochi   3 // 行がたくさんでてくる
Tama    2
Tama    2

ウィンドウ関数は

• 集約関数と同じく特定の列でグループ化された行の中で処理をするけど
• 集約のように1行にまとめるのではなく、1行ごとに結果が欲しい

という場合に使うと便利で、代表的なものとしてRANKがある。ウィンドウ関数が使用可能なのはSELECT句とORDER BY句。

SELECT author, name, RANK() OVER(ORDER BY price DESC) as rank 
FROM books;

author  name                        rank // ウィンドウ関数は処理を行う列以外もSELECTできる
Pochi   Master Of Dog               1    // 集約する際に集約に使わない行を消すが、その際、集約に使った列以外はどこを残すか、
Tama    High Performance For Cat    2    // といった問題が発生しないので表示できる状態にしてても困らないとか？
Tama    Introduciton For Cat        3
Pochi   High Performance For Dog    4
Pochi   Introduciton For Dog        5

一つのSQLの中に複数のウィンドウ関数を書くことができ、それぞれのウィンドウ関数に異なるOVER句を記述することが可能。

複数のウィンドウ関数で同じOVER句を使う場合は OVER(ORDER BY price DESC) の ORDER BY price DESC の部分にWindow句で名前をつけることができて、OVER句内で参照することができる。

SELECT author, name, RANK() OVER price_order as rank 
FROM books
WINDOW price_order AS (ORDER BY price DESC);

参考: https://www.postgresql.jp/document/9.4/html/tutorial-window.html