sql - 根据它们之间的间隔对时间戳进行分组

Question

我在 Hive (SQL) 中有一个表，其中包含一堆需要分组的时间戳，以便根据时间戳之间的时间差创建单独的 session 。

例子:
考虑以下时间戳(为简单起见，以 HH:MM 给出):
9.00
9.10
9.20
9.40
9.43
10.30
10.45
11.25
12.30
12.33等等..

所以现在，在下一个时间戳的 30 分钟内的所有时间戳都属于同一个 session ，
即 9.00,9.10,9.20,9.40,9.43 形成 1 个 session 。

但由于 9.43 和 10.30 之间的差异超过 30 分钟，时间戳 10.30 属于不同的 session 。同样，10.30 和 10.45 属于一个交易日。

创建这些 session 后，我们必须获得该 session 的最小时间戳和最大时间戳。

我试图用它的 LEAD 减去当前时间戳，并在它大于 30 分钟时放置一个标志，但我对此有困难。

你们的任何建议将不胜感激。如果问题不够清楚，请告诉我。

此示例数据的预期输出:

Session_start   Session_end
9.00                9.43
10.30               10.45
11.25               11.25 (same because the next time is not within 30 mins)
12.30               12.33

希望这可以帮助。

Answer 1

所以它不是 MySQL 而是 Hive。我不知道 Hive，但如果它支持 LAG，就像你说的，试试这个 PostgreSQL 查询。您可能必须更改时间差计算，这通常从一个 dbms 到另一个不同。

select min(thetime) as start_time, max(thetime) as end_time
from
(
  select thetime, count(gap) over (rows between unbounded preceding and current row) as groupid
  from
  (
    select thetime, case when thetime - lag(thetime) over (order by thetime) > interval '30 minutes' then 1 end as gap
    from mytable
  ) times
) groups
group by groupid
order by min(thetime);

查询查找差距，然后使用差距计数的运行总数来构建组 ID，其余的就是聚合。

SQL fiddle : http://www.sqlfiddle.com/#!17/8bc4a/6 。