Rank, Pivot, CTE, EAV

Aujourd’hui, je veux pouvoir extraire des données d’une table EAV (Entity Attribute Value), les mettre à plat, et en plus prendre la données la plus récente, chaque attribut étant historisé.

Voici la structure de la table :

Et voici un jeu de données

On note que le client 1 possède un attribut 1001 qui représente sa taille.

Il possède deux fois l’attribut 1000 qui représente le poids, mais à des dates différentes.

Le résultat qu’on souhaite obtenir est l’ensemble de nos clients, avec sur chaque ligne, le poids et la taille, de date la plus récente.

Pour notre client 1, nous devons avoir une ligne du genre :

Pour ce faire, nous allons utiliser :

La fonction RANK : elle nous permettra de ne retenir que les données de RANK 1 partitionnées sur le ClientID et sur le DateCreation

Select ClientId, AttributId,  Valeur,
       RANK() over(Partition by ClientId, AttributId Order By DateCreation Desc ) R
From Client

Grâce à ce rank, nous avons bien les données qui nous intéressent, au rang 1 pour chaque attribut de chaque client

Une Common Table Expression : Elle nous permettra de récupérer de la requête RANK uniquement les RANK = 1

With C (ClientId, AttributId, Valeur, Position) as
(
    Select    ClientId, AttributId,  Valeur, 
            RANK() over(Partition by ClientId, AttributId Order By DateCreation Desc ) 
    From Client
    Where ExpId = 1
)
Select * from C where Position = 1

Exit donc les données dont le RANK est supérieur à 1

La fonction PIVOT : Pour mettre tout ça en ligne. Petite astuce, le Pivot demandant un agrégat, on va utiliser le Max de la valeur. Celle ci étant unique, le tour est joué !

With C (ClientId, AttributId, Valeur, Position) as
(
    Select    ClientId, AttributId,  Valeur, 
            RANK() over(Partition by ClientId, AttributId Order By DateCreation Desc ) 
    From Client
)
Select ClientId, [1000] as Poids, [1001] as Taille
From C Pivot (Max(Valeur) for AttributId in ([1000], [1001])) as PV
Where PV.Position = 1 

Et voilà, vous avez bien, en lignes, les données les plus récentes d’une table EAV historisée

Evidemment, vous pouvez aussi vous passer de ce genre de tables, en utilisant les Sparses Columns bien sûr !