概要
タイトル通り、SQLでデータを成形する際に複数のカラムをkeyとして通し番号がつけたくなりました。
意外とすんなりできる方法が見つかったため、備忘録として残しておきます。
なお筆者は普段Hiveでクエリを流しているため、他とお作法が異なる部分があったら申し訳ないです。
設定
次のようなtestテーブルが存在する場合を考えます。
| dt | hour | minute |
|---|---|---|
| 20190401 | 0 | 0 |
| 20190401 | 0 | 0 |
| 20190401 | 0 | 10 |
| 20190402 | 4 | 50 |
| 20190402 | 4 | 50 |
| 20190403 | 22 | 40 |
| 20190403 | 22 | 50 |
今までの方法
testテーブルに存在するdt, hour, minuteの3つのカラムをkeyとして通し番号が欲しい場合、今まではWITH句を使ってSELECT DISTINCTでdt・hour・minuteを選択し、row_numberで連番を振ったテーブルをJOINしていました(頭の悪い方法なのでクエリは省略)。
今回の方法
通し番号を順番と読み替え、dense_rankを使えば解決することに気がつきました！
serial.sql
SELECT
dt
,hour
,minute
,dense_rank() OVER (ORDER BY dt, hour, minute) AS serial_number
FROM
test
ORDER BY
serial_number
結果はこうなります。
| dt | hour | minute | serial_number |
|---|---|---|---|
| 20190401 | 0 | 0 | 1 |
| 20190401 | 0 | 0 | 1 |
| 20190401 | 0 | 10 | 2 |
| 20190402 | 4 | 50 | 3 |
| 20190402 | 4 | 50 | 3 |
| 20190403 | 22 | 40 | 4 |
| 20190403 | 22 | 50 | 5 |
MySQLだと8.0以降のver.でWindow関数が実装されたようですね。
Window関数を使っているため実行速度は担保されないものの、今までのJOINクエリよりは良さそうです。
まとめ
SQLだと細かな作業が引っかかったりします。
自分以外にも困っている方がいるかもしれないので、よろしければ参考にしてください。
あと、もっと上手い方法があったら切実に教えてください...。