[SQL] 視窗函數應用 - 連續字串分組

延續 [SQL] 分析函數應用 - 排班班表，班別基本上是連續字串，想要直接處理字串就產生群組，而不依賴日期欄位，資料來源和 TSQL 流程如下圖

TSQL

DECLARE @Temp TABLE
(
    ID int identity(1,1) ,
    Col1 char(1)
)

INSERT INTO @Temp VALUES
    ('A') , ('A') , ('A') , ('B') , ('C') , ('C') , ('A') , ('A') ,  ('A')

;
WITH T1 AS
(
    SELECT 
        * , 
        -- 第三參數：null 的預設值
        LAG(Col1 , 1 , Col1) OVER (ORDER BY ID)AS PreValue
    FROM @Temp
)
, T2 AS
(
    SELECT
        * ,
        -- 判斷連續字串是否有變
        CAST(IIF(Col1 = PreValue , 0 , 1) AS int) AS GroupChange
    FROM T1
)
, T3 AS
(
    SELECT
        * ,
        -- 使用彙總視窗函數跑 Running Total
        SUM(GroupChange) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING) AS GroupNO 
	FROM T2
)
SELECT * 
FROM T3
ORDER BY ID

LAG default 參數

官方文件說明

The value to return when offset is beyond the scope of the partition. If a default value is not specified, NULL is returned. default can be a column, subquery, or other expression, but it cannot be an analytic function. default must be type-compatible with scalar_expression.

該範例就是把第一筆資料的 null 塞 A 來取代

• 延伸閱讀
• [SQL] 次序函數應用-資料分組
• [SQL] 彙總函數應用 - 資料分組