SQL如何统计连续达标的月份_ROW_NUMBER与日期关联算法

在业务数据统计工作中，经常需要统计某个主体连续达标的月份数量，比如统计连续3个月销售额超过10万的门店，或者连续6个月能耗低于阈值的设备。这类需求如果直接用分组统计很难实现，结合ROW_NUMBER窗口函数和日期关联算法可以轻松解决。

核心实现思路

连续达标统计的核心逻辑是：先筛选出所有达标的月份记录，然后对每个主体的达标月份排序，用达标月份减去排序序号得到同一个连续区间的标识值，最后对标识值分组统计数量即可。

这里用到的两个关键技术点分别是：

• ROW_NUMBER窗口函数：为每个主体的达标月份生成连续的序号，序号从1开始递增
• 日期关联算法：通过月份日期和ROW_NUMBER序号的差值，判断是否属于同一个连续区间

具体实现示例

场景说明

假设我们有门店销售记录表store_sales，表结构如下：

需求是统计每个门店连续销售额超过10万的月份数量，以及对应的连续区间起始和结束月份。

实现步骤

第一步：筛选达标月份

先筛选出所有销售额超过10万的记录，作为后续统计的基础数据。

-- 筛选达标月份
SELECT 
    store_id,
    sale_month,
    sale_amount
FROM store_sales
WHERE sale_amount > 100000

第二步：生成ROW_NUMBER序号

对每个门店的达标月份按时间升序生成ROW_NUMBER序号，序号从1开始递增。

-- 生成序号
SELECT 
    store_id,
    sale_month,
    sale_amount,
    ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY sale_month) AS rn
FROM (
    -- 筛选达标月份
    SELECT 
        store_id,
        sale_month,
        sale_amount
    FROM store_sales
    WHERE sale_amount > 100000
) t1

第三步：计算连续区间标识

用达标月份减去序号对应的月份间隔，同一个连续区间的记录会得到相同的标识值。这里需要将日期转换为月份数来计算差值，比如2023-01-01对应月份数为2023*12+1=24277，2023-02-01对应24278，以此类推。

-- 计算连续区间标识
SELECT 
    store_id,
    sale_month,
    sale_amount,
    rn,
    -- 将日期转换为月份数，减去序号得到连续区间标识
    (YEAR(sale_month) * 12 + MONTH(sale_month)) - rn AS continuous_flag
FROM (
    -- 生成序号
    SELECT 
        store_id,
        sale_month,
        sale_amount,
        ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY sale_month) AS rn
    FROM (
        -- 筛选达标月份
        SELECT 
            store_id,
            sale_month,
            sale_amount
        FROM store_sales
        WHERE sale_amount > 100000
    ) t1
) t2

第四步：分组统计连续达标情况

对store_id和continuous_flag分组，统计每个连续区间的月份数量、起始月份和结束月份。

-- 最终统计结果
SELECT 
    store_id,
    MIN(sale_month) AS start_month,
    MAX(sale_month) AS end_month,
    COUNT(*) AS continuous_month_count
FROM (
    -- 计算连续区间标识
    SELECT 
        store_id,
        sale_month,
        sale_amount,
        rn,
        (YEAR(sale_month) * 12 + MONTH(sale_month)) - rn AS continuous_flag
    FROM (
        -- 生成序号
        SELECT 
            store_id,
            sale_month,
            sale_amount,
            ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY sale_month) AS rn
        FROM (
            -- 筛选达标月份
            SELECT 
                store_id,
                sale_month,
                sale_amount
            FROM store_sales
            WHERE sale_amount > 100000
        ) t1
    ) t2
) t3
GROUP BY store_id, continuous_flag
ORDER BY store_id, start_month

算法原理说明

为什么用月份数减去ROW_NUMBER序号就能得到连续区间标识？我们可以举个例子：

假设某个门店的达标月份分别是2023-01、2023-02、2023-03、2023-05、2023-06：

• 2023-01对应月份数24277，ROW_NUMBER是1，差值为24276
• 2023-02对应月份数24278，ROW_NUMBER是2，差值为24276
• 2023-03对应月份数24279，ROW_NUMBER是3，差值为24276
• 2023-05对应月份数24281，ROW_NUMBER是4，差值为24277
• 2023-06对应月份数24282，ROW_NUMBER是5，差值为24277

可以看到连续的三个月份差值相同，断开后的月份差值发生了变化，这样就能通过分组统计出每个连续区间的长度。

注意事项

• 日期计算时要确保sale_month是当月的第一天，避免月份计算错误
• 如果达标月份存在重复记录，需要先去重再进行计算
• 不同数据库的日期函数可能有差异，上述示例基于MySQL，其他数据库可调整日期转月份数的逻辑

这种方法的性能表现较好，即使数据量较大，只要对store_id和sale_month建立联合索引，查询效率也能得到保障。

SQLROW_NUMBER连续达标月份日期关联算法修改时间：2026-07-06 03:36:25