sql - 如何选择没有间隔的日期间隔？

Question

每一行都有每天的日期，包括开始和结束以及它们是否适用于此查询。当没有间隙时，我需要返回所有天数间隔。

示例表:

+----+---------------------+---------------------+--------+--------------------------------------+
| ID | DATE_START          | DATE_END            | STATUS | COMMENT                              |
|----+---------------------+---------------------+--------+--------------------------------------+
|  1 | 2019-01-01 00:00:00 | 2019-01-02 00:00:00 | 1      |                                      |
|----+---------------------+---------------------+--------+--------------------------------------+
|  2 | 2019-01-02 00:00:00 | 2019-01-03 00:00:00 | 1      |                                      |
|----+---------------------+---------------------+--------+--------------------------------------+|
|    |                     |                     |        | <-- did this gap visually, following |
|    |                     |                     |        | <-- dates are more than 1 day off    |
|----+---------------------+---------------------+--------+--------------------------------------+
| 10 | 2019-02-07 06:00:00 | 2019-02-08 06:00:00 | 1      |                                      |
|----+---------------------+---------------------+--------+--------------------------------------+
| 11 | 2019-02-08 06:00:00 | 2019-02-09 06:00:00 | 1      |                                      |
|----+---------------------+---------------------+--------+--------------------------------------+
| 12 | 2019-02-09 06:00:00 | 2019-02-10 06:00:00 | 1      |                                      |
|----+---------------------+---------------------+--------+--------------------------------------+
| 13 | 2019-02-10 06:00:00 | 2019-02-11 06:00:00 | 0      | <-- gap, as STATUS=0                 |
|----+---------------------+---------------------+--------+--------------------------------------+
| 14 | 2019-02-11 06:00:00 | 2019-02-12 06:00:00 | 1      |                                      |
|----+---------------------+---------------------+--------+--------------------------------------+

结果表应该是这样的:

+---------------------+---------------------+----------+
| INTERVAL_START      | INTERVAL_END        | IDS      |
+---------------------+---------------------+----------+
| 2019-01-01 00:00:00 | 2019-01-03 00:00:00 | 1,2      |
+---------------------+---------------------+----------+
| 2019-02-07 06:00:00 | 2019-02-10 06:00:00 | 10,11,12 |
+---------------------+---------------------+----------+
| 2019-02-11 06:00:00 | 2019-02-12 06:00:00 | 14       |
+---------------------+---------------------+----------+

好的，选择 if STATUS<>0没关系。我纠结的是，我不知道我应该如何开始重新查找，如果第二天也可用，如果是这样，继续直到没有第二天(并收集这些天的 ID)。

由于此查询将包含大量其他数据，因此这根本不是问题。我就是无法理解这些递归的东西。

如果这是尽可能标准的 SQL，那将非常有帮助，因为将来可能会移植此查询。

编辑:哦，正如您在那里看到的时间戳，DATE_START始终与 DATE_END 相同的小时/分钟从前一天开始(如果存在)。

Answer 1

MT0 是正确的，尽管我认为 count ... filter 子句比 sum ... case 更易于阅读:

with t as (
  select 1 as id, timestamp '2019-01-01 00:00:00' as date_start, timestamp '2019-01-02 00:00:00' as date_end, 1 as status union
  select 2 as id, timestamp '2019-01-02 00:00:00' as date_start, timestamp '2019-01-03 00:00:00' as date_end, 1 as status union
  select 10 as id, timestamp '2019-01-07 06:00:00' as date_start, timestamp '2019-01-08 06:00:00' as date_end, 1 as status union
  select 11 as id, timestamp '2019-01-08 06:00:00' as date_start, timestamp '2019-01-09 06:00:00' as date_end, 1 as status union
  select 12 as id, timestamp '2019-01-09 06:00:00' as date_start, timestamp '2019-01-10 06:00:00' as date_end, 1 as status union
  select 13 as id, timestamp '2019-01-10 06:00:00' as date_start, timestamp '2019-01-11 06:00:00' as date_end, 0 as status union
  select 14 as id, timestamp '2019-01-11 06:00:00' as date_start, timestamp '2019-01-12 06:00:00' as date_end, 1 as status
), t2 as (
  select t.*, lag(date_end) over (order by date_start) as prev_date_end
  from t
  where status = 1
), t3 as (
  select t2.*, count(1) filter (where date_start is distinct from prev_date_end) over (order by date_start) as g
  from t2
)
select min(date_start), max(date_end), string_agg(cast(id as text),',') from t3
group by g
order by g

工作于 https://www.db-fiddle.com/ PG 9.6 版本。