sql—如何迭代行以创建组

20jt8wwn 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(328)

我有一张这样的table：

User | Time_stamp | Group
1    | 12:00:00   | NewGroup
1    | 15:00:00   | NewGroup
2    | 13:00:00   | NewGroup
2    | 14:00:00   | OverlapsPreviousGroup
3    | 13:00:00   | NewGroup
3    | 14:00:00   | OverlapsPreviousGroup
3    | 15:00:00   | OverlapsPreviousGroup
3    | 16:00:00   | OverlapsPreviousGroup
3    | 18:00:00   | NewGroup
3    | 19:00:00   | OverlapsPreviousGroup
4    | 14:00:00   | NewGroup

并希望创建一个列，根据值“newgroup”为每个用户创建组，例如。

User | Time_stamp | Group                 | UserGroup
1    | 12:00:00   | NewGroup              | 1
1    | 15:00:00   | NewGroup              | 2
2    | 13:00:00   | NewGroup              | 1
2    | 14:00:00   | OverlapsPreviousGroup | 1
2    | 20:00:00   | NewGroup              | 2
3    | 13:00:00   | NewGroup              | 1
3    | 14:00:00   | OverlapsPreviousGroup | 1
3    | 15:00:00   | OverlapsPreviousGroup | 1
3    | 16:00:00   | OverlapsPreviousGroup | 1
3    | 18:00:00   | NewGroup              | 2
3    | 19:00:00   | OverlapsPreviousGroup | 2
4    | 14:00:00   | NewGroup              | 1

i、 e.对于每个用户，每次看到值“newgroup”时，usergroup值都会增加1，否则它会采用与前一行相同的值。我该怎么做呢？

sql google-bigquery grouping

来源：https://stackoverflow.com/questions/62695824/how-to-iterate-over-rows-to-create-groups

1条答案

按热度按时间

zaqlnxep1#

您需要newgroup计数的累计和。使用 countif() 作为窗口函数：

select t.*,
       countif(group = 'NewGroup') over (partition by user order by time_stamp) as usergroup
from t;

赞(0）回复(0）举报 2021-07-29

我来回答

sql—如何迭代行以创建组

1条答案

相关问题

热门标签

最新问答