PostgreSQLのWindow関数

サンプルのテーブル

SELECT depname, empno, salary,
  rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

• row_number() OVER () を使って、連番付与のSQLクエリ例
• ソートした後に番号をふるのがポイント
• PARTITION BY が無い場合は、テーブル全体が対象となる

SELECT row_number() OVER (), *
  FROM (SELECT * FROM tbl ORDER BY sortkey) AS t;

• 上位10%を取得する場合ののSQLクエリ例
• 全体に対する割合を計算するには、結局は全体を取得する必要があることに注意

SELECT ...
FROM (SELECT *, cume_dist() OVER (ORDER BY i) AS rank FROM tbl) AS t
WHERE rank <= 0.1;

• PARTITION BY と ORDER BY は、同時に使用することも可能
• 地域ごとに身長の低い順に番号付けする場合のSQLクエリ例

SELECT id, 地域, 身長,
  rank() OVER (PARTITION BY 地域 ORDER BY 身長)
FROM tbl;

• 列 id でソートした際に1つ前の行と比較して、連番になっていない行を抜き出す場合のSQLクエリ例
• 前の行を取得する lag() 関数を使うと、ただ1回のテーブルスキャンで処理することが可能
• NOT IN や NOT EXISTS などコストの高いSQLを使う必要がなくなる

SELECT id, prev_id
FROM (
  SELECT id,
    lag(id, 1, 0) OVER (ORDER BY id) AS prev_id
  FROM tbl
) AS t
WHERE id <> prev_id + 1;

• Window演算が行われた後、行にフィルタ処理を行ったりグループ化を行う必要が生じた場合、副問い合わせを使用する
• 3 より小さい rank を持った内部問い合わせからの行のみを表示するSQLクエリ例

SELECT depname, empno, salary, enroll_date
FROM (
  SELECT depname, empno, salary, enroll_date,
    rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
  FROM empsalary
) AS ss
WHERE pos < 3;

• WINDOW 句でWindow処理動作に名前を付け、これを OVER 内で参照することが可能

SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);