sql - 重复值的密集排名

Question

id     val       dense_rank
1       11   =>     1
2       11          1
3       22          2
4       33          3
5       33          3
6       11          4

获得这种dense_rank应该喜欢什么sql？

标签： sqlpsqlwindow-functionsgaps-and-islandsdense-rank

Answer 1

用于lag()计算前一个值是多少，然后是一个累积和：

select t.*,
       count(*) filter (where prev_val is null or prev_val <> val) over (order by id) as dense_rank
from (select t.*, lag(val) over (order by id) as prev_val
      from t
     ) t;

这是一个 db<>fiddle。