主页 / dba / 问题 / 282722
Accepted
Mallikarjun
Asked: 2021-01-08 00:59:58 +0800 CST

如何从 bigquery 获得每周未绑定的保留？

  • 772

我需要获得每周留存用户。如果用户在第 4 周进行了交易，则该用户在第 0 周、第 1 周、第 2 周、第 3 周、第 4 周时在那里。

如果用户在第 0 周和第 3 周进行了交易，那么用户将在第 0 周、第 1 周、第 2 周、第 3 周到达谢谢@bbaird，

我正在用准确的信息和问题更新问题

这是我的表格数据

TxnId 用户身份 交易周
T1 U1 0
T2 U2 0
T3 U3 0
T4 U4 0
T5 U1 0
T6 U2 0
T7 U6 0
T8 U1 1
T9 U8 1
T10 U12 1
T11 U3 1
T12 U5 1
T13 U9 2
T14 U15 2
T15 U20 3
T16 U15 3
T17 U15 3
T18 U8 3
T19 U9 4
T20 U20 5

我正在寻找的结果

星期 active_users_count
0 11
1 8
2 4
3 4
4 2
5 1

用户数有点累积但唯一的用户。

例如，如果用户 1 在第 0 周执行了 3 次事务，则第 0 周的计数为 1

如果用户 1 在第 0 周执行了 3 次交易，在第 2 周执行了 2 次交易，在第 3 周执行了 1 次交易，那么计数将是这样的

week-0 -> 1（即他已执行交易 >= 第 0 周）

第 1 周 -> 1（即他已执行交易 >= 第 1 周）

第 2 周 -> 1（即他已执行交易 >= 第 2 周）

第 3 周 -> 1（即他已执行交易 >= 第 3 周）

可以有任意周数。它不固定为 4 周或 8 周。

google-bigquery

1 个回答

  1. Best Answer

    这应该是一个相对简单的聚合Week，但是您需要加入大于或等于当前周的所有记录，并从那里获取您的不同计数。

    但是，Bigquery 需要处理很多行，因此这可能对平台更有效：

    SELECT
  TransactedWeek AS Week
 ,SUM(SUM(LastWeekInd))
    OVER
      (
        PARTITION BY 
          1 
        ORDER BY 
          TransactedWeek
        ROWS BETWEEN 
          CURRENT ROW
            AND
          UNBOUNDED FOLLOWING
      ) AS active_users_count
FROM
  (
    SELECT DISTINCT
      UserId
     ,TransactedWeek
     ,CASE
        WHEN TransactedWeek = MAX(TransactedWeek) OVER (PARTITION BY UserId) THEN 1
        ELSE 0
      END AS LastWeekInd
    FROM
      <Your Table>
  ) x
GROUP BY
  TransactedWeek

    您可以添加逻辑来限制内部查询中的范围。

    注意：如果您的数据中缺少任何星期，它们将在您的最终结果中丢失（尽管计数仍然是准确的）。如果您有几周没有交易，您可能需要生成一个周列表并将这些结果加入其中。

    • 0

相关问题