SQL Server自定义SeqNumber序列号列计算逻辑错误排查

原有实现及存在的问题 #

最初使用的计算语句如下：

((ROW_NUMBER() OVER (PARTITION by FileDate ORDER BY AutoNumberKey)-1)/(CalcSeqNumber)+1) SeqNumber

该语句在CalcSeqNumber=5时运行符合预期，可将070、071、300、301、306归为同一组，但是CalcSeqNumber=3时计算结果不符合要求。

预期逻辑 #

每当遇到以070为起始的新5长度序列，或是在3长度序列内遇到新的300值时，就重启SeqNumber的计数。

当前错误输出结果 #

测试表及样例数据脚本 #

CREATE TABLE [dbo].[SeqDataCheck](
    [FileDate] [varchar](50) NULL,
    [RTI] [varchar](50) NULL,
    [AutoNumberKey] [int] NULL,
    [CalcSeqNumber] [int] NULL  
 ) ON [PRIMARY]
 GO

INSERT INTO [dbo].[SeqDataCheck]
           ([FileDate]
           ,[RTI]
           ,[AutoNumberKey]
           ,[CalcSeqNumber])       
     VALUES
           ('20211004','070',55644,5),
           ('20211004','071',55645,5),
           ('20211004','300',55646,5),
           ('20211004','301',55647,5),
           ('20211004','306',55648,5),
           ('20211004','300',55649,5),
           ('20211004','301',55650,5),
           ('20211004','306',55651,5),
           ('20211004','300',55652,5),
           ('20211004','301',55653,5),
           ('20211004','306',55654,5),
           ('20211004','300',55655,5),
           ('20211004','301',55656,5),
           ('20211004','306',55657,5),
           ('20211004','300',55658,5),
           ('20211004','301',55659,5),
           ('20211004','306',55660,5)
GO

正确实现代码 #

通过标记分组起点+累加标记的窗口函数逻辑即可实现需求：

WITH GroupStartMark AS (
    SELECT *,
        CASE 
            -- 满足新分组条件则标记为1，否则为0
            WHEN RTI = '070' OR (CalcSeqNumber = 3 AND RTI = '300') 
            THEN 1 
            ELSE 0 
        END AS IsNewGroup
    FROM SeqDataCheck
)
SELECT FileDate,RTI,AutoNumberKey,CalcSeqNumber,
    -- 按日期分区、按自增键排序累加标记值，得到分组编号
    SUM(IsNewGroup) OVER (
        PARTITION BY FileDate 
        ORDER BY AutoNumberKey 
        ROWS UNBOUNDED PRECEDING
    ) AS SeqNumber
FROM GroupStartMark
ORDER BY AutoNumberKey

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