google-bigquery - BigQuery，来自 DAU 的 WAU

Question

我正在尝试使用此链接从 DAU 计算 WAU：

修复在 Amazon Redshift 上计算 DAU 和 MAU 时的 MAU 问题

但是链接中的解决方案适用于 Redshift。我试图在 BigQuery 中做同样的事情，但它给了我这个错误：

LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join

我的代码在这里：

    WITH
    data AS (
      SELECT
        EXTRACT(date FROM active_time) AS active_date,
        COUNT(distinct user_id) AS DAU
      FROM
        abc
      GROUP BY
        active_date )
  SELECT
    active_date,
    DAU,
    (select count(distinct user_id)
    from abc
    where EXTRACT(date FROM abc.active_time) between DATE_SUB(data.active_date, interval 7 day) and data.active_date
    ) as WAU
  from data

有人可以帮忙吗？TIA

标签： google-bigquery

Answer 1

这是实现结果的一种方法（注意：通过 BQ 中的左连接相等性检查是一种平均黑客攻击，但它可以完成工作）：

select 
   a.active_date, 
   count(distinct a.user) as dau, 
   array_length(array_agg(distinct b.user)) as wau
from abc a
left join data b on array_length(split(cast(a.active_date as string), '-')) = array_length(split(cast(b.active_date as string), '-'))
and b.active_date between date_sub(a.active_date, interval 1 day) and a.active_date
group by 1