En el mundo del análisis de datos, la capacidad de transformar y reorganizar datos es esencial para obtener insights valiosos. En este artículo, te mostraremos cómo pivotar una tabla en BigQuery, optimizando tus consultas y mejorando la presentación de tus datos.
Índice:
Cuando trabajamos con tablas de BigQuery, una necesidad habitual es la de ver los datos en forma de tabla cruzada. Este tipo de visualización nos permite comparar dos dimensiones diferentes y las métricas que se derivan de ambas, lo cual es esencial para un análisis más profundo y detallado de los datos. Al observar los datos en este formato, podemos identificar patrones, tendencias y relaciones que no serían evidentes en una tabla tradicional.
Esta acción, conocida como pivotar una variable, implica dejar una dimensión fija mientras se mueve la otra a columnas para visualizar sus resultados de manera más clara y comprensible. Pivotar es una técnica muy común y también bastante sencilla de realizar dentro de BigQuery, aunque la forma de implementarla puede variar dependiendo de lo que queramos lograr con nuestros datos. Por ejemplo, podríamos querer analizar cómo se distribuyen ciertos eventos a lo largo de diferentes plataformas o cómo varían las métricas en función de diferentes categorías.
Cómo pivotar de manera estática
Una acción que podríamos querer realizar es, sobre una dimensión específica, ver solamente los valores pivotados de una serie de dimensiones seleccionadas. Por ejemplo, si tenemos la variable de eventos, podríamos estar interesados en ver solamente algunos de los eventos y no todos. Esto nos permitiría visualizar de forma gráfica un funnel, que es una representación visual del proceso por el cual pasan los usuarios, o analizar algún tipo de evolución de una variable en función de las fechas. Este tipo de análisis es importante para entender el comportamiento de los usuarios y optimizar estrategias de negocio.
Para llevar a cabo esta primera acción, la forma de pivotar una tabla en BigQuery es utilizando el código que se muestra a continuación:
-- Pivotar tabla de manera estática:
WITH a AS ( SELECT DISTINCT event_date, event_name, count(*) AS n_events FROM `[PROJECT_id].[DATASET_ID].[TABLA_EVENTS_YYYYMMDD]` GROUP BY all) SELECT * FROM a PIVOT(sum(n_events) AS s_events FOR event_name IN ('Pageview','screen_view'));En este código, estamos extrayendo dos dimensiones, event_date y event_name, y generando una métrica, n_events, a traves de una subconsulta.
A continuación, recogemos las dimensiones de la subconsulta anterior y comenzamos a pivotar una de ellas, event_name, seleccionando esta dimensión cuando event_name tome los valores de Pageview o screen_view.
Además, deseamos que la métrica generada anteriormente, n_events, sea la base para agregar los resultados, sumandose en una metrica s_events.
Al ejecutar esta consulta, obtendremos una tabla cruzada donde las columnas mostrarán estos dos valores de las dimensiones Pageview y screen_view, y las filas mostrarán el volumen de cada una de estos event_name para cada event_date.
De esta forma, logramos pivotar una tabla de manera estática, lo que significa que el proceso se realiza únicamente en función de los parámetros que hemos especificado previamente en el código. Esto implica que debemos conocer de antemano los valores que queremos pivotar y definirlos explícitamente en nuestra consulta. Al hacerlo, limitamos el análisis a un conjunto específico de datos, lo cual es útil cuando tenemos un objetivo claro y definido.
Podemos añadir tantas dimensiones como queramos o tratar de complicar la tabla al gusto, pero la mecánica será siempre similar.
Sin embargo, esta metodología puede ser restrictiva si los valores de las dimensiones que deseamos analizar son variables o desconocidos, ya que requeriría modificar el código cada vez que se presenten nuevos valores o cambios en los datos.
Para pivotar de manera dinámica, el código que necesitamos se compone de dos secciones. En la primera, definimos la consulta que deseamos ejecutar e incluimos una variable que contendrá el contenido de la dimensión a pivotar. En la segunda, generamos el contenido de la dimensión que vamos a pivotar.
A continuación, vamos a ver el código de ejemplo y lo explicaremos detalladamente paso a paso:
-- Pivotar tabla de manera dinámica:
EXECUTE IMMEDIATE FORMAT(
"""
with a as (SELECT DISTINCT event_date, event_name, count(*) AS n_events
FROM `[PROJECT_id].[DATASET_ID].[TABLA_EVENTS_YYYYMMDD]`
GROUP BY all)
select * from a pivot(sum(n_events) as s_events for event_name in %s);
""",
(select
concat("(",string_agg(distinct concat("'",event_name,"'")),")")
FROM `[PROJECT_id].[DATASET_ID].[TABLA_EVENTS_YYYYMMDD]`)
);EXECUTE IMMEDIATE es una función de SQL en BigQuery que permite ejecutar consultas dinámicas. En lugar de ejecutar una consulta fija escrita directamente en el código, permite construir la consulta de forma dinámica durante la ejecución del script.FORMAT se utiliza para construir una consulta SQL dinámica, donde se inserta un valor calculado dentro de una plantilla SQL.%s dentro de la consulta sea reemplazado por una lista generada dinámicamente mediante la función STRING_AGG(), en la segunda sección de la función. Dentro de las triples comillas, insertamos la consulta que vamos a ejecutar. En este ejemplo, es la misma que empleamos para pivotar las variables de manera estática, pero con una modificación: en la cláusula PIVOT sustituimos 'event_name IN ('Pageview','screen_view')' por 'event_name IN %s'. La variable añadida al final es la que contiene el contenido de la dimensión que vamos a pivotar, y se definirá en la segunda sección de la función.
En la segunda parte de la función, cogemos el contenido de la dimensión event_name y lo metemos entre paréntesis, cada valor entre comilla simple y separado con una coma. Y para ello usamos dos funciones:STRING_AGG: Combina todos los valores distintos destream_platformen una sola cadena, separados por comas. Cada valor está encerrado entre comillas simples (') para que sea compatible con la sintaxis de SQL.CONCAT: Encierra el resultado deSTRING_AGGentre paréntesis para que sea válido como una lista de valores en la cláusulaIN.
Ahora que entendemos cómo funciona el código, simplemente debemos ejecutarlo para observar cómo todos los valores de la dimensión seleccionada se han pivotado, creando una tabla cruzada con la dimensión event_date. Desde este punto, podemos comenzar a añadir más dimensiones que amplíen la información que buscamos, y una vez comprendida la mecánica, será muy fácil hacerlo.
Conclusión
Una vez comprendida la diferencia entre pivotar de manera estática y dinámica, así como la implementación de ambas técnicas en BigQuery, podemos ver cómo esta metodología amplía las posibilidades de análisis. Pivotar tablas permite transformar datos complejos en visualizaciones claras que revelan patrones y tendencias difíciles de identificar en tablas tradicionales.
Mientras que el pivot estático resulta útil para análisis concretos y definidos, el enfoque dinámico ofrece una solución más flexible y automatizada, aunque más compleja, especialmente en entornos donde los valores de las dimensiones pueden cambiar constantemente. La combinación de funciones como EXECUTE IMMEDIATE y STRING_AGG() permite automatizar la adaptación de la consulta a cualquier conjunto de datos, simplificando el proceso y ahorrando tiempo.
Con esta base, ahora tienes las herramientas necesarias para realizar análisis más completos y ajustados a tus necesidades. La clave está en adaptar cada método a tu escenario de negocio y seguir explorando las capacidades de BigQuery para sacar el máximo provecho de tus datos.
