如何使用SQL查询满足指定条件的最长连续天数及对应起止日期

针对你这种日期+字段名+字段值的长表结构，我们用日期分组间隙法实现需求，兼容表中缺日期、缺字段记录的场景，同时支持输出连续周期的起止日期和天数。 #

核心逻辑说明 #

1. 先过滤出符合目标条件的记录（比如要算咖啡可用就筛field_name = 'coffee_available' and field_value = 1）
2. 对过滤后的记录按日期升序排序，生成递增行号
3. 用日期 - 行号天数生成分组标识：连续的日期减去递增的行号，得到的结果是同一个固定值，相同值的记录属于同一个连续周期
4. 按分组标识聚合，计算每个周期的天数、起止日期，排序取最大值即可

单指标查询示例（以咖啡可用最长连续周期为例） #

WITH filtered_data AS (
    -- 过滤出符合条件的记录
    SELECT date
    FROM your_table_name
    WHERE field_name = 'coffee_available' 
      AND field_value = 1
),
ranked_data AS (
    -- 给日期排序生成行号
    SELECT date,
           ROW_NUMBER() OVER (ORDER BY date) AS rn
    FROM filtered_data
),
grouped_data AS (
    -- 生成连续周期分组标识
    SELECT date,
           DATE_SUB(date, INTERVAL rn DAY) AS group_flag -- 不同SQL方言日期函数可能有差异，比如PostgreSQL用date - rn * INTERVAL '1 day'
    FROM ranked_data
)
-- 聚合计算每个周期的信息，取最长的
SELECT MIN(date) AS start_date,
       MAX(date) AS end_date,
       DATEDIFF(MAX(date), MIN(date)) + 1 AS consecutive_days
FROM grouped_data
GROUP BY group_flag
ORDER BY consecutive_days DESC
LIMIT 1;

样例数据运行结果 #

多指标批量查询示例（同时算咖啡可用、无茶可用的最长连续周期） #

WITH filtered_data AS (
    -- 同时过滤多个目标条件
    SELECT field_name, date
    FROM your_table_name
    WHERE (field_name = 'coffee_available' AND field_value = 1)
       OR (field_name = 'tea_available' AND field_value = 0)
),
ranked_data AS (
    -- 按不同字段分区排序生成行号
    SELECT field_name,
           date,
           ROW_NUMBER() OVER (PARTITION BY field_name ORDER BY date) AS rn
    FROM filtered_data
),
grouped_data AS (
    -- 按字段+分组标识区分不同指标的连续周期
    SELECT field_name,
           date,
           DATE_SUB(date, INTERVAL rn DAY) AS group_flag
    FROM ranked_data
},
period_stats AS (
    -- 计算每个指标下所有连续周期的信息
    SELECT field_name,
           MIN(date) AS start_date,
           MAX(date) AS end_date,
           DATEDIFF(MAX(date), MIN(date)) + 1 AS consecutive_days,
           -- 给每个指标的周期按长度排序，取最长的
           ROW_NUMBER() OVER (PARTITION BY field_name ORDER BY DATEDIFF(MAX(date), MIN(date)) + 1 DESC) AS period_rn
    FROM grouped_data
    GROUP BY field_name, group_flag
)
SELECT field_name, start_date, end_date, consecutive_days
FROM period_stats
WHERE period_rn = 1;

样例数据运行结果 #

特殊场景适配 #

如果你的业务逻辑是「某个字段某天没有记录时，默认延续上一次的字段值，不算中断」，需要先按如下步骤补全数据再运行上面的逻辑：

1. 先生成所有要统计的日期的全量序列
2. 按字段名分区，用LAG窗口函数补全缺失日期的field_value
3. 再按上面的逻辑计算连续周期

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