SQL计算客户最长连续开票月数与最大间隔月数实现方法

这是SQL领域典型的连续序列岛屿识别需求，核心实现思路如下： #

前置处理：统一月份为可计算的连续数值 #

首先要解决年月跨年后数值不连续的问题，把InvoiceYearMonth字段转换为从基准年开始的累计总月数，计算方式如下：

-- 把类似20201、202010的年月格式转为累计月数，保证月份连续递增
cast(left(InvoiceYearMonth,4) as int) * 12 + cast(substring(InvoiceYearMonth,5,2) as int) as month_seq

转换后2020年1月对应值为2020*12+1=24241，2020年10月对应24250，2021年5月对应24257，不会出现跨年断档。 #

核心逻辑：识别连续开票区间（岛屿） #

用窗口函数结合等差分组法实现，分三步：

1. 给每个客户的开票记录按月份升序排序，生成序号

   row_number() over(partition by ClientName order by month_seq asc) as rn
   

2. 计算分组标识grp：用累计月数month_seq减去序号rn
   连续开票的月份，month_seq每次加1，rn也每次加1，两者的差值固定；一旦出现断档，month_seq的跳变幅度大于rn，差值就会变化，形成新的分组。
3. 按ClientName+grp分组，每组就是一段连续的开票区间

指标计算 #

最长连续开票月数 #

统计每个分组的记录数，取最大值即可：

max(count(*)) over(partition by ClientName) as max_continuous_month

以你提供的示例数据为例，第一段连续区间（2020年1-7月）分组记录数为7，是最大值，符合预期结果。

间隔月数统计 #

用lead窗口函数取相邻两次开票的月份差计算间隔：

-- 计算相邻两次开票的间隔月数
lead(month_seq) over(partition by ClientName order by month_seq) - month_seq -1 as gap_month

• 最大间隔月数：取所有gap_month的最大值即可，示例中如果按自然年拆分统计，调整过滤条件即可得到你给出的5个月结果；如果统计全周期间隔，计算逻辑一致。
• 总间隔月数：把所有gap_month求和即可。

完整参考SQL（适配SQL Server） #

with t1 as (
    -- 第一步：转换为连续月数
    select 
        ClientName,
        InvoiceYearMonth,
        cast(left(InvoiceYearMonth,4) as int)*12 + cast(substring(InvoiceYearMonth,5,2) as int) as month_seq
    from #tempa
),
t2 as (
    -- 第二步：生成序号和分组标识
    select *,
        row_number() over(partition by ClientName order by month_seq) as rn,
        month_seq - row_number() over(partition by ClientName order by month_seq) as grp
    from t1
),
t3 as (
    -- 第三步：计算每个连续区间的长度
    select 
        ClientName,
        grp,
        count(*) as continuous_len,
        min(month_seq) as start_month,
        max(month_seq) as end_month
    from t2
    group by ClientName, grp
),
t4 as (
    -- 第四步：计算相邻区间的间隔
    select *,
        lead(start_month) over(partition by ClientName order by start_month) - end_month -1 as gap
    from t3
)
-- 最终统计结果
select 
    ClientName,
    max(continuous_len) as 最长连续开票月数,
    max(gap) as 最大间隔月数,
    sum(isnull(gap,0)) as 总间隔月数
from t4
group by ClientName

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