如何查询customer_service_ticket表中开启30天免费窗口的可收费工单？

核心需求是筛选出每个付费周期的起始工单——这类工单提交时需付费，同时开启后续30天的免费提交窗口；窗口内的工单无需付费，窗口过期后首次提交的工单则开启新的付费周期。

方案1：递归CTE实现 #

通过递归方式逐个判断每个工单是否需要开启新的付费周期：

WITH recursive ticket_cycles AS (
    -- 初始化：取最早的工单作为第一个收费周期起点
    SELECT 
        date,
        date AS cycle_start
    FROM customer_service_ticket
    ORDER BY date
    LIMIT 1

    UNION ALL

    -- 递归遍历后续工单，判断是否触发新周期
    SELECT 
        t.date,
        CASE 
            WHEN t.date > tc.cycle_start + INTERVAL '30 days' THEN t.date
            ELSE tc.cycle_start
        END AS cycle_start
    FROM customer_service_ticket t
    JOIN ticket_cycles tc ON t.date > tc.date
    -- 确保每个工单仅关联前一个周期记录，避免重复计算
    WHERE NOT EXISTS (
        SELECT 1 FROM ticket_cycles tc2 
        WHERE tc2.date > tc.date AND tc2.date < t.date
    )
)
-- 筛选出自身就是周期起点的工单（即收费工单）
SELECT DISTINCT date 
FROM ticket_cycles 
WHERE date = cycle_start
ORDER BY date;

方案2：窗口函数累加实现（更高效） #

利用窗口函数标记新周期的触发点，再通过累加生成周期ID，最终提取每个周期的起始工单：

WITH ticket_ordered AS (
    SELECT 
        date,
        -- 标记当前工单是否为新周期候选：第一个工单/与上一个工单间隔超30天
        CASE 
            WHEN LAG(date) OVER (ORDER BY date) IS NULL THEN 1
            WHEN date > LAG(date) OVER (ORDER BY date) + INTERVAL '30 days' THEN 1
            ELSE 0
        END AS is_new_candidate
    FROM customer_service_ticket
),
cycle_markers AS (
    SELECT 
        date,
        -- 累加标记生成唯一周期ID
        SUM(is_new_candidate) OVER (ORDER BY date) AS cycle_id
    FROM ticket_ordered
)
-- 每个周期的最早工单即为收费工单
SELECT date 
FROM cycle_markers
GROUP BY cycle_id, date
HAVING date = MIN(date)
ORDER BY date;

验证示例 #

针对工单日期：1月3日、1月4日、1月8日、1月30日、2月4日、2月20日、3月20日：

• 1月3日：首个工单，触发新周期，标记为收费
• 1月4日-1月30日：均在1月3日+30天窗口内，属于同一周期，不收费
• 2月4日：超出1月3日的30天窗口，触发新周期，标记为收费
• 2月20日：在2月4日+30天窗口内，不收费
• 3月20日：超出2月4日的30天窗口，触发新周期，标记为收费

最终返回结果：1月3日、2月4日、3月20日，符合需求。

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