SQL如何统计每个日期对应30天滚动区间内的去重活跃用户数

修正后可直接使用的SQL #

WITH daily_active_users AS (
  -- 第一步：得到所有去重的<活跃日期, 组织ID>对，避免单用户单日重复计数
  SELECT DISTINCT
    DATE_TRUNC(PARSE_DATE('%Y%m%d', event_date), DAY) AS active_date,
    -- 从当前行的用户属性中取组织ID，修正原有SQL交叉连接导致的关联错误
    (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'organization_id') AS organization_id
  FROM `[app]-project.analytics_[numbers].events_intraday_*`
  WHERE 
    event_name = 'Active Seller Event'
    -- 过滤无组织ID的无效数据
    AND (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'organization_id') IS NOT NULL
),
all_stat_dates AS (
  -- 第二步：拿到所有需要统计的日期维度
  SELECT DISTINCT active_date AS event_date
  FROM daily_active_users
  -- 如果需要补全没有活跃记录的日期，可替换为下面的语句，替换起止日期即可
  -- SELECT date AS event_date FROM UNNEST(GENERATE_DATE_ARRAY('2021-07-01', CURRENT_DATE())) AS date
)
-- 第三步：按30天区间关联统计去重用户数
SELECT
  a.event_date,
  -- 拼接日期区间显示，调整INTERVAL数值可修改统计范围
  CONCAT(DATE_SUB(a.event_date, INTERVAL 29 DAY), ' to ', a.event_date) AS count_date_range,
  COUNT(DISTINCT b.organization_id) AS active_30_days
FROM all_stat_dates a
LEFT JOIN daily_active_users b
  ON b.active_date BETWEEN DATE_SUB(a.event_date, INTERVAL 29 DAY) AND a.event_date
GROUP BY a.event_date, count_date_range
ORDER BY a.event_date DESC

逻辑说明 #

• 原有SQL的FROM子句使用了交叉连接，会导致组织ID和事件行没有关联，统计结果存在错误，上面的方案直接从当前事件行的用户属性中提取对应组织ID，保证数据关联正确
• 如果需要统计的是「往前推30自然日+当天共31天」的范围，把SQL里的INTERVAL 29 DAY改成INTERVAL 30 DAY即可
• 若数据量较大，可使用BigQuery原生的HLL近似去重优化性能，误差在2%以内，性能提升10倍以上，优化写法如下：

WITH daily_hll AS (
  SELECT
    DATE_TRUNC(PARSE_DATE('%Y%m%d', event_date), DAY) AS active_date,
    -- 按日预计算组织ID的HLL sketch
    HLL_COUNT.INIT((SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'organization_id'), 15) AS hll_org
  FROM `[app]-project.analytics_[numbers].events_intraday_*`
  WHERE event_name = 'Active Seller Event'
  GROUP BY active_date
)
SELECT
  active_date AS event_date,
  CONCAT(DATE_SUB(active_date, INTERVAL 29 DAY), ' to ', active_date) AS count_date_range,
  -- 用窗口函数合并近30天的HLL sketch得到去重数
  HLL_COUNT.MERGE(hll_org) OVER (ORDER BY UNIX_DATE(active_date) RANGE BETWEEN 29 PRECEDING AND CURRENT ROW) AS active_30_days
FROM daily_hll
ORDER BY active_date DESC

内容的提问来源于stack exchange，提问作者Mahmood H. Alnasser