【SQL】データマート作成でも大活躍！WINDOW関数
みなさん、こんにちは！
今回の記事は、note運用チームの今田が担当します。
日ごろはGoogle BigQueryでデータ抽出や、Tableauを活用したデータ分析・ビジュアライズ作成などをしています。
UAからGA4移行にあたってデータマートを作成するクエリの修正をする機会も増えました。
特定のイベントごとの発生した順番、遷移元のページURL、最初・最後の値をデータとして持ちたいなどなど、
WINDOW関数を使用することが多かったので、いくつ使用したものをまとめておきたいと思います。
1．関数の概要
■RANK関数
RANK関数は指定した（列の）値に順位をつけて返します。
RANK関数では同じ順位のレコードが複数ある場合は、その次にくる順位が同数分加算され、結果として順位が飛ぶことになります。
※使用例
RANK() OVER (PARTITION BY カラム名 ORDER BY カラム名 desc)・PARTITION BYで指定したカラムごとの順位を返します。
・指定するカラムがない場合は、partition byを省略可能です。
・ORDER BYを記述し、順位を比較するカラムを指定します。order byは必須です。
・降順にする場合は、descを記載します。
■DENSE_RANK関数
DENSE_RANK関数はRANK関数と似ていますが、同じ順位のレコードがある場合に、その次のレコードの順位がRANK関数と異なります。
RANK関数では同じ順位のレコードが複数ある場合は、次のレコードの順位が飛びますが、DENSE_RANK関数は順位の番号が飛びません。
※使用例
DENSE_RANK() OVER (PARTITION BY カラム名 ORDER BY カラム名 desc)・基本的な構文はRANK関数と一緒です。
■ROW_NUMBER関数
ROW_NUMBER関数もRANK関数に似ていますが、ROW_NUMBER関数は厳密にいうと順位を付ける関数ではなく、連番を振る関数になります。
RANK関数、DENSE_RANK関数では順位を比較する値が同一の場合は同じ順位が付けられましたが、ROW_NUMBER関数では値が同じ場合でも違う順位（連番）が付けられます。
※使用例
ROW_NUMBER() OVER (PARTITION BY カラム名 ORDER BY カラム名 desc)・ROW_NUMBER関数は、ORDER BYも省略が可能ですが連番の振られ方が変わる可能性があります。
■LAG関数、LEAD関数
指定した行数分の前（LAG）後ろ（LEAD）の値を取得できます。
※使用例
LAG(カラム名, 指定行数) OVER (PARTITION BY カラム名 ORDER BY カラム名)
LEAD(カラム名, 指定行数) OVER (PARTITION BY カラム名 ORDER BY カラム名)■FIRST_VALUE関数、LAST_VALUE関数
指定したカラムごとの最初、最後の値を取得します。
※使用例
FIRST_VALUE(カラム名) OVER (PARTITION BY カラム名 ORDER BY カラム名)
LAST_VALUE(カラム名) OVER (PARTITION BY カラム名 ORDER BY カラム名 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)・OVER()内でWindow Frame句を指定しない場合は、デフォルトで先頭行から現在の行までが処理されます。データセット全体での最後の値を取得したい場合、LAST_VALUE関数にはROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGを指定します。
2．サンプルデータで挙動を確認する
まずは、サンプルのデータを作成します。
CREATE TABLE TEST_TABLE (
`name` VARCHAR(3),
`score` INTEGER
);
INSERT INTO TEST_TABLE
(`name`, `score`)
VALUES
('AAA', 1),
('AAA', 2),
('AAA', 3),
('BBB', 2),
('CCC', 3),
('DDD', 4),
('EEE', 4),
('FFF', 5),
('GGG', 6),
('HHH', 6),
('III', 7);サンプルデータをもとに各関数がどのような値を取得するか確認したいと思います。
SELECT
name
,score
,RANK() OVER(ORDER BY score) RANK_F
,DENSE_RANK() OVER (ORDER BY score) DENSE_RANK_F
,ROW_NUMBER() OVER (ORDER BY score) ROW_NUMBER_F
,LAG(score, 2) OVER (PARTITION BY name ORDER BY score) LAG_F
,LEAD(score, 2) OVER (PARTITION BY name ORDER BY score) LEAD_F
,FIRST_VALUE(score) OVER (PARTITION BY name ORDER BY score) FIRST_VALUE_F
,LAST_VALUE(score) OVER (PARTITION BY name ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LAST_VALUE_F
FROM
TEST_TABLE実行結果は、以下のようになります。各関数の違いが確認できます。
---
| name | score | RANK_F | DENSE_RANK_F | ROW_NUMBER_F | LAG_F | LEAD_F | FIRST_VALUE_F | LAST_VALUE_F |
| ---- | ----- | ------ | ------------ | ------------ | ----- | ------ | ------------- | ------------ |
| AAA | 1 | 1 | 1 | 1 | | 3 | 1 | 3 |
| AAA | 2 | 2 | 2 | 2 | | | 1 | 3 |
| AAA | 3 | 4 | 3 | 4 | 1 | | 1 | 3 |
| BBB | 2 | 2 | 2 | 3 | | | 2 | 2 |
| CCC | 3 | 4 | 3 | 5 | | | 3 | 3 |
| DDD | 4 | 6 | 4 | 6 | | | 4 | 4 |
| EEE | 4 | 6 | 4 | 7 | | | 4 | 4 |
| FFF | 5 | 8 | 5 | 8 | | | 5 | 5 |
| GGG | 6 | 9 | 6 | 9 | | | 6 | 6 |
| HHH | 6 | 9 | 6 | 10 | | | 6 | 6 |
| III | 7 | 11 | 7 | 11 | | | 7 | 7 |
---
今回は、WINDOW関数について紹介しました。
今後も日ごろの業務で得た便利な方法を、こちらのnoteで発信していきたいと思います！
最後までご覧いただきありがとうございました。
ではまた。
●●●
SMKT事業部では、データアナリスト・データエンジニアを募集しています。
▽その他募集職種こちらから▽
●●●
✉ サービスに関するお問い合わせ ✉
パーソルプロセス＆テクノロジー SMKT事業部
smkt_markegr_note@persol-pt.co.jp