SQL计算车辆状态日期间周期数并实现行值转列的实现问题

需求说明 #

现有车辆信息表vehicle_table，包含字段：S.No、Vehicle_ID、status（状态值为Start/Failed）、date_on（状态日期），需按如下规则生成周期统计结果：

• 每辆车每条Start状态记录对应一个使用周期
• 周期结束日期取该Start之后最早的Failed日期，若无对应Failed记录则取当前日期
• 按时间顺序为每辆车的周期分配递增的Cycle序号
• 输出字段：Vehicle_ID、Start（周期开始日期）、Failed/Running（周期结束/当前运行日期）、Cycle（周期序号）

原有代码问题 #

你此前用PIVOT行转列的方式无法实现需求，核心原因是PIVOT会按维度聚合所有同状态的日期，无法实现相邻Start和Failed的配对匹配，也没法按顺序分配周期序号，需要用窗口函数实现配对逻辑。

实现代码（兼容SQL Server/PostgreSQL/MySQL 8.0+等主流支持窗口函数的数据库） #

WITH start_records AS (
    -- 提取所有Start状态记录，按车辆+日期排序分配周期序号
    SELECT 
        Vehicle_ID,
        date_on AS start_date,
        ROW_NUMBER() OVER(PARTITION BY Vehicle_ID ORDER BY date_on ASC) AS cycle_seq
    FROM vehicle_table
    WHERE status = 'Start' AND Vehicle_ID IS NOT NULL
),
failed_records AS (
    -- 提取所有Failed状态记录，为每个Failed匹配前面最近的Start对应的周期序号
    SELECT 
        f.Vehicle_ID,
        f.date_on AS failed_date,
        MAX(s.cycle_seq) AS matched_cycle
    FROM vehicle_table f
    LEFT JOIN start_records s 
        ON f.Vehicle_ID = s.Vehicle_ID 
        AND f.date_on > s.start_date
    WHERE f.status = 'Failed' AND f.Vehicle_ID IS NOT NULL
    GROUP BY f.Vehicle_ID, f.date_on
)
-- 关联生成最终结果
SELECT 
    s.Vehicle_ID,
    s.start_date AS `Start`,
    COALESCE(MIN(f.failed_date), CURRENT_DATE) AS `Failed/Running`,
    s.cycle_seq AS `Cycle`
FROM start_records s
LEFT JOIN failed_records f 
    ON s.Vehicle_ID = f.Vehicle_ID 
    AND s.cycle_seq = f.matched_cycle
GROUP BY s.Vehicle_ID, s.start_date, s.cycle_seq
ORDER BY s.Vehicle_ID, s.cycle_seq;

适配说明 #

如果使用SQL Server，将代码中的CURRENT_DATE替换为GETDATE()即可；如果是不支持CTE的低版本MySQL，可以把CTE子查询改写为临时表实现相同逻辑。

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