如何根据两行日期间隔重置分区内的ROW_NUMBER值？

这是一个典型的基于时间间隔的分组行号生成问题，常规的ROW_NUMBER()没法直接处理日期间隔重置的逻辑，我们可以通过分层使用窗口函数来实现需求：

核心思路 #

1. 识别每个分区内需要重置行号的起始行（即与上一行日期间隔超过12个月的行）
2. 通过累积求和生成连续的分组ID
3. 在每个分组内使用ROW_NUMBER()生成递增的行号

完整SQL代码（以SQL Server为例） #

WITH ranked_data AS (
    SELECT 
        customer_id,
        product,
        region,
        date,
        -- 标记是否为新分组的起始行：第一行 或 与上一行间隔超12个月
        CASE 
            WHEN LAG(date) OVER (PARTITION BY customer_id, product, region ORDER BY date) IS NULL 
                THEN 1
            WHEN date > DATEADD(month, 12, LAG(date) OVER (PARTITION BY customer_id, product, region ORDER BY date))
                THEN 1
            ELSE 0
        END AS new_group_flag
    FROM your_table
),
grouped_data AS (
    SELECT 
        *,
        -- 对标记做累积求和，生成每个连续组的唯一ID
        SUM(new_group_flag) OVER (PARTITION BY customer_id, product, region ORDER BY date) AS group_id
    FROM ranked_data
)
SELECT 
    customer_id,
    product,
    region,
    date,
    -- 在每个分区+分组内生成行号
    ROW_NUMBER() OVER (PARTITION BY customer_id, product, region, group_id ORDER BY date) AS desired_row_number
FROM grouped_data
ORDER BY customer_id, date;

针对不同数据库的适配 #

如果使用其他数据库，只需调整日期间隔的判断逻辑：

• MySQL：将日期判断部分替换为TIMESTAMPDIFF(MONTH, LAG(date) OVER (...), date) > 12
• PostgreSQL：替换为date > (LAG(date) OVER (...) + INTERVAL '12 months')
• Oracle：替换为date > ADD_MONTHS(LAG(date) OVER (...), 12)

示例结果验证 #

针对你提供的测试数据，执行上述代码后会得到：

完全符合你要求的行号规则：第三行因与上一行间隔超12个月，行号重置为1。

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