如何编写SQL查询，从给定表数据生成指定日期范围输出？

问题回顾 #

现有表结构及数据如下：

需要按cust_id分组，提取attendance_ind=1的连续日期段，输出起始日期（月-日格式）和对应日期范围，目标输出：

通用SQL实现（兼容PostgreSQL/Oracle等） #

WITH filtered_records AS (
    SELECT 
        cust_id,
        dates,
        TO_CHAR(dates, 'Mon-dd') AS formatted_date
    FROM your_table_name
    WHERE attendance_ind = 1
),
interval_groups AS (
    SELECT 
        cust_id,
        formatted_date,
        dates,
        -- 生成连续区间的分组标识：连续月份的该值保持一致
        ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY dates)
        - (EXTRACT(YEAR FROM dates)*12 + EXTRACT(MONTH FROM dates)) AS group_id
    FROM filtered_records
),
interval_summary AS (
    SELECT 
        cust_id,
        MIN(formatted_date) AS start_date,
        CONCAT(MIN(formatted_date), ' to ', MAX(formatted_date)) AS date_range
    FROM interval_groups
    GROUP BY cust_id, group_id
)
SELECT 
    cust_id,
    start_date AS "date",
    date_range
FROM interval_summary
ORDER BY cust_id, start_date;

MySQL 适配版本 #

WITH filtered_records AS (
    SELECT 
        cust_id,
        dates,
        DATE_FORMAT(dates, '%b-%d') AS formatted_date
    FROM your_table_name
    WHERE attendance_ind = 1
),
interval_groups AS (
    SELECT 
        cust_id,
        formatted_date,
        dates,
        ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY dates)
        - (YEAR(dates)*12 + MONTH(dates)) AS group_id
    FROM filtered_records
),
interval_summary AS (
    SELECT 
        cust_id,
        MIN(formatted_date) AS start_date,
        CONCAT(MIN(formatted_date), ' to ', MAX(formatted_date)) AS date_range
    FROM interval_groups
    GROUP BY cust_id, group_id
)
SELECT 
    cust_id,
    start_date AS `date`,
    date_range
FROM interval_summary
ORDER BY cust_id, start_date;

实现思路 #

1. 过滤有效记录：先筛选出attendance_ind=1的行，同时将日期格式化为月-日形式。
2. 生成连续区间分组：通过ROW_NUMBER()按用户分组排序，结合日期的年月数值计算分组ID——连续的月份中，该ID会保持相同，以此区分非连续的区间。
3. 聚合区间信息：按用户和分组ID聚合，取每个区间的起始和结束格式化日期，拼接成日期范围。
4. 输出结果：整理字段顺序并排序，得到目标格式的数据。

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