如何用SQL查询近1个月内3天内有多张工单的客户的所有工单？

现有tickets表，包含receive_date（工单创建日期）、customer_id（客户ID）、ticket_number（工单号）字段，需要查询近1个月内，存在3天内创建超过1张工单的客户的所有对应工单。

示例数据表 #

期望输出结果 #

当前进展 #

我知道应该用窗口函数来实现，但还没理清具体用法。目前只写出了统计上月客户工单总数的SQL：

SELECT customer_id,
    COUNT(customer_id)
FROM tickets
WHERE EXTRACT(
        YEAR
        FROM receive_date
    ) = YEARNUMBER_OF_CALENDAR(ADD_MONTHS(CURRENT_DATE, -1))
    AND EXTRACT(
        MONTH
        FROM receive_date
    ) = MONTHNUMBER_OF_YEAR(ADD_MONTHS(CURRENT_DATE, -1))
HAVING COUNT(customer_id) > 1
GROUP BY customer_id
ORDER BY COUNT(customer_id) DESC

核心逻辑 #

这个问题拆成两步更清晰：

1. 先找出近1个月内，有任意3天窗口内工单数量超过1的客户；
2. 再拉取这些客户在近1个月内的所有工单。

窗口函数是高效实现的最优选择，下面分步骤说明：

步骤1：过滤近1个月的数据 #

先把时间范围限定在近1个月，减少后续计算的数据量：

SELECT *
FROM tickets
WHERE receive_date >= ADD_MONTHS(CURRENT_DATE, -1)

注：不同数据库的日期函数有差异，比如MySQL用DATE_SUB(CURDATE(), INTERVAL 1 MONTH)，PostgreSQL用CURRENT_DATE - INTERVAL '1 month'，根据你的数据库调整即可

步骤2：用窗口函数标记3天内的工单数量 #

通过窗口函数，按客户分组、按日期排序，计算每个工单往前3天内的同客户工单数量：

WITH ticket_stats AS (
    SELECT 
        *,
        COUNT(*) OVER (
            PARTITION BY customer_id 
            ORDER BY receive_date 
            RANGE BETWEEN INTERVAL '3 days' PRECEDING AND CURRENT ROW
        ) AS cnt_3days
    FROM tickets
    WHERE receive_date >= ADD_MONTHS(CURRENT_DATE, -1)
)

这里的RANGE BETWEEN INTERVAL '3 days' PRECEDING AND CURRENT ROW会统计当前工单日期往前推3天内，同客户的所有工单数量。如果这个数量大于1，说明该客户存在3天内多工单的情况。

步骤3：提取目标客户并查询所有工单 #

从上面的统计结果中，提取出存在3天内多工单的客户，再关联原表获取这些客户的所有近1个月工单：

WITH ticket_stats AS (
    SELECT 
        *,
        COUNT(*) OVER (
            PARTITION BY customer_id 
            ORDER BY receive_date 
            RANGE BETWEEN INTERVAL '3 days' PRECEDING AND CURRENT ROW
        ) AS cnt_3days
    FROM tickets
    WHERE receive_date >= ADD_MONTHS(CURRENT_DATE, -1)
),
target_customers AS (
    SELECT DISTINCT customer_id
    FROM ticket_stats
    WHERE cnt_3days > 1
)
SELECT 
    t.ticket_number,
    t.customer_id,
    t.receive_date
FROM tickets t
JOIN target_customers tc ON t.customer_id = tc.customer_id
WHERE t.receive_date >= ADD_MONTHS(CURRENT_DATE, -1)
ORDER BY t.customer_id, t.receive_date;

兼容方案（针对不支持RANGE间隔的数据库） #

如果你的数据库不支持窗口函数的RANGE间隔语法，可以用LAG()函数对比前后工单的日期，或者用自连接：

方法1：用LAG()判断日期差

WITH ticket_lag AS (
    SELECT 
        *,
        LAG(receive_date, 1) OVER (PARTITION BY customer_id ORDER BY receive_date) AS prev_date
    FROM tickets
    WHERE receive_date >= ADD_MONTHS(CURRENT_DATE, -1)
),
target_customers AS (
    SELECT DISTINCT customer_id
    FROM ticket_lag
    WHERE DATEDIFF(receive_date, prev_date) <= 3 AND prev_date IS NOT NULL
)
SELECT 
    t.ticket_number,
    t.customer_id,
    t.receive_date
FROM tickets t
JOIN target_customers tc ON t.customer_id = tc.customer_id
WHERE t.receive_date >= ADD_MONTHS(CURRENT_DATE, -1)
ORDER BY t.customer_id, t.receive_date;

方法2：自连接找符合条件的客户

WITH target_customers AS (
    SELECT DISTINCT t1.customer_id
    FROM tickets t1
    JOIN tickets t2 
        ON t1.customer_id = t2.customer_id
        AND t1.ticket_number != t2.ticket_number
        AND t2.receive_date BETWEEN t1.receive_date - INTERVAL '3 days' AND t1.receive_date
    WHERE t1.receive_date >= ADD_MONTHS(CURRENT_DATE, -1)
)
SELECT 
    t.ticket_number,
    t.customer_id,
    t.receive_date
FROM tickets t
JOIN target_customers tc ON t.customer_id = tc.customer_id
WHERE t.receive_date >= ADD_MONTHS(CURRENT_DATE, -1)
ORDER BY t.customer_id, t.receive_date;

结果验证 #

用你提供的示例数据测试，以上方案都会返回期望的结果：

• 客户0001的NY1和NY3间隔1天，符合3天内多工单的条件，所以这两张工单都被纳入；
• 客户0003的MD1、MD2、MD3都在3天窗口内，全部被纳入；
• 客户0002只有1张工单，不符合条件，被排除。

内容的提问来源于stack exchange，提问作者Joshua Pelton-Stroud