MySQL客户订单频次统计查询难点：订单间隔小于2个月实现

嘿，我太懂你卡在第三个条件的痛苦了——“所有订单间隔小于2个月”这个要求，还要兼顾新用户的首次购买时间，确实比前两个条件绕不少。不过别慌，咱们用窗口函数就能搞定这个问题，我给你拆解一下思路，再附上完整的SQL代码。

核心思路拆解 #

要满足第三个条件，关键是要检查每个客户的所有相邻订单之间的间隔都不超过2个月，同时对于新用户（首次购买在过去6个月），我们只需要看他首次购买后的订单间隔（这部分其实不需要额外特殊处理，因为我们已经限定了订单在过去12个月内，新用户的所有订单自然都在首次购买之后）。

具体步骤：

1. 先筛选出过去12个月的订单，同时标记每个客户的首次购买时间，并用LAG()窗口函数拿到每个订单的上一个订单日期。
2. 计算每对相邻订单的间隔月数（用TIMESTAMPDIFF比天数转换更准确，毕竟不同月份天数不一样）。
3. 聚合每个客户的订单数据，确保：
   • 总订单数≥3
   • 所有相邻订单的间隔都≤2个月（用MAX()来检查，只要最大间隔符合要求，所有间隔就都符合）

完整SQL代码 #

WITH customer_orders AS (
    -- 第一步：获取过去12个月的订单，标记首次购买时间和上一个订单日期
    SELECT
        customer_id,
        order_date,
        -- 标记每个客户的首次购买时间
        MIN(order_date) OVER (PARTITION BY customer_id) AS first_order_date,
        -- 获取当前订单的上一个订单日期（按时间排序）
        LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date
    FROM orders
    -- 筛选过去12个月的订单
    WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
),
order_intervals AS (
    -- 第二步：计算相邻订单的间隔月数，排除第一个订单（没有上一个订单）
    SELECT
        customer_id,
        first_order_date,
        -- 计算两个订单之间的间隔月数
        TIMESTAMPDIFF(MONTH, prev_order_date, order_date) AS months_between_orders
    FROM customer_orders
    WHERE prev_order_date IS NOT NULL
),
valid_customers AS (
    -- 第三步：筛选符合所有条件的客户
    SELECT
        customer_id,
        first_order_date,
        -- 总订单数 = 间隔数 + 1（因为排除了第一个订单）
        COUNT(*) + 1 AS total_orders
    FROM order_intervals
    GROUP BY customer_id, first_order_date
    HAVING
        -- 订单数≥3
        total_orders >= 3
        -- 所有相邻订单的间隔都≤2个月（最大间隔符合则全部符合）
        AND MAX(months_between_orders) <= 2
)
-- 最终输出客户统计数据
SELECT
    vc.customer_id,
    vc.first_order_date,
    vc.total_orders,
    MAX(co.order_date) AS last_order_date
FROM valid_customers vc
JOIN customer_orders co ON vc.customer_id = co.customer_id
GROUP BY vc.customer_id, vc.first_order_date, vc.total_orders
ORDER BY vc.total_orders DESC;

关键细节说明 #

• TIMESTAMPDIFF的使用：这个函数直接计算两个日期之间的月份差，比用天数除以30更准确，比如2023-01-31到2023-03-01，它会返回1个月，符合实际间隔逻辑。
• 新用户的处理：如果客户首次购买是在过去6个月，他的所有订单自然都在首次购买之后，LAG()会自动从他的第一个订单开始计算间隔，不需要额外调整条件。
• 间隔检查逻辑：用MAX(months_between_orders) <= 2是确保客户的所有相邻订单间隔都不超过2个月——只要有一个间隔超过，这个最大值就会大于2，客户就会被排除，完美符合你的需求。

测试案例验证 #

举几个例子看看逻辑是否生效：

• ✅ 客户A：3个订单，间隔分别是1个月、1.5个月 → 符合条件，会被选中。
• ❌ 客户B：3个订单，间隔分别是2个月、3个月 → 最大间隔3个月，被排除。
• ✅ 客户C（新用户）：首次购买5个月前，3个订单间隔都是1个月 → 符合条件，会被选中。

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