Lucrul cu LAG() și LEAD()

Analiza seriilor de timp în SQL Server

Maham Faisal Khan

Senior Data Science Content Developer

Funcția de fereastră 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

Funcția de fereastră 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

Specificarea numărului de rânduri înapoi

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

Ferestre și filtre

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

Ferestre, filtre și CTE-uri

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

Să exersăm!

Analiza seriilor de timp în SQL Server