主页 / 问题 / 1475309
Accepted
Ayo
Asked:2022-12-08 20:09:38 +0800 CST2022-12-08 20:09:38 +0800 CST

最大时隙交叉数

  • 772

有一个表格列出了事件的开始和结束。事件有不同的类型。我们需要找到同时发生的事件的最大数量。

表格示例：

事件编号 时间_开始 时间结束 事件类型
一 2022-10-01 15:01:21 2022-10-01 15:03:21 一个
2个 2022-10-01 15:02:13 2022-10-01 15:03:10 一个
3个 2022-10-13 14:18:11 2022-10-13 15:07:01 乙
四 2022-10-10 12:04:51 2022-10-10 12:06:28 乙
5个 2022-10-01 15:03:22 2022-10-01 15:05:43 一个

例如，在这个表中，对于类型 A，有一个事件 id 1 和 2 同时发生的时刻，对于类型 B，事件不相交，即 结果将是这样的：

事件类型 最大限度（）
一个 2个
乙 一

往哪个方向挖，不清楚:(

BD-垂直

sql

1 个回答

  1. Best Answer

    让我们创建一个表并将数据加载到其中。我们在这里不会对 ID 感兴趣。

    create table events (
    time_start datetime,
    time_end datetime,
    event_type varchar
);

insert into events values
('2022-10-01 15:01:21', '2022-10-01 15:03:21',  'A'),
('2022-10-01 15:02:13', '2022-10-01 15:03:10',  'A'),
('2022-10-13 14:18:11', '2022-10-13 15:07:01',  'B'),
('2022-10-10 12:04:51', '2022-10-10 12:06:28',  'B'),
('2022-10-01 15:03:22', '2022-10-01 15:05:43',  'A');

    让我们想想如何在一张纸/日历/谷歌日历上解决这个问题。您可以在日历上的每个事件的开始和结束之间绘制一个条带。不同类型的事件 - 不同的颜色。然后再次从头到尾，记下每个时间点有多少条相同颜色的条纹。在下一个事件开始或结束的地方这样做就足够了。然后从记录的每种事件的总和中找出最大值。

    在此处输入图像描述

    仍然需要在 SQL 中重现这一点。让我们将每个事件分成两行：开始和结束。此外，我们将添加一个列来指示事件数量的变化。开头将指示事件数 +1，结尾为 -1。让我们按时间排序以获得递增的时间序列。

    select time_start as ts, event_type, +1 as cnt from events
union all
select time_end   as ts, event_type, -1 as cnt from events

ts  event_type  cnt
2022-10-01 15:01:21.000000  A   1
2022-10-01 15:02:13.000000  A   1
2022-10-01 15:03:22.000000  A   1
2022-10-10 12:04:51.000000  B   1
2022-10-13 14:18:11.000000  B   1
2022-10-01 15:03:21.000000  A   -1
2022-10-01 15:03:10.000000  A   -1
2022-10-01 15:05:43.000000  A   -1
2022-10-10 12:06:28.000000  B   -1
2022-10-13 15:07:01.000000  B   -1

    现在您需要为每一行找到其上方所有 cnt 值的总和，并按个人进行细分event_type。这是通过运行/累积总和完成的。在 Vertica 中 -解析和：

    select ts, event_type, cnt, sum(cnt) OVER (PARTITION BY event_type ORDER BY ts) as cnt_cum from (
    select time_start as ts, event_type, +1 as cnt from events
    union all
    select time_end as ts, event_type, -1 as cnt from events
) e1

ts  event_type  cnt cnt_cum
2022-10-01 15:01:21.000000  A    1  1
2022-10-01 15:02:13.000000  A    1  2
2022-10-01 15:03:10.000000  A   -1  1
2022-10-01 15:03:21.000000  A   -1  0
2022-10-01 15:03:22.000000  A    1  1
2022-10-01 15:05:43.000000  A   -1  0
2022-10-10 12:04:51.000000  B    1  1
2022-10-10 12:06:28.000000  B   -1  0
2022-10-13 14:18:11.000000  B    1  1
2022-10-13 15:07:01.000000  B   -1  0

    cnt_cum仍然需要为每个找到最大值event_type：

    select event_type, max(cnt_cum) from (
  select ts, event_type, cnt, sum(cnt) OVER (PARTITION BY event_type ORDER BY ts) as cnt_cum from (
    select time_start as ts, event_type, +1 as cnt from events
    union all
    select time_end as ts, event_type, -1 as cnt from events
  ) e1
order by ts
) e2 group by event_type;

event_type  max
B   1
A   2
    • 2

相关问题