google-bigquery - 如何在 BigQuery 中进行子选择

Question

我们有一个巨大的事件表，用户注册和玩我们的游戏。

现在我想确定每一天的第二天留存率，即前一天注册的玩家在后天注册的百分比。

所以假设我们有三个字段

timestamp ts
int  userId
int  eventId               (I.e. 1 = Register, 2 = Login)

这在 BigQuery 语法中是如何完成的？即我想要以下输出:

Date         Register    Logins day after    % Second day retention
2013-08-23   25 563      4 567               17.8

我在子选择和连接方面失败了，但它必须是可行的!

Answer 1

使用公共(public)数据进行此查询怎么样:

SELECT
  a.day, first_day, return_next_day,
  integer((return_next_day / first_day) * 100) percent
FROM (
  SELECT COUNT(DISTINCT actor, 50000) first_day,
    STRFTIME_UTC_USEC(
      UTC_USEC_TO_DAY(PARSE_UTC_USEC(created_at)), "%Y-%m-%d") day,
  FROM
    [publicdata:samples.github_timeline]
  GROUP BY day) a
JOIN (
  SELECT
    COUNT(*) return_next_day, day
  FROM (
    SELECT
      a.day day, a.actor, b.day, b.actor
    FROM (
      SELECT
        STRFTIME_UTC_USEC(
          UTC_USEC_TO_DAY(PARSE_UTC_USEC(created_at)), "%Y-%m-%d") day,
        MAX(STRFTIME_UTC_USEC(86400000000 + UTC_USEC_TO_DAY(
          PARSE_UTC_USEC(created_at)), "%Y-%m-%d")) dayplus,
        actor
      FROM
        [publicdata:samples.github_timeline]
      GROUP EACH BY actor, day) a
    JOIN EACH (
      SELECT
        STRFTIME_UTC_USEC(
          UTC_USEC_TO_DAY(PARSE_UTC_USEC(created_at)), "%Y-%m-%d") day,
        actor
      FROM
        [publicdata:samples.github_timeline]
      GROUP EACH BY actor, day) b
      ON a.actor = b.actor
      AND a.dayplus = b.day
      )
  GROUP BY day) b
  ON a.day = b.day

这给了我想要的结果:



注意查询使用 STRFTIME_UTC_USEC(UTC_USEC_TO_DAY(PARSE_UTC_USEC(created_at)), "%Y-%m-%d") day多次，将源字符串数据转换为日期。如果我拥有数据，我会事先在表上运行 ETL，以跳过这个重复的步骤。

该查询连接 2 个表:

第一个表计算了在特定日期出现了多少不同的“ Actor ”。注意 COUNT DISTINCT 的第二个参数，以使计数精确。

鉴于同一 Actor 在两天都在场，第二张表在给定的一天和第二天加入。然后，您可以计算在给定的一天和第二天有多少 Actor 在场。

加入这两个表会让你都算数，你可以继续划分。

还有其他方法，这只是众多方法中的一种。还可以进一步优化此查询。