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

本文介绍了计算时间范围内的分组间隙的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

I'm looking to find how many grouped gaps exist for a given 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

更新以进行澄清：

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

Data is stored with start and end timestamps per type. For example:

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。基于相同的测试夹具：

Here's a variant on wildplasser's answer that uses windows instead of a CTE. Based on the same test fixture:

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相同的事情，添加一些额外的条目，这些条目在边界的端点不做任何贡献，以便找到没有任何东西覆盖边界的组...

This is based on using sum() as a window aggregate adding 1 for a range start and subtracting 1 for a range end, and then looking for points where the running sum goes to 0 within the target range. I had to do much the same thing as wildplasser did, add a couple of extra entries that don't contribute anything at the endpoints of the boundary so that groups where there is nothing covering the boundary are found...

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

This seems to cost less on the test data, but I think it might be highly dependent on not having much data in the tables to go through. With some rearranging (which would make it even harder to read) it can work off just two full scans of tmp.gaps (one of which is just getting distinct ztypes).