递归CTE是否可行？按时间规则筛选销售数据技术求助

核心思路 #

用递归CTE分两步实现需求：

• 锚点成员：确定第一条要选中的记录（按业务逻辑取最早的销售记录）
• 递归成员：每次从剩余记录中找到比上一条已选中记录晚至少6个月的最早记录，直到没有符合条件的记录为止

通用SQL实现（以MySQL为例） #

假设你的数据表名为sales，包含id、sale_date以及其他业务字段：

WITH RECURSIVE selected_sales AS (
    -- 锚点：取最早的销售记录作为起始点
    SELECT id, sale_date
    FROM sales
    ORDER BY sale_date ASC
    LIMIT 1

    UNION ALL

    -- 递归：筛选比上一条选中记录晚6个月以上的最早记录
    SELECT s.id, s.sale_date
    FROM sales s
    JOIN selected_sales ss 
      ON s.sale_date >= DATE_ADD(ss.sale_date, INTERVAL 6 MONTH)
    -- 确保只选符合条件的最早记录，避免一次性多选
    WHERE NOT EXISTS (
        SELECT 1
        FROM sales s2
        WHERE s2.sale_date >= DATE_ADD(ss.sale_date, INTERVAL 6 MONTH)
          AND s2.sale_date < s.sale_date
    )
    LIMIT 1
)
-- 关联原表获取完整记录
SELECT s.*
FROM sales s
JOIN selected_sales ss ON s.id = ss.id
ORDER BY s.sale_date ASC;

适配不同数据库 #

• PostgreSQL：把DATE_ADD(ss.sale_date, INTERVAL 6 MONTH)替换为ss.sale_date + INTERVAL '6 months'
• SQL Server：替换为DATEADD(month, 6, ss.sale_date)

分组筛选（按销售人员/客户） #

如果需要按销售人员或客户分别筛选，只需在锚点和递归成员中加入分组条件：

WITH RECURSIVE selected_sales AS (
    -- 按销售人员+客户取最早记录
    SELECT id, sale_date, salesperson_id, customer_id
    FROM sales
    WHERE salesperson_id = 'XXX' AND customer_id = 'YYY'
    ORDER BY sale_date ASC
    LIMIT 1

    UNION ALL

    SELECT s.id, s.sale_date, s.salesperson_id, s.customer_id
    FROM sales s
    JOIN selected_sales ss 
      ON s.salesperson_id = ss.salesperson_id
      AND s.customer_id = ss.customer_id
      AND s.sale_date >= DATE_ADD(ss.sale_date, INTERVAL 6 MONTH)
    WHERE NOT EXISTS (
        SELECT 1
        FROM sales s2
        WHERE s2.salesperson_id = ss.salesperson_id
          AND s2.customer_id = ss.customer_id
          AND s2.sale_date >= DATE_ADD(ss.sale_date, INTERVAL 6 MONTH)
          AND s2.sale_date < s.sale_date
    )
    LIMIT 1
)
SELECT s.*
FROM sales s
JOIN selected_sales ss ON s.id = ss.id
ORDER BY s.sale_date ASC;

关键说明 #

• 递归成员中的NOT EXISTS和LIMIT 1是核心：确保每次只选符合条件的最早记录，严格遵循“以上一条已选中记录为基准”的逻辑，而不是和原始数据中的上一条记录对比
• 该查询支持数据表持续新增记录，每次执行都会自动纳入最新数据重新筛选

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