如何基于max last_modified_date统计各分组的归属数量？

嘿，我明白你现在的需求——要给每个唯一标识只保留最新修改日期对应的分组，还要处理那些只有last_modified_date为NULL的记录对吧？我之前也遇到过类似的场景，用窗口函数就能完美解决，给你一步步说清楚：

核心思路 #

我们需要给每个标识的所有记录按「修改日期有效性+日期新旧」排序，只保留排序后的第一条记录（也就是每个标识的最新有效分组），之后再基于这个结果做分组统计。

具体SQL实现 #

假设你的表名为your_table，字段分别是：

• id：唯一标识（比如你的001、002、003）
• group_name：分组名称（GROUP A/B/C）
• last_modified_date：修改日期（可能为NULL）

第一步：筛选每个标识的最新分组记录 #

WITH ranked_records AS (
    SELECT 
        id,
        group_name,
        last_modified_date,
        -- 给每个标识的记录排序：优先保留有日期的记录，再按日期从新到旧排；NULL记录仅在无其他记录时保留
        ROW_NUMBER() OVER (
            PARTITION BY id 
            ORDER BY 
                CASE WHEN last_modified_date IS NOT NULL THEN 1 ELSE 0 END DESC,
                last_modified_date DESC
        ) AS record_rank
    FROM your_table
)
SELECT 
    id,
    group_name,
    last_modified_date
FROM ranked_records
WHERE record_rank = 1;

代码解释 #

1. CTEranked_records：用PARTITION BY id把数据按标识分组，给每个组内的记录打排序序号
2. 排序规则：
   • 先用CASE把有修改日期的记录权重设为1，NULL的设为0，降序排序后，有日期的记录会排在NULL前面
   • 再对有日期的记录按last_modified_date降序，确保最新的日期排在最前面
3. 筛选有效记录：取每个标识的record_rank=1的记录，就是该标识的最新有效分组

验证你的示例场景 #

• 标识001：如果有GROUP A（旧日期）和GROUP B（新日期），GROUP B的record_rank会是1，被选中
• 标识002：同理，最新的GROUP C会被保留
• 标识003：只有NULL的记录，record_rank为1，对应的GROUP B会被保留

第二步：按分组统计数量 #

如果要基于上面的结果统计每个分组的标识数量，直接在筛选后的结果上再分组即可：

WITH ranked_records AS (
    SELECT 
        id,
        group_name,
        last_modified_date,
        ROW_NUMBER() OVER (
            PARTITION BY id 
            ORDER BY 
                CASE WHEN last_modified_date IS NOT NULL THEN 1 ELSE 0 END DESC,
                last_modified_date DESC
        ) AS record_rank
    FROM your_table
)
SELECT 
    group_name,
    COUNT(*) AS group_member_count
FROM ranked_records
WHERE record_rank = 1
GROUP BY group_name;

可能踩过的坑 #

如果你之前尝试没成功，大概率是没处理NULL的排序优先级——比如直接按last_modified_date DESC排序的话，NULL会被排在最前面（不同数据库可能有差异），导致有有效日期的记录被覆盖。用上面的CASE语句就能规避这个问题。

内容的提问来源于stack exchange，提问作者tx-911