如何创建SQL窗口函数标记30天窗口的起始交易

核心逻辑修正 #

之前用LAG()函数的局限在于，它仅对比当前交易与上一笔交易的时间差，但如果上一笔交易属于更早的30天窗口（即上一笔交易距离它自身的窗口起始已超过30天），当前交易仍需被标记为新窗口起点。正确的思路应该是：追踪每个客户最近一次的窗口起始时间，判断当前交易与该时间的间隔是否超过30天，若是则标记为新窗口起始，同时更新最近窗口起始时间为当前交易时间。

具体SQL实现（以PostgreSQL为例，其他数据库可适配） #

假设交易表包含customer_id（客户ID）、transaction_date（交易日期）、transaction_id（交易ID）字段，可通过累积窗口函数实现需求：

WITH ranked_transactions AS (
    SELECT
        customer_id,
        transaction_id,
        transaction_date,
        -- 标记客户的第一笔交易为初始窗口起始
        CASE WHEN ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY transaction_date) = 1 THEN 1 ELSE 0 END AS is_initial_window
    FROM your_transaction_table
),
window_tracking AS (
    SELECT
        *,
        -- 累积追踪最新的窗口起始日期：如果当前是新窗口则用当前日期，否则沿用之前的起始日期
        MAX(CASE WHEN is_initial_window = 1 THEN transaction_date END) 
            OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_window_start
    FROM ranked_transactions
),
final_marking AS (
    SELECT
        customer_id,
        transaction_id,
        transaction_date,
        CASE
            -- 第一笔交易直接标记
            WHEN is_initial_window = 1 THEN 1
            -- 判断当前交易与最近窗口起始的间隔是否超过30天
            WHEN transaction_date > last_window_start + INTERVAL '30 days' THEN 1
            ELSE 0
        END AS is_new_window_start
    FROM window_tracking
)
SELECT * FROM final_marking ORDER BY customer_id, transaction_date;

逻辑拆解 #

1. ranked_transactions：按客户分组、交易日期排序，标记每个客户的第一笔交易为初始窗口起始。
2. window_tracking：用累积MAX()函数，始终保留当前行及之前所有行中最新的窗口起始日期，确保追踪的是最近的窗口起点而非仅上一笔交易。
3. final_marking：对非初始交易，判断其与最近窗口起始的时间差是否超过30天，满足则标记为新窗口起始。

跨数据库适配 #

• MySQL：将INTERVAL '30 days'替换为INTERVAL 30 DAY
• SQL Server：将判断条件改为transaction_date > DATEADD(day, 30, last_window_start)

以SQL Server为例，修正后的判断逻辑：

WHEN transaction_date > DATEADD(day, 30, last_window_start) THEN 1

这种方式能正确识别你示例中的第1、4、5行——第4行虽与第2行间隔31天，但第2行属于第1行的30天窗口，第4行距离第1行的窗口起始已超过30天，因此会被标记为新窗口起始。

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