PIVOT en BigQuery

Convertir filas en columnas

A veces, es posible que desee reformatear el resultado de una tabla para tener columnas separadas para cada valor único. Esto se denomina tabla dinámica; normalmente, es solo una función de visualización compatible con las herramientas de BI. Sin embargo, en ocasiones puede resultar útil crear la tabla dinámica en SQL. A continuación, se explica cómo utilizar el operador PIVOT en Google BigQuery para crear una tabla dinámica.

Imagen de Gerd Altmann en Pixabay

¿Qué hace Pivot?

Pivot cambia filas a columnas. Por ejemplo, supongamos que tenemos una tabla de vuelos y retrasos de vuelos como esta:

Y queremos un resultado donde obtenemos el retraso de salida promedio para cada par aeropuerto-aerolínea, donde los códigos de aerolínea son columnas:

Hacíamos un PIVOT. Tenga en cuenta que si simplemente queremos la demora de salida promedio y estamos de acuerdo con que los promedios estén en diferentes filas, simplemente podríamos hacer:

SELECT 
  airline,
  departure_airport,
  AVG(departure_delay)
FROM `bigquery-samples.airline_ontime_data.flights`
GROUP BY 1, 2

Es el hecho de que lo necesitamos presentado como una tabla 2D que requiere un PIVOT.

Sintaxis de PIVOT

El operador Pivot en BigQuery necesita que especifiques tres cosas:

  • from_item que funciona como entrada. Las tres columnas (línea aérea, aeropuerto de salida, retraso de salida) de la tabla de vuelos es nuestro elemento de origen.
  • agregado, ya que cada celda de la tabla de salida consta de varios valores. Aquí, ese es el AVG de la demora de salida.
  • pivot_column , la columna cuyos valores forman las columnas en la tabla de salida. Desafortunadamente, esto debe ser una constante: no puede realizar una consulta allí.

Tabla de vuelos PIVOT

Queremos retrasos en las salidas por aeropuerto y aerolínea. La consulta dinámica es:

SELECT * FROM
(
  -- #1 from_item
  SELECT 
    airline,
    departure_airport,
    departure_delay
  FROM `bigquery-samples.airline_ontime_data.flights`
)
PIVOT
(
  -- #2 aggregate
  AVG(departure_delay)
  -- #3 pivot_column
  FOR airline in ('AA', 'KH', 'DL', 'AS', 'UA')
)

Resultado:

PIVOT de desbordamiento de pila

Creemos un pivote del número de preguntas con una etiqueta determinada por año.

SELECT
    tag,
    EXTRACT(YEAR from creation_date) AS year,
    COUNT(*) AS n
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions`,
    UNNEST(SPLIT(tags, '|')) AS tag
  WHERE tags IS NOT null
  GROUP BY 1, 2

      

SELECT * FROM 
(
  -- #1 from_item
  SELECT
    tag,
    EXTRACT(YEAR from creation_date) AS year
  FROM 
    `bigquery-public-data.stackoverflow.posts_questions`,
    UNNEST(SPLIT(tags, '|')) AS tag
  WHERE tags IS NOT null
)
PIVOT
(
  -- #2 aggregate
  COUNT(*) AS n
  -- #3 pivot_column
  FOR year in (2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020)
)

      

¿Qué sucede si no puede enumerar todos los valores posibles o la columna dinámica de antemano? Una solución es utilizar secuencias de comandos. Aquí, creo una variable llamada aerolíneas que imprime la cadena en el formato correcto (pruébelo):

DECLARE airlines STRING;
SET airlines = (
  SELECT 
    CONCAT('("', STRING_AGG(DISTINCT airline, '", "'), '")'),
  FROM `bigquery-samples.airline_ontime_data.flights`
  WHERE airline != '9E'
);

EXECUTE IMMEDIATE format("""
SELECT * FROM
(
  SELECT 
    airline,
    departure_airport,
    departure_delay
  FROM `bigquery-samples.airline_ontime_data.flights`
)
PIVOT
(
  AVG(departure_delay)
  FOR airline in %s
)
ORDER BY departure_airport ASC
""", airlines);

Suggested posts

Conceptos básicos de SQL: todo lo que necesita saber en una breve guía

Conceptos básicos de SQL: todo lo que necesita saber en una breve guía

SQL, o lenguaje de consulta estructurado, es una herramienta esencial para administrar y manipular datos en bases de datos relacionales. Con su amplia gama de comandos, SQL le permite ver, crear y eliminar bases de datos y tablas, modificar datos y seleccionar datos usando varios comandos.

Fundamentos de la tecnología avanzada.

Fundamentos de la tecnología avanzada.

El campo de la tecnología ha crecido a pasos agigantados en los últimos años y uno de los desarrollos más emocionantes es el surgimiento de la Inteligencia Artificial (IA) y su descendencia, la IA Generativa. Pero, ¿qué ocurre exactamente con estas tecnologías de vanguardia que las hacen tan avanzadas? Hoy, echemos un vistazo más de cerca a algunos fundamentos tecnológicos básicos que subyacen a estas innovaciones.

Related posts

3 formas comprobadas de mejorar su habilidad de escritura

Los beneficios de escribir y estrategias para ayudarte a hacerlo

3 formas comprobadas de mejorar su habilidad de escritura

En 2022, con el lanzamiento de GPT-3, la habilidad de escritura parece perder importancia. Con IA, las personas con mala escritura también pueden generar personal de calidad.

El asesinato de Jordan Neely y el problema del racismo “patriótico”

¿Puede Estados Unidos dar el paso esencial de no equiparar el comportamiento racista con ser un buen estadounidense?

El asesinato de Jordan Neely y el problema del racismo “patriótico”

[Alerta de activación: este ensayo incluye capturas de pantalla de personas que elogiaron al asesino de Jordan Neely.] Los funcionarios encargados de hacer cumplir la ley finalmente, y quizás a regañadientes, acusaron a Daniel Penny, el hombre que estranguló a Jordan Neely hasta que murió, por ese homicidio.

Cómo salvar el planeta y ganar dinero

Cómo salvar el planeta y ganar dinero

¿Cuántas veces has tirado una botella de plástico, un tarro de cristal, una lata de aluminio o un paquete de energía verde sin pensarlo dos veces? ¿Cuántas veces has deseado poder obtener algo a cambio de reciclarlos? Si usted es como la mayoría de las personas, la respuesta probablemente sea muchas veces. El reciclaje a menudo se ve como una molestia, una tarea o un deber que no brinda ningún beneficio inmediato o tangible.

La revolución del lugar de trabajo: los mejores expertos hablan sobre el trabajo híbrido y remoto

La revolución del lugar de trabajo: los mejores expertos hablan sobre el trabajo híbrido y remoto

En el panorama empresarial en rápida evolución actual, el concepto de trabajo híbrido y remoto ha ganado un impulso sin precedentes. A medida que las empresas continúan adaptándose a las necesidades y expectativas cambiantes de sus empleados, expertos de varios campos se han reunido para analizar los beneficios, los desafíos y el futuro de esta forma transformadora de trabajar.