如何编写SQL查询任意滚动6个月内购买次数超10次的消费者？

需用到的核心函数 #

• 窗口函数（OVER() 子句）：按用户维度分组后计算滑动窗口内的统计值，性能远高于自关联方案
• 日期计算函数：不同数据库对应语法略有差异，例如MySQL的DATE_SUB()/DATEDIFF()、PostgreSQL的AGE()、SQL Server的DATEDIFF()，用于判断两个日期间隔是否在6个月范围内
• 去重函数DISTINCT：避免同一用户多次符合条件重复返回

推荐实现（支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL 9.4+、SQL Server 2012+） #

WITH consumer_rolling_stats AS (
    SELECT
        consumer_id,
        purchase_date,
        -- 按用户分组，对每一笔订单统计当前日期往前6个月内的总购买次数
        COUNT(id) OVER (
            PARTITION BY consumer_id
            ORDER BY purchase_date
            RANGE BETWEEN INTERVAL 6 MONTH PRECEDING AND CURRENT ROW
        ) AS rolling_6m_cnt
    FROM sales
)
SELECT DISTINCT c.id, c.name, MAX(crs.rolling_6m_cnt) AS max_6m_purchase_cnt
FROM consumers c
INNER JOIN consumer_rolling_stats crs ON c.id = crs.consumer_id
WHERE crs.rolling_6m_cnt >= 10 -- 若要求严格超过10次可改为>10
GROUP BY c.id, c.name
ORDER BY max_6m_purchase_cnt DESC;

逻辑说明 #

1. CTE部分对每个消费者的每一笔购买记录，计算当前记录对应的日期往前推6个月的滚动购买次数，窗口范围仅包含当前用户的对应时间区间内的订单
2. 外层查询筛选出存在任意一个滚动6个月周期购买次数达标的用户，去重后返回结果，避免同一用户多次符合条件被重复返回

兼容旧版无窗口函数数据库的实现 #

如果使用的数据库版本不支持窗口函数，可以用自关联方案实现：

SELECT DISTINCT c.id, c.name, COUNT(s2.id) AS rolling_6m_cnt
FROM consumers c
INNER JOIN sales s1 ON c.id = s1.consumer_id
-- 关联同一用户所有在s1订单日期往前6个月内的订单
INNER JOIN sales s2 ON s1.consumer_id = s2.consumer_id
    AND s2.purchase_date BETWEEN DATE_SUB(s1.purchase_date, INTERVAL 6 MONTH) AND s1.purchase_date
GROUP BY c.id, c.name, s1.id
HAVING COUNT(s2.id) >= 10
ORDER BY rolling_6m_cnt DESC;

注意事项 #

• 需确保sales表的purchase_date字段为日期类型，若存储为字符串格式，需先通过STR_TO_DATE类函数转换为标准日期后再做计算
• 若数据量较大，建议给sales表的consumer_id和purchase_date字段加联合索引，可大幅提升查询性能
• 6个月的边界规则可根据业务需求调整，例如不包含当前日期的话，修改窗口范围或BETWEEN的边界值即可

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