计算时间范围内的分组间隔时间

一尘不染

计算时间范围内的分组间隔时间

sql

我正在寻找给定时间范围内存在多少分组间隙。

starting range: 2012-01-12 00:00:00
  ending range: 2012-01-18 59:59:59

大致翻译为：

type  10 11 12 13 14 15 16 17 18 19 20 
a        |--========]
a                             |==------]
b                 |==============--]
c     |-----===========]
d        |--=====================------]

相同的数据按类型分组：

a        |--========]         |==------]
b                 |==============--]
c     |-----===========]
d        |--=====================------]

导致：

type  gap
---------
a     1  (yes)
b     1  (yes)
c     1  (yes)
d     0  (no)

最终…

SUM(gap) AS gaps
----------------
3

更新以进行澄清：

每种类型的数据均以开始和结束时间戳记存储。例如：

id  type  start_datetime       end_datetime
--------------------------------------------------
1   a     2012-01-11 00:00:00  2012-01-14 59:59:59
2   a     2012-01-18 00:00:00  2012-01-20 59:59:59
3   b     2012-01-14 00:00:00  2012-01-19 59:59:59
4   c     2012-01-10 00:00:00  2012-01-15 59:59:59
5   d     2012-01-11 00:00:00  2012-01-20 59:59:59

阅读 181

2021-05-16

共1个答案

一尘不染

这是wildplasser回答的一种变体，它使用Windows而不是CTE。基于相同的测试夹具：

select ztype, count(*) as gaps
from (
    select ztype, datetime, sum(n) over(partition by ztype order by datetime asc) as level
    from (
        select id, ztype, start_datetime as datetime, 1 as n from tmp.gaps
        union all
        select id, ztype, end_datetime, -1 from tmp.gaps
        union all
        select 0, ztype, '2012-01-12 00:00:00', 0 from (select distinct ztype from tmp.gaps) z
        union all
        select 0, ztype, '2012-01-19 00:00:00', 0 from (select distinct ztype from tmp.gaps) z
    ) x
) x
where level = 0 and datetime >= '2012-01-12 00:00:00' and datetime < '2012-01-19 00:00:00'
group by ztype
;

这是基于使用sum（）作为窗口聚合，将1作为范围起点，将1减去范围终点，然后在目标范围内查找运行总和变为0的点。我必须做与wildplasser差不多的事情，添加一些额外的条目，这些条目在边界的端点处不做任何贡献，以便找到没有任何东西覆盖边界的组…

这似乎减少了测试数据的开销，但是我认为这可能很大程度上取决于表中没有太多数据要通过。进行一些重新排列（这将使其变得更难阅读），它可以仅对tmp.gaps进行两次完整扫描（其中一次只是获取不同的ztype）。

2021-05-16