文章/答案/技术大牛

发布

社区首页 >问答首页 >在Big Query中比较两个连续行中的时间戳，这两个行的A列的值不同，B列的值相同

问在Big Query中比较两个连续行中的时间戳，这两个行的A列的值不同，B列的值相同
EN

Stack Overflow用户

提问于 2020-04-01 05:46:39

回答 1查看 76关注 0票数 0

伙计们，我有一个很大的查询结果，它显示了骑手(在rider_id列)注销应用程序(event列)的时间(在local_time列中)，所以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”的连续事件，这是非常令人困惑的。我真的很感谢任何人的帮助。谢谢!

google-bigquery

data-cleaning

data-wrangling

sql

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-04-01 05:53:50

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

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/60960431

复制

相似问题

问在Big Query中比较两个连续行中的时间戳，这两个行的A列的值不同，B列的值相同
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Big Query中比较两个连续行中的时间戳，这两个行的A列的值不同，B列的值相同EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Big Query中比较两个连续行中的时间戳，这两个行的A列的值不同，B列的值相同
EN