mysql - 每小时持续时间的 SQL 计数事件

Question

我有一个事件的持续时间数据（例如，在餐厅吃饭），我想知道在任何给定小时内发生了多少事件。数据如下所示：

Event     |   Start Time    |   End Time
-----------------------------------------
  1       |   12:03         |   14:20
  2       |   12:30         |   12:50
  3       |   13:05         |   14:45
  4       |   14:01         |   14:49

我还可以使用“持续时间”来替代“结束时间”。我正在寻找的结果是这样的：

  Hour     |     Count
-----------------------
   12      |     2
   13      |     2
   14      |     3

在第 12 小时，发生了两个事件（1 和 2），第 13 小时也发生了两个事件（1 和 3），第 14 小时发生了三个事件（1、3 和 4）。

我可以通过循环以编程方式执行此操作。我可以计算事件在 SQL 中开始（或结束）的时间。但我真的很想弥合差距并在 SQL 中做到这一点，但我想不出办法。

标签： mysqlsqldatedatetimemariadb

Answer 1

如果您正在运行 MySQL 8.0，则可以使用UNION ALL、 窗口函数和聚合，如下所示：

select hr, sum(sum(cnt)) over(order by hr) cnt
from (
    select hour(start_time) hr, 1 cnt from mytable
    union all select hour(end_time) + 1, -1 from mytable
) t
group by hr

DB Fiddle 上的演示：

小时 | cnt
-: | --:
12 | 2
13 | 2
14 | 3
15 | 0