如何基于Site+Reservation分区生成Umbrella Category列？

现有包含Site、Reservation等字段的预订数据，需新增Umbrella Category列，要求该列根据同一Site+Reservation组合下的Category值统一赋值：

• 若组合内存在OWNER，则全部标记为OWNER
• 若无OWNER但存在GUEST，则全部标记为GUEST
• 其余情况保留原Category值

因需保留所有原始记录，无法直接使用分组聚合操作，当前的CASE语句无法实现按Site+Reservation分区判断，需寻找合适的函数或方法。

原查询语句 #

select
    [Site],
    Reservation,
    Arrival,
    Departure,
    Category,
    case 
        when Category in ('GUEST', 'OWNER') then 'OWNER'
        when Category in ('OWNER', 'COMP') then 'OWNER'
        when Category in ('GUEST', 'COMP') then 'GUEST'
        else Category end as [Umbrella Category]
from #Reservations

原数据示例 #

期望结果示例 #

解决方案 #

以下几种方法均可实现需求，且保留所有原始记录：

方法1：窗口函数+优先级权重判断 #

通过给不同Category分配优先级权重（OWNER=3 > GUEST=2 > COMP=1），使用窗口函数MAX() OVER (PARTITION BY [Site], Reservation)获取分组内的最高权重，再映射回对应的Category：

SELECT
    [Site],
    Reservation,
    Arrival,
    Departure,
    Category,
    CASE 
        WHEN MAX(CASE Category 
                    WHEN 'OWNER' THEN 3 
                    WHEN 'GUEST' THEN 2 
                    WHEN 'COMP' THEN 1 
                    ELSE 0 
                 END) OVER (PARTITION BY [Site], Reservation) = 3 THEN 'OWNER'
        WHEN MAX(CASE Category 
                    WHEN 'OWNER' THEN 3 
                    WHEN 'GUEST' THEN 2 
                    WHEN 'COMP' THEN 1 
                    ELSE 0 
                 END) OVER (PARTITION BY [Site], Reservation) = 2 THEN 'GUEST'
        WHEN MAX(CASE Category 
                    WHEN 'OWNER' THEN 3 
                    WHEN 'GUEST' THEN 2 
                    WHEN 'COMP' THEN 1 
                    ELSE 0 
                 END) OVER (PARTITION BY [Site], Reservation) = 1 THEN 'COMP'
        ELSE Category
    END AS [Umbrella Category]
FROM #Reservations

方法2：EXISTS子查询判断分组内是否存在目标Category #

通过关联子查询，判断当前记录所属的Site+Reservation组合内是否存在特定Category，按优先级依次判断：

SELECT
    r.[Site],
    r.Reservation,
    r.Arrival,
    r.Departure,
    r.Category,
    CASE
        WHEN EXISTS (SELECT 1 FROM #Reservations r2 
                     WHERE r2.[Site] = r.[Site] 
                       AND r2.Reservation = r.Reservation 
                       AND r2.Category = 'OWNER') THEN 'OWNER'
        WHEN EXISTS (SELECT 1 FROM #Reservations r2 
                     WHERE r2.[Site] = r.[Site] 
                       AND r2.Reservation = r.Reservation 
                       AND r2.Category = 'GUEST') THEN 'GUEST'
        ELSE r.Category
    END AS [Umbrella Category]
FROM #Reservations r

方法3：窗口函数+COUNT统计目标Category数量 #

使用窗口函数COUNT() OVER (PARTITION BY [Site], Reservation)统计分组内目标Category的数量，根据数量是否大于0来判断：

SELECT
    [Site],
    Reservation,
    Arrival,
    Departure,
    Category,
    CASE
        WHEN COUNT(CASE WHEN Category = 'OWNER' THEN 1 END) OVER (PARTITION BY [Site], Reservation) > 0 THEN 'OWNER'
        WHEN COUNT(CASE WHEN Category = 'GUEST' THEN 1 END) OVER (PARTITION BY [Site], Reservation) > 0 THEN 'GUEST'
        ELSE Category
    END AS [Umbrella Category]
FROM #Reservations

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