本文介紹了Azure Synapse Analytics中的日期遞歸查詢的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！
問題描述
在下表中，如何在Start_Date和End_Date列之間插入年份的第一個和最后一個日期的行？
| EMPID | 名稱 | 開始日期 | 結(jié)束日期 |
|---|---|---|---|
| 1001 | Shivansh | 2015-09-01 | 2018-03-31 |
| 1004 | Mayank | 2019-04-01 | 2020-06-30 |
輸出應(yīng)如下所示：
| EMPID | 名稱 | 開始日期 | 結(jié)束日期 |
|---|---|---|---|
| 1001 | Shivansh | 2015-09-01 | 2015-12-31 |
| 1001 | Shivansh | 2016-01-01 | 2016-12-31 |
| 1001 | Shivansh | 2017-01-01 | 2017-12-31 |
| 1001 | Shivansh | 2018-01-01 | 2018-03-31 |
| 1004 | Mayank | 2019-04-01 | 2019-12-31 |
| 1004 | Mayank | 2020-01-01 | 2020-06-30 |
這必須使用循環(huán)實(shí)現(xiàn)，因?yàn)?strong>Azure Synapse Analytics不支持遞歸公用表表達(dá)式
推薦答案
此方法使用一個數(shù)字表和許多在Azure Synapse Analytics專用SQL池中可用的日期函數(shù)，包括DATEFROMPARTS、DATEDIFF和YEAR。
NB這不是遞歸查詢。在創(chuàng)建Numbers表時使用了一個循環(huán)，但這只執(zhí)行一次。一旦存在Numbers表，它就可以用于類似的方案，例如將遞歸CTE轉(zhuǎn)換為與Azure Synapse Analytics兼容的基于集合的方法。
DATEFROMPARTS用于構(gòu)造計(jì)算記錄中一年的第一天。然后我使用DATEADD添加一年，然后去掉一天，得到一年的最后一天。DATEDIFFWith Year用于確定兩個日期之間的年份間隔，從而確定需要添加的記錄數(shù)。然后UNION完整結(jié)果的原始記錄和計(jì)算記錄。
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
GO
CREATE TABLE #tmp (
empId INT NOT NULL,
empName VARCHAR(50) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NULL
)
GO
-- Setup test data
INSERT INTO #tmp ( empId, empName, start_date, end_date )
SELECT 1001, 'Shivansh', '2015-09-01', '2018-03-31'
UNION ALL
SELECT 1004, 'Mayank', '2019-04-01', '2020-06-30'
GO
;WITH cte AS (
SELECT *,
DATEFROMPARTS( YEAR(start_date) + n.number, 1, 1 ) newStart
FROM #tmp t
CROSS JOIN dbo.numbers n
WHERE n.number <= DATEDIFF( year, start_date, end_date )
)
SELECT 'o' s, empId, empName, start_date,
CASE
WHEN YEAR(start_date) = YEAR(end_date) THEN end_date
ELSE DATEFROMPARTS( YEAR(start_date), 12, 13 )
END end_date
FROM #tmp
UNION ALL
SELECT 'c', empId, empName,
newStart AS start_date,
CASE
WHEN YEAR(end_date) = YEAR(newStart) THEN end_date
ELSE DATEADD( day, -1, DATEADD( year, 1, newStart ) )
END newEnd
FROM cte
ORDER BY empId, start_date
我的結(jié)果：
我添加了o和c來表示原始行和計(jì)算行，但如果愿意，您可以刪除該列。如果您還沒有數(shù)字表格，那么我用來創(chuàng)建這個表格的腳本是here。此代碼已在Azure Synapse Analytics專用SQL池上進(jìn)行了測試，版本為Microsoft Azure SQL數(shù)據(jù)倉庫-10.0.15554.0 Dec 10 2020 03：11：10。
這篇關(guān)于Azure Synapse Analytics中的日期遞歸查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，
