伙计们,我有一个很大的查询结果,它向我显示了骑手(在rider_id
列中)注销应用程序(event
列)的时间(在local_time
列中),因此event
列有两个不同的值,"authentication_complete"和"注销"。
event_date rider_id event local_time
20200329 100695 authentication_complete 20:07:09
20200329 100884 authentication_complete 12:00:51
20200329 100967 logout 10:53:17
20200329 100967 authentication_complete 10:55:24
20200329 100967 logout 11:03:28
20200329 100967 authentication_complete 11:03:47
20200329 101252 authentication_complete 7:55:21
20200329 101940 authentication_complete 8:58:44
20200329 101940 authentication_complete 17:19:57
20200329 102015 authentication_complete 14:20:27
20200329 102015 logout 22:47:50
20200329 102015 authentication_complete 22:48:34
我想要实现的是对于每个曾经注销的骑手,在一列中我想获取他们注销的时间,而在另一列中,我想获取该骑手注销事件之后的事件"authentication_complete"的时间。通过这种方式,我可以看到每个乘客退出应用程序的时间段。 我想要获得的查询结果如下所示。
event_date rider_id time_of_logout authentication_complete_right_after_the_logout
20200329 100967 10:53:17 10:55:24
20200329 100967 11:03:28 11:03:47
20200329 102015 22:47:50 22:48:34
这是一个非常不干净的数据集,到目前为止,我能够清理这么多,但是在这一步,我感到非常卡住。我正在研究像lag()
这样的函数,但由于数据是 180,000 行,一个rider_id可以有多个名为"logout"的事件,并且同一rider_id有多个名为"authentication_complete"的连续事件,这特别令人困惑。我真的很感激任何帮助。谢谢!
我想你想要lead()
:
select event_date, rider_id, date, local_time as logout_date,
authentication_date
from (select t.*,
lead(local_time) over (partition by event_date, rider_id order by local_time) as authentication_date
from t
) t
where event = 'logout';
这假定下一个事件确实是身份验证,如示例数据中所示。 如果不是这种情况,则无需指定要执行的操作。
如果您特别想要下一个身份验证日期,则可以使用min()
:
select event_date, rider_id, date, local_time as logout_date,
authentication_date
from (select t.*,
min(case when event = 'authentication_complete' then local_time end) over (partition by event_date, rider_id order by local_time desc) as authentication_date
from t
) t
where event = 'logout';