Arbeta med LAG() och LEAD()

Tidsserieanalys i SQL Server

Maham Faisal Khan

Senior Data Science Content Developer

Fönsterfunktionen 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

Fönsterfunktionen 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

Ange antal rader bakåt

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

Fönster och filter

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

Fönster, filter och CTE:er

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

Nu kör vi en övning!

Tidsserieanalys i SQL Server