Los tres trucos de BigQuery que todo data scientist necesita saber
Pequeños consejos y trucos que pueden facilitar las consultas SQL.
Para las personas que hemos hecho alguna vez una consulta en SQL, sabemos que hay muchas maneras de llegar al resultado que queremos y ocupamos una (o quizás vamos variando). También sabemos que hay muchas funciones (básicas y otras no tanto) que nos permiten escribir consultas de manera fácil y robustas. Pero lo que definitivamente está claro es que nunca vamos a dejar de aprender o necesitar nuevas estrategias y/o combinaciones de sentencias para lograr nuestro objetivo.
Por otro lado, no es un misterio que muchas de las cosas que podemos hacer en BigQuery, se puede hacer en Python/Pandas o en cualquier paquete que permita manejar datos, llegando a los mismos resultados.
Si bien uno elige por lo general el que más le acomoda, para un pipeline en producción puede ser mucho más eficiente hacer el tratamiento de datos inicial en SQL y pasar el dataset de entrenamiento listo al modelo o al siguiente paso del proceso.
La idea de este artículo es contarles sobre tres de los trucos que me han acompañado durante los últimos dos años y que me han servido para distintos casos en el tratamiento de datos, salvándome en más de una oportunidad.
Trabajar con ventanas de tiempo
Cuando estamos armando una tabla (con datos temporales) y queremos extraer la mayor cantidad de información, previo a saber qué variables son las que realmente importan, probablemente queramos sacar todo tipo de dato estadístico como: promedio, mediana, mínimos, máximos, percentiles, entre otros. En esta búsqueda por las métricas “más informativas posibles”, asumiendo que sabemos las limitaciones que puede tener el promedio y la mediana de una distribución muy grande de datos, o que al ser datos temporales, en un año puede haber cambios drásticos entre invierno y verano, días de la semana, etc., nos acercamos rápidamente a estas mismas métricas pero en ventanas de tiempo, como por ejemplo, las medias móviles. De esta manera, somos capaces de capturar la información que nos interesa durante el periodo de tiempo “correcto”.
Cláusula OVER
Pero, ¿cómo lo hacemos todo en una misma query? En realidad no es nada complicado, solo depende de la agregación de fecha que tenemos y que queremos. Para partir, nos vamos a centrar en la cláusula OVER de BigQuery, que permite hacer maravillas!
analytic_function_name ([ argument_list ] ) OVER over_clause
over_clause:
{ named_window | ( [ window_specification ] ) }
window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
window_frame_clause:
{ rows_range } { frame_start | frame_between }
rows_range:
{ ROWS | RANGE }Al verla así, no parece tan amigable, pero al tratarla y entender cómo rellenarla, va a convertirse en nuestra mejor amiga. Tenemos que pensar en que nuestro objetivo es: Queremos calcular la métrica W de la variable X agrupada por Y en los últimos Z unidades de tiempo.
Para esto, lo primero es determinar la variable de interés, es decir, ¿qué variable nos entrega información a lo largo del tiempo?¿Cuánto es ese tiempo? y ¿esa variable tiene que estar agrupada en algún grado?. Con estas respuestas vamos a conocer la variable X, la agrupación por Y y las Z unidades de tiempo del objetivo.
Métrica a calcular y ventana de tiempo
Lo siguiente es definir qué queremos calcular, o la función analítica que vamos a usar: AVG(), MIN(),SUM(),LAG(), o mejor dicho, definir cuál va a ser nuestra métrica W (o nuestras métricas).
Viéndolo con un ejemplo para clarificar, pensemos en las ventas mensuales de una tienda de zapatos (Tabla 1). Pensemos además que esta tienda de zapatos, con dos tiendas (A y B), tiene una línea de zapatos de verano (Sandals) y una de invierno (Boots), por lo que interesa saber qué pasa en las dos estaciones del año.
Así, la variable que nos entrega información a lo largo del tiempo es la cantidad de zapatos vendidos, durante los meses de verano y de invierno. Nos importa también separar la cantidad de ventas por producto (y eventualmente por tienda). Finalmente, en cuanto a la métrica, no podemos decidir cuál es la más informativa, por lo que podríamos querer el promedio, el máximo, el mínimo o la suma de los zapatos vendidos en los últimos seis meses. Vamos a quedarnos con esta última, que su construcción se vería más o menos así:
SELECT Shoes, Store, Month, Year, year_month, Sales,
SUM(Sales) OVER(
PARTITION BY shoes
ORDER BY UNIX_DATE(year_month)
RANGE BETWEEN 150 PRECEDING AND CURRENT ROW) sum_sales
FROM `TEMP.sales_monthly`
ORDER BY year_monthCabe destacar que al seleccionar esa ventana de tiempo, estamos tomando el mes/fecha actual (CURRENT ROW) y los 5 meses anteriores, que serían 5·30=150 días (150 PRECEDING). Otra opción podría ser, según lo que necesitemos, tomar los últimos 6 meses sin considerar el mes actual, resultados que vemos en la Tabla 2:
SELECT Shoes, Store, Month, Year, year_month, Sales,
SUM(Sales) OVER(
PARTITION BY shoes
ORDER BY UNIX_DATE(year_month)
RANGE BETWEEN 180 PRECEDING AND 1 PRECEDING) sum_sales
FROM `TEMP.sales_monthly`
ORDER BY year_month
Para tener en mente: Podemos considerar en el RANGE, meses siguientes, con FOLLOWING. Por ejemplo, para tomar los seis meses anteriores y los dos siguientes, el intervalo quedaría: RANGE BETWEEN 180 PRECEDING AND 60 FOLLOWING .
PARTITION BY vs GROUP BY
Vamos viendo paso a paso la función que construimos. En primer lugar tenemos la métrica: SUM(Sales), que va simplemente a sumar la columna Sales según las indicaciones que siguen. Dentro de la cláusula OVER, tenemos: PARTITION BY Shoes. Si bien esta parte se refiere a la agrupación de las variables, no es lo mismo que el GROUP BY, ya que en este caso se muestra un resultado por fila, considerando solo las filas de esa partición, como se ve en la Tabla 2, mientras que el GROUP BY entrega una única fila como resultado. Por lo que si hiciéramos GROUP BY Shoes, year_month, nos quedaría una única línea para Sandals, que sumaría las dos tiendas, dando 137 (para 2019–01). Y si hiciéramos GROUP BY Shoes, Store, year_month, nos quedarían dos líneas para Sandals, con Store A y B, con sum_sales 75 y 62 respectivamente, ya que no hay otra fila que agrupar y sumar. Ojo que en este caso estamos dejando fija la variable de tiempo, para poder comparar.
Ahora, si agregamos a la partición la variable Store, vamos a tener la suma de ventas distinta según producto-tienda, que es igual a hacer GROUP BY ya que es la mínima agrupación existente (hay una única fila por producto-tienda-mes_año), como se muestra en la Tabla 3.
Cabe destacar que sum_sales es igual a Sales en el primer mes (primeras dos columnas) porque la tabla empieza el 2019–01, por lo que no existen “6 meses anteriores” para ir a sumar. Si vemos los meses siguientes, es decir, sacamos la variable year-month de las variables que agrupan, los resultados serían distintos en cada caso para GROUP BY y OVER PARTITION.
Elección de la variable temporal
Retomando la revisión de la estructura de la cláusula OVER, llegamos a la ventana de tiempo. En este caso tenemos datos mensuales y hay más de un año de datos (2019–2020). Si ordenáramos únicamente por mes, tendríamos el mes de 2019 y 2020 seguidos (por ejemplo: 1–2019 y 1–2020), por lo que estaría sumando “los últimos seis meses de todos los años que estén en la base”: todos los eneros, todos los diciembres, y así. Por lo tanto, creamos una variable llamada year_month que concatena el año, el mes y el día 1, de modo que nuestra unidad de tiempo es un mes dentro de un año específico. Es importante considerar según qué variable vamos a ordenar en este ORDER BY, ya que se permite una sola, que es la llave que tomará el RANGE que sigue. Este último, va a revisar el intervalo que se le entrega de la variable llave, en este caso 6 meses-año anteriores al mes de la fila actual y calcular la suma de Sales, por tipo de zapato.
Si tuviéramos una tabla con fechas diarias, el ejercicio es el mismo, lo importante es definir correctamente la variable del ORDER BY y los intervalos del RANGE:
SELECT Shoes, Store, Month, Year, Sales,
SUM(Sales) OVER(
PARTITION BY shoes
ORDER BY UNIX_DATE(date)
RANGE BETWEEN 180 PRECEDING AND CURRENT ROW) sum_sales
FROM `TEMP.sales_daily`En este caso, la función UNIX_DATE permite transformar la fecha en un número y dejarlas ordenadas, y el rango de 180 representa 6 meses en días (que probablemente puede ser afinado calculando la diferencia exacta entre la fecha actual y 6 meses, para considerar los que tienen 30 y 31 días).
Características únicas
Otra de las clásicas variables que uno quiere tener en su base se relacionan con un único número o dato, donde la idea es condensar/resumir información a partir de otras tablas que tienen muchas filas repetidas. Esto sobre todo pensando en tablas de resumen de datos de clientes y/o productos. Si bien siempre está la opción del GROUP BY para llegar a una única fila según la cantidad de variables que queramos agrupar, me encontré con otro método para lograr quedarnos con la fila que queremos entre muchas que tienen la misma información de agrupación, siendo importante el orden que le damos y con cuál decidimos quedarnos según eso.
Para entender más concretamente lo que quiero mostrarles, pongamos ejemplos. Si quiero quedarme con la última posición GPS recibida de cada automóvil conectado a mi red, si quiero saber el último estado de las transacciones por producto y cliente en mi E-commerce, o si quiero saber cuál fue el primer ticket comprado en cada campaña de marketing que ha hecho mi equipo, solo necesito el siguiente pedazo de código, que nuevamente se basa en la cláusula OVER.
SELECT *EXCEPT(row)
FROM
(SELECT *,
ROW_NUMBER() OVER(
PARTITION BY {}
ORDER BY {}) AS row
FROM {})
WHERE row = 1Ejemplo de la última posición GPS
Al verlo con un ejemplo se simplifica rápidamente. Solo tenemos que pensar en responder a: ¿A partir de qué variables quiero agrupar y con cuál quiero ordenar, ascendente o descendentemente? Tomemos el ejemplo del GPS de los autos, que se vería como la Tabla 4.
Si queremos obtener la última posición GPS (latitud y longitud) por auto y por día (primera pregunta lista), agrego estas dos variables al PARTITION BY. Luego, ¿cómo quiero ordenar? Quiero ordenar cronológicamente, quedándome con la última parada, por lo que en el ORDER BY dejo la variable de fecha-hora, ordenada DESC, para que la primera fila, sea el último registro. Así, la primera parte de la query queda:
WITH last_notification AS
(
SELECT *EXCEPT(row)
FROM
(SELECT *,
ROW_NUMBER() OVER(
PARTITION BY car_id, EXTRACT(DATE FROM notification_datetime)
ORDER BY notification_datetime DESC) AS row
FROM `TEMP.car_gps`)
WHERE row = 1
)SELECT *
FROM last_notification
WHERE car_id = '27126a411199f4bc2226a68b36c31600'
ORDER BY notification_datetime DESC
La segunda parte de la query, solo imprime los resultados de la Tabla 5, que muestra un único registro diario, más precisamente el último del día, para el un car_id dado:
Cabe destacar que si sacáramos la variable de fecha del PARTITION BY, solo nos quedaríamos con el último registro por car_id, por lo que habría una única fila por auto, que en este ejemplo, sería la del 2020–06–13.
Columnas a filas
Para terminar con estos trucos que en lo personal me han sido de infinita utilidad, quería pasar por uno que estoy segura todos lo hemos necesitado. Y digo esto porque lo encontré en su momento buscando en Internet una solución a este problema, habiendo muchas personas antes que yo en esta posición.
En el mundo ideal, todas las bases de datos están construidas de la manera correcta, con todas las tablas auxiliares correctas para hacer los cruces y no necesitar información adicional. Sin embargo, en data science no siempre queremos ocupar las tablas como están construidas originalmente, por lo general las tablas nacen a partir de un csv o de una mezcla de fuentes de datos que terminan en una tabla “lo más funcional posible” o “lo más cercano a ese ideal”. Pero muchas veces, hay estructuras en esos csvs o fuentes de datos que no se adaptan a lo que uno necesita, como ciertas columnas que uno necesita en forma de fila.
Ejemplos de esto pueden ser encuestas telefónicas a clientes, que para facilitar la fluidez del encuestador, rellenan hacia el lado (una fila por encuestado). Pero al momento de evaluar los resultados, sería de utilidad tener una fila por pregunta, independiente del que responde. Otro ejemplo podría ser cuando las ventas anuales (o cualquier métrica por el estilo), se dejan como columna: ventas 2019, ventas 2020. Nuevamente, si quisiéramos tomar decisiones por año-venta, no es posible agrupar por estas columnas, ya que vienen agrupadas de por sí. Si bien tiene mucho sentido que venga así, sobre todo pensando en archivos que son elaborados de manera manual, es importante poder cambiarlos fácilmente. Además, si se fijan en ese último ejemplo, es muy parecido a las variables que creamos en el primer truco! Por lo que en realidad acá vamos a hacer lo contrario, en vez de pasar de filas a columnas, vamos a pasar de columnas a filas, con el siguiente código:
WITH base AS
(SELECT {UNGROUPED VARIABLES + COLS_TO_ROWS}
FROM {})SELECT {UNGROUPED VARIABLES},
ungrouped_variable, SAFE_CAST(col_to_row AS FLOAT64) col_to_row
FROM (
SELECT {UNGROUPED VARIABLES},
REGEXP_REPLACE(SPLIT(pair, ':')[OFFSET(0)], r'^"|"$', '') ungrouped_variable,
REGEXP_REPLACE(SPLIT(pair, ':')[SAFE_OFFSET(1)], r'^"|"$', '') col_to_row
FROM base t,
UNNEST(SPLIT(REGEXP_REPLACE(to_json_string(t), r'{|}', ''))) pair
)
WHERE ungrouped_variable NOT IN ({UNGROUPED VARIABLES: 'variable_1', 'variable_2'...})
Este sí es más difícil de entender, por lo que vamos con un ejemplo, como el de las encuestas. En la Tabla 6, cada fila es un encuestado, donde las preguntas de la 1 a la 4 se relacionan con el producto comprado a través de su transaction_id:
Para tener una fila por encuestado (o user_id) y por pregunta, manteniendo el resto de la información constante (address, year, age), tendríamos que hacer lo siguiente, donde SOLO hay que reemplazar los {UNGROUPED VARIABLES} por las variables que queremos mantener constantes, además de rellenar la tabla “base” con la base de datos de origen:
WITH base AS
(SELECT user_id, address, age, year, transaction_id, question_1, question_2, question_3, question_4
FROM `TEMP.customers_survey`)SELECT user_id, address, age, year, transaction_id,
question, SAFE_CAST(answer AS FLOAT64) answer
FROM (
SELECT user_id, address, age, year, transaction_id,
REGEXP_REPLACE(SPLIT(pair, ':')[OFFSET(0)], r'^"|"$', '') question,
REGEXP_REPLACE(SPLIT(pair, ':')[SAFE_OFFSET(1)], r'^"|"$', '') answer
FROM base t,
UNNEST(SPLIT(REGEXP_REPLACE(to_json_string(t), r'{|}', ''))) pair
)
WHERE question NOT IN ('user_id', 'address', 'age', 'year', 'transaction_id')
Ahora, la tabla de resultados (Tabla 7) tiene cuatro filas por user_id, donde la columna question nos indica a qué pregunta se refiere:
Cabe destacar que en el código tenemos el siguiente comando: SAFE_CAST(answer AS FLOAT64). Este se usa porque todas las columnas que estamos pasando a filas son numéricas (precisamente float). Sin embargo, si hubiese una mezcla de formatos, con respuestas numéricas y de texto, se puede sacar ese CAST. Por otro lado, solo para cerrar el comentario, se usa el SAFE para evitar que haya un error en la query y simplemente deje un null en caso de que una de las respuestas sea algo distinto a float. Pero, nuevamente, se puede sacar esa parte.
Pensamientos finales
Habiendo hecho esta pasada por tres técnicas que podrían resultarles útiles a algunos, a otros les permitan encontrar una aún mejor o definitivamente no les sirvan. Lo más importante es quedarse con que una vez que se maneja lo básico, es posible complejizar las queries lo suficiente como para resolverlo TODO.
Pero al mismo tiempo, no hay que perder de vista que Bigquery es muy potente para algunas cosas, pero para otras hay herramientas mucho más eficientes y hechas para eso.
Por lo que siempre pensar en que quizás no vale la pena darse mil vueltas y agregar ocho mil líneas a la query, si pasándolo a otro programa, se resuelve en una.
