如何为按动态7天分组的客户交易记录生成trans_flag列？

这个需求的核心是基于上一个标记记录的动态时间窗口，而非固定日历周期，常规窗口函数很难直接适配这种依赖前序结果的分组逻辑，我推荐用递归CTE来处理，它能完美实现你的需求。

实现步骤 #

先对每个客户的交易按日期倒序排序，给每条记录分配行号方便递归遍历，再通过递归逻辑动态判断分组边界：

WITH ranked_trans AS (
    SELECT 
        trans_number,
        client,
        trans_date,
        -- 用窗口函数替代子查询，高效计算当前记录与下一条晚交易的天数差
        DATEDIFF(day, trans_date, LEAD(trans_date) OVER (PARTITION BY client ORDER BY trans_date DESC)) AS date_diff,
        ROW_NUMBER() OVER (PARTITION BY client ORDER BY trans_date DESC) AS rn
    FROM [table]
    -- 可移除WHERE条件，适配所有客户的批量处理
    WHERE client = 'client1'
),
recursive_groups AS (
    -- 锚点：每个客户的最新交易作为第一个分组起点
    SELECT 
        trans_number,
        client,
        trans_date,
        date_diff,
        rn,
        1 AS trans_flag,
        trans_date AS group_start_date
    FROM ranked_trans
    WHERE rn = 1

    UNION ALL

    -- 递归遍历后续交易，动态判断分组
    SELECT 
        rt.trans_number,
        rt.client,
        rt.trans_date,
        rt.date_diff,
        rt.rn,
        -- 若当前交易与上一组起点的日期差超过7天，标记为新组起点
        CASE WHEN DATEDIFF(day, rt.trans_date, rg.group_start_date) > 7 THEN 1 ELSE 0 END AS trans_flag,
        -- 新组更新起点日期，旧组沿用原起点
        CASE WHEN DATEDIFF(day, rt.trans_date, rg.group_start_date) > 7 THEN rt.trans_date ELSE rg.group_start_date END AS group_start_date
    FROM ranked_trans rt
    JOIN recursive_groups rg ON rt.client = rg.client AND rt.rn = rg.rn + 1
)
SELECT 
    trans_number,
    client,
    trans_date,
    date_diff,
    trans_flag
FROM recursive_groups
ORDER BY trans_date DESC;

逻辑拆解 #

1. ranked_trans CTE：

   • 用LEAD()窗口函数高效获取当前记录的下一条晚交易日期，计算天数差得到date_diff，和你原SQL结果一致但性能更优；
   • 用ROW_NUMBER()给每个客户的交易按日期倒序编号，为递归遍历提供顺序依据。

2. recursive_groups CTE：

   • 锚点部分：取每个客户的最新交易（行号1），标记为分组起点（trans_flag=1），并记录该日期为组起始日期；
   • 递归部分：依次遍历后续交易，判断当前交易与上一组起始日期的天数差：
     • 若差超过7天，标记为新组起点（trans_flag=1），并更新组起始日期为当前交易日期；
     • 若差≤7天，标记为当前组成员（trans_flag=0），沿用原组起始日期。

验证结果 #

运行代码后将得到你期望的结果：

比如abc007与上一个标记的abc003日期差为8天（超过7天），因此标记为新组起点；abc005与abc003日期差为6天（≤7天），因此标记为0，完全符合你的需求逻辑。

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