~楓花雪岳~: [SQL] 分析函數應用

星期二, 10月 15, 2024

[SQL] 分析函數應用 - 排班班表

社群問題，簡化為下圖來理解，資料來源只顯示單一位員工資訊

相關商業邏輯

WorkWay 班別會混雜 [特、休] 休假資訊，A 代表 08-16 早班時段、B 代表 13-21 午班時段
[特、休] 所屬班別判斷邏輯：班別是若遇當月休假換班別，就歸屬前一個班別，例如 10/13 休 10/14 換成 A 班，那麼 10/13 會往前抓到最後一天的班別，但是像月初特休就會往後抓班別來判斷

資料來源

USE TempDB
GO

DROP TABLE IF EXISTS #Attend

CREATE TABLE #Attend(
	Name nvarchar(20) ,
	WorkDate date ,
	WorkWay nvarchar(5))

INSERT INTO #Attend
VALUES 
	(N'小明','2024/10/1' ,N'A' ) , (N'小明','2024/10/2' ,N'A' ) , (N'小明','2024/10/3' ,N'A' ) , (N'小明','2024/10/4' ,N'A' ) , (N'小明','2024/10/5' ,N'休') , 
	(N'小明','2024/10/6' ,N'休') , (N'小明','2024/10/7' ,N'A' ) , (N'小明','2024/10/8' ,N'A' ) , (N'小明','2024/10/9' ,N'A' ) , (N'小明','2024/10/10',N'休') ,
	(N'小明','2024/10/11',N'A' ) , (N'小明','2024/10/12',N'休') , (N'小明','2024/10/13',N'休') , (N'小明','2024/10/14',N'B' ) , (N'小明','2024/10/15',N'B' ) ,
	(N'小明','2024/10/16',N'B' ) , (N'小明','2024/10/17',N'B' ) , (N'小明','2024/10/18',N'B' ) , (N'小明','2024/10/19',N'休') , (N'小明','2024/10/20',N'休') ,
	(N'小明','2024/10/21',N'A' ) , (N'小明','2024/10/22',N'A' ) , (N'小明','2024/10/23',N'A' ) , (N'小明','2024/10/24',N'A' ) , (N'小明','2024/10/25',N'A' ) ,
	(N'小明','2024/10/26',N'休') , (N'小明','2024/10/27',N'休') , (N'小明','2024/10/28',N'A' ) , (N'小明','2024/10/29',N'A' ) , (N'小明','2024/10/30',N'A' ) , (N'小明','2024/10/31',N'A') ,
	(N'大白','2024/10/1' ,N'特') , (N'大白','2024/10/2' ,N'特') , (N'大白','2024/10/3' ,N'B' ) , (N'大白','2024/10/4' ,N'B' ) , (N'大白','2024/10/5' ,N'休') ,	(N'大白','2024/10/6' ,N'休') , (N'大白','2024/10/7' ,N'B' ) , (N'大白','2024/10/8' ,N'B' ) , (N'大白','2024/10/9' ,N'B' ) , (N'大白','2024/10/10',N'休') ,
	(N'大白','2024/10/11',N'B' ) , (N'大白','2024/10/12',N'休') , (N'大白','2024/10/13',N'休') , (N'大白','2024/10/14',N'A' ) , (N'大白','2024/10/15',N'A' ) ,
	(N'大白','2024/10/16',N'A' ) , (N'大白','2024/10/17',N'A' ) , (N'大白','2024/10/18',N'A' ) , (N'大白','2024/10/19',N'休') , (N'大白','2024/10/20',N'休') ,
	(N'大白','2024/10/21',N'B' ) , (N'大白','2024/10/22',N'B' ) , (N'大白','2024/10/23',N'B' ) , (N'大白','2024/10/24',N'B' ) , (N'大白','2024/10/25',N'B' ) , 
	(N'大白','2024/10/26',N'休') , (N'大白','2024/10/27',N'休') , (N'大白','2024/10/28',N'B' ) , (N'大白','2024/10/29',N'B' ) , (N'大白','2024/10/30',N'B ') , (N'大白','2024/10/31',N'B')

TSQL 寫法

主要分為三個步驟，分別為

把 [特、休] 字樣改成所屬班別，使用分析視窗函數參數 IGNORE NULLS 來整理
透過連續日期、連續班別來判斷群組
產生對應需求資料

; 
WITH T1 AS 
( 
    SELECT * ,
        IIF(WorkWay IN ('A' , 'B') , WorkWay , NULL) AS NewWorkWay
    FROM #Attend
)
, T2 AS
(
    SELECT * ,
        COALESCE(
            NewWorkWay ,
            LAG(NewWorkWay) IGNORE NULLS OVER (PARTITION BY Name ORDER BY WorkDate) ,
            LEAD(NewWorkWay) IGNORE NULLS OVER (PARTITION BY Name ORDER BY WorkDate)) AS GroupNO
    FROM T1
)
, T3 AS 
(
    SELECT 
        * ,
        DATEADD(
            d , 
            ROW_NUMBER() OVER (PARTITION BY Name , GroupNO ORDER BY WorkDate) * -1 , 
            WorkDate) AS GroupDate
        FROM T2
)
, T4 AS
(
    SELECT 
        T3.Name , 
        T3.GroupDate ,
        T3.GroupNO ,
        MIN(WorkDate) AS MinDate ,
        T3.GroupNO + '：' + CAST(DAY(MIN(WorkDate)) AS varchar(2)) + '~' + CAST(DAY(MAX(WorkDate)) AS varchar(2)) AS WorkInfo
    FROM T3
    GROUP BY Name , GroupDate , GroupNO
)
SELECT
    Name ,
    STRING_AGG(WorkInfo , ' , ') WITHIN GROUP (ORDER BY MinDate)
FROM T4
GROUP BY Name

分析視窗函數參數 IGNORE NULLS

因為 WorkWay 有 [特、休] 字樣來呈現該員工休假狀態，所以要把 [特、休] 改為 null，方便後續分析函數搭配 IGNORE NULLS，下面語法故意把 LAG 產生的 PreviousValue 和 LEAD 產生的 NextValue 資料獨立出來顯示，從圖片就可以觀察到 T1 和 T2 這兩個階段處理事項

; 
WITH T1 AS 
( 
    SELECT * ,
        IIF(WorkWay IN ('A' , 'B') , WorkWay , NULL) AS NewWorkWay
    FROM #Attend
    WHERE Name = N'大白'
)
, T2 AS
(
    SELECT * ,
        -- 顯示資料來理解
        LAG(NewWorkWay) IGNORE NULLS OVER (PARTITION BY Name ORDER BY WorkDate) AS PreviousValue ,
        -- 顯示資料來理解
        LEAD(NewWorkWay) IGNORE NULLS OVER (PARTITION BY Name ORDER BY WorkDate) AS NextValue ,
        COALESCE(
            NewWorkWay ,
            LAG(NewWorkWay) IGNORE NULLS OVER (PARTITION BY Name ORDER BY WorkDate) ,
            LEAD(NewWorkWay) IGNORE NULLS OVER (PARTITION BY Name ORDER BY WorkDate)) AS GroupNO
    FROM T1
)
SELECT * FROM T2
ORDER BY Name , WorkDate

透過連續日期、連續班別來判斷群組

透過 Name 和 GroupNO 來跑 Row_Number() 並和工作日期運算來判斷群組

, T3 AS 
(
    SELECT 
        * ,
        -- 顯示資料來理解
        ROW_NUMBER() OVER (PARTITION BY Name , GroupNO ORDER BY WorkDate) * -1 AS DisplayRowNO,
        DATEADD(
            d , 
            ROW_NUMBER() OVER (PARTITION BY Name , GroupNO ORDER BY WorkDate) * -1 , 
            WorkDate) AS GroupDate
    FROM T2
)
SELECT * FROM T3
ORDER BY Name , WorkDate

產生對應需求資料

分出員工連續工作日後，就可以找出每個群組起始和結束日期，再把資料整理成問題需求，最後使用 STRING_AGG 把資料串在一起呈現

, T4 AS
(
    SELECT 
        T3.Name , 
        T3.GroupDate ,
        T3.GroupNO ,
        MIN(WorkDate) AS MinDate ,
        T3.GroupNO + '：' + CAST(DAY(MIN(WorkDate)) AS varchar(2)) + '~' + CAST(DAY(MAX(WorkDate)) AS varchar(2)) AS WorkInfo
    FROM T3
    GROUP BY Name , GroupDate , GroupNO
)
SELECT
    Name ,
    STRING_AGG(WorkInfo , ' , ') WITHIN GROUP (ORDER BY MinDate)
FROM T4
GROUP BY Name