SQL从标志中获取2个相邻动作 [英] SQL to get 2 adjacent actions from the flag

问题描述

希望你一切都好！

我有一个如下的伪数据.

I have an dummy data as below.

我希望每个用户从标志中获得2个相邻的动作.

I want to get 2 adjacent actions from the flag by each user.

这是描述我思想的图表.

Here's the chart to describe my thought.

这就是我想要的:

如何实现SQL(我使用Google Bigquery)?希望有人可以照亮我.谢谢一百万！

How can I implement SQL(I use Google Bigquery)? Hope someone can light me up. Thanks a million!

推荐答案

您似乎想要 lag().我会离开动作序列"作为两个单独的列:

You seem to want lag(). I would leave the "action sequence" as two separate columns:

select user, prev_action, action, flag
from (select t.*,
             lag(action) over (partition by user order by sequence) as prev_action
      from t
     ) t
where prev_action is not null;

这篇关于SQL从标志中获取2个相邻动作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！