Azure SQL中按特定序列合并行的实现方法问询

要实现合并连续Auto-Productive交替序列中的中间行（汇总Duration并转为Productive状态），可以通过窗口函数分组识别目标序列，再合并对应行。以下是具体步骤和SQL实现：

思路 #

1. 数据预处理：将字符串类型的Duration转为数值（替换逗号为小数点），Timestamp转为日期类型以便排序。
2. 分组识别目标序列：通过窗口函数将连续的Auto/Productive行划分为同一组，遇到Error/Manual等其他状态时切换分组。
3. 标记合并候选行：在每个组内，判断是否满足「首尾状态为Auto、组内仅含Auto和Productive」的条件，标记组内中间行作为合并候选。
4. 合并与结果输出：保留非候选行，将候选行汇总为一行Productive状态的记录，最后重新分配ID并按时间降序排序。

SQL代码（以MySQL为例） #

WITH processed_data AS (
    SELECT 
        ID,
        Timestamp,
        -- 将逗号分隔的Duration转为数值
        CAST(REPLACE(Duration, ',', '.') AS DECIMAL(10,2)) AS Duration_num,
        State,
        -- 转换Timestamp为日期类型用于排序
        STR_TO_DATE(Timestamp, '%d.%m.%Y  %H:%i') AS ts_date
    FROM machine_data
),
grouped_data AS (
    SELECT 
        *,
        -- 按非Auto/Productive状态划分分组
        SUM(CASE WHEN State NOT IN ('Auto', 'Productive') THEN 1 ELSE 0 END) 
            OVER (ORDER BY ts_date DESC) AS group_id
    FROM processed_data
),
group_metadata AS (
    SELECT 
        group_id,
        -- 获取组内第一个状态（最新行的状态）
        FIRST_VALUE(State) OVER (PARTITION BY group_id ORDER BY ts_date DESC) AS first_state,
        -- 获取组内最后一个状态（最旧行的状态）
        LAST_VALUE(State) OVER (
            PARTITION BY group_id 
            ORDER BY ts_date DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS last_state,
        -- 组内包含的不同状态数量
        COUNT(DISTINCT State) OVER (PARTITION BY group_id) AS state_count
    FROM grouped_data
),
target_rows AS (
    SELECT 
        gd.*,
        gm.first_state,
        gm.last_state,
        gm.state_count,
        -- 标记是否为需要合并的中间行
        CASE 
            WHEN gm.state_count = 2 
                AND gm.first_state = 'Auto' 
                AND gm.last_state = 'Auto'
                -- 排除组内首尾行
                AND ID NOT IN (
                    SELECT ID FROM grouped_data WHERE group_id = gd.group_id ORDER BY ts_date DESC LIMIT 1
                    UNION
                    SELECT ID FROM grouped_data WHERE group_id = gd.group_id ORDER BY ts_date ASC LIMIT 1
                )
            THEN 1 
            ELSE 0 
        END AS is_merge_candidate
    FROM grouped_data gd
    JOIN group_metadata gm ON gd.group_id = gm.group_id
),
merged_records AS (
    SELECT 
        -- 取组内最早的Timestamp作为合并行的时间
        MIN(Timestamp) AS Timestamp,
        SUM(Duration_num) AS Duration,
        'Productive' AS State,
        MIN(ts_date) AS ts_date
    FROM target_rows
    WHERE is_merge_candidate = 1
    GROUP BY group_id
)
-- 合并最终结果
SELECT 
    ROW_NUMBER() OVER (ORDER BY ts_date DESC) AS ID,
    Timestamp,
    -- 保留逗号作为小数点分隔符
    REPLACE(CAST(Duration AS CHAR(10)), '.', ',') AS Duration,
    State
FROM (
    -- 保留不需要合并的行
    SELECT 
        ts_date,
        Timestamp,
        Duration_num AS Duration,
        State
    FROM target_rows
    WHERE is_merge_candidate = 0
    UNION ALL
    -- 添加合并后的行
    SELECT 
        ts_date,
        Timestamp,
        Duration,
        State
    FROM merged_records
) combined_result
ORDER BY ID DESC;

说明 #

• 代码中假设表名为machine_data，请根据实际表名调整。
• 若使用其他SQL方言（如PostgreSQL），需调整日期转换、数值转换的函数（例如TO_TIMESTAMP、TO_NUMBER）。
• 合并后的行使用组内最早的Timestamp，Duration汇总后将小数点转回逗号格式，与原数据格式一致。

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