BigQuery中滚动90天活跃用户统计性能优化（DAU/MAU/WAU）

碰到这种大数据集下滚动窗口统计独立用户/事件的内存问题太常见了！原来的方案估计是把窗口内的userid聚合到一个集合或者拼接成字符串，数据量一大就直接撑爆内存。下面给你几个针对性的高效实现思路，分场景适用：

1. 利用数据库原生窗口函数（精确统计，优先选择） #

如果你的数据库支持RANGE窗口（比如PostgreSQL、Spark SQL、BigQuery等），直接用窗口函数就能高效计算，不需要手动聚合大集合：

SELECT
  date,
  -- 统计往前7天的独立用户数
  COUNT(DISTINCT userid) OVER (
    ORDER BY date
    RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
  ) AS rolling_7d_users,
  -- 同理替换成30/90天
  COUNT(DISTINCT userid) OVER (
    ORDER BY date
    RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW
  ) AS rolling_30d_users,
  COUNT(DISTINCT userid) OVER (
    ORDER BY date
    RANGE BETWEEN INTERVAL '90 days' PRECEDING AND CURRENT ROW
  ) AS rolling_90d_users
FROM your_table
ORDER BY date;

注意：部分数据库（比如MySQL 8.0+）对窗口函数里的COUNT(DISTINCT)支持有限，这时候可以用SUM(CASE ...)结合窗口内的用户去重逻辑，或者改用下面的增量方法。

2. 增量维护滑动窗口（精确统计，适合极端大数据） #

如果窗口函数不够用，或者数据量大到窗口函数也扛不住，可以用增量更新的思路：只维护当前窗口内的用户集合，每天移除超出窗口的用户，添加当天的新用户，避免每次重新计算整个窗口。

举个Python + Pandas的离线计算示例：

import pandas as pd

# 先按日期排序
df = df.sort_values('date')

# 初始化滑动窗口用户集合和结果列表
window_users = set()
rolling_counts = []
window_days = 7  # 可替换为30/90

# 遍历每个日期的用户组
for date, group in df.groupby('date'):
    # 添加当天的用户到窗口
    current_users = set(group['userid'])
    window_users.update(current_users)
    
    # 移除超出窗口的日期的用户
    cutoff_date = date - pd.Timedelta(days=window_days)
    expired_users = set(df[df['date'] <= cutoff_date]['userid'])
    window_users -= expired_users
    
    # 记录当前窗口的独立用户数
    rolling_counts.append({'date': date, f'rolling_{window_days}d_users': len(window_users)})

# 转成结果DataFrame
result = pd.DataFrame(rolling_counts)

如果是SQL场景，记得给date和userid加联合索引，再用自连接限制窗口范围：

SELECT
  t1.date,
  COUNT(DISTINCT t2.userid) AS rolling_7d_users
FROM your_table t1
JOIN your_table t2
  ON t2.date BETWEEN t1.date - INTERVAL '7 days' AND t1.date
GROUP BY t1.date
ORDER BY t1.date;

这个方法一定要加索引，否则大数据集下自连接会慢到离谱！

3. 近似统计（超大数据集，允许微小误差） #

如果数据量极端庞大（比如亿级以上），精确统计内存压力太大，可以用HyperLogLog这类近似算法，它能以极小的内存占用（几KB到几十KB）估算独立元素数量，误差通常在1%以内。

比如PostgreSQL里用hll扩展的示例：

-- 先创建hll扩展
CREATE EXTENSION IF NOT EXISTS hll;

SELECT
  date,
  -- 合并窗口内的hll值并估算基数
  hll_cardinality(hll_union_agg(hll_hash_text(userid)) OVER (
    ORDER BY date
    RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
  )) AS rolling_7d_users_approx
FROM your_table;

Spark SQL里也有approx_count_distinct函数可以直接用在窗口里：

SELECT
  date,
  approx_count_distinct(userid) OVER (
    ORDER BY date
    RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
  ) AS rolling_7d_users_approx
FROM your_table;

总结选择建议 #

• 优先用窗口函数：如果数据库支持，代码简洁且性能足够；
• 极端大数据精确统计用增量滑动窗口：内存占用低，但需要手动维护窗口；
• 超大数据且允许误差用近似算法：内存占用极小，性能最优。

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