タイトルにはPrestoって書いてますが、例によってTreasureDataで検証しています。
集計業務を行なっていて、レコードに番号や順位を振りたい場面は意外にあるものです。レコードを何らかのグループごとに分けて、そのグループ内で特定の列ごとにソートしてそのグループ内で何番目のレコードかな、という情報を取得します。
そのようなときに使える関数がPrestoには複数実装されており、主に次の3つが使えます。
– ROW_NUMBER()
– RANK()
– DENSE_RANK()
ROW_NUMBER は単純で、1,2,3,4,… と通し番号を振ります。
RANKは順位なので、もしソートキーになる列の値が等しければ、同じ番号を振ってくれます。例えば、2位と3位が同じ値だったら、1,2,2,4,…という具合です。
DENSE_RANKはRANKと似ていますが、同じ順位のレコードが発生したとき、その次のレコードの番号を振るときに値が飛びません。先程の例で言えば、1,2,2,3,…と番号を振ります。
これらの関数は全てWINDOW関数と呼ばれるカテゴリに属しており、ドキュメントも次のページにまとまっています。
参考: Window Functions
これらのWINDOW関数は利用する時は、必ず OVER() という関数をセットで使います。
ROW_NUMBER() OVER (PARTITION BY [グループ分けする列] ORDER BY [ソートする列])
のようなイメージです。
PARTITION BY は必須ではなく、指定しなければ全レコードを通した番号を振ってくれ、指定すれば、指定した列の値が等しいレコードでグループを作って、そのグループ内での番号を振ってくれます。
ORDER BY も必須ではなく、未指定であればROW_NUMBER()の場合は、DBがSELECTするときに抽出した順で番号を振ってくれますが、普通は何かしら欲しい順序があると思うので指定するようにしましょう。
RANK() と DENSE_RANK() については、ORDER BYを指定しないと、どの列で順位をつけたら良いか不明なので、全部1を返してしまいます。これらを使う時は必ず指定しましょう。
ソートする列名の後ろに、 ASC(昇順)/DESC(降順) を指定することもできます。省略した場合は、ASC(昇順)です。
PARTITION BY, ORDER BY ともに、カンマ区切りで複数の列を指定することもできます。
さて、文章で説明ばっかり書いちゃったので実際にやってみましょう。
使うデータは実データではなく、最初のWITH句で生成したダミーデータです。以下のレコードを生成してます。
id, category, value
1, ‘A’, 20
2, ‘A’, 10
3, ‘A’, 20
4, ‘A’, 30
5, ‘B’, 15
6, ‘B’, 30
WITH sample_table AS(
SELECT
id,
category,
value
FROM
UNNEST(
ARRAY[1, 2, 3, 4, 5, 6],
ARRAY['A', 'A', 'A', 'A', 'B', 'B'],
ARRAY[20, 10, 20, 30, 15, 30]
) AS t(
id,
category,
value
)
)
SELECT
id,
category,
value,
ROW_NUMBER() OVER(PARTITION BY category ORDER BY value) AS row_number_,
RANK() OVER(PARTITION BY category ORDER BY value) AS rank_,
DENSE_RANK() OVER(PARTITION BY category ORDER BY value) AS dense_rank_
FROM
sample_table結果は次のようになります。
| id | category | value | row_number_ | rank_ | dense_rank_ |
| 2 | A | 10 | 1 | 1 | 1 |
| 1 | A | 20 | 2 | 2 | 2 |
| 3 | A | 20 | 3 | 2 | 2 |
| 4 | A | 30 | 4 | 4 | 3 |
| 5 | B | 15 | 1 | 1 | 1 |
| 6 | B | 30 | 2 | 2 | 2 |
想定通りの結果が得られましたね。