如何用窗口函数与递归查询实现门店日期超20天的标记？

原始数据表 #

DATE     STORE   PRODUCT
01.01.2020  Store1  Product1
01.01.2020  Store1  Product2
01.01.2020  Store1  Product3
01.01.2020  Store1  Product4
01.02.2020  Store1  Product5
01.02.2020  Store1  Product6
01.02.2020  Store1  Product7
01.02.2020  Store1  Product8
01.01.2020  Store2  Product1
01.01.2020  Store2  Product2
01.01.2020  Store2  Product3
01.01.2020  Store2  Product4
03.01.2020  Store2  Product1
01.03.2020  Store2  Product2
02.03.2020  Store2  Product8
03.03.2020  Store2  Product10
06.08.2020  Store2  Product6
08.11.2020  Store2  Product7

需求说明 #

• 按门店分配初始编号（Store1对应1，Store2对应2）
• 同一门店内，若当前批次日期与上一批次日期间隔超过20天，后续所有记录的标记追加一个"e"
• 每触发一次超期，标记多一个"e"（如首次超期为1e，再次超期为1ee，以此类推）
• 同一日期的所有记录共用同一标记

现有问题 #

原查询仅判断单条记录与前一条的间隔，未累计超期次数，且同日期记录未统一标记，导致输出不符合预期。

递归CTE解决方案 #

通过递归CTE累计超期次数，生成正确标记，步骤如下：

1. 提取每个门店的唯一日期并排序，为后续迭代做准备
2. 递归初始化：给每个门店的最早日期分配初始标记
3. 递归迭代：依次处理后续日期，判断日期间隔是否超20天，更新标记
4. 关联原始表，将标记匹配到每条记录上

完整SQL代码 #

WITH date_groups AS (
    -- 提取每个门店的唯一日期并排序，分配行号
    SELECT 
        store,
        date,
        ROW_NUMBER() OVER (PARTITION BY store ORDER BY date) AS rn
    FROM (
        SELECT DISTINCT store, date FROM your_table
    ) t
),
recursive_marks AS (
    -- 递归初始化：每个门店的第一条日期记录，生成初始标记
    SELECT 
        store,
        date,
        CAST(DENSE_RANK() OVER (ORDER BY store) AS VARCHAR) AS mark
    FROM date_groups
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归迭代：处理后续日期，根据间隔更新标记
    SELECT 
        dg.store,
        dg.date,
        CASE 
            WHEN dg.date - rm.date > 20 THEN rm.mark || 'e'
            ELSE rm.mark
        END AS mark
    FROM date_groups dg
    JOIN recursive_marks rm ON dg.store = rm.store AND dg.rn = rm.rn + 1
)
-- 关联原始表，输出最终结果
SELECT 
    t.date,
    t.store,
    t.product,
    rm.mark AS product_x
FROM your_table t
JOIN recursive_marks rm ON t.store = rm.store AND t.date = rm.date
ORDER BY t.store, t.date, t.product;

代码解释 #

• date_groups：去重门店日期并排序，给每个日期分配行号，保证递归按时间顺序处理
• recursive_marks：
  • 初始化部分：用DENSE_RANK()生成门店编号，作为初始标记
  • 递归部分：通过行号关联上一日期的记录，判断间隔是否超20天，决定是否追加"e"
• 最终关联原始表，将标记匹配到每条记录，按门店、日期、产品排序输出

预期输出 #

date Store   Product Product_x
01.01.2020  Store1  Product1    1
01.01.2020  Store1  Product2    1
01.01.2020  Store1  Product3    1
01.01.2020  Store1  Product4    1
01.02.2020  Store1  Product5    1e
01.02.2020  Store1  Product6    1e
01.02.2020  Store1  Product7    1e
01.02.2020  Store1  Product8    1e
01.01.2020  Store2  Product1    2
01.01.2020  Store2  Product2    2
01.01.2020  Store2  Product3    2
01.01.2020  Store2  Product4    2
03.01.2020  Store2  Product1    2
01.03.2020  Store2  Product2    2e
02.03.2020  Store2  Product8    2e
03.03.2020  Store2  Product10   2e
06.08.2020  Store2  Product6    2ee
08.11.2020  Store2  Product7    2eee

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