はじめに
最近データベーススペシャリスト試験の勉強をしていますが、たまに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の方言みたいなものもあるかもですが、体験よかったの紹介でした！