让我们假设我有一个表
Id | 事件 | 时间|
---|---|---|
1 | 事件1 | 3:30|
1 | 事件2 | 3:30 |
3 | 事件1 | 3:35 |
1 | 事件3 | 3:40 |
事件3 | 3:50 | |
1 | 事件1 | 3:55 |
1 | 事件3 | 3:56 |
您可以使用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<gt;仔细检查。
SparkSQL没有时间数据类型,所以我必须假设time
列是一个字符串。这意味着您需要将其转换为数字。
所以,你的问题可以归结为1:
- 如何处理多个id/事件对
- 如何将数据透视到单独的列中
- 如何做减法
首先,我将枚举id/事件对。第二,条件聚合。第三个,字符串拆分魔法:
那就是:
with t as (
select id,
max(case when event = 'event1' then time end) as event1_time,
max(case when event = 'event3' then time end) as evente_time
from (select t.*,
row_number() over (partition by id, event order by time) as seqnum
from mytable t
) t
group by id, seqnum
)
select t.*,
((split(event3_time, ':')[0] * 60 + split(event3_time, ':')[1]) -
(split(event1_time, ':')[0] * 60 + split(event1_time, ':')[1])
) as diff
from t;