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 :

image

Et voici un jeu de données

image

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 :

image

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

imageGrâ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

image

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

image

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

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