从数据表中获取符合多事件条件的accountNumber子集的高效方法

你现有查询的性能问题根源是每新增一个筛选条件就多一次全表扫描，多个CTE会多次读取同一张表的数据，数据量越大性能下降越明显。我们可以通过单次分组聚合查询实现需求，全程只扫描一次表，扩展条件也不会增加扫描次数。

优化后查询语句 #

SELECT accountNumber
FROM table1
GROUP BY accountNumber
HAVING MAX(CASE WHEN event = 'start' THEN 1 ELSE 0 END) = 1
   AND MAX(CASE WHEN event = 'finish' THEN 1 ELSE 0 END) = 1
   AND MAX(CASE WHEN event = 'jump' THEN 1 ELSE 0 END) = 0;

逻辑说明 #

• 按照accountNumber分组，每个账号仅聚合计算一次，结果天然去重不需要额外加DISTINCT
• 用CASE WHEN对每个账号的event记录做判断：
  • 只要存在一条event为start的记录，MAX计算结果就为1，符合要求
  • 同理判断是否存在finish记录
  • 只要存在一条event为jump的记录，MAX计算结果就为1，我们要求不存在所以设置判断条件为等于0
• 如果需要新增筛选条件，只需要在HAVING子句中新增对应CASE WHEN判断即可，无需额外扫描表

额外性能优化建议 #

• 建议创建(accountNumber, event)联合索引，查询可以直接走索引覆盖，不需要回表读取数据，性能会有量级提升
• 注意修正你现有代码中的语法错误：原有CTE3的WHERE条件缺少event字段，INSERT语句中也存在多处逗号缺失的问题，执行前需要先修正

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