按合同起始日的月度周期统计客户服务量，求高效SQL优化方案

嘿，这个问题我熟！咱们先拆解下核心需求：要以客户的合同起始日为起点，每30天划一个周期，统计每个周期内的服务数量，而你当前的SQL其实是硬编码了每月15号作为分割点，逻辑和需求并不匹配，而且在大数据量下还有优化空间。

现有方案的问题 #

你当前的SQL存在几个明显的不足：

• 逻辑偏离需求：固定以每月15号分割周期，完全没结合客户的合同起始日，比如合同起始日是2019-02-15，现有SQL会把2019-03-15之后的服务归到2019-03周期，但实际这属于第二个30天周期的内容
• 性能损耗：用字符串拼接DATE_PART结果作为分组键，计算开销大，且数据库难以高效利用索引
• 扩展性差：如果后续要调整周期长度（比如改成31天），修改起来非常麻烦

更高效的优化方案 #

我们可以通过计算服务日期与合同起始日的时间差，转化为周期数来分组，完全不需要自连接，性能更优，逻辑也更准确。

场景1：存在客户表（存储合同起始日） #

如果有customers表记录每个客户的合同起始日，推荐用这个写法：

WITH customer_contract AS (
    -- 获取目标客户的合同起始日
    SELECT contract_start_date
    FROM customers
    WHERE id = 1
)
SELECT
    -- 生成易读的周期范围标识
    TO_CHAR(c.contract_start_date + (period * 30)::INTERVAL, 'YYYY-MM-DD') || ' 至 ' ||
    TO_CHAR(c.contract_start_date + ((period + 1) * 30)::INTERVAL - '1 day'::INTERVAL, 'YYYY-MM-DD') AS bill_period,
    COUNT(s.id) AS service_count
FROM services s
CROSS JOIN customer_contract c
WHERE s.id_customer = 1
  AND s.service_date >= c.contract_start_date -- 只统计合同生效后的服务
-- 计算每个服务所属的周期：0代表第1个30天周期，1代表第2个，以此类推
GROUP BY FLOOR((s.service_date - c.contract_start_date)::NUMERIC / 30) AS period
ORDER BY period;

场景2：无客户表，直接传入合同起始日 #

如果没有客户表，直接指定目标客户的合同起始日即可：

SELECT
    -- 生成周期范围
    TO_CHAR('2019-02-15'::DATE + (period * 30)::INTERVAL, 'YYYY-MM-DD') || ' 至 ' ||
    TO_CHAR('2019-02-15'::DATE + ((period + 1) * 30)::INTERVAL - '1 day'::INTERVAL, 'YYYY-MM-DD') AS bill_period,
    COUNT(id) AS service_count
FROM services
WHERE id_customer = 1
  AND service_date >= '2019-02-15'::DATE
-- 计算周期数
GROUP BY FLOOR((service_date - '2019-02-15'::DATE)::NUMERIC / 30) AS period
ORDER BY period;

性能优化关键 #

为了让这个查询在大数据量表上跑得飞快，一定要创建联合索引：

CREATE INDEX idx_services_customer_date ON services(id_customer, service_date);

这个索引会帮数据库快速过滤出目标客户的服务记录，同时直接利用索引完成分组排序，避免全表扫描。

方案优势 #

• 完全贴合需求：以合同起始日为起点，严格按30天划分周期
• 性能更优：用数值计算周期数，分组效率远高于字符串拼接
• 无自连接：避免了自连接带来的大数据量开销
• 灵活性高：如果后续需要调整周期长度（比如改成31天），只需要修改除以的数值即可

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