sql - 分组依据以在 Google BigQuery 中获取不同的行需要更长的时间

Question

我正在对 Google BigQuery 中的 publicdata:samples.github_timeline 数据集进行漏斗分析。我想按时间顺序提取完成一系列三项事件的所有唯一用户。

事件及其顺序:

• 观察事件
• 推送事件
• 创建事件

这是查询:

    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

最后没有 GROUP BY 用户，它非常快(10 秒)。但是当我添加它时，它需要很多时间才能完成(超过 20 分钟)。

查询有什么问题？您可以在此处测试查询:https://bigquery.cloud.google.com/

Answer 1

你有一个连接爆炸；也就是说，如果用户 A 有 20 个 WatchEvents、20 个 PushEvents 和 20 个 CreateEvents，您的查询可以从这 60 行中生成 8000 行。这是因为当 JOIN 的两边有多个匹配键时，它会生成笛卡尔积两侧。您可以通过仅采用最小匹配时间来解决此问题，因此您只查看用户的最小 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 100