MySQL实现按availability统计连续多日期段计数的查询方法

核心实现思路 #

针对带单日期多可用性值的表结构，采用经典的连续段分组（岛屿问题）计算逻辑，步骤如下：

1. 按availability字段拆分计算队列，避免不同可用性标识的日期互相干扰，同时对同日期同可用性的重复记录做去重
2. 对每个可用性标识下的日期按时间排序，用「日期减去对应行号」的差值作为连续段的分组标记——连续日期序列内该标记值固定，一旦出现日期断裂，标记值会发生变化
3. 按分组标记聚合每个连续段的最早、最晚日期，以及段内总日期数
4. 过滤掉仅包含单条日期的孤立记录，仅保留段内日期数≥2的连续段

可直接执行的查询语句 #

SELECT
    availability,
    MIN(date) AS start_date,
    MAX(date) AS end_date,
    COUNT(date) AS date_count
FROM (
    SELECT
        date,
        availability,
        -- 按可用性分组计算日期排序后的行号，生成连续段标识
        DATE_SUB(date, INTERVAL ROW_NUMBER() OVER (
            PARTITION BY availability
            ORDER BY date
        ) DAY) AS segment_tag
    FROM your_table_name
    -- 同日期同可用性重复记录去重，避免计数虚高
    GROUP BY date, availability
) AS base_calc
GROUP BY availability, segment_tag
-- 过滤孤立单天记录，仅保留日期跨度≥2天的连续段
HAVING date_count >= 2
ORDER BY start_date;

针对样例数据的匹配结果 #

你给出的测试数据场景下，查询输出内容为：

• 可用性标识为y的连续段：起止日期2021-01-15 ~ 2021-01-17，总日期数3
• 2021-01-20对应的y记录因为和前面的y段（15-17）存在日期断裂，仅单条记录，会被HAVING条件过滤，不会输出
• 可用性标识为n的连续段：若2021-01-20的n记录存在，会和18、19号的n记录聚合为2021-01-18 ~ 2021-01-20的连续段，总日期数3；若20号的n记录不存在，则输出2021-01-18 ~ 2021-01-19的连续段，总日期数2

注意：如果使用的是MySQL 8.0以下版本不支持窗口函数，可以用用户自定义变量替代内层的ROW_NUMBER()计算逻辑，整体分组和过滤规则完全一致。

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