时间戳

首页 > 解决方案 > 比较 Big Query 中 A 列具有不同值而 B 列具有相同值的两个连续行中的时间戳

sql - 比较 Big Query 中 A 列具有不同值而 B 列具有相同值的两个连续行中的时间戳

问题描述

伙计们，我有一个很大的查询结果，它显示了local_time骑手（在列中rider_id）退出应用程序（列）的时间（在列中event），因此列有两个不同的值event，“authentication_complete”和“logout ”。

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”的连续事件，这更加令人困惑。我真的很感激任何帮助。谢谢！

标签： sqlgoogle-bigquerydata-cleaningdata-wrangling

解决方案

我想你想要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';

推荐阅读