如何优化SQL中WHERE语句的组织方式实现CRM联系人活动统计需求

原始写法存在的问题 #

你当前的实现使用了3个独立的关联子查询，每统计一个活动类型就要对ContactActivities表做一次全量扫描，逻辑重复度高，数据量大时查询性能会明显下降。

优化方案 #

推荐使用LEFT JOIN + 条件聚合的写法，仅需关联一次ContactActivities表即可完成所有活动类型的判断，性能和可维护性都更好，优化后代码如下：

SELECT
  c1.ContactID,
  c1.Name,
  MAX(CASE WHEN ca.Activity = 'opt-in' THEN 1 ELSE 0 END) AS OPT_IN,
  MAX(CASE WHEN ca.Activity = 'purchase' THEN 1 ELSE 0 END) AS PURCHASE,
  MAX(CASE WHEN ca.Activity = 'deleted' THEN 1 ELSE 0 END) AS DELETED
FROM Contacts c1
LEFT JOIN ContactActivities ca
  ON ca.ContactID = c1.ContactID
  -- 统一过滤创建后2周内的活动
  AND DATEDIFF(ca.ActivityDate, c1.CreateDate) < 14
GROUP BY c1.ContactID, c1.Name

注：上述代码中的DATEDIFF为日期差计算函数，不同数据库语法略有差异，可根据你使用的数据库替换为对应标准函数，避免直接用日期相减出现兼容性问题。

优化点说明 #

• 减少表扫描次数：从原来的3次子查询扫表改为1次关联扫表，数据量越大性能提升越明显
• 逻辑更易维护：后续需要新增其他活动类型的标识，仅需新增一行MAX(CASE)的聚合逻辑即可，不需要重复写子查询
• 执行效率更高：用MAX(CASE)直接判断是否存在符合条件的记录，不需要统计全量符合条件的记录数，比SIGN(COUNT(*))的执行开销更低

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