SQL Server中如何按分区对最新重发批次的记录求和

要解决重复计算重发记录的问题，核心是先给每个parameter下的记录按min_id=1的起始点划分批次，再筛选出最新批次进行统计，避免包含重发前的旧数据。具体实现如下：

1. 划分重发批次 #

用窗口函数标记每个批次：每当遇到min_id=1的行，就视为新批次的开始，通过累积求和生成唯一的批次号batch_id，同一批次的所有行将共享同一个batch_id。

2. 定位最新批次 #

对每个parameter，提取最大的batch_id，这就是该参数对应的最新重发批次。

3. 统计最新批次数据 #

筛选出最新批次的记录，聚合计算所需的总和、最小min_id、最大max_id和最新创建时间。

完整SQL查询 #

WITH batch_data AS (
    SELECT 
        parameter,
        rows,
        min_id,
        max_id,
        create_date,
        status,
        -- 生成批次号：每出现min_id=1，批次号递增1
        SUM(CASE WHEN min_id = 1 THEN 1 ELSE 0 END) 
            OVER (PARTITION BY parameter ORDER BY create_date ROWS UNBOUNDED PRECEDING) AS batch_id
    FROM my_table
    WHERE status = 'sent' -- 先过滤已发送的有效记录
),
latest_batch AS (
    SELECT 
        parameter,
        MAX(batch_id) AS max_batch_id
    FROM batch_data
    GROUP BY parameter
)
SELECT 
    bd.parameter,
    SUM(bd.rows) AS total_rows,
    MIN(bd.min_id) AS min_id, -- 最新批次的min_id必然为1
    MAX(bd.max_id) AS max_id,
    MAX(bd.create_date) AS latest_create_date
FROM batch_data bd
JOIN latest_batch lb 
    ON bd.parameter = lb.parameter 
    AND bd.batch_id = lb.max_batch_id
GROUP BY bd.parameter;

关键逻辑说明 #

• 批次划分：SUM(CASE...) OVER(...) 窗口函数按parameter分组、create_date排序，从第一行到当前行累积计数，每遇到min_id=1就加1，确保同一重发批次的行拥有相同的batch_id。
• 最新批次筛选：子查询latest_batch获取每个parameter的最大批次号，确保只统计最新的重发批次数据。
• 聚合统计：对最新批次的记录进行求和、取最值操作，得到每个参数对应的目标统计结果。

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