SQL/BigQuery查询优化：如何高效查询同时间戳≥3条记录的会话

优化方案 #

原查询存在大量冗余计算，以下是改写后的高性能查询语句：

SELECT DISTINCT sessionID
FROM ContentAccess
GROUP BY sessionID, TIMESTAMP_TRUNC(timestamp, SECOND)
HAVING COUNT(*) >= 3

核心优化点 #

• 移除无用CTE结构：原查询的CTE没有新增任何计算逻辑，单纯做了一次数据中转，会额外生成临时中间表，带来不必要的内存、IO开销，直接合并到主查询即可
• 删除冗余去重逻辑：原CTE内部对sessionID加的DISTINCT属于无效计算，GROUP BY sessionID, ets已经保证了分组维度的唯一性，不需要额外去重
• 支持按需追加过滤条件：如果业务有时间范围限制，可在GROUP BY前加WHERE timestamp BETWEEN '起始时间' AND '结束时间'，提前缩小扫描的数据量

索引优化建议（性能提升核心） #

• 优先创建联合索引 (sessionID, timestamp)，该索引可以完全覆盖分组、计数的全流程计算，不需要回表查询原始数据，性能可提升数倍至数十倍
• 若使用的数据库支持函数索引，可直接创建 (sessionID, TIMESTAMP_TRUNC(timestamp, SECOND)) 函数索引，进一步省去分组时的时间截断计算成本

参考数据示意图：

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