大规模聚合查询优化：亿级日增events表30天聚合回填效率提升咨询

针对日增超十亿行的events表，要计算每个user_id+event_type在过去30天的累计事件数，当前逐天全量扫描30天数据的方式效率极低，可通过以下分层聚合方案优化：

1. 预构建日粒度基础聚合表 #

先创建日维度的轻量聚合表daily_event_agg，仅每日计算当日新增数据，避免全量扫描历史：

INSERT INTO daily_event_agg (user_id, event_type, date, daily_count)
SELECT user_id, event_type, date, COUNT(*)
FROM events
WHERE date = CURRENT_DATE
GROUP BY user_id, event_type, date;

该操作仅处理当日十亿行数据，耗时远低于全量扫30天原始表。

2. 基于日聚合表计算30天滚动窗口 #

有了日聚合表后，30天窗口的聚合只需对小量的日聚合数据求和：

• 计算当日窗口结果：

SELECT 
  user_id, 
  event_type, 
  SUM(daily_count) AS total_count,
  CURRENT_DATE AS window_end_date
FROM daily_event_agg
WHERE date >= CURRENT_DATE - INTERVAL '29 days' -- 含当日共30天
GROUP BY user_id, event_type;

• 回填历史日期窗口（如2024-05-01为窗口结束日）：

SELECT 
  user_id, 
  event_type, 
  SUM(daily_count) AS total_count,
  '2024-05-01' AS window_end_date
FROM daily_event_agg
WHERE date >= '2024-05-01' - INTERVAL '29 days' 
  AND date <= '2024-05-01'
GROUP BY user_id, event_type;

此方式下，每个历史日期的计算仅扫描30天的日聚合数据，效率大幅提升。

3. 增量维护滚动30天聚合表（准实时场景） #

若需实时获取30天窗口数据，可维护rolling_30d_event_agg表，每日增量更新：

-- 插入/更新当日的30天窗口聚合结果
INSERT INTO rolling_30d_event_agg (user_id, event_type, window_end_date, total_count)
SELECT 
  dea.user_id,
  dea.event_type,
  CURRENT_DATE,
  SUM(dea.daily_count)
FROM daily_event_agg dea
WHERE dea.date >= CURRENT_DATE - INTERVAL '29 days'
GROUP BY dea.user_id, dea.event_type
ON DUPLICATE KEY UPDATE total_count = VALUES(total_count);

日常维护仅处理30天的日聚合数据，回填时可按日期批量生成窗口结果，彻底规避原始大表的全量扫描。

4. 存储层配套优化 #

• 对daily_event_agg按date字段分区，查询时快速过滤指定日期范围，减少扫描量。
• 为user_id和event_type建立联合索引，加速分组聚合操作。
• 若使用列存数据库（如ClickHouse、BigQuery、Hive），日聚合表的存储和查询性能会比行存更优，适配大规模数据场景。

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