SQLite のウィンドウ関数の使い方についてまとめます。
ウィンドウ関数の機能
SUM や MAX などの集約関数の後ろに OVER 句をつけることで、列の値全体を使った計算をすることができます。集約を行うグループごとに 1 行にまとめられるのではなく、各行のデータを残したまま計算結果を付与できるのが GROUP BY 句を使う場合との大きな相違点です。
使い方
ウィンドウ関数は以下のような構文で使うことができます。
aggr(column_1) OVER(PARTITION BY column_2 ORDER BY column_3)
ここで、aggr は SUM や MAX などの周約関数です。
PARTITION BY 句
column_2 の値ごとに分類した後に集約することができます。ここは GROUP BY column_2 で集約するのと同様です。
PARTITION BY 句を省略することもでき、その場合は全体が 1 つのグループとなります。
ORDER BY 句
column_3 をキーとしてソートした後に、先頭行から該当する行までだけを集約することができます。この ORDER BY 句の後に ROWS BETWEEN x PRECEDING AND y FOLLOWING と続けると、該当する行の 行前から
行目後まで (inclusive) だけを集約することもできます。より詳細な行の指定方法については、以下の記事が参考になります。
分析関数（ウインドウ関数）をわかりやすく説明してみた #MySQL - Qiita
ORDER BY 句を省略することもでき、その場合は PARTITION BY 句で分割されたグループごとに全体を集約します。
使用例
以降では、このウィンドウ関数の具体的な使用例について見ていきます。
順位付け
以下の表 TITLE_RANKING において、TITLE の降順に順位を付与することを考えます。
NAME STATE TITLE -------- ------ ----- fujii active 19 habu active 99 nakahara retire 64 ooyama retire 80 tanigawa active 27 watanabe active 31 yonenaga retire 19
集約範囲を全体にして RANK 関数を適用すればよいです。
SELECT *, RANK() OVER( ORDER BY TITLE DESC ) AS RANK FROM TITLE_RANKING ORDER BY STATE ASC, RANK ASC;
NAME STATE TITLE RANK -------- ------ ----- ---- habu active 99 1 ooyama retire 80 2 nakahara retire 64 3 watanabe active 31 4 tanigawa active 27 5 yonenaga retire 19 6 fujii active 19 6
STATE で分類して順位をつけることもできます。
SELECT *, RANK() OVER( PARTITION BY STATE ORDER BY TITLE DESC ) AS RANK FROM TITLE_RANKING ORDER BY STATE ASC, RANK ASC;
NAME STATE TITLE RANK -------- ------ ----- ---- habu active 99 1 watanabe active 31 2 tanigawa active 27 3 fujii active 19 4 ooyama retire 80 1 nakahara retire 64 2 yonenaga retire 19 3
累積和
以下の表 POST_RECORD において、POST の累積和を取ることを考えます。
YEAR MONTH POST ---- ----- ---- 2022 7 1 2023 8 3 2023 9 3 2023 10 1 2023 11 2 2023 12 2
時系列でソートし、先頭から該当行までを集約して SUM 関数を適用することで、以前の POST の総和を計算することができます。
SELECT *, SUM(POST) OVER( ORDER BY YEAR ASC MONTH ASC ) AS POST_SUM FROM POST_RECORD ORDER BY YEAR ASC, POST_SUM ASC;
YEAR MONTH POST POST_SUM ---- ----- ---- -------- 2022 7 1 1 2023 8 3 4 2023 9 3 7 2023 10 1 8 2023 11 2 10 2023 12 2 12
差分計算
以下の表 R_TRANSITION において、前回からの R の変動値を計算します。
DATE R ---------- ---- 2023-06-18 2796 2023-07-02 2802 2023-07-31 2816 2023-08-14 2853 2023-12-18 2833
ウィンドウを DATE でソートしたときの 1 行前のみに設定して MAX 関数 (MIN や SUM でも良い) を適用することで、直前の値を取得することができます。
SELECT
*,
R - MAX(R) OVER(
ORDER BY DATE
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS DELTA
FROM
R_TRANSITION
ORDER BY
DATE;
DATE R DELTA ---------- ---- ----- 2023-06-18 2796 2023-07-02 2802 6 2023-07-31 2816 14 2023-08-14 2853 37 2023-12-18 2833 -20
まとめ
関数型言語っぽさがありつつも表現力が高いので面白いですね。
