回答

ウィンドウ関数は、特定の行をグループ化することなく、行の「ウィンドウ」に対して計算を実行することを可能にし、レポートや分析に便利です。

ROW_NUMBER() — 各パーティション（ウィンドウの分割）内の行に対して、一意の連続番号を付与し、指定された基準に従ってソートします。ORDER BY で同じ値がある場合には番号の飛びが発生することがあります。
RANK() — ORDER BY で同じ値を持つ行に同じランクを与えますが、次の番号の行は飛ばします（ギャップが生じます）。
DENSE_RANK() — 同じ値を持つ行に同じランクを与えますが、番号は連続しており、飛ぶことはありません。

例:

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS num,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

テーブル:

name	salary
ヴァーシャ	10000
ペーチャ	10000
マーシャ	9000

結果:

name	salary	num	rank	dense_rank
ヴァーシャ	10000	1	1	1
ペーチャ	10000	2	1	1
マーシャ	9000	3	3	2

注意点:

不適切に選択された ORDER BY は、不正確なソートを引き起こす可能性があります。
(row_number) ORDER BY でユニークなフィールドを選択しない場合、安定した順序の保証はありません。
PARTITION BY を必要とする場合に利用しないと、全体の行集合に対して不正確な番号付けが行われます。

意地悪な質問

ウィンドウ関数で PARTITION BY を指定しなかった場合、ROW_NUMBER() での行の番号付けはどのように行われますか？

回答: データのすべての選択が一つのパーティションとして扱われます。つまり、番号付けはすべての行に対して連続的であり、グループ化を考慮しません。

例:

SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) FROM employees;
-- すべての従業員は部門ごとの分割なしに 1 から始まる一意の番号を受け取ります

物語 №1

BI レポートで部門ごとの PARTITION BY を指定し忘れました。すべての従業員が連続した通し番号を受け取り、タスクは各部門内での最良の識別でした。その結果、部門ごとの不正確な TOP-N 従業員ができました。

物語 №2

開発者は「グループ内の最良の」と言うために RANK() を選択しましたが、同じ指標があるために同じ番号が割り当てられ、分析のリーダーに暗黙の重複が発生しました。

物語 №3

DENSE_RANK() の使用時に、ランクの飛びを禁止することを考慮しなかったため、売上分析時に「ユニーク」な重要なポジションの数が歪められました。ビジネスロジックのチェックが分配のエラーを明らかにしました。

ROW_NUMBER()、RANK()、DENSE_RANK() ウィンドウ関数は SQL レポートのプログラミングでどのように機能し、どのように異なるのか？それらを使用する際の注意点は何か？

回答

意地悪な質問