SQL Pivot: Conversión de filas en columnas

Pruebe este cuaderno en Databricks

Consulte el libro electrónico Por qué Data Lakehouse es su próximo Data Warehouse para descubrir el funcionamiento interno de la plataforma Databricks Lakehouse.

ACTUALIZADO el 10/11/2018

Pivot se introdujo por primera vez en Apache Spark 1.6 como una nueva característica de DataFrame que permite a los usuarios rotar una expresión con valores de tabla convirtiendo los valores únicos de una columna en columnas individuales.

La versión Apache Spark 2.4 amplía esta potente funcionalidad de dinamización de datos a nuestros usuarios de SQL también. En este blog, utilizando las grabaciones de temperaturas en Seattle, mostraremos cómo podemos utilizar esta característica común de SQL Pivot para lograr transformaciones de datos complejas.

Examen de las temperaturas de verano con Pivot

Este verano en Seattle las temperaturas subieron a niveles incómodos, alcanzando un máximo de 80 y 90 grados, durante nueve días en julio.

Supongamos que queremos explorar o examinar si hubo una tendencia histórica en el aumento de los niveles de mercurio. Una forma intuitiva de examinar y presentar estos números es tener los meses como las columnas y luego los máximos promedios mensuales de cada año en una sola fila. De esa manera, será fácil comparar las temperaturas tanto horizontalmente, entre meses adyacentes, como verticalmente, entre diferentes años.

Ahora que tenemos soporte para la sintaxis PIVOT en Spark SQL, podemos lograr esto con la siguiente consulta SQL.

La consulta anterior producirá un resultado como:

Bueno, parece que hay años buenos y años malos. El año 2016 parece un año bastante respetuoso con la energía.

Dinamización en SQL

Echemos un vistazo más de cerca a esta consulta para entender cómo funciona. Primero, necesitamos especificar la cláusula FROM, que es la entrada del pivote, en otras palabras, la tabla o subconsulta en la que se basará la dinamización. En nuestro caso, nos preocupan los años, los meses y las altas temperaturas, por lo que esos son los campos que aparecen en la subconsulta.

En segundo lugar, consideremos otra parte importante de la consulta, la cláusula PIVOT. El primer argumento de la cláusula PIVOT es una función agregada y la columna que se va a agregar. A continuación, especificamos la columna de pivote en la sub-cláusula FOR como el segundo argumento, seguido del operador IN que contiene los valores de la columna de pivote como el último argumento.

La columna de pivote es el punto alrededor del cual se rotará la tabla, y los valores de la columna de pivote se transpondrán en columnas en la tabla de salida. La cláusula IN también le permite especificar un alias para cada valor de pivote, lo que facilita la generación de nombres de columna más significativos.

Una idea importante sobre el pivote es que realiza una agregación agrupada basada en una lista de columnas implícitas de group-by junto con la columna de pivote. Las columnas implícitas de group-by son columnas de la cláusula FROM que no aparecen en ninguna función agregada o como la columna de pivote.

En la consulta anterior, siendo la columna de pivote la columna month y la columna implícita group-by la columna year, la expresión avg(temp) se agregará en cada par de valores distintos de (year, month), donde month es igual a uno de los valores de columna de pivote especificados. Como resultado, cada uno de estos valores agregados se asignará a su celda correspondiente de la fila year y el mes de la column.

Vale la pena señalar que debido a este group-by implícito, debemos asegurarnos de que cualquier columna que no deseemos que forme parte de la salida del pivote se deje fuera de la cláusula FROM, de lo contrario, la consulta produciría resultados no deseados.

Especificación de varias expresiones agregadas

El ejemplo anterior muestra solo una expresión agregada que se utiliza en la cláusula PIVOT, mientras que, de hecho, los usuarios pueden especificar varias expresiones agregadas si es necesario. De nuevo, con los datos meteorológicos anteriores, podemos enumerar las temperaturas máximas altas junto con las temperaturas altas promedio entre junio y septiembre.

En el caso de varias expresiones agregadas, las columnas serán el producto cartesiano de los valores de la columna de pivote y las expresiones agregadas, con los nombres como _.

Agrupación de columnas frente a columnas de pivote

Ahora supongamos que queremos incluir las bajas temperaturas en nuestra exploración de las tendencias de temperatura de esta tabla de temperaturas diarias bajas:

Para combinar esta tabla con la tabla anterior de temperaturas diarias altas, podríamos unir estas dos tablas en la columna "Fecha". Sin embargo, dado que vamos a utilizar pivot, que realiza la agrupación en las fechas, podemos simplemente concatenar las dos tablas usando UNION ALL. Y verá más adelante, este enfoque también nos proporciona más flexibilidad:

Ahora probemos nuestra consulta de pivote con la nueva tabla combinada:

Como resultado, obtenemos el promedio alto y el promedio bajo para cada mes de los últimos 4 años en una tabla. Tenga en cuenta que necesitamos incluir la columna flag en la consulta de pivote, de lo contrario, la expresión avg(temp) se basaría en una mezcla de temperaturas altas y bajas.

Es posible que haya notado que ahora tenemos dos filas para cada año, una para las temperaturas altas y la otra para las temperaturas bajas. Esto se debe a que hemos incluido una columna más, flag, en la entrada de pivote, que a su vez se convierte en otra columna de agrupación implícita además de la columna original year.

Alternativamente, en lugar de ser una columna de agrupación, el flag también puede servir como una columna de pivote. Así que ahora tenemos dos columnas de pivote, month y flag:

Esta consulta nos presenta un diseño diferente de los mismos datos, con una fila para cada año, pero dos columnas para cada mes.

Qué sigue

Para ejecutar los ejemplos de consulta utilizados en este blog, consulte los ejemplos de SQL de pivote en este cuaderno adjunto.

¡Gracias a los colaboradores de la comunidad Apache Spark por sus contribuciones!

(Esta entrada del blog ha sido traducida utilizando herramientas basadas en inteligencia artificial) Publicación original