如何按分组计算row_number以得到符合预期的排序编号结果

你原来的写法是按id+color分区后在组内排序，得到的是同id同color下的行序号，而你的预期结果要求的是同id下，按color维度聚合后的排序序号，且同id同color的所有行共享同一个序号，所以原来的分区逻辑不符合需求。

实现逻辑 #

• 先计算每个id+color分组下的最大time值，同id同color的所有行的这个值相同
• 按id分区，根据上一步得到的最大time值做降序排序，生成排序序号，同id同color的行因为最大time相同，会得到相同的序号
• 最终结果按id、time升序排序即可

正确SQL代码 #

支持窗口函数嵌套的版本 #

select 
  id,
  color,
  time,
  dense_rank() over (
    partition by id 
    order by max(time) over (partition by id, color) desc
  ) as group_order
from 你的表名
order by id, time;

兼容老版本SQL的子查询写法 #

select 
  t.id,
  t.color,
  t.time,
  dense_rank() over (partition by t.id order by t.color_max_time desc) as group_order
from (
  select 
    *,
    max(time) over (partition by id, color) as color_max_time
  from 你的表名
) t
order by t.id, t.time;

内容的提问来源于stack exchange，提问作者user458