使用MySQL窗口函数实现连续相同Site ID记录的聚合与清理

你现在的需求是把同代理下连续相同Site ID的记录合并：保留最早的登录时间（TimeIn）、最晚的登出时间（TimeOut），把工单数量和金额累加，删掉中间重复的冗余记录。之前只更新当前行TimeOut为下一行的方式，遇到3条及以上连续记录就失效，不用递归也能搞定，用分组聚合的思路就能解决，步骤如下：

第一步：给连续相同的Site ID打分组标签 #

用窗口函数把每一段连续的相同Site ID分成一组，给每组分配唯一的group_id：

WITH grouped_records AS (
    SELECT 
        *,
        -- 上一行Site ID和当前不同时，分组ID+1，否则保持不变
        SUM(CASE WHEN Site_ID = LAG(Site_ID) OVER (ORDER BY TimeIn) THEN 0 ELSE 1 END) 
            OVER (ORDER BY TimeIn) AS group_id
    FROM 你的代理记录表名
    WHERE Agent_ID = 1001 -- 单代理筛选，多代理场景去掉此条件或加PARTITION BY Agent_ID
)

第二步：生成聚合后的目标数据 #

基于上面的分组，计算每组的最终聚合值：

, aggregated_data AS (
    SELECT
        Agent_ID,
        Site_ID,
        MIN(TimeIn) AS TimeIn, -- 取组内最早的登录时间
        MAX(TimeOut) AS TimeOut, -- 取组内最晚的登出时间
        SUM(工单数量字段名) AS total_tickets, -- 累加工单数量
        SUM(金额字段名) AS total_amount -- 累加金额
    FROM grouped_records
    GROUP BY group_id, Agent_ID, Site_ID
)

第三步：更新原表并清理冗余记录 #

MySQL无法在同一张表同时完成更新和删除操作，给你两种实用方案：

方法一：临时表替换法（简单直接） #

先把聚合结果存入临时表，清空原表后再插入聚合数据，适合数据量不大的场景：

-- 创建临时表存储聚合结果
CREATE TEMPORARY TABLE temp_aggregated AS
WITH grouped_records AS (
    SELECT 
        *,
        SUM(CASE WHEN Site_ID = LAG(Site_ID) OVER (ORDER BY TimeIn) THEN 0 ELSE 1 END) 
            OVER (ORDER BY TimeIn) AS group_id
    FROM 你的代理记录表名
),
aggregated_data AS (
    SELECT
        Agent_ID,
        Site_ID,
        MIN(TimeIn) AS TimeIn,
        MAX(TimeOut) AS TimeOut,
        SUM(工单数量字段名) AS total_tickets,
        SUM(金额字段名) AS total_amount
    FROM grouped_records
    GROUP BY group_id, Agent_ID, Site_ID
)
SELECT * FROM aggregated_data;

-- 清空原表（操作前务必备份数据！）
TRUNCATE TABLE 你的代理记录表名;

-- 将聚合数据插回原表
INSERT INTO 你的代理记录表名 (Agent_ID, Site_ID, TimeIn, TimeOut, 工单数量字段名, 金额字段名)
SELECT Agent_ID, Site_ID, TimeIn, TimeOut, total_tickets, total_amount FROM temp_aggregated;

-- 删除临时表
DROP TEMPORARY TABLE temp_aggregated;

方法二：更新保留行+删除冗余行（适合不想清空表的场景） #

先定位每组的首行，更新它的TimeOut、工单数量和金额为组内聚合值，再删除组内其他冗余行：

-- 1. 标记分组、组内行号和组大小
WITH grouped_records AS (
    SELECT 
        *,
        SUM(CASE WHEN Site_ID = LAG(Site_ID) OVER (ORDER BY TimeIn) THEN 0 ELSE 1 END) 
            OVER (ORDER BY TimeIn) AS group_id,
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY TimeIn) AS row_in_group,
        COUNT(*) OVER (PARTITION BY group_id) AS group_size
    FROM 你的代理记录表名
),
-- 2. 计算每组的聚合值
group_aggregates AS (
    SELECT
        group_id,
        MAX(TimeOut) AS final_TimeOut,
        SUM(工单数量字段名) AS total_tickets,
        SUM(金额字段名) AS total_amount
    FROM grouped_records
    GROUP BY group_id
)
-- 3. 更新每组首行的字段值
UPDATE 你的代理记录表名 t
JOIN grouped_records gr ON t.主键字段 = gr.主键字段
JOIN group_aggregates ga ON gr.group_id = ga.group_id
SET 
    t.TimeOut = ga.final_TimeOut,
    t.工单数量字段名 = ga.total_tickets,
    t.金额字段名 = ga.total_amount
WHERE gr.row_in_group = 1;

-- 4. 删除每组中除首行外的所有冗余记录
DELETE t
FROM 你的代理记录表名 t
JOIN (
    SELECT 
        主键字段,
        SUM(CASE WHEN Site_ID = LAG(Site_ID) OVER (ORDER BY TimeIn) THEN 0 ELSE 1 END) 
            OVER (ORDER BY TimeIn) AS group_id,
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY TimeIn) AS row_in_group
    FROM 你的代理记录表名
) gr ON t.主键字段 = gr.主键字段
WHERE gr.row_in_group > 1;

注意事项 #

• 替换代码中的你的代理记录表名、主键字段、工单数量字段名、金额字段名为你实际的表和字段名称。
• 无论用哪种方法，操作前必须备份数据，避免误操作导致数据丢失。
• 多代理场景下，记得在窗口函数中添加PARTITION BY Agent_ID（比如LAG(Site_ID) OVER (PARTITION BY Agent_ID ORDER BY TimeIn)），确保每个代理的分组独立计算。

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