如何基于状态分组日期，汇总车辆的状态及生效财政年份？

问题描述 #

现有车辆数据库，每辆车对应多条分配记录，每条记录包含状态、生效日和到期日。同一状态可能因续期产生多条连续的记录，需按车辆汇总每个状态及其生效的年份范围，目标格式如下：

ID | Status and Years
----+-----------------------------------------------
 0  | A (2020-2021), B (2021-2022)
 1  | Z (2022-2023)
 2  | A (2012-2013), Z (2013-2015)

当前查询的原始数据（来自CAR_ASGNMT表）：

SELECT Id, Status, Effective_dt, Expiration_dt
FROM CAR_ASGNMT

返回结果：

Id | Status | Effective_dt | Expiration_dt
---+--------+--------------+---------------
 0 |   A    |  28-SEP-2020 |  06-DEC-2020
 0 |   A    |  07-DEC-2020 |  28-MAR-2021
 0 |   A    |  28-MAR-2021 |  26-SEP-2021
 0 |   A    |  27-SEP-2021 |  05-DEC-2021
 0 |   B    |  06-DEC-2021 |  26-MAR-2022

解决方案 #

核心思路 #

1. 合并连续状态记录：将同一车辆、同一状态的连续记录（上一条到期日的次日为当前生效日）合并为一个完整时间段。
2. 格式化年份范围：为每个合并后的状态时间段生成状态 (起始年-结束年)格式的字符串。
3. 按车辆聚合：将同一车辆的所有状态字符串用逗号分隔汇总。

针对不同数据库的SQL实现 #

1. Oracle

WITH status_segments AS (
    SELECT 
        Id,
        Status,
        Effective_dt,
        Expiration_dt,
        -- 标记新状态段：当前记录与上一条不连续时生成新ID
        SUM(CASE WHEN LAG(Expiration_dt) OVER (PARTITION BY Id, Status ORDER BY Effective_dt) + INTERVAL '1' DAY = Effective_dt THEN 0 ELSE 1 END) 
            OVER (PARTITION BY Id, Status ORDER BY Effective_dt) AS segment_id
    FROM CAR_ASGNMT
),
merged_segments AS (
    SELECT 
        Id,
        Status,
        MIN(Effective_dt) AS start_dt,
        MAX(Expiration_dt) AS end_dt
    FROM status_segments
    GROUP BY Id, Status, segment_id
),
status_year_ranges AS (
    SELECT 
        Id,
        Status || ' (' || EXTRACT(YEAR FROM start_dt) || '-' || EXTRACT(YEAR FROM end_dt) || ')' AS status_year_str
    FROM merged_segments
)
SELECT 
    Id,
    LISTAGG(status_year_str, ', ') WITHIN GROUP (ORDER BY start_dt) AS "Status and Years"
FROM status_year_ranges
GROUP BY Id
ORDER BY Id;

2. MySQL

WITH status_segments AS (
    SELECT 
        Id,
        Status,
        Effective_dt,
        Expiration_dt,
        SUM(CASE WHEN LAG(Expiration_dt) OVER (PARTITION BY Id, Status ORDER BY Effective_dt) + INTERVAL 1 DAY = Effective_dt THEN 0 ELSE 1 END) 
            OVER (PARTITION BY Id, Status ORDER BY Effective_dt) AS segment_id
    FROM CAR_ASGNMT
),
merged_segments AS (
    SELECT 
        Id,
        Status,
        MIN(Effective_dt) AS start_dt,
        MAX(Expiration_dt) AS end_dt
    FROM status_segments
    GROUP BY Id, Status, segment_id
),
status_year_ranges AS (
    SELECT 
        Id,
        CONCAT(Status, ' (', YEAR(start_dt), '-', YEAR(end_dt), ')') AS status_year_str
    FROM merged_segments
)
SELECT 
    Id,
    GROUP_CONCAT(status_year_str ORDER BY start_dt SEPARATOR ', ') AS `Status and Years`
FROM status_year_ranges
GROUP BY Id
ORDER BY Id;

3. PostgreSQL

WITH status_segments AS (
    SELECT 
        Id,
        Status,
        Effective_dt,
        Expiration_dt,
        SUM(CASE WHEN LAG(Expiration_dt) OVER (PARTITION BY Id, Status ORDER BY Effective_dt) + INTERVAL '1 day' = Effective_dt THEN 0 ELSE 1 END) 
            OVER (PARTITION BY Id, Status ORDER BY Effective_dt) AS segment_id
    FROM CAR_ASGNMT
),
merged_segments AS (
    SELECT 
        Id,
        Status,
        MIN(Effective_dt) AS start_dt,
        MAX(Expiration_dt) AS end_dt
    FROM status_segments
    GROUP BY Id, Status, segment_id
),
status_year_ranges AS (
    SELECT 
        Id,
        Status || ' (' || EXTRACT(YEAR FROM start_dt) || '-' || EXTRACT(YEAR FROM end_dt) || ')' AS status_year_str
    FROM merged_segments
)
SELECT 
    Id,
    STRING_AGG(status_year_str, ', ' ORDER BY start_dt) AS "Status and Years"
FROM status_year_ranges
GROUP BY Id
ORDER BY Id;

自定义财政年度规则 #

如果你的财政年度不是自然年（例如7月1日至次年6月30日），需调整年份提取逻辑。以Oracle为例，修改status_year_ranges部分：

status_year_ranges AS (
    SELECT 
        Id,
        Status || ' (' || 
        -- 计算起始财年
        CASE WHEN EXTRACT(MONTH FROM start_dt) >=7 THEN EXTRACT(YEAR FROM start_dt) ELSE EXTRACT(YEAR FROM start_dt)-1 END || '-' ||
        -- 计算结束财年
        CASE WHEN EXTRACT(MONTH FROM end_dt) >=7 THEN EXTRACT(YEAR FROM end_dt)+1 ELSE EXTRACT(YEAR FROM end_dt) END || ')' AS status_year_str
    FROM merged_segments
)

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