基于具有動態偏移的LAG函式獲取先前的非NULL值

我有一個棘手的情況。

我有一個源資料集；它具有基于生效日期的四名員工及其部門的資料。

我需要將此源資料集轉換為目標資料集。

兩個資料集均按 EmployeeName 和 EffectiveDate (ASC) 順序正確排序。

（請參閱使用臨時表的 T-SQL 腳本。）

CREATE TABLE #source

(

EmployeeName varchar(100),
EffectiveDate date,
CurrentDepartment varchar(100)

);

INSERT INTO #source

VALUES

('Lisa','2017-06-25','Catering'),
('Lisa','2018-08-17',NULL),
('Lisa','2021-12-05','Gardening'),
('Melissa','2015-08-27',NULL),
('Melissa','2017-11-29','Office'),
('Melissa','2020-10-10','Driving'),
('Melissa','2022-07-11',NULL),
('Omar','2019-01-03',NULL),
('Omar','2020-04-07','Retail'),
('Omar','2021-03-29',NULL),
('Pat', '2012-09-12','Laundry'),
('Pat', '2013-10-30',NULL),
('Pat', '2014-11-29',NULL),
('Pat', '2015-08-16',NULL),
('Pat', '2016-11-05',NULL)


CREATE TABLE #destination

(

EmployeeName varchar(100),
EffectiveDate date,
CurrentDepartment varchar(100),
PreviousNonNULLDepartmentIfAvailable varchar(100)

);

INSERT INTO #destination

VALUES

('Lisa','2017-06-25','Catering',NULL),
('Lisa','2018-08-17',NULL,'Catering'),
('Lisa','2021-12-05','Gardening','Catering'),
('Melissa','2015-08-27',NULL,NULL),
('Melissa','2017-11-29','Office',NULL),
('Melissa','2020-10-10','Driving','Office'),
('Melissa','2022-07-11',NULL,'Driving'),
('Omar','2019-01-03',NULL,NULL),
('Omar','2020-04-07','Retail',NULL),
('Omar','2021-03-29',NULL,'Retail'),
('Pat', '2012-09-12','Laundry',NULL),
('Pat', '2013-10-30',NULL,'Laundry'),
('Pat', '2014-11-29',NULL,'Laundry'),
('Pat', '2015-08-16',NULL,'Laundry'),
('Pat', '2016-11-05',NULL,'Laundry')



SELECT *
FROM #source
ORDER BY EmployeeName, EffectiveDate


SELECT *
FROM #destination
ORDER BY EmployeeName, EffectiveDate

在目標資料集中，我需要一個名為 [PreviousNonNULLDepartmentIfAvailable] 的新列。

推匯出以上新列的邏輯是什么？

我需要獲取每個人最近（以前）的部門；使用 LAG 函式很容易獲得最近（以前）的部門。請參閱下面的 T-SQL 代碼：

PreviousNonNULLDepartmentIfAvailable = LAG(CurrentDepartment) OVER(PARTITION BY EmployeeName ORDER BY EffectiveDate)

但是，我需要最近的（以前的）非 NULL 部門；如果 EmployeeName 的 PARTITION 中沒有這樣的“最近（以前）非 NULL”部門值，那么我需要顯示 NULL。

我嘗試過諸如 LAG、LAST_VALUE、IGNORE NULLS 子句、UNBOUNDED PRECEDING 子句等選項。這些選項接近我需要的，但不完全是我需要的。

實際上，我需要了解 LAG 函式的功能；但是 LAG 函式的偏移值必須是動態的，而不是靜態值，例如 1 或 2 或 3...；LAG 函式需要根據需要迭代（向后）盡可能多的行，以在 EmployeeName 的 PARTITION 內捕獲最新（先前）非 NULL 部門值。

這就是說，如果在 EmployeeName 的 PARTITION 中沒有這樣的“最近（以前）非 NULL”部門值可用，則列 [PreviousNonNULLDepartmentIfAvailable]仍然可以具有 NULL 值。

此外，根據 EmployeeName 每個磁區的 Effective Date 升序排列的第一行將始終將 NULL 作為其 [PreviousNonNULLDepartmentIfAvailable] 值（顯然）。這在 LAG 函式的作業方式中是很自然的。

關于如何將源資料集轉換為目標資料集的任何想法？

uj5u.com熱心網友回復：

LAG, ROW_NUMBER, a的組合Flag來檢查值，一個子查詢來獲取CurrentDepartment帶有a的NULL第一行號（基于and ），以及一個檢查是否應該在這里完成作業的陳述句：CurrentDepartmentIS NOT NULLRNFlagCASELag_Value IS NULL

WITH CTE AS (SELECT
               EmployeeName, EffectiveDate, CurrentDepartment,
               LAG(CurrentDepartment) OVER(PARTITION BY EmployeeName ORDER BY EffectiveDate) AS Lag_Value,
               ROW_NUMBER() OVER(PARTITION BY EmployeeName ORDER BY EffectiveDate) AS RN,
               CASE WHEN CurrentDepartment IS NOT NULL THEN 1 ELSE 0 END AS Flag 
             FROM #source
)
SELECT EmployeeName, EffectiveDate, CurrentDepartment,
  CASE 
    WHEN Lag_Value IS NULL THEN PreviousDepartment
    ELSE Lag_Value 
  END AS PreviousNonNULLDepartmentIfAvailableFROM 
FROM (SELECT *,
  (SELECT TOP(1) CurrentDepartment FROM CTE WHERE Flag = 1 
  AND RN < a.RN AND EmployeeName = a.EmployeeName) AS PreviousDepartment
  FROM CTE a) a

在這里拉小提琴。

在這里更新小提琴。

uj5u.com熱心網友回復：

SELECT

a.*,

c.PreviousNonBlankCurrentDepartment AS PreviousNonNULLDepartmentIfAvailable

FROM #source a

OUTER APPLY

(

SELECT TOP 1 b.CurrentDepartment AS PreviousNonBlankCurrentDepartment

FROM #source b
WHERE b.EmployeeName = a.EmployeeName
AND b.EffectiveDate < a.EffectiveDate
AND b.CurrentDepartment IS NOT NULL

ORDER BY b.EffectiveDate DESC

) c

標籤：tsql抵消落后带领分区

上一篇：如何檢查json中字典串列傳遞的所有值？

下一篇：選擇值在范圍之間的行，默認為值超出范圍的最后一行ID