SQLite实现日期合并为时间区间的查询问题求助

核心逻辑 #

你要解决的是把分散的同周几日期合并成连续区间，本质是Gaps and Islands问题——把连续的同属性（同周几）数据归为一个“岛”，再提取每个岛的首尾日期生成区间文本。

步骤1：给连续同周几日期打分组标签 #

先对非特殊状态（day_of_week != 0）的日期按周几分组、按日期升序排序，然后用窗口函数生成分组ID：

• 对每个周几的日期，计算日期 - (行号-1)天，连续的同周几日期会得到同一个分组ID（因为每过一周，日期加7天，行号加1，差值刚好抵消，结果一致）。

SQL代码片段：

WITH grouped_dates AS (
    SELECT 
        date,
        day_of_week,
        -- 生成连续同周几的分组标识
        date - INTERVAL (ROW_NUMBER() OVER (PARTITION BY day_of_week ORDER BY date) - 1) DAY AS island_id
    FROM your_table
    WHERE day_of_week != 0
)

步骤2：提取每个分组的首尾日期 #

基于上面的分组，聚合每个周几分组的最小、最大日期，同时统计该组的日期数量：

, island_ranges AS (
    SELECT
        day_of_week,
        MIN(date) AS start_date,
        MAX(date) AS end_date,
        COUNT(*) AS date_count
    FROM grouped_dates
    GROUP BY day_of_week, island_id
)

步骤3：生成格式化的区间文本 #

根据每组的日期数量，生成对应的描述：

• 如果只有1个日期：显示「YYYY-MM-DD 星期X」
• 如果是多个连续日期：显示「YYYY-MM-DD 至 YYYY-MM-DD 星期X」

同时需要把day_of_week映射成星期名称，用CASE语句即可：

CASE day_of_week 
    WHEN 1 THEN '周一' WHEN 2 THEN '周二' WHEN 3 THEN '周三'
    WHEN 4 THEN '周四' WHEN 5 THEN '周五' WHEN 6 THEN '周六' WHEN 7 THEN '周日'
END

步骤4：合并所有结果 #

把工作日说明（如果包含所有周一至周五，直接写「周一至周五」）、特殊日期（day_of_week=0）、同周几的区间/单个日期拼接成最终的描述文本，用GROUP_CONCAT合并多个条目。

额外注意事项 #

• 清理无效数据：比如你当前输出里的空逗号，要提前过滤掉日期为空的记录
• 区分全域工作日和部分工作日：如果你的数据里包含所有周一到周五，直接用「周一至周五」；如果只是部分工作日，需要单独列出对应的日期或区间

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