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
まとめ
関数型言語っぽさがありつつも表現力が高いので面白いですね。