BigQuery窗口函数中计算月度累计去重活跃用户的问题

你遇到的核心问题是：BigQuery不允许在窗口函数的COUNT(DISTINCT)中搭配ORDER BY和RANGE/ROWS框架——这是BigQuery的语法限制，直接写COUNT(DISTINCT active_users) OVER (...)会报错，因为窗口函数中的去重计数无法和行范围筛选一起使用。

要实现「每月从月初到当前日的累计唯一活跃用户数」，我们需要换一种思路：先标记每个用户在当月的首次活跃记录，再通过窗口函数累加这些首次记录的数量，就能得到累计去重用户数。

下面是针对你完整查询的修改方案：

方案一：预计算用户当月首次活跃日期 #

with user_first_monthly_activity as (
  -- 先获取每个用户在每个月、sample、app_id下的首次活跃日期
  select 
    td.user_id,
    td.SAMPLE,
    td.APPNAME as APP_ID,
    date_trunc(fd.date, month) as month_start,
    min(fd.date) as first_active_date_in_month
  from DWH.DailyUser fd 
  join DWH.Depositors td using (userid)
  group by td.user_id, td.SAMPLE, td.APPNAME, date_trunc(fd.date, month)
),
mtd1 as (
  select 
    'MonthToDate' as TIMELINE,
    fd.date as DATE,
    td.SAMPLE as SAMPLE,
    td.APPNAME as APP_ID,
    sum(fd.revenue) as REVENUE,
    td.user_id as USER_ID,
    -- 标记当前日期是否是该用户当月的首次活跃日
    case when fd.date = ufma.first_active_date_in_month then 1 else 0 end as is_first_active
  from DWH.DailyUser fd 
  join DWH.Depositors td using (userid)
  join user_first_monthly_activity ufma 
    on td.user_id = ufma.user_id
    and td.SAMPLE = ufma.SAMPLE
    and td.APPNAME = ufma.APP_ID
    and date_trunc(fd.date, month) = ufma.month_start
  group by 1,2,3,4,6, ufma.first_active_date_in_month
),
mtd as ( 
  select 
    TIMELINE,
    DATE,
    SAMPLE,
    APP_ID,
    -- 累计收入逻辑保持不变
    sum(revenue) over (
      partition by date_trunc(date, month), sample, app_id 
      order by date 
      range between unbounded preceding and current row
    ) as REVENUE,
    -- 累加首次活跃标记，得到累计唯一用户数
    sum(is_first_active) over (
      partition by date_trunc(date, month), sample, app_id 
      order by date 
      range between unbounded preceding and current row
    ) as ACTIVE_USERS
  from mtd1 
) 
select * from mtd where extract(day from date) = extract(day from current_date) group by 1,2,3,4,5,6

方案二：用ROW_NUMBER标记首次活跃记录 #

如果不想额外增加CTE，也可以直接在mtd1中用ROW_NUMBER()标记用户当月的第一条记录，再累加计数：

with mtd1 as (
  select 
    'MonthToDate' as TIMELINE,
    fd.date as DATE,
    td.SAMPLE as SAMPLE,
    td.APPNAME as APP_ID,
    sum(fd.revenue) as REVENUE,
    td.user_id as USER_ID,
    -- 对每个用户当月的记录按日期排序，第一条标记为1
    row_number() over (
      partition by td.user_id, date_trunc(fd.date, month), td.SAMPLE, td.APPNAME 
      order by fd.date
    ) as rn
  from DWH.DailyUser fd 
  join DWH.Depositors td using (userid)
  group by 1,2,3,4,6
),
mtd as ( 
  select 
    TIMELINE,
    DATE,
    SAMPLE,
    APP_ID,
    sum(revenue) over (
      partition by date_trunc(date, month), sample, app_id 
      order by date 
      range between unbounded preceding and current row
    ) as REVENUE,
    -- 只累加第一条记录的计数，得到累计唯一用户数
    sum(case when rn = 1 then 1 else 0 end) over (
      partition by date_trunc(date, month), sample, app_id 
      order by date 
      range between unbounded preceding and current row
    ) as ACTIVE_USERS
  from mtd1 
) 
select * from mtd where extract(day from date) = extract(day from current_date) group by 1,2,3,4,5,6

原理说明 #

这两种方案的核心逻辑一致：每个用户在当月只会被计数一次（在首次活跃的日期），之后通过窗口函数的SUM累加这些首次标记，就能得到从月初到当前日的累计唯一活跃用户数，完美替代了无法直接使用的COUNT(DISTINCT)窗口函数写法。

内容的提问来源于stack exchange，提问作者Porada Kev