Presto row_number/rank/dense_rank

• prestoにもrank/desne_rank/row_numberがある
  • row_number 機械的にidを割り振る
  • rank 重複値は同順位だが、次の順位は重複ぶんだけ飛ばす
  • dense_rank 重複値は同順位だが、次の順位は+1

クエリ。

WITH dataset AS
  ( SELECT *
   FROM (
         VALUES 10,
                100,
                500,
                500,
                1000,
                1500 ) AS t(num))
SELECT num,
       ROW_NUMBER() OVER( ORDER BY num) as row_number,
       RANK() OVER( ORDER BY num) as rank,
       DENSE_RANK() OVER( ORDER BY num) as dense_rank
FROM dataset;

結果。

NUM	ROW_NUMBER	RANK	DENSE_RANK
10	1	1	1
100	2	2	2
500	3	3	3
500	4	3	3
1000	5	5	4
1500	6	6	5

6.15. Window Functions — Presto 0.202 Documentation

Data Sampling In Presto

http://itref.fc2web.com/oracle/function/row_number.html