Werken met LAG() en LEAD()

Tijdreeksanalyse in SQL Server

Maham Faisal Khan

Senior Data Science Content Developer

De windowfunctie LAG()

SELECT
    dsr.CustomerID,
    dsr.MonthStartDate,
    LAG(dsr.NumberOfVisits) OVER (PARTITION BY dsr.CustomerID ORDER BY dsr.MonthStartDate) AS Prior,
    dsr.NumberOfVisits
FROM dbo.DaySpaRollup dsr;

CustomerID	MonthStartDate	Prior	NumberOfVisits
1	2018-12-01	NULL	49
1	2019-01-01	49	117
1	2019-02-01	117	104

De windowfunctie LEAD()

SELECT
    dsr.CustomerID,
    dsr.MonthStartDate,
    dsr.NumberOfVisits,
    LEAD(dsr.NumberOfVisits) OVER (PARTITION BY dsr.CustomerID ORDER BY dsr.MonthStartDate) AS Next
FROM dbo.DaySpaRollup dsr;

CustomerID	MonthStartDate	NumberOfVisits	Next
1	2018-12-01	49	117
1	2019-01-01	117	104
1	2019-02-01	104	108

Aantal rijen terug opgeven

SELECT
   dsr.CustomerID,
   dsr.MonthStartDate,
   LAG(dsr.NumberOfVisits, 2) OVER (PARTITION BY dsr.CustomerID ORDER BY dsr.MonthStartDate) AS Prior2,
   LAG(dsr.NumberOfVisits, 1) OVER (PARTITION BY dsr.CustomerID ORDER BY dsr.MonthStartDate) AS Prior1,
   dsr.NumberOfVisits
FROM dbo.DaySpaRollup dsr;

CustomerID	MonthStartDate	Prior2	Prior	NumberOfVisits
1	2018-12-01	NULL	NULL	49
1	2019-01-01	NULL	49	117
1	2019-02-01	49	117	104

Windows, filters

SELECT
    Date,
    LAG(Val, 1) OVER(ORDER BY DATE) AS PriorVal,
    Val
FROM t;

Date	PriorVal	Val
2019-01-01	NULL	3
2019-01-02	3	6
2019-01-03	6	4

SELECT
    Date,
    LAG(Val, 1) OVER(ORDER BY DATE) AS PriorVal,
    Val
FROM t
WHERE
    t.Date > '2019-01-02';

Date	PriorVal	Val
2019-01-03	NULL	4

Windows, filters en CTE's

WITH records AS (
  SELECT
      Date,
      LAG(Val, 1) OVER(ORDER BY Date) AS PriorVal,
      Val
  FROM t
)
SELECT
    r.Date,
    r.PriorVal,
    r.Val
FROM records r
WHERE
    r.Date > '2019-01-02';

Date	PriorVal	Val
2019-01-03	6	4

Laten we oefenen!

Tijdreeksanalyse in SQL Server