Oracle 11g与PostgreSQL 12连续等值分组排名实现方案

测试表构造 #

现有如下构造的测试表tbl：

with tbl as
(
  select  1 ord, 'A' name from dual
  union all
  select  2 ord, 'A' name from dual
  union all
  select  3 ord, 'A' name from dual
  union all
  select  4 ord, 'B' name from dual
  union all
  select  5 ord, 'B' name from dual
  union all
  select  6 ord, 'A' name from dual
  union all
  select  7 ord, 'A' name from dual
  union all
  select  8 ord, 'C' name from dual
  union all
  select  9 ord, 'C' name from dual
  union all
  select 10 ord, 'B' name from dual
  union all
  select 11 ord, 'B' name from dual
  union all
  select 12 ord, 'B' name from dual
) 
select ord, name, myrank(...)
from tbl
order by
  ord;

期望查询结果 #

执行后需得到如下结果：

ORD NAME     MYRANK
---------- ---- ----------
         1 A             1
         2 A             1
         3 A             1
         4 B             2
         5 B             2
         6 A             3
         7 A             3
         8 C             4
         9 C             4
        10 B             5
        11 B             5
        12 B             5

需求规则 #

• 连续相同name值的记录为同一排名
• 同一name的不同连续分组需分配不同排名
• 排名按ord字段顺序单调递增

适配Oracle 11g的实现 #

借助窗口函数生成分组标识，再通过累计求和得到唯一排名：

with tbl as
(
  select  1 ord, 'A' name from dual
  union all
  select  2 ord, 'A' name from dual
  union all
  select  3 ord, 'A' name from dual
  union all
  select  4 ord, 'B' name from dual
  union all
  select  5 ord, 'B' name from dual
  union all
  select  6 ord, 'A' name from dual
  union all
  select  7 ord, 'A' name from dual
  union all
  select  8 ord, 'C' name from dual
  union all
  select  9 ord, 'C' name from dual
  union all
  select 10 ord, 'B' name from dual
  union all
  select 11 ord, 'B' name from dual
  union all
  select 12 ord, 'B' name from dual
),
grouped as (
  select 
    ord,
    name,
    -- 当前行name与上一行不同时标记为1，否则为0
    case when name = lag(name) over(order by ord) then 0 else 1 end as group_flag
  from tbl
),
ranked_groups as (
  select
    ord,
    name,
    -- 累计求和生成单调递增的分组排名
    sum(group_flag) over(order by ord) as myrank
  from grouped
)
select ord, name, myrank
from ranked_groups
order by ord;

适配PostgreSQL 12的实现 #

逻辑与Oracle一致，仅需移除dual表的引用：

with tbl as
(
  select  1 ord, 'A' name
  union all
  select  2 ord, 'A' name
  union all
  select  3 ord, 'A' name
  union all
  select  4 ord, 'B' name
  union all
  select  5 ord, 'B' name
  union all
  select  6 ord, 'A' name
  union all
  select  7 ord, 'A' name
  union all
  select  8 ord, 'C' name
  union all
  select  9 ord, 'C' name
  union all
  select 10 ord, 'B' name
  union all
  select 11 ord, 'B' name
  union all
  select 12 ord, 'B' name
),
grouped as (
  select 
    ord,
    name,
    case when name = lag(name) over(order by ord) then 0 else 1 end as group_flag
  from tbl
),
ranked_groups as (
  select
    ord,
    name,
    sum(group_flag) over(order by ord) as myrank
  from grouped
)
select ord, name, myrank
from ranked_groups
order by ord;

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