时间戳

首页 > 解决方案 > 表中两个事件之间的时间差（SQL）

sql - 表中两个事件之间的时间差（SQL）

问题描述

假设我有一张桌子

ID 事件 时间
1 事件1 3:30
1 事件2 3:30
3 事件1 3:35
1 事件3 3:40
3 事件3 3:50
1 事件1 3:55
1 事件3 3:56

现在这些事件一次又一次地发生。我想编写和 sql 查询来计算每个 id 的两个不同事件之间的所有时间差。例如，如果我必须为每个 id 计算 event3 的当前时间与 event1 的最近一次时间之间的所有时间差。预期的结果是。

ID 事件1次 事件3时间 区别
1 3:30 3:40 10
3 3:35 3:50 15
1 3:55 3:56 1

这些事件将一次又一次地发生，并且该表可以包含两个事件之间的 n 个其他事件。对于 event3 发生的所有时间，我想找到 event1 发生的最近时间，然后计算两者之间的差异（按 id 分区）。

类比 - 这可以与事务数据库进行比较，其中事件是特定人员（id）的事务事件。我想要一张表格，给出该特定人发生的所有两次事件（例如收到的金额、支付的金额）之间的时间差。

我将在 spark 上运行此查询，因为我的数据很大，因此我想避免连接。我可以为此使用窗口函数（lag，last_value）等吗？我在为当前事件 3 选择上一个事件 1 时遇到问题。

标签： sqlapache-spark-sql

解决方案

您确实可以使用lag，但请记住，这仅适用于每个event3只有一对配对，event1否则此查询将中断。所以会有更好的解决方案，但仅针对这种情况：

select id,l_time event1time,time event3time,(time-l_time) as diff from (
  select id,event,
  lag(event) over(partition by id order by time) l_event,
  time,
  lag(time)over(partition by id order by time) l_time 
  from [table]
  where event in('event1','event3') )a
where event = 'event3' and l_event = 'event1'

同样在我用于时间列的伪数据int中，因此可能会稍作调整以获得时差。
这是db<>fiddle以便更好地检查。

推荐阅读