计算时间范围内的分组时间间隔[英] Count Grouped Gaps In Time For Time Range

本文是小编为大家收集整理的关于计算时间范围内的分组时间间隔的处理方法,想解了计算时间范围内的分组时间间隔的问题怎么解决?计算时间范围内的分组时间间隔问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

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

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

推荐答案

这是 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 进行两次完整扫描(其中一次只是获得不同的 ztypes).

本文地址:https://www.itbaoku.cn/post/1764218.html