基于Flag列从日期列提取日期范围的技术实现需求

原始表结构说明 #

表包含两列：

• 日期：按顺序排列的日期字段
• Flag：仅取值为'Y'或'N'的标识字段

场景1：提取Flag为‘N’起始至Flag为‘Y’的日期范围 #

需求：筛选所有以连续'N'段开头、紧跟连续'Y'段的日期区间，输出区间的起始日期（from_date）和结束日期（to_date）

实现SQL（以MySQL为例） #

WITH grouped_data AS (
    SELECT 
        日期,
        Flag,
        -- 标记连续相同Flag的分组ID
        SUM(CASE WHEN LAG(Flag) OVER (ORDER BY 日期) != Flag THEN 1 ELSE 0 END) OVER (ORDER BY 日期) AS group_id
    FROM your_table
)
SELECT 
    MIN(g1.日期) AS from_date,
    MIN(g2.日期) AS to_date
FROM grouped_data g1
JOIN grouped_data g2 ON g1.group_id + 1 = g2.group_id
WHERE g1.Flag = 'N' AND g2.Flag = 'Y'
GROUP BY g1.group_id;

场景2：提取以Flag‘Y’起始、中间含Flag‘N’且以Flag‘Y’结束的日期范围 #

需求：筛选所有Y -> N -> Y三段连续的日期区间，输出整个区间的起始日期（第一段Y的首个日期）和结束日期（第三段Y的末次日期）

实现SQL（以MySQL为例） #

WITH grouped_data AS (
    SELECT 
        日期,
        Flag,
        -- 标记连续相同Flag的分组ID
        SUM(CASE WHEN LAG(Flag) OVER (ORDER BY 日期) != Flag THEN 1 ELSE 0 END) OVER (ORDER BY 日期) AS group_id
    FROM your_table
),
group_summary AS (
    SELECT 
        group_id,
        Flag,
        MIN(日期) AS group_start,
        MAX(日期) AS group_end,
        LEAD(Flag) OVER (ORDER BY group_id) AS next_flag,
        LEAD(group_id) OVER (ORDER BY group_id) AS next_group_id
    FROM grouped_data
    GROUP BY group_id, Flag
)
SELECT 
    sc1.group_start AS from_date,
    sc3.group_end AS to_date
FROM group_summary sc1
JOIN group_summary sc2 ON sc1.next_group_id = sc2.group_id
JOIN group_summary sc3 ON sc2.next_group_id = sc3.group_id
WHERE sc1.Flag = 'Y' AND sc2.Flag = 'N' AND sc3.Flag = 'Y';

场景3：提取Flag为‘Y’起始至Flag为‘N’的日期范围 #

需求：筛选所有以连续'Y'段开头、紧跟连续'N'段的日期区间，输出区间的起始日期（段内首个Y的日期）和结束日期（段内首个N的日期）

实现SQL（以MySQL为例） #

WITH grouped_data AS (
    SELECT 
        日期,
        Flag,
        -- 标记连续相同Flag的分组ID
        SUM(CASE WHEN LAG(Flag) OVER (ORDER BY 日期) != Flag THEN 1 ELSE 0 END) OVER (ORDER BY 日期) AS group_id
    FROM your_table
)
SELECT 
    MIN(g1.日期) AS from_date,
    MIN(g2.日期) AS to_date
FROM grouped_data g1
JOIN grouped_data g2 ON g1.group_id + 1 = g2.group_id
WHERE g1.Flag = 'Y' AND g2.Flag = 'N'
GROUP BY g1.group_id;

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