Prestoで行番号や順位を振る関数まとめ

タイトルには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

結果は次のようになります。

idcategoryvaluerow_number_rank_dense_rank_
2A10111
1A20222
3A20322
4A30443
5B15111
6B30222

想定通りの結果が得られましたね。

カテゴリーSQL

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です