Dans le domaine de la finance, les demandes de reporting (SSRS) intègrent souvent des métriques de comparaison, de tendance comme le YOY (YearOverYear). Dance ce cas, J’ai l’habitude de coder mes rapports SSRS en MDX, les fonctions MDX : ParallelPeriode, LEAD, LAG…sont d’une grande aide dans ce genre de situation.

Pour rappel : LE LEAD (X) est une fonction MDX qui permet de récupérer l’élément N+1 d’un set de données suivant un pas de X

Le LAG (X) quant à lui fait l’inverse (se déplace vers l’arrière N-1)

Exemple requête MDX

 

J’ai, naturellement, été confronté d’essayer de reproduire cette fonctionnalité en SQL, à coup de requêtes imbriquées, du nombre de métriques à sortir et au détriment de la lisibilité de la requête finale, l’exercice devenait fastidieux.

Heureusement que SQL Server 2012 est arrivé avec ces nouveautés. Désormais il existe les mêmes fonctions LEAD & LAG nativement implémentée en SQL. Nous allons découvrir ensemble leur fonctionnement

Etape1 : création de la table de travail

Use [Work_BI]

CREATE TABLE [dbo].[SalesOrderDetail](

      [ID] [int] Not NULL,

      [ID Datail] [int] Not NULL,

      [Orders] FLOAT NULL

  ) ON [PRIMARY]

GO

INSERT INTO [SalesOrderDetail]([ID],[ID Datail],[Orders]) VALUES(1,10,100)

INSERT INTO [SalesOrderDetail]([ID],[ID Datail],[Orders]) VALUES(2,20,200)

INSERT INTO [SalesOrderDetail]([ID],[ID Datail],[Orders]) VALUES(2,21,210)

INSERT INTO [SalesOrderDetail]([ID],[ID Datail],[Orders]) VALUES(2,22,220)

INSERT INTO [SalesOrderDetail]([ID],[ID Datail],[Orders]) VALUES(2,23,230)

INSERT INTO [SalesOrderDetail]([ID],[ID Datail],[Orders]) VALUES(3,30,300)

INSERT INTO [SalesOrderDetail]([ID],[ID Datail],[Orders]) VALUES(4,40,400)

INSERT INTO [SalesOrderDetail]([ID],[ID Datail],[Orders]) VALUES(4,41,410)

INSERT INTO [SalesOrderDetail]([ID],[ID Datail],[Orders]) VALUES(4,42,420)

INSERT INTO [SalesOrderDetail]([ID],[ID Datail],[Orders]) VALUES(4,43,430)

 

SELECT [ID],[ID Datail],[Orders] FROM [dbo].[SalesOrderDetail]

 

Etap2 : Utilisation des fonctions LEAD & LAG

Syntaxe

 

LEAD ( scalar_expression [ ,offset ] , [ default ] )

OVER ( [ partition_by_clause ] order_by_clause )

Use [Work_BI]

SELECT s.ID,s.[ID Detail],s.Orders,

LEAD([ID Detail]) OVER (ORDER BY [ID Detail]) LeadValue,

LAG  ([ID Detail]) OVER (ORDER BY [ID Detail]) LagValue

FROM SalesOrderDetail s

ORDER BY s.ID,s.[ID Detail],s.Orders

GO

Résultat

Il est possible de passer en paramètre le saut de pas dans les fonctions Lead et Lag : [ ,offset ]

Exemple : ici saut de 2 pas

Use [Work_BI]

SELECT s.ID,s.[ID Detail],s.Orders,

LEAD([ID Detail],2) OVER (ORDER BY [ID Detail]) LeadValue,

LAG  ([ID Detail],2) OVER (ORDER BY [ID Detail]) LagValue

FROM SalesOrderDetail s

ORDER BY s.ID,s.[ID Detail],s.Orders

GO

Resultat

Comme il est possible de remplacer directement les valeur Null dans le second paramètre de ces fonctions, [ default ]

Use [Work_BI]

SELECT s.ID,s.[ID Detail],s.Orders,

LEAD([ID Detail],2,0) OVER (ORDER BY [ID Detail]) LeadValue,

LAG  ([ID Detail],2,0) OVER (ORDER BY [ID Detail]) LagValue

FROM SalesOrderDetail s

ORDER BY s.ID,s.[ID Detail],s.Orders

GO

Désormais, le calcul d’une variation de métrique comme le YOY  est plus simple et plus rapide à calculer

Bonus :

Il est , également, possible de choisir l’axe de partitionnement comme on peut le voir sur la figure ci-dessous :

Use [Work_BI]

SELECT s.ID,s.[ID Detail],s.Orders,

LEAD([ID Detail]) OVER (PARTITION  BY [ID] ORDER BY [ID Detail]) LeadValue,

LAG  ([ID Detail]) OVER (PARTITION  BY [ID] ORDER BY [ID Detail]) LagValue

FROM SalesOrderDetail s

ORDER BY s.ID,s.[ID Detail],s.Orders

GO