はじめに

最近データベーススペシャリスト試験の勉強をしていますが、たまにWindow関数の問題が出てきます。

データ分析の需要はますます増えており、出題確率も増えている、ということらしいので、動作確認ながらWindow関数を覚えたいな。と思いました。

そこで簡単にセットアップでき、サーバレスでコストメリットもあるAurora DSQLで動作確認してみよう！って記事です。

DSQLのセットアップ

初めて作成してみましたが本当に簡単です！

シングルリージョンまたは、マルチリージョンを選択し、

クラスター名を指定するだけです。

今回はシングルリージョンで作成しましたが、マルチリージョンの場合は、ウィットネスリージョンというデータ受信のみ行うリージョンの指定が必要です。

DDL、投入データ

今回は以下のテーブルやデータを使って動作確認してみます。

DDLおよび投入データ

-- Window関数練習用のサンプルテーブル作成

-- 1. 売上テーブル
CREATE TABLE sales (
    id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department VARCHAR(30),
    sale_date DATE,
    amount DECIMAL(10,2)
);

-- 2. サンプルデータ挿入
INSERT INTO sales VALUES
(1, '田中太郎', '営業部', '2024-01-15', 150000),
(2, '佐藤花子', '営業部', '2024-01-16', 200000),
(3, '鈴木次郎', 'マーケティング部', '2024-01-17', 180000),
(4, '田中太郎', '営業部', '2024-01-20', 120000),
(5, '高橋三郎', 'マーケティング部', '2024-01-22', 250000),
(6, '佐藤花子', '営業部', '2024-01-25', 300000),
(7, '鈴木次郎', 'マーケティング部', '2024-02-01', 160000),
(8, '田中太郎', '営業部', '2024-02-03', 180000),
(9, '山田四郎', 'IT部', '2024-02-05', 220000),
(10, '高橋三郎', 'マーケティング部', '2024-02-08', 190000),
(11, '佐藤花子', '営業部', '2024-02-10', 280000),
(12, '山田四郎', 'IT部', '2024-02-12', 240000),
(13, '田中太郎', '営業部', '2024-02-15', 160000),
(14, '鈴木次郎', 'マーケティング部', '2024-02-18', 210000),
(15, '山田四郎', 'IT部', '2024-02-20', 200000);

接続も、CloudShell上で簡単にできました。

では、Window関数をいろいろ試してみます！！

ランキング関数

ROW_NUMBER()

amountの降順で各行に一意の連番を付与することができます。

項番とかに使えそうですね。

SELECT 
    employee_name,
    amount,
+    ROW_NUMBER() OVER (ORDER BY amount DESC) as row_num
FROM sales
ORDER BY row_num;

employee_name	amount	row_num
佐藤花子	300000.00	1
佐藤花子	280000.00	2
高橋三郎	250000.00	3
山田四郎	240000.00	4
山田四郎	220000.00	5
鈴木次郎	210000.00	6
山田四郎	200000.00	7
佐藤花子	200000.00	8
高橋三郎	190000.00	9
田中太郎	180000.00	10
鈴木次郎	180000.00	11
田中太郎	160000.00	12
鈴木次郎	160000.00	13
田中太郎	150000.00	14
田中太郎	120000.00	15

RANK()

同様に金額の降順で順位をつけていますが、同順位がある場合、次の順位を飛ばすように採番されます。

20000.00が２つあるため、7が２つ並び、8が欠番になってますね。

SELECT 
    employee_name,
    amount,
+    RANK() OVER (ORDER BY amount DESC) as rank_num
FROM sales
ORDER BY rank_num;

employee_name	amount	rank_num
佐藤花子	300000.00	1
佐藤花子	280000.00	2
高橋三郎	250000.00	3
山田四郎	240000.00	4
山田四郎	220000.00	5
鈴木次郎	210000.00	6
山田四郎	200000.00	7
佐藤花子	200000.00	7
高橋三郎	190000.00	9
田中太郎	180000.00	10
鈴木次郎	180000.00	10
田中太郎	160000.00	12
鈴木次郎	160000.00	12
田中太郎	150000.00	14
田中太郎	120000.00	15

DENSE_RANK()

欠番にならないパターン。7が２つ続いた後、8になっています。

SELECT 
    employee_name,
    amount,
+    DENSE_RANK() OVER (ORDER BY amount DESC) as dense_rank_num
FROM sales
ORDER BY dense_rank_num;

employee_name	amount	dense_rank_num
佐藤花子	300000.00	1
佐藤花子	280000.00	2
高橋三郎	250000.00	3
山田四郎	240000.00	4
山田四郎	220000.00	5
鈴木次郎	210000.00	6
山田四郎	200000.00	7
佐藤花子	200000.00	7
高橋三郎	190000.00	8
田中太郎	180000.00	9
鈴木次郎	180000.00	9
田中太郎	160000.00	10
鈴木次郎	160000.00	10
田中太郎	150000.00	11
田中太郎	120000.00	12

NTILE(n)

amountの降順でn個のグループに分け、連番を付与します。

バッチで並列処理するときのグループ分けとかに使えそうです！

SELECT 
    employee_name,
    amount,
+    NTILE(4) OVER (ORDER BY amount DESC) as group_num
FROM sales
ORDER BY group_num;

今回の場合は4つのグループに分けています。
今回は15行に対して、4つのグループなので、４つ目のグループのみ3行ですね。

employee_name	amount	group_num
佐藤花子	300000.00	1
佐藤花子	280000.00	1
高橋三郎	250000.00	1
山田四郎	240000.00	1
山田四郎	220000.00	2
鈴木次郎	210000.00	2
山田四郎	200000.00	2
佐藤花子	200000.00	2
高橋三郎	190000.00	3
田中太郎	180000.00	3
鈴木次郎	180000.00	3
田中太郎	160000.00	3
鈴木次郎	160000.00	4
田中太郎	150000.00	4
田中太郎	120000.00	4

集計関数（Window版）

Group Byで使う集計ではなく、Window関数版の集計関数です。

SUM() OVER()

sale_dateの昇順で並べ、現在の行までの累積合計を求めることができます。

ROWS UNBOUNDED PRECEDINGが行ベースで最初の行から現在の行まで。という意味ですが、ROWS 2 PRECEDINGのような指定をすると、過去3行分（現在行含む）みたいな集計もできます。

こういう積み上げ系はプログラムで頑張ってたなぁ。

SELECT 
    employee_name,
    sale_date,
    amount,
+    SUM(amount) OVER (ORDER BY sale_dates ROWS UNBOUNDED PRECEDING) as cumulative_sum
FROM sales
ORDER BY sale_date;

employee_name	sale_date	amount	cumulative_sum
田中太郎	2024-01-15	150000.00	150000.00
佐藤花子	2024-01-16	200000.00	350000.00
鈴木次郎	2024-01-17	180000.00	530000.00
田中太郎	2024-01-20	120000.00	650000.00
高橋三郎	2024-01-22	250000.00	900000.00
佐藤花子	2024-01-25	300000.00	1200000.00
鈴木次郎	2024-02-01	160000.00	1360000.00
田中太郎	2024-02-03	180000.00	1540000.00
山田四郎	2024-02-05	220000.00	1760000.00
高橋三郎	2024-02-08	190000.00	1950000.00
佐藤花子	2024-02-10	280000.00	2230000.00
山田四郎	2024-02-12	240000.00	2470000.00
田中太郎	2024-02-15	160000.00	2630000.00
鈴木次郎	2024-02-18	210000.00	2840000.00
山田四郎	2024-02-20	200000.00	3040000.00

COUNT() OVER()

合計ではなく、件数。

SELECT 
    employee_name,
    sale_date,
    amount,
+    COUNT(*) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING) as cumulative_count
FROM sales
ORDER BY sale_date;

employee_name	sale_date	amount	cumulative_count
田中太郎	2024-01-15	150000.00	1
佐藤花子	2024-01-16	200000.00	2
鈴木次郎	2024-01-17	180000.00	3
田中太郎	2024-01-20	120000.00	4
高橋三郎	2024-01-22	250000.00	5
佐藤花子	2024-01-25	300000.00	6
鈴木次郎	2024-02-01	160000.00	7
田中太郎	2024-02-03	180000.00	8
山田四郎	2024-02-05	220000.00	9
高橋三郎	2024-02-08	190000.00	10
佐藤花子	2024-02-10	280000.00	11
山田四郎	2024-02-12	240000.00	12
田中太郎	2024-02-15	160000.00	13
鈴木次郎	2024-02-18	210000.00	14
山田四郎	2024-02-20	200000.00	15

AVG() OVER()

同じく、平均。
この例はROWS 2 PRECEDINGを指定しているので、前の２行と現在行の平均となります。

SELECT 
    employee_name,
    sale_date,
    amount,
    AVG(amount) OVER (ORDER BY sale_date ROWS 2 PRECEDING) as cumulative_avg
FROM sales
ORDER BY sale_date;

高橋三郎を例にすると２つ前の田中太郎からの平均となり、(180000+220000+190000)/3で、196666.666666666667です。

employee_name	sale_date	amount	cumulative_avg
田中太郎	2024-01-15	150000.00	150000.000000000000
佐藤花子	2024-01-16	200000.00	175000.000000000000
鈴木次郎	2024-01-17	180000.00	176666.666666666667
田中太郎	2024-01-20	120000.00	166666.666666666667
高橋三郎	2024-01-22	250000.00	183333.333333333333
佐藤花子	2024-01-25	300000.00	223333.333333333333
鈴木次郎	2024-02-01	160000.00	236666.666666666667
田中太郎	2024-02-03	180000.00	213333.333333333333
山田四郎	2024-02-05	220000.00	186666.666666666667
高橋三郎	2024-02-08	190000.00	196666.666666666667
佐藤花子	2024-02-10	280000.00	230000.000000000000
山田四郎	2024-02-12	240000.00	236666.666666666667
田中太郎	2024-02-15	160000.00	226666.666666666667
鈴木次郎	2024-02-18	210000.00	203333.333333333333
山田四郎	2024-02-20	200000.00	190000.000000000000

MAX() OVER()

最初から現在行を含む中での最大値です。

SELECT 
    employee_name,
    sale_date,
    amount,
+    MAX(amount) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING) as cumulative_max
FROM sales
ORDER BY sale_date;

employee_name	sale_date	amount	cumulative_max
田中太郎	2024-01-15	150000.00	150000.00
佐藤花子	2024-01-16	200000.00	200000.00
鈴木次郎	2024-01-17	180000.00	200000.00
田中太郎	2024-01-20	120000.00	200000.00
高橋三郎	2024-01-22	250000.00	250000.00
佐藤花子	2024-01-25	300000.00	300000.00
鈴木次郎	2024-02-01	160000.00	300000.00
田中太郎	2024-02-03	180000.00	300000.00
山田四郎	2024-02-05	220000.00	300000.00
高橋三郎	2024-02-08	190000.00	300000.00
佐藤花子	2024-02-10	280000.00	300000.00
山田四郎	2024-02-12	240000.00	300000.00
田中太郎	2024-02-15	160000.00	300000.00
鈴木次郎	2024-02-18	210000.00	300000.00
山田四郎	2024-02-20	200000.00	300000.00

MIN() OVER()

最初から現在行を含む中での最小値です。

SELECT 
    employee_name,
    sale_date,
    amount,
+    MIN(amount) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING) as cumulative_min
FROM sales
ORDER BY sale_date;

employee_name	sale_date	amount	cumulative_min
田中太郎	2024-01-15	150000.00	150000.00
佐藤花子	2024-01-16	200000.00	150000.00
鈴木次郎	2024-01-17	180000.00	150000.00
田中太郎	2024-01-20	120000.00	120000.00
高橋三郎	2024-01-22	250000.00	120000.00
佐藤花子	2024-01-25	300000.00	120000.00
鈴木次郎	2024-02-01	160000.00	120000.00
田中太郎	2024-02-03	180000.00	120000.00
山田四郎	2024-02-05	220000.00	120000.00
高橋三郎	2024-02-08	190000.00	120000.00
佐藤花子	2024-02-10	280000.00	120000.00
山田四郎	2024-02-12	240000.00	120000.00
田中太郎	2024-02-15	160000.00	120000.00
鈴木次郎	2024-02-18	210000.00	120000.00
山田四郎	2024-02-20	200000.00	120000.00

値アクセス関数

現在の行の前後の値を取ってくる関数です。

LAG()

LAG(amount)で一つ前の行のamountを取得します。
LAG(amount, 2)みたく、値を指定することでn行前といった指定も可能です。

SELECT 
    employee_name,
    sale_date,
    amount,
+    LAG(amount) OVER (ORDER BY sale_date) as prev_amount,
+    LAG(amount, 2) OVER (ORDER BY sale_date) as prev_2_amount
FROM sales
ORDER BY sale_date;

employee_name	sale_date	amount	prev_amount	prev_2_amount
田中太郎	2024-01-15	150000.00
佐藤花子	2024-01-16	200000.00	150000.00
鈴木次郎	2024-01-17	180000.00	200000.00	150000.00
田中太郎	2024-01-20	120000.00	180000.00	200000.00
高橋三郎	2024-01-22	250000.00	120000.00	180000.00
佐藤花子	2024-01-25	300000.00	250000.00	120000.00
鈴木次郎	2024-02-01	160000.00	300000.00	250000.00
田中太郎	2024-02-03	180000.00	160000.00	300000.00
山田四郎	2024-02-05	220000.00	180000.00	160000.00
高橋三郎	2024-02-08	190000.00	220000.00	180000.00
佐藤花子	2024-02-10	280000.00	190000.00	220000.00
山田四郎	2024-02-12	240000.00	280000.00	190000.00
田中太郎	2024-02-15	160000.00	240000.00	280000.00
鈴木次郎	2024-02-18	210000.00	160000.00	240000.00
山田四郎	2024-02-20	200000.00	210000.00	160000.00

LEAD()

こちらは次の行。

SELECT 
    employee_name, 
    sale_date, 
    amount, 
+    LEAD(amount) OVER (ORDER BY sale_date) as next_amount,
+    LEAD(amount, 2) OVER (ORDER BY sale_date) as next_2_amount
FROM sales 
ORDER BY sale_date;

employee_name	sale_date	amount	next_amount	next_2_amount
田中太郎	2024-01-15	150000.00	200000.00	180000.00
佐藤花子	2024-01-16	200000.00	180000.00	120000.00
鈴木次郎	2024-01-17	180000.00	120000.00	250000.00
田中太郎	2024-01-20	120000.00	250000.00	300000.00
高橋三郎	2024-01-22	250000.00	300000.00	160000.00
佐藤花子	2024-01-25	300000.00	160000.00	180000.00
鈴木次郎	2024-02-01	160000.00	180000.00	220000.00
田中太郎	2024-02-03	180000.00	220000.00	190000.00
山田四郎	2024-02-05	220000.00	190000.00	280000.00
高橋三郎	2024-02-08	190000.00	280000.00	240000.00
佐藤花子	2024-02-10	280000.00	240000.00	160000.00
山田四郎	2024-02-12	240000.00	160000.00	210000.00
田中太郎	2024-02-15	160000.00	210000.00	200000.00
鈴木次郎	2024-02-18	210000.00	200000.00
山田四郎	2024-02-20	200000.00

FIRST_VALUE()

現在行からの相対位置ではなく、常に最初の行の値を取得します。

SELECT 
    employee_name,
    sale_date,
    amount,
+    FIRST_VALUE(amount) OVER (ORDER BY sale_date) as first_amount
FROM sales
ORDER BY sale_date;

employee_name	sale_date	amount	first_amount
田中太郎	2024-01-15	150000.00	150000.00
佐藤花子	2024-01-16	200000.00	150000.00
鈴木次郎	2024-01-17	180000.00	150000.00
田中太郎	2024-01-20	120000.00	150000.00
高橋三郎	2024-01-22	250000.00	150000.00
佐藤花子	2024-01-25	300000.00	150000.00
鈴木次郎	2024-02-01	160000.00	150000.00
田中太郎	2024-02-03	180000.00	150000.00
山田四郎	2024-02-05	220000.00	150000.00
高橋三郎	2024-02-08	190000.00	150000.00
佐藤花子	2024-02-10	280000.00	150000.00
山田四郎	2024-02-12	240000.00	150000.00
田中太郎	2024-02-15	160000.00	150000.00
鈴木次郎	2024-02-18	210000.00	150000.00
山田四郎	2024-02-20	200000.00	150000.00

LAST_VALUE()

常に最後の行の値を取得します。

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGを指定しないとうまく取得できないので、最初から最後までという指定を明示的にしておきます。

SELECT 
    employee_name,
    sale_date,
    amount,
+    LAST_VALUE(amount) OVER (
+        ORDER BY sale_date 
+        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as last_amount
FROM sales
ORDER BY sale_date;

employee_name	sale_date	amount	last_amount
田中太郎	2024-01-15	150000.00	200000.00
佐藤花子	2024-01-16	200000.00	200000.00
鈴木次郎	2024-01-17	180000.00	200000.00
田中太郎	2024-01-20	120000.00	200000.00
高橋三郎	2024-01-22	250000.00	200000.00
佐藤花子	2024-01-25	300000.00	200000.00
鈴木次郎	2024-02-01	160000.00	200000.00
田中太郎	2024-02-03	180000.00	200000.00
山田四郎	2024-02-05	220000.00	200000.00
高橋三郎	2024-02-08	190000.00	200000.00
佐藤花子	2024-02-10	280000.00	200000.00
山田四郎	2024-02-12	240000.00	200000.00
田中太郎	2024-02-15	160000.00	200000.00
鈴木次郎	2024-02-18	210000.00	200000.00
山田四郎	2024-02-20	200000.00	200000.00

NTH_VALUE()

最初の行からn番目の値を取得します。

SELECT 
    employee_name,
    sale_date,
    amount,
+    NTH_VALUE(amount, 3) OVER (
+        ORDER BY sale_date 
+        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as third_amount
FROM sales
ORDER BY sale_date;

employee_name	sale_date	amount	third_amount
田中太郎	2024-01-15	150000.00	180000.00
佐藤花子	2024-01-16	200000.00	180000.00
鈴木次郎	2024-01-17	180000.00	180000.00
田中太郎	2024-01-20	120000.00	180000.00
高橋三郎	2024-01-22	250000.00	180000.00
佐藤花子	2024-01-25	300000.00	180000.00
鈴木次郎	2024-02-01	160000.00	180000.00
田中太郎	2024-02-03	180000.00	180000.00
山田四郎	2024-02-05	220000.00	180000.00
高橋三郎	2024-02-08	190000.00	180000.00
佐藤花子	2024-02-10	280000.00	180000.00
山田四郎	2024-02-12	240000.00	180000.00
田中太郎	2024-02-15	160000.00	180000.00
鈴木次郎	2024-02-18	210000.00	180000.00
山田四郎	2024-02-20	200000.00	180000.00

分析関数

PERCENT_RANK()

その値より小さい値が全体の何%を占めるかを表します。

100点満点のテストで80点を取り、PERCENT_RANK()が0.6の場合、「80点は60%の人より上の成績」という意味です。

SELECT 
    employee_name,
    amount,
+    PERCENT_RANK() OVER (ORDER BY amount) as percent_rank
FROM sales
ORDER BY amount;

employee_name	amount	percent_rank
田中太郎	120000.00	0
田中太郎	150000.00	0.07142857142857142
鈴木次郎	160000.00	0.14285714285714285
田中太郎	160000.00	0.14285714285714285
田中太郎	180000.00	0.2857142857142857
鈴木次郎	180000.00	0.2857142857142857
高橋三郎	190000.00	0.42857142857142855
山田四郎	200000.00	0.5
佐藤花子	200000.00	0.5
鈴木次郎	210000.00	0.6428571428571429
山田四郎	220000.00	0.7142857142857143
山田四郎	240000.00	0.7857142857142857
高橋三郎	250000.00	0.8571428571428571
佐藤花子	280000.00	0.9285714285714286
佐藤花子	300000.00	1

CUME_DIST()

その値以下の値が全体の何%を占めるかを表します。

PERCENT_RANKは「その値未満の値が全体の何%を占めるか」に対し、CUME_DISTは「その値以下の値が全体の何%を占めるか」です。

「以下」と「未満」の違いですね。

SELECT 
    employee_name,
    amount,
+    CUME_DIST() OVER (ORDER BY amount) as cume_dist
FROM sales
ORDER BY amount;

employee_name	amount	cume_dist
田中太郎	120000.00	0.06666666666666667
田中太郎	150000.00	0.13333333333333333
鈴木次郎	160000.00	0.26666666666666666
田中太郎	160000.00	0.26666666666666666
田中太郎	180000.00	0.4
鈴木次郎	180000.00	0.4
高橋三郎	190000.00	0.4666666666666667
山田四郎	200000.00	0.6
佐藤花子	200000.00	0.6
鈴木次郎	210000.00	0.6666666666666666
山田四郎	220000.00	0.7333333333333333
山田四郎	240000.00	0.8
高橋三郎	250000.00	0.8666666666666667
佐藤花子	280000.00	0.9333333333333333
佐藤花子	300000.00	1

PERCENTILE_CONT()

「指定したパーセンタイル位置の値を連続的に計算して返す関数」らしいです。

ちょっと分かりやすいように別のデータを例に挙げます。

WITH test_data AS (
    SELECT 1 as value UNION ALL
    SELECT 3 as value UNION ALL
    SELECT 4 as value UNION ALL
    SELECT 6 as value
)
SELECT 
+    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) as 中央値
FROM test_data;

1,3,4,6の４つのデータの場合、PERCENTILE_CONT(0.5)は、(3+4)/2で3.5となります。

連続的というのは実際には存在しないデータを補完して、という意味みたいです。

中央値
3.5

PERCENTILE_DISC()

こちらは、「指定したパーセンタイル位置に最も近い実際のデータ値を返す関数」らしいです。

WITH test_data AS (
    SELECT 1 as value UNION ALL
    SELECT 3 as value UNION ALL
    SELECT 4 as value UNION ALL
    SELECT 6 as value
)
SELECT 
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY value) as 中央値
FROM test_data;

PERCENTILE_CONT()と違い、存在する値のみが返却されます。

ここで、4ではなく、3なのは、４つのデータのうち、value順で並べた際に、４個のデータ中２個目の'3'が50%の値になっているからみたいです。

中央値
3

最後に

DSQLを使うと、手っ取り早く、SQLの動作確認ができそうです！

PostgreSQLの方言みたいなものもあるかもですが、体験よかったの紹介でした！