SQL Pivot : Conversion des lignes en colonnes

Essayez ce notebook dans Databricks

Consultez l’e-book Pourquoi le Data Lakehouse est votre prochain Data Warehouse pour découvrir le fonctionnement interne de Databricks Lakehouse Platform.

MIS À JOUR LE 10/11/2018

Pivot a été introduit pour la première fois dans Apache Spark 1.6 en tant que nouvelle fonctionnalité DataFrame qui permet aux utilisateurs de faire pivoter une expression à valeur de table en transformant les valeurs uniques d’une colonne en colonnes individuelles.

La version Apache Spark 2.4 étend également cette puissante fonctionnalité de pivotement des données à nos utilisateurs SQL. Dans ce blog, à l’aide des enregistrements de température à Seattle, nous allons montrer comment nous pouvons utiliser cette fonctionnalité SQL Pivot courante pour réaliser des transformations de données complexes.

Examen des températures estivales avec Pivot

Cet été, à Seattle, les températures ont atteint des niveaux inconfortables, culminant à plus de 26 à 32 °C pendant neuf jours en juillet.

Supposons que nous voulions explorer ou examiner s’il existe une tendance historique à la hausse des niveaux de mercure. Une façon intuitive d’examiner et de présenter ces chiffres est d’avoir les mois comme colonnes, puis les moyennes mensuelles élevées de chaque année sur une seule ligne. De cette façon, il sera facile de comparer les températures horizontalement, entre les mois adjacents, et verticalement, entre les différentes années.

Maintenant que nous prenons en charge la syntaxe PIVOT dans Spark SQL, nous pouvons y parvenir avec la requête SQL suivante.

La requête ci-dessus produira un résultat comme celui-ci :

Eh bien, il semble qu’il y ait de bonnes et de mauvaises années. L’année 2016 semble être une année plutôt économe en énergie.

Pivotement dans SQL

Examinons de plus près cette requête pour comprendre comment elle fonctionne. Tout d’abord, nous devons spécifier la clause FROM, qui est l’entrée du pivot, en d’autres termes, la table ou la sous-requête sur laquelle le pivotement sera effectué. Dans notre cas, nous nous intéressons aux années, aux mois et aux températures élevées, ce sont donc les champs qui apparaissent dans la sous-requête.

Deuxièmement, examinons une autre partie importante de la requête, la clause PIVOT. Le premier argument de la clause PIVOT est une fonction d’agrégation et la colonne à agréger. Nous spécifions ensuite la colonne pivot dans la sous-clause FOR comme deuxième argument, suivi de l’opérateur IN contenant les valeurs de la colonne pivot comme dernier argument.

La colonne pivot est le point autour duquel la table pivote, et les valeurs de la colonne pivot sont transposées en colonnes dans la table de sortie. La clause IN vous permet également de spécifier un alias pour chaque valeur pivot, ce qui facilite la génération de noms de colonnes plus significatifs.

Une idée importante concernant le pivot est qu’il effectue une agrégation groupée basée sur une liste de colonnes group-by implicites ainsi que sur la colonne pivot. Les colonnes group-by implicites sont les colonnes de la clause FROM qui n’apparaissent dans aucune fonction d’agrégation ou comme colonne pivot.

Dans la requête ci-dessus, la colonne pivot étant la colonne month et la colonne group-by implicite étant la colonne year, l’expression avg(temp) sera agrégée sur chaque paire de valeurs distinctes de (year, month), où month est égal à l’une des valeurs de colonne pivot spécifiées. Par conséquent, chacune de ces valeurs agrégées sera mappée dans sa cellule correspondante de la ligne year et du mois de la column.

Il convient de noter qu’en raison de ce group-by implicite, nous devons nous assurer que toute colonne que nous ne souhaitons pas faire partie de la sortie pivot doit être omise de la clause FROM, sinon la requête produirait des résultats indésirables.

Spécification de plusieurs expressions d’agrégation

L’exemple ci-dessus montre qu’une seule expression d’agrégation est utilisée dans la clause PIVOT, alors qu’en fait, les utilisateurs peuvent spécifier plusieurs expressions d’agrégation si nécessaire. Encore une fois, avec les données météorologiques ci-dessus, nous pouvons répertorier les températures maximales élevées ainsi que les températures élevées moyennes entre juin et septembre.

En cas d’expressions d’agrégation multiples, les colonnes seront le produit cartésien des valeurs de la colonne pivot et des expressions d’agrégation, avec les noms comme _.

Colonnes de regroupement et colonnes pivots

Supposons maintenant que nous voulions inclure les basses températures dans notre exploration des tendances de température à partir de ce tableau des basses températures quotidiennes :

Pour combiner ce tableau avec le tableau précédent des températures élevées quotidiennes, nous pourrions joindre ces deux tableaux sur la colonne « Date ». Toutefois, étant donné que nous allons utiliser pivot, qui effectue un regroupement sur les dates, nous pouvons simplement concaténer les deux tableaux à l’aide de UNION ALL. Et vous verrez plus tard, cette approche nous offre également plus de flexibilité :

Essayons maintenant notre requête pivot avec le nouveau tableau combiné :

Par conséquent, nous obtenons la moyenne élevée et la moyenne basse pour chaque mois des 4 dernières années dans un tableau. Notez que nous devons inclure la colonne flag dans la requête pivot, sinon l’expression avg(temp) serait basée sur un mélange de températures élevées et basses.

Vous avez peut-être remarqué que nous avons maintenant deux lignes pour chaque année, une pour les températures élevées et l’autre pour les basses températures. En effet, nous avons inclus une colonne supplémentaire, flag, dans l’entrée pivot, qui à son tour devient une autre colonne de regroupement implicite en plus de la colonne d’origine year.

Alternativement, au lieu d’être une colonne de regroupement, l’indicateur flag peut également servir de colonne pivot. Nous avons donc maintenant deux colonnes pivots, month et flag :

Cette requête nous présente une disposition différente des mêmes données, avec une ligne pour chaque année, mais deux colonnes pour chaque mois.

Et ensuite ?

Pour exécuter les exemples de requêtes utilisés dans ce blog, veuillez consulter les exemples SQL pivot dans ce notebook d’accompagnement.

Merci aux contributeurs de la communauté Apache Spark pour leurs contributions !

(Cet article de blog a été traduit à l'aide d'outils basés sur l'intelligence artificielle) Article original