SQL中Partition By子句同组行列比较与Flag批量设置问题

你的需求是按Serial_Number分组，只要组内存在任意重复的Last_update_date，该组所有行的Flag设为Y，否则设为N。你之前用count(distinct)的思路方向正确，但缺少最终的判断逻辑，下面提供两种可行的实现方案：

方法一：窗口函数直接判断重复日期 #

通过嵌套窗口函数，先计算每个日期在组内的出现次数，再判断组内是否存在次数≥2的日期，最终生成Flag：

SELECT 
    Id,
    Serial_Number,
    Last_update_date,
    CASE 
        WHEN MAX(COUNT(1) OVER (PARTITION BY Serial_Number, Last_update_date)) 
             OVER (PARTITION BY Serial_Number) >= 2 
        THEN 'Y' 
        ELSE 'N' 
    END AS Flag
FROM my_table;

逻辑说明： #

1. COUNT(1) OVER (PARTITION BY Serial_Number, Last_update_date)：统计每个日期在对应序列号组内的出现次数；
2. MAX(...) OVER (PARTITION BY Serial_Number)：获取该序列号组内日期出现次数的最大值；
3. 若最大值≥2，说明组内存在重复日期，Flag设为Y，否则设为N。

方法二：先分组标记再关联 #

先通过子查询统计每个Serial_Number是否存在重复日期，再将标记结果与原表关联，应用到每一行：

WITH serial_flag AS (
    SELECT 
        Serial_Number,
        CASE 
            WHEN COUNT(*) > COUNT(DISTINCT Last_update_date) 
            THEN 'Y' 
            ELSE 'N' 
        END AS group_flag
    FROM my_table
    GROUP BY Serial_Number
)
SELECT 
    t.Id,
    t.Serial_Number,
    t.Last_update_date,
    s.group_flag AS Flag
FROM my_table t
JOIN serial_flag s ON t.Serial_Number = s.Serial_Number;

逻辑说明： #

1. 子查询serial_flag中，对比每个序列号组的总行数与不同日期的数量：如果总行数大于不同日期数，说明存在重复日期，标记为Y；
2. 将原表与该子查询关联，把组级别的标记同步到组内每一行。

你之前的CTE仅计算了组内不同日期的数量，未与总行数对比，因此无法直接判断是否存在重复。上述两种方法均可满足需求，数据量较大时方法二的性能可能更优。

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