SQLのウィンドウ関数とは ― OVER句とRANK

SQLのウィンドウ関数とは、行の数を減らさずに、グループ内の順位や合計を各行に付ける関数です。「部署ごとに売上の高い順で番号を振りたい」「各行の横に部署の合計も出したい」と頼まれたときに使います。

GROUP BYで部署ごとに集計すると、結果は部署ごとに1行になり、担当者の行は消えます。ウィンドウ関数なら、担当者の行を残したまま、その横に順位や合計を足せます。

書き方の中心は、関数の後ろに付けるOVER句です。順位を付ける3つの関数の違いと、部署ごとの上位N件を取り出す書き方を、実行結果を示しながら説明します。

担当者ごとの売上を入れた、7行の売上実績テーブルを例にします。このテーブルから部署ごとの合計を、2通りの書き方で求めます。1つ目がGROUP BY、2つ目がウィンドウ関数です。

SELECT 部署,
       SUM(売上) AS 部署合計
FROM 売上実績
GROUP BY 部署;

SELECT 担当者, 部署, 売上,
       SUM(売上) OVER (
         PARTITION BY 部署
       ) AS 部署合計
FROM 売上実績;

GROUP BYは行をまとめて行数を減らし、ウィンドウ関数は行数を変えません。標準SQLでは、この例のGROUP BYの結果に担当者の列を出せません。1つの部署に担当者が何人もいて、どの値を返すか決まらないためです。ウィンドウ関数なら元の行が残るので、担当者の列も一緒に出せます。

部署ごとの集計結果だけが欲しいならGROUP BYを使います。1行ずつの値と集計結果を見比べたいなら、ウィンドウ関数が向いています。

OVER句とは、関数がどの行を使って計算するかを指定する部分です。関数名のすぐ後ろに書き、OVER句を付けた関数がウィンドウ関数になります。次の式は、部署の中での売上の順位を求めます。

RANK() OVER (
  -- 部署ごとに分ける
  PARTITION BY 部署
  -- 売上の高い順に並べる
  ORDER BY 売上 DESC
) AS 順位

データベースは、この式を次の順に計算します。

1. PARTITION BYで、部署が同じ行を1つのグループに分けます。
2. ORDER BYで、グループの中を売上の高い順に並べます。
3. RANKが、並んだ順に順位を付けます。

PARTITION BYで分けた1つ1つのグループを、パーティションと呼びます。PARTITION BYとORDER BYはどちらも省略でき、書き方によって計算の対象が変わります。

OVER句の中のORDER BYが決めるのは、計算するときの並びだけです。結果の表示順は保証されないので、表示の順番を決めたいときは文の最後にもORDER BYを書きます。また、ウィンドウ関数が計算に使うのは、WHERE・GROUP BY・HAVINGを通った後の行です。WHEREで消えた行は、順位にも合計にも入りません。

順位を付ける関数では、ROW_NUMBER・RANK・DENSE_RANKの3つをよく使います。どれも1から数え、違いが出るのはORDER BYの値が同じ行があるときです。営業1課の4人に3つの関数を使うと、次の結果になります。佐藤と鈴木の売上は、どちらも300です。

• ROW_NUMBERは、同じ値の行にも別の番号を振ります。番号は1、2、3、4と続きます。
• RANKは、同じ値の行に同じ順位を付け、次の順位を飛ばします。順位は1、2、2、4です。
• DENSE_RANKは、同じ値の行に同じ順位を付け、次の順位を飛ばしません。順位は1、2、2、3です。

試合の順位表のように、同点の次は人数分飛ばしたいならRANKを使います。順位を詰めて何種類の値があるかを見たいならDENSE_RANKが合います。同じ番号を出したくないなら、ROW_NUMBERです。

ROW_NUMBERでは、同じ値の行のどちらが先になるかは決まっていません。上の表で佐藤が2番なのは、今回の実行でそうなっただけで、鈴木が2番になることもあります。番号を固定するには、ORDER BY 売上 DESC, 担当者のように列を足して、並びを1通りに決めます。

次の図でグループの分け方と関数を選ぶと、SQL文と実行結果が変わります。図は実際のデータベースには接続していません。結果は、同じSQL文をPostgreSQLで実行したものです。

SELECT 担当者, 部署, 売上,
       ROW_NUMBER() OVER (
         PARTITION BY 部署
         ORDER BY 売上 DESC
       ) AS 順位
FROM 売上実績
ORDER BY 部署, 順位;

SELECT 担当者, 部署, 売上,
       RANK() OVER (
         PARTITION BY 部署
         ORDER BY 売上 DESC
       ) AS 順位
FROM 売上実績
ORDER BY 部署, 順位;

SELECT 担当者, 部署, 売上,
       DENSE_RANK() OVER (
         PARTITION BY 部署
         ORDER BY 売上 DESC
       ) AS 順位
FROM 売上実績
ORDER BY 部署, 順位;

SELECT 担当者, 部署, 売上,
       ROW_NUMBER() OVER (
         ORDER BY 売上 DESC
       ) AS 順位
FROM 売上実績
ORDER BY 順位;

SELECT 担当者, 部署, 売上,
       RANK() OVER (
         ORDER BY 売上 DESC
       ) AS 順位
FROM 売上実績
ORDER BY 順位;

SELECT 担当者, 部署, 売上,
       DENSE_RANK() OVER (
         ORDER BY 売上 DESC
       ) AS 順位
FROM 売上実績
ORDER BY 順位;

SUM・AVG・COUNTなどの集約関数は、後ろにOVER句を付けるとウィンドウ関数として動きます。OVER句にPARTITION BYだけを書くと、グループ全体の集計結果が各行に付きます。最初に見た部署合計がこの形です。

ORDER BYを足すと、計算の範囲が変わります。グループの先頭から今の行までを集計するので、SUMなら累計になります。次の例は、月ごとの売上を入れた月別売上テーブルで累計を求めます。

SELECT 月, 売上,
       SUM(売上) OVER (
         ORDER BY 月
       ) AS 累計
FROM 月別売上
ORDER BY 月;

ORDER BYの値が同じ行は、まとめて計算の範囲に入ります。たとえば営業1課の売上の高い順の累計は、同額の佐藤と鈴木がどちらも1100になります。1行ずつ足したいときは、ORDER BYに列を足して並びを1通りに決めます。

この計算の範囲をフレームと呼びます。「直近3行」のように範囲を細かく指定するときは、ROWS BETWEENを使います。

LAGは1つ前の行の値を返し、LEADは1つ後の行の値を返します。前月との差を求める場面でよく使います。並びの基準は、OVER句のORDER BYで決めます。

SELECT 月, 売上,
  LAG(売上) OVER (ORDER BY 月)
    AS 前月,
  LEAD(売上) OVER (ORDER BY 月)
    AS 翌月,
  売上 - LAG(売上)
    OVER (ORDER BY 月)
    AS 前月との差
FROM 月別売上
ORDER BY 月;

最初の月には前の行が無いので、LAGはNULLを返します。最後の月のLEADも同じです。NULLとの計算結果はNULLなので、最初の月の前月との差もNULLになります。

部署ごとの上位2件を取り出す

部署ごとの売上の上位2件が欲しいとき、WHEREに順位の条件を書きたくなります。しかし、ウィンドウ関数はWHEREに書けません。WHEREやHAVINGの処理が終わった後で計算するからです。

書けるのはSELECT句とORDER BY句だけです。WHEREに書くと、PostgreSQLでは「window functions are not allowed in WHERE」のエラーになります。そこで、サブクエリの中で順位を計算し、外側のWHEREで絞ります。

SELECT 担当者, 部署, 売上
FROM (
  SELECT 担当者, 部署, 売上,
         RANK() OVER (
           PARTITION BY 部署
           ORDER BY 売上 DESC
         ) AS 順位
  FROM 売上実績
) AS t
WHERE 順位 <= 2
ORDER BY 部署, 順位;

RANKを使ったので、2位で並んだ佐藤と鈴木はどちらも残り、営業1課は3行になります。部署ごとにちょうど2行にしたいなら、ROW_NUMBERに替えてORDER BYに列を足します。その場合、佐藤と鈴木のどちらか1人だけが残ります。

重複した行から1行だけ残す

次の登録テーブルには、同じメールアドレスの行が3件あります。メールアドレスごとに、登録日が最も新しい1行だけを取り出します。

PARTITION BYに重複を判定する列を書き、ORDER BYに残す行の基準を書きます。ROW_NUMBERの番号が1の行は、各メールアドレスで最も新しい行です。

SELECT 登録ID, メール, 登録日
FROM (
  SELECT 登録ID, メール, 登録日,
         ROW_NUMBER() OVER (
           PARTITION BY メール
           ORDER BY 登録日 DESC
         ) AS 番号
  FROM 登録
) AS t
WHERE 番号 = 1
ORDER BY メール;

全部の列が同じ行なら、SELECT DISTINCTでも1行にできます。この例は登録IDと登録日が行ごとに違うので、DISTINCTでは1行にまとまりません。

• PostgreSQL公式ドキュメント「3.5. Window Functions」（ウィンドウ関数の考え方、書ける場所、サブクエリで絞る方法）
• PostgreSQL公式ドキュメント「9.22. Window Functions」（ROW_NUMBER・RANK・DENSE_RANK・LAG・LEADの定義）
• PostgreSQL公式ドキュメント「4.2.8. Window Function Calls」（OVER句の構文）
• MySQL 8.4 マニュアル「14.20.3 Window Function Frame Specification」（ORDER BYの有無と計算の範囲）