SQLite：将日期合并到范围

我有一个更复杂的表，一年中每天的开放时间，人，一些额外的笔记等。根据需要分组。
但是，我被一个查询卡住了。
| 日期|开启时间|星期几|blahblah无关栏目|
| - ------| - ------| - ------| - ------|
| 2023年1月1日|零|七|...|
| 2023年1月2日|十二至十六|无|...|
| 2023年1月3日|八至十六|第二章|...|
| 2023年1月4日|八至十六|三个|...|
| 2023年1月5日|八至十六|四个|...|
| 2023年1月6日|八至十六|五个|...|
| 2023年1月7日|八至十六|六个|...|
| ...|...|...|...|
| ...|...|...|...|
| ...|...|...|...|
| 2023年1月14日|零|六个|...|
| ...|...|...|...|
| ...|...|...|...|
| ...|...|...|...|

... group_concat(distinct case when day_of_week between 1 and 5 then ('' || strftime('%d.%m ', date)) else 'WD' end) as 'operating_days', ...

其中day_of_week == 0用于所选日期（特殊状态），仅供参考，1 - 7表示周一至周日。
查询的当前输出为：

Opening hours valid on WD and 2023-01-02, 2023-01-07, 2023-02-11, 2023-02-18, 2023-05-01, ,2023-07-08 ,2023-07-15 ,2023-07-22 ,2023-07-29 ,2023-08-05 , 2023-08-12 ,2023-08-19 ,2023-08-26 ,2023-09-02, 2023–09-30.

为了清楚起见，我想将其合并为间隔，例如：“开放时间为周一至周五和2023 - 01 - 02，2023 - 01 - 07，2023 - 02 - 11，...，在7月8日至9月2日和9月30日之间的周六”。
（不要管这里的日期格式，这只是为了说明）。
这是一种差距和岛屿的问题，但我错过了一种方法，如何确定这几天是连续的星期六，并在同一时间的第一个和最后一个间隔。
我已经设法通过铸造和案例将日期分组到工作日和其他日期。我知道这是一种间隙和岛屿问题，但我缺少一种方法来确定这些天是否是连续的星期六，同时是间隔中的第一个和最后一个。
提前非常感谢您的任何提示!

对于开放时间，通常的期望是每周的时间表，并且易于阅读。因此，最佳的group by值将是week of year。由于您要查找From和To星期几，因此似乎除了星期六以外，工作日总是开放的。星期日总是关闭的。但是，如果不是这样，如果可行的话，请随意调整查询以满足您的需要。

在SQLite中，星期天的星期几为0。因此，CTE中的查找表。
按一年中的周对开业日期分组;也可以从/到day of week。

with cte_day_of_week as (
select 1 as dow, 'Monday'    as day_of_week union all
select 2 as dow, 'Tuesday'   as day_of_week union all
select 3 as dow, 'Wednesday' as day_of_week union all
select 4 as dow, 'Thursday'  as day_of_week union all
select 5 as dow, 'Friday'    as day_of_week union all
select 6 as dow, 'Saturday'  as day_of_week union all
select 7 as dow, 'Sunday'    as day_of_week),
cte_opening_dates as (
select group_concat(date)  as opening_dates,
       min(day_of_week)    as from_dow,
       max(day_of_week)    as to_dow,
       strftime('%W',date) as week_of_year
  from office_hour
 where opening_time is not null
 group by week_of_year)
select o.opening_dates  as weekly_opening_dates,
       'From ' || fd.day_of_week ||
       ' to '  || td.day_of_week as day_of_week_range
  from cte_opening_dates o
  join cte_day_of_week fd
    on o.from_dow = fd.dow
  join cte_day_of_week td
    on o.to_dow = td.dow

结果是一个52行的每周计划：

weekly_opening_dates                                             |day_of_week_range      |
-----------------------------------------------------------------+-----------------------+
2023-01-02,2023-01-03,2023-01-04,2023-01-05,2023-01-06,2023-01-07|From Monday to Saturday|
2023-01-09,2023-01-10,2023-01-11,2023-01-12,2023-01-13           |From Monday to Friday  |
2023-01-16,2023-01-17,2023-01-18,2023-01-19,2023-01-20           |From Monday to Friday  |
2023-01-23,2023-01-24,2023-01-25,2023-01-26,2023-01-27           |From Monday to Friday  |
2023-01-30,2023-01-31,2023-02-01,2023-02-02,2023-02-03,2023-02-04|From Monday to Saturday|
2023-02-06,2023-02-07,2023-02-08,2023-02-09,2023-02-10           |From Monday to Friday  |
2023-02-13,2023-02-14,2023-02-15,2023-02-16,2023-02-17           |From Monday to Friday  |
2023-02-20,2023-02-21,2023-02-22,2023-02-23,2023-02-24           |From Monday to Friday  |
2023-02-27,2023-02-28,2023-03-01,2023-03-02,2023-03-03           |From Monday to Friday  |
2023-03-06,2023-03-07,2023-03-08,2023-03-09,2023-03-10           |From Monday to Friday  |
2023-03-13,2023-03-14,2023-03-15,2023-03-16,2023-03-17,2023-03-18|From Monday to Saturday|
2023-03-20,2023-03-21,2023-03-22,2023-03-23,2023-03-24,2023-03-25|From Monday to Saturday|
2023-03-27,2023-03-28,2023-03-29,2023-03-30,2023-03-31           |From Monday to Friday  |
2023-04-03,2023-04-04,2023-04-05,2023-04-06,2023-04-07           |From Monday to Friday  |
2023-04-10,2023-04-11,2023-04-12,2023-04-13,2023-04-14           |From Monday to Friday  |
2023-04-17,2023-04-18,2023-04-19,2023-04-20,2023-04-21           |From Monday to Friday  |
2023-04-24,2023-04-25,2023-04-26,2023-04-27,2023-04-28           |From Monday to Friday  |
2023-05-01,2023-05-02,2023-05-03,2023-05-04,2023-05-05           |From Monday to Friday  |
2023-05-08,2023-05-09,2023-05-10,2023-05-11,2023-05-12,2023-05-13|From Monday to Saturday|
2023-05-15,2023-05-16,2023-05-17,2023-05-18,2023-05-19           |From Monday to Friday  |
2023-05-22,2023-05-23,2023-05-24,2023-05-25,2023-05-26,2023-05-27|From Monday to Saturday|
2023-05-29,2023-05-30,2023-05-31,2023-06-01,2023-06-02           |From Monday to Friday  |
2023-06-05,2023-06-06,2023-06-07,2023-06-08,2023-06-09,2023-06-10|From Monday to Saturday|
2023-06-12,2023-06-13,2023-06-14,2023-06-15,2023-06-16           |From Monday to Friday  |
2023-06-19,2023-06-20,2023-06-21,2023-06-22,2023-06-23           |From Monday to Friday  |
2023-06-26,2023-06-27,2023-06-28,2023-06-29,2023-06-30,2023-07-01|From Monday to Saturday|
2023-07-03,2023-07-04,2023-07-05,2023-07-06,2023-07-07,2023-07-08|From Monday to Saturday|
2023-07-10,2023-07-11,2023-07-12,2023-07-13,2023-07-14           |From Monday to Friday  |
2023-07-17,2023-07-18,2023-07-19,2023-07-20,2023-07-21           |From Monday to Friday  |
2023-07-24,2023-07-25,2023-07-26,2023-07-27,2023-07-28           |From Monday to Friday  |
2023-07-31,2023-08-01,2023-08-02,2023-08-03,2023-08-04           |From Monday to Friday  |
2023-08-07,2023-08-08,2023-08-09,2023-08-10,2023-08-11           |From Monday to Friday  |
2023-08-14,2023-08-15,2023-08-16,2023-08-17,2023-08-18           |From Monday to Friday  |
2023-08-21,2023-08-22,2023-08-23,2023-08-24,2023-08-25           |From Monday to Friday  |
2023-08-28,2023-08-29,2023-08-30,2023-08-31,2023-09-01,2023-09-02|From Monday to Saturday|
2023-09-04,2023-09-05,2023-09-06,2023-09-07,2023-09-08           |From Monday to Friday  |
2023-09-11,2023-09-12,2023-09-13,2023-09-14,2023-09-15,2023-09-16|From Monday to Saturday|
2023-09-18,2023-09-19,2023-09-20,2023-09-21,2023-09-22           |From Monday to Friday  |
2023-09-25,2023-09-26,2023-09-27,2023-09-28,2023-09-29           |From Monday to Friday  |
2023-10-02,2023-10-03,2023-10-04,2023-10-05,2023-10-06           |From Monday to Friday  |
2023-10-09,2023-10-10,2023-10-11,2023-10-12,2023-10-13,2023-10-14|From Monday to Saturday|
2023-10-16,2023-10-17,2023-10-18,2023-10-19,2023-10-20           |From Monday to Friday  |
2023-10-23,2023-10-24,2023-10-25,2023-10-26,2023-10-27,2023-10-28|From Monday to Saturday|
2023-10-30,2023-10-31,2023-11-01,2023-11-02,2023-11-03,2023-11-04|From Monday to Saturday|
2023-11-06,2023-11-07,2023-11-08,2023-11-09,2023-11-10           |From Monday to Friday  |
2023-11-13,2023-11-14,2023-11-15,2023-11-16,2023-11-17           |From Monday to Friday  |
2023-11-20,2023-11-21,2023-11-22,2023-11-23,2023-11-24,2023-11-25|From Monday to Saturday|
2023-11-27,2023-11-28,2023-11-29,2023-11-30,2023-12-01,2023-12-02|From Monday to Saturday|
2023-12-04,2023-12-05,2023-12-06,2023-12-07,2023-12-08,2023-12-09|From Monday to Saturday|
2023-12-11,2023-12-12,2023-12-13,2023-12-14,2023-12-15,2023-12-16|From Monday to Saturday|
2023-12-18,2023-12-19,2023-12-20,2023-12-21,2023-12-22           |From Monday to Friday  |
2023-12-25,2023-12-26,2023-12-27,2023-12-28,2023-12-29,2023-12-30|From Monday to Saturday|

SQLite：将日期合并到范围

1条答案

相关问题

热门标签

最新问答