如何在SQL中用Group By处理重复列实现多阶段聚合与转置

步骤说明与代码实现 #

先通过窗口函数识别连续的Activityname分组，再聚合得到每个阶段的关键信息，最后转置为目标宽表结构：

-- 第一步：识别同一流程下连续的Activityname分组
WITH activity_groups AS (
    SELECT 
        ProcessInstanceId,
        Activityname,
        Username,
        ActionDatetime,
        -- 生成连续分组ID：当前活动与上一行不同时，分组ID递增
        SUM(CASE WHEN prev_activity = Activityname THEN 0 ELSE 1 END) OVER (
            PARTITION BY ProcessInstanceId 
            ORDER BY ActionDatetime, LogId
        ) AS group_id
    FROM (
        SELECT 
            *,
            -- 获取上一行的Activityname，用于判断是否连续
            LAG(Activityname) OVER (
                PARTITION BY ProcessInstanceId 
                ORDER BY ActionDatetime, LogId
            ) AS prev_activity
        FROM TableA
    ) t
),
-- 第二步：按分组聚合，计算阶段时间、最后处理人及TAT
group_agg AS (
    SELECT 
        ProcessInstanceId,
        Activityname,
        group_id,
        MIN(ActionDatetime) AS enter_time,
        MAX(ActionDatetime) AS exit_time,
        -- 取分组内最后处理的用户（按时间+LogId排序确保准确性）
        LAST_VALUE(Username) OVER (
            PARTITION BY ProcessInstanceId, Activityname, group_id
            ORDER BY ActionDatetime, LogId
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS last_user,
        -- 计算TAT，时间差单位根据数据库调整（示例用秒）
        DATEDIFF(SECOND, MIN(ActionDatetime), MAX(ActionDatetime)) AS TAT
    FROM activity_groups
    GROUP BY ProcessInstanceId, Activityname, group_id
)
-- 第三步：转置为宽表，每个连续阶段生成一行记录
SELECT 
    ProcessInstanceId,
    -- 为每个Activityname生成对应的用户和TAT字段，需枚举所有可能的Activityname
    CASE WHEN Activityname = 'Activity1' THEN last_user END AS Activity1_User,
    CASE WHEN Activityname = 'Activity1' THEN TAT END AS Activity1_TAT,
    CASE WHEN Activityname = 'Activity2' THEN last_user END AS Activity2_User,
    CASE WHEN Activityname = 'Activity2' THEN TAT END AS Activity2_TAT,
    CASE WHEN Activityname = 'Activity3' THEN last_user END AS Activity3_User,
    CASE WHEN Activityname = 'Activity3' THEN TAT END AS Activity3_TAT
    -- 如有更多Activityname，继续添加对应CASE语句
FROM group_agg
ORDER BY ProcessInstanceId, group_id;

关键细节说明 #

• 连续分组逻辑：利用LAG窗口函数获取上一行的Activityname，通过累计求和生成group_id，确保同一流程中连续的相同活动被归为一组，间隔出现的则分为不同组。
• 最后处理人获取：使用LAST_VALUE窗口函数并指定全范围行，确保取到该分组中时间最晚（LogId最大）的处理人。
• TAT计算：时间差函数需适配数据库类型：
  • MySQL：改用TIMESTAMPDIFF(SECOND, enter_time, exit_time)
  • PostgreSQL：用EXTRACT(EPOCH FROM (exit_time - enter_time))获取秒数
• 动态转置适配：如果Activityname值不固定，可结合数据库的动态SQL实现自动转置（比如SQL Server的PIVOT+动态语句、MySQL的GROUP_CONCAT生成动态列）。

内容的提问来源于stack exchange，提问作者MD KAMRAN AZAM