SQL中如何调整row_number排序规则实现客户破产状态自定义聚合

问题原因 #

你当前直接对IsBankrupt字段按字典序升序排序，字典序中N < N/D，因此同一分组内同时存在N和N/D时，N会排在首位被取出，不符合你的聚合规则。

解决方案 #

通过CASE表达式为不同破产状态自定义排序权重，优先级按照你的聚合规则设置为：Y > N/D > N，权重越小优先级越高，排序后取第一条即可符合所有规则。

修改后的完整查询代码 #

with sample as (
    select date('2020-12-31') as CDate, 123 as CustomerID, 'N/D' as IsBankrupt
    union all
    select date('2020-12-31') as CDate, 123 as CustomerID, 'N' as IsBankrupt)
select CDate, CustomerID, IsBankrupt, case when CustomerID = 123 then 'N/D' end as ExpectedResult
from
(
    select CDate, CustomerID, IsBankrupt,
        row_number() over (partition by CustomerID, CDate order by case IsBankrupt when 'Y' then 1 when 'N/D' then 2 when 'N' then 3 end asc) as flag
    from sample
) as subsample
where flag = 1

验证说明 #

修改后排序逻辑可完全匹配你的聚合规则：

• 分组内存在Y时，Y权重为1排在首位，返回Y
• 分组内无Y但存在N/D时，N/D权重为2排在N前面，返回N/D
• 分组内只有N时，返回N

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