是否可以根据列值对 BigQuery/SQL 中的行进行"分组"?假设我想为 stream_start_init 和 stream_start 之间的所有行分配一个字符串/id,然后对 stream_resume 和最后一个stream_ad之间的行执行相同的操作。
stream_ad事件的数量可能会有所不同,因此我不能使用 RANK(( 或 ROW(( 根据这些值对它们进行分组。
|id, timestamp, event|
|1 | 1231231 | first_visit|
|2 | 1231232 | login|
|3 | 1231233 | page_view|
|4 | 1231234 | page_view|
|5 | 1231235 | stream_start_init|
|6 | 1231236 | stream_ad|
|7 | 1231237 | stream_ad|
|8 | 1231238 | stream_ad|
|9 | 1231239 | stream_start|
|6 | 1231216 | stream_resume|
|6 | 1231236 | stream_ad|
|7 | 1231217 | stream_ad|
|8 | 1231258 | stream_ad|
|10| 1231240 | page_view|
我希望这张桌子是怎样的
|id, timestamp, event, group_id|
|1 | 1231231 | first_visit, null|
|2 | 1231232 | login, null|
|3 | 1231233 | page_view, null|
|4 | 1231234 | page_view, null|
|5 | 1231235 | stream_start_init, group_1|
|6 | 1231236 | stream_ad, group_1|
|7 | 1231237 | stream_ad, group_1|
|8 | 1231238 | stream_ad, group_1|
|9 | 1231239 | stream_start, group_1|
|6 | 1231216 | stream_resume, group_2|
|6 | 1231236 | stream_ad, group_2|
|7 | 1231217 | stream_ad, group_2|
|8 | 1231258 | stream_ad, group_2|
|10| 1231240 | page_view, null|
我不会分配字符串。 我会分配一个数字。 这似乎是一个累积总和。 我认为"stream_start_init"和"stream_resume"的数量之和可以满足您的需求:
select t.*,
countif(event in ('stream_start_init', 'stream_resume')) over (order by timestamp) as group_id
from t;
请注意,这会为第一组产生0
- 这似乎是一件好事。 您可以使用NULLIF()
将其转换为NULL
。
如果你真的想要字符串,你可以使用CONCAT()
.
下面是 BigQuery Standard SQL
#standardSQL
SELECT *,
IF(event IN ('stream_start_init', 'stream_start', 'stream_resume', 'stream_ad'),
COUNTIF(event IN ('stream_start_init', 'stream_resume')) OVER(ORDER BY timestamp),
NULL
) AS group_id
FROM `project.dataset.table`