按组在Google BigQuery中获得不同的行要花费更长的时间。
我正在对Google BigQuery中的publicdata:samples.github_timeline数据集进行漏斗分析。我想提取所有独特的用户谁做了一系列的三个事件，按时间顺序。
事件及其顺序：
- WatchEvent
- PushEvent
- CreateEvent
这是一个查询：
select user from (
SELECT user1 as user,
ts1 as eventDate1,
ts2 as eventDate2,
IF(ts2 < ts3, ts3, NULL) as eventDate3
FROM
(SELECT user1,
ts1,
ts2,
ts3
FROM (SELECT user1,
ts1,
IF(ts1 < ts2, ts2, NULL) as ts2
FROM
(SELECT user1,
ts1,
ts2
FROM (SELECT repository_owner as user1,
created_at as ts1
FROM [publicdata:samples.github_timeline]
WHERE type = "WatchEvent") as step1
LEFT JOIN EACH (SELECT repository_owner as user2,
created_at as ts2
FROM [publicdata:samples.github_timeline]
WHERE type = "PushEvent") as step2
ON user1 = user2 where ts1 is not NULL)
) as steps1_2
LEFT JOIN (SELECT repository_owner as user3,
created_at as ts3
FROM [publicdata:samples.github_timeline]
WHERE type = "CreateEvent") as step3
ON user1 = user3
where ts2 is not NULL
)
)
where eventDate3 is not null
group by user
limit 100在结束时没有按用户分组的情况下，速度相当快(10秒)。但是当我加上它时，它需要很多时间来完成(超过20分钟)。
查询有什么问题吗？您可以在这里测试查询：https://bigquery.cloud.google.com/
回答 3
Stack Overflow用户
发布于 2014-04-29 15:08:16
您有一个连接爆炸；也就是说，如果用户A有20 WatchEvents、20 PushEvents和20 CreateEvents，那么您的查询可以在这60行中生成8000行。这是因为当连接的两边都有多个匹配键时，它会生成两边的笛卡儿积。您可以通过使用最小匹配时间来解决这个问题，因此您只需要查看用户查找后续WatchEvent时间的最小PushEvent时间，然后查看比WatchEvent时间晚的最小pushEvent时间来查找匹配的CreateEvent时间。
下面是一个在20秒内运行的查询：
SELECT user
FROM (
SELECT step2_2.user1 as user,
MIN(step2_2.ts1) as eventDate1,
MIN(step2_2.ts2) as eventDate2,
MIN(step3.ts3) as eventDate3
FROM (
SELECT user1, MIN(ts1) as ts1, MIN(ts2) as ts2
FROM (
SELECT repository_owner as user1,
MIN(created_at) as ts1
FROM [publicdata:samples.github_timeline]
WHERE type = "WatchEvent"
GROUP EACH BY user1) as step1
JOIN EACH (
SELECT repository_owner as user2,
created_at as ts2
FROM [publicdata:samples.github_timeline]
WHERE type = "PushEvent") as step2
ON user1 = user2
WHERE ts1 < ts2
GROUP EACH BY user1
) as step2_2
JOIN EACH (
SELECT repository_owner as user3,
created_at as ts3
FROM [publicdata:samples.github_timeline]
WHERE type = "CreateEvent") as step3
ON user1 = user3
WHERE step2_2.ts2 < step3.ts3
GROUP EACH BY user
)
GROUP BY user
LIMIT 100Stack Overflow用户
发布于 2014-04-29 15:29:49
如果您的数据集不太大，您可以使用铅()窗口函数来查找序列并完全避免联接。
Select repository_owner
FROM
(
Select repository_owner,type as Event0,
LEAD(x,1) OVER(Partition by repository_owner order by ts) as Event1,
LEAD(x,2) OVER(Partition by repository_owner order by ts) as Event2,
FROM
(
SELECT repository_owner as user,created_at as ts,type as x
from [publicdata:samples.github_timeline]
where type in ("WatchEvent","PushEvent","CreateEvent")
))
where Event0="WatchEvent"
and Event1="PushEvent"
and Event2="CreateEvent"
Group by repository_owner7秒..。
如果事件不是以“背靠背的顺序”(指约旦的评论)，则需要使其更加复杂：
Select repository_owner from
(
Select repository_owner,Event0,Event1,
Lead(Event0,1) OVER (Partition by repository_owner order by ts) as Event2,
Lead(Event1,1) OVER (Partition by repository_owner order by ts) as Event3,
FROM
(Select * from
(Select repository_owner,type as Event0,ts,
LEAD(x,1) OVER(Partition by repository_owner order by ts) as Event1,
FROM
(
SELECT repository_owner as user,created_at as ts,type as x
from [publicdata:samples.github_timeline]
where type in ("WatchEvent","PushEvent","CreateEvent")
))
where (Event0="WatchEvent" and
Event1 in("PushEvent" ,"CreateEvent"))
OR ( Event1="CreateEvent" and
Event0 in("PushEvent" ,"WatchEvent")))
)
Where Event0="WatchEvent" and
(Event1="PushEvent" Or Event2="PushEvent") and
Event3="CreateEvent"
Group by repository_owner如果您的数据集太大，则会遇到以下问题：Parallelizable OVER EACH BY
希望它能帮上忙
Stack Overflow用户
发布于 2014-04-29 14:06:33
如果在非分组查询中使用“限制100”，则在获取前100行数据后，orchestrator将中断执行。
“按用户分组限制100”要求在分组之前必须计算所有数据行。然后执行分组。最后，“限制100”终于生效了。
https://stackoverflow.com/questions/23366731
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号