如何优化SQL实现动态过滤上一季度日期统计客户预约次数

API 探路者

2026-10-3

动态统计上一季度客户预约次数SQL优化方案

核心优化逻辑

把固定日期范围替换为基于当前时间动态计算的上一季度时间边界，同时优化日期过滤写法避免索引失效，补充缺失的分组逻辑。

不同数据库实现版本

PostgreSQL（适配你现有SQL的语法风格）

SELECT 
  customer_id, 
  COUNT(DISTINCT appointment_id) AS last_quarter_appointments
FROM dwh_schema.appointments 
WHERE 
  -- 动态计算上一季度的起始和结束边界，避免字段转译可命中索引
  created_at >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months')
  AND created_at < DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY customer_id;

逻辑说明：

DATE_TRUNC('quarter', CURRENT_DATE) 可以得到当前日期所属季度的第一天
往前推3个月再取季度第一天，就是上一季度的第一天
用< 当前季度第一天替代<=上一季度最后一天，可以覆盖created_at带时分秒的场景，不会漏算上一季度最后一天24点前的所有数据
补充了原SQL缺失的GROUP BY customer_id子句，解决分组聚合逻辑错误问题

MySQL版本

SELECT 
  customer_id, 
  COUNT(DISTINCT appointment_id) AS last_quarter_appointments
FROM dwh_schema.appointments 
WHERE 
  created_at >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL (QUARTER(CURDATE())*3 -3) MONTH)
  AND created_at < DATE_FORMAT(CURDATE(), '%Y-%m-01') - INTERVAL (QUARTER(CURDATE())*3 -3) MONTH + INTERVAL 3 MONTH
GROUP BY customer_id;

SQL Server版本

SELECT 
  customer_id, 
  COUNT(DISTINCT appointment_id) AS last_quarter_appointments
FROM dwh_schema.appointments 
WHERE 
  created_at >= DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0)
  AND created_at < DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)
GROUP BY customer_id;