実行できるSQLiteドキュメント: ウィンドウ関数

Q: SQLite のウィンドウ関数とは何ですか？

ウィンドウ関数は、現在の行に関連する行の集合に対して計算を行う関数です。GROUP BY のように行をまとめてしまわず、各行をそのまま残したまま、計算結果を別カラムとして付け足せるのが特徴です。ROW_NUMBER() や RANK()、SUM()、LAG() などに OVER (...) を付けることで、対象とする「窓（ウィンドウ）」を指定します。

Q: SQLite の RANK と DENSE_RANK は何が違うのですか？

どちらも ORDER BY に基づいて順位を付けますが、同順位の扱いが違います。RANK() は同順位があった後に順位を飛ばすので、1位が2人いると次は3位になります。一方の DENSERANK() は飛ばさず、次は2位になります。連番の順位が欲しいときは DENSERANK()、順位の差そのものに意味を持たせたいときは RANK() を使うのがおすすめです。

Q: SQLite で累計（running total）を計算するには？

SUM(column) OVER (ORDER BY ...) のようにフレーム付きで書くだけでOKです。OVER 句の中に ORDER BY を書くと、デフォルトのフレームは RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW になるので、自然に累計が得られます。グループごとに累計をリセットしたい場合は PARTITION BY を追加してください。

ウィンドウ関数は行をまとめずに列を追加する

GROUP BY は複数の行を1行に集約します。一方、SQLite のウィンドウ関数はちょっと違う動きをします。関連する行のまとまりに対して値を計算しつつ、入力行をすべてそのまま結果に残してくれる のです。つまり、1行ごとの明細と集計値を横並びで取得できるわけですね。

書き方はいつも同じパターンです。関数のあとに OVER (...) を付けるだけ。

CREATE TABLE sales (
    employee TEXT,
    region   TEXT,
    amount   INTEGER
);

INSERT INTO sales VALUES
    ('Ada',   'EU', 100),
    ('Boris', 'EU', 250),
    ('Cleo',  'EU', 175),
    ('Dan',   'US', 300),
    ('Eve',   'US', 150);

SELECT employee, region, amount,
       SUM(amount) OVER () AS total_all
FROM sales;

total_all 列には全行の合計が並びますが、すべての行に同じ値が繰り返し表示されます。元の行はそのまま残っています。SELECT SUM(amount) FROM sales と比べてみてください。返ってくる数字は同じでも、結果は1行だけ。ウィンドウ関数なら、両方のビューを同時に手に入れられます。

PARTITION BY でグループごとに集計する

OVER () を空のままにすると、テーブル全体が集計対象になります。ここに PARTITION BY を加えると、GROUP BY と同じようにグループ単位で集計できます。ただし、行をまとめてしまわないのがポイントです。

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

各行に、その地域の合計と、その合計に占める割合が同時に並びます。普通の GROUP BY だと従業員ごとの明細が消えてしまいますが、ウィンドウ関数なら明細と集計を1つのクエリで両立できる ── これがsqlite ウィンドウ関数の最大の強みです。

ランキング関数:ROW_NUMBER、RANK、DENSE_RANK の違い

ランキング系の関数は、OVER 内の ORDER BY に従って行に番号を振ります。3種類ありますが、違いは「同順位(タイ)の扱い方」だけです。

CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES
    ('Ada', 90), ('Boris', 85), ('Cleo', 90),
    ('Dan', 70), ('Eve', 85);

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS rn,
       RANK()       OVER (ORDER BY points DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dr
FROM scores;

出力結果を読み解いてみましょう。

ROW_NUMBER() は常に一意な番号を返します。同点があっても適当に順序が決まるので、行ごとに必ず異なる番号を振りたいときに使います。
RANK() は同点の行に同じ順位を付けたあと、次の番号を 飛ばします 。1位タイが2人いれば、その次は3位になります。
DENSE_RANK() も同点には同じ順位を付けますが、番号は飛ばしません。1位タイの次は2位です。

「グループごとの上位N件」を取りたいときは、ランキング関数と PARTITION BY を組み合わせて、外側のクエリで絞り込みます。WHERE 句の中ではウィンドウ関数を直接参照できないので注意してください。

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT *
FROM (
    SELECT employee, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
WHERE rn <= 2;

各地域で給与トップ2の社員を取得できます。

LAG・LEAD：前後の行を覗き見する

LAG(col) はウィンドウ内で1行前の col の値を返し、LEAD(col) は逆に次の行の値を取ってきます。どちらも「前回からどう変わったか」を調べるときに重宝します。

CREATE TABLE temps (day INTEGER, celsius INTEGER);
INSERT INTO temps VALUES (1, 18), (2, 21), (3, 19), (4, 24), (5, 22);

SELECT day, celsius,
       LAG(celsius)  OVER (ORDER BY day) AS yesterday,
       celsius - LAG(celsius) OVER (ORDER BY day) AS delta
FROM temps;

最初の行の yesterday は NULL になります。前の行が存在しないからですね。デフォルト値を指定したい場合は、LAG(celsius, 1, celsius) OVER (ORDER BY day) のように書けば、前の行がないときに当日の値を使ってくれます。

LEAD は LAG の反対方向版です。この2つを PARTITION BY と組み合わせれば、グループごとのシーケンスが取れます。たとえば、地域ごとに今月と先月の売上を比較する、といった使い方ですね。

SUM OVER で累計を計算する（ウィンドウフレーム）

OVER の中に ORDER BY を加えると、SUM、AVG、COUNT などの集約関数が累計として計算されるようになります。

CREATE TABLE deposits (day INTEGER, amount INTEGER);
INSERT INTO deposits VALUES (1, 50), (2, 30), (3, 70), (4, 20), (5, 40);

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3day
FROM deposits;

ここで押さえておきたいポイントが2つあります。

SUM(amount) OVER (ORDER BY day) は累計（ランニングトータル）になります。ORDER BY だけ書いてフレームを明示しなかった場合のデフォルトは「ウィンドウの先頭から現在行まで」です。
2つ目のカラムでは ROWS BETWEEN 2 PRECEDING AND CURRENT ROW とフレームを明示しています。これは3行ぶんのスライディングウィンドウ、つまり移動平均ですね。

フレームのイメージはこう捉えると分かりやすいです。ウィンドウ関数はどれも、現在行を基準に決まる「フレーム」の範囲で計算を行います。よく使うフレームはこのあたり。

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — 累計（暗黙のデフォルト）。
ROWS BETWEEN N PRECEDING AND CURRENT ROW — 直近N行ぶんのトレーリングウィンドウ。
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — パーティション全体。

ROWS は物理的な行数で数えます。これとは別に RANGE というのもあって、こちらは値でグループ化するので、ORDER BY の対象カラムに同値（タイ）があるときに、それらをまとめて1ステップとして扱いたい場合に便利です。

FIRST_VALUE、LAST_VALUE、NTILE

ついでに覚えておきたいウィンドウ関数をいくつか紹介します。

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT employee, region, amount,
       FIRST_VALUE(employee) OVER (PARTITION BY region ORDER BY amount DESC) AS top_earner,
       NTILE(2) OVER (ORDER BY amount DESC) AS half
FROM sales;

FIRST_VALUE と LAST_VALUE はフレーム内の最初・最後の値を返します。LAST_VALUE を使うときはフレームに注意してください。デフォルトのフレームは CURRENT ROW で終わるので、パーティション全体の最後の値が欲しい場合はたいてい ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING を指定する必要があります。
NTILE(n) は行を n 個のほぼ均等なバケットに分割します。四分位数やパーセンタイル、A/Bテスト的な分割をしたいときに便利です。

WINDOW句でウィンドウに名前を付ける

複数のカラムで同じ OVER (...) 句を使っていると、毎回書くのが面倒になってきます。そんなときは SQLite の WINDOW 句でウィンドウに名前を付けて使い回せます。

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount)   OVER region_window AS region_total,
       AVG(amount)   OVER region_window AS region_avg,
       RANK()        OVER region_window AS region_rank
FROM sales
WINDOW region_window AS (PARTITION BY region ORDER BY amount DESC);

同じクエリでも、ノイズがぐっと減ります。WINDOW句を書く位置は、WHERE/GROUP BY/HAVINGの後ろ、ORDER BYの前です。

ウィンドウ関数とGROUP BYの違い

どちらも集計に関わりますが、答えてくれる「問い」が違います。

GROUP BYは集約してまとめる。グループごとに1行だけ残るので、サマリーだけ欲しいときに使います。
ウィンドウ関数は元の行を残す。すべての入力行がそのまま残り、計算結果が列として横に追加されます。

もし「GROUP BYで集計してから、その結果を元のテーブルに再ジョインしている」場面に出くわしたら、それはウィンドウ関数1本で書き直せるサインです。

ハマりどころいくつか

WHEREではウィンドウ関数を参照できない。 フィルタはウィンドウ計算より先に走るためです。サブクエリかCTEで包んで、外側でフィルタしましょう。
暗黙のフレームに注意。 SUM(x) OVER (ORDER BY y)が累計(ランニングトータル)になるのは、デフォルトのフレームがRANGE UNBOUNDED PRECEDINGだからです。パーティション全体の合計がほしいなら、ORDER BYを付けずにOVER (PARTITION BY ...)と書くか、フレームを明示的に指定します。
LAST_VALUEは最初みんな驚く。 デフォルトのフレームは現在行で終わるので、返ってくるのはパーティションの最後の値ではなく「現在行の値」です。フレームを上書きして使いましょう。
ウィンドウ関数はSQLite 3.25以降が必要(2018年リリース)。最近のSQLiteならまず使えますが、組み込み環境では古いバージョンが残っていることもあります。

次回: 生成列(Generated Columns)

ウィンドウ関数は「クエリ実行時」の計算でした。次のページでは「保存時」の計算、つまり生成列を取り上げます。式で値が定義され、元データの変更に合わせて自動的に更新される列です。

よくある質問

SQLite のウィンドウ関数とは何ですか？

ウィンドウ関数は、現在の行に関連する行の集合に対して計算を行う関数です。GROUP BY のように行をまとめてしまわず、各行をそのまま残したまま、計算結果を別カラムとして付け足せるのが特徴です。ROW_NUMBER() や RANK()、SUM()、LAG() などに OVER (...) を付けることで、対象とする「窓（ウィンドウ）」を指定します。

SQLite の RANK と DENSE_RANK は何が違うのですか？

どちらも ORDER BY に基づいて順位を付けますが、同順位の扱いが違います。RANK() は同順位があった後に順位を飛ばすので、1位が2人いると次は3位になります。一方の DENSE_RANK() は飛ばさず、次は2位になります。連番の順位が欲しいときは DENSE_RANK()、順位の差そのものに意味を持たせたいときは RANK() を使うのがおすすめです。

SQLite で累計（running total）を計算するには？

SUM(column) OVER (ORDER BY ...) のようにフレーム付きで書くだけでOKです。OVER 句の中に ORDER BY を書くと、デフォルトのフレームは RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW になるので、自然に累計が得られます。グループごとに累計をリセットしたい場合は PARTITION BY を追加してください。

SQLite ウィンドウ関数入門：OVER と PARTITION BY