はじめに
SQL Serverのウィンドウ関数は、実業務を行う以上高頻度でお世話になる機能です。
業務でよく使用されるROW_NUMBER, RANK, DENSE_RANKおよび順位付けの仲間NTILEの4つの関数を具体例を交えてまとめておきます。
参考
具体的なSQL例
ROW_NUMBER() 関数
ROW_NUMBER()は、結果セット内の各行に一意の連続した整数を割り当てます。
下記例では、給与の高い順番に各従業員の順位付けを行っています。
ROW_NUMBER()
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
FROM
employees;
RANK() 関数
RANK()は、順位を付けますが、同じ値の場合は同じ順位を割り当て、次のランクをスキップします。
下記例では、各部門内で給与に基づいて順位付けを行っています。同じ給与の従業員は同じランクになります。
RANK()
SELECT
employee_name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_salary_rank
FROM
employees;
DENSE_RANK() 関数
DENSE_RANK()もRANK()と同様に順位を付けますが、同じ値の場合は同じランクを割り当て、次のランクをスキップしません。
下記例では、各製品カテゴリー内で売上高に基づいて順位付けを行っています。同じ売上額の製品は同じランクになりますが、次のランクは連続した数字になります。
DENSE_RANK()
SELECT
product_name,
category,
sales_amount,
DENSE_RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS category_sales_rank
FROM
products;
NTILE() 関数
NTILE()は、指定された数のグループに行を均等に分割します。各行にグループ番号を割り当てます。
下記例では、従業員を給与に基づいて4つの等しいグループ（四分位）に分けています。
正直つかったことないです。
NTILE()
SELECT
employee_name,
department,
salary,
NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM
employees;
4つの関数の違い
| 値 | ROW_NUMBER() | RANK() | DENSE_RANK() | NTILE(4) |
|---|---|---|---|---|
| 100 | 1 | 1 | 1 | 1 |
| 90 | 2 | 2 | 2 | 1 |
| 90 | 3 | 2 | 2 | 2 |
| 80 | 4 | 4 | 3 | 2 |
| 70 | 5 | 5 | 4 | 3 |
| 60 | 6 | 6 | 5 | 3 |
| 50 | 7 | 7 | 6 | 4 |
- ROW_NUMBER(): 常に一意の連続した数字を割り当てます
- RANK(): 同じ値には同じ順位を割り当て、次の順位をスキップします
- DENSE_RANK(): 同じ値には同じ順位を割り当てますが、次の順位は連続した数字になります
- NTILE(): データを指定された数のグループに均等に分配します
おわりに
ウィンドウ関数のROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()は、データの順位付けや分析に使えます。
それぞれの関数を理解し、適切に使い分けることで、効果的なデータ分析が可能になります。特にROW_NUMBER()は様々な場面でお世話になります。