Cómo solucionar fechas faltantes para el análisis de series de tiempo
Solucionar fechas faltantes en análisis de series de tiempo
Aprende cómo usar TVFs en BigQuery para generar fácilmente rangos de fechas para tu análisis de series de tiempo.
Mi objetivo con este artículo es ayudarte a entender TVFs y cómo utilizarlos mediante un ejemplo que aborda el problema común de fechas faltantes en el análisis de series de tiempo.
En algunas situaciones, las fechas con datos nulos son importantes y deben mostrarse/incluirse en tu conjunto de datos. Por ejemplo:
- Las empresas pueden beneficiarse identificando días sin ventas en el comercio minorista. Estos días son influenciados por días festivos o cambios en el comportamiento del cliente.
- Encontrar fechas faltantes en los datos ayuda a mejorar la calidad de los datos al revelar anomalías o valores atípicos causados por fallas en el sistema o captura de datos incompleta. Mostrar fechas faltantes es una herramienta útil para lograr este objetivo.
Estas fechas faltantes pueden causar problemas en el análisis y visualización. Por lo tanto, necesitas una solución que garantice que todas las fechas estén presentes en el resultado, incluso si no hay datos correspondientes.
Al final de este artículo, tendrás tu propio TVF, que puede generar esto…
- ¿Logra tu canalización de LLM alcanzar tu objetivo?
- Investigadores de Stanford proponen EVAPORATE Un nuevo enfoque de I...
- Conoce a Chapyter Una nueva extensión de Jupyter que permite que Ch...
¡A partir de una sola línea de código!
Cubriremos:
- Cómo generar fechas para llenar los espacios faltantes en tus datos
- Cómo puedes crear un TVF y el uso de parámetros
- Cómo llamar un TVF
- Exploraremos expandir nuestro generador de fechas para mayor flexibilidad.
- Finalmente, compartiré cómo puedes acceder a mi TVF y te presentaré un proyecto de código abierto llamado BigFunctions.
El Problema
Considera este escenario: has ejecutado una consulta que proporciona resultados de respuestas totales de encuestas por fecha para las últimas cuatro semanas. Luego, llevas los resultados a Google Sheets para visualizar rápidamente los datos.
El gráfico anterior no resalta ninguna fecha faltante; se ve exactamente como se esperaba. Incluso si eliges mostrar todas las fechas en el eje x, es posible que no te des cuenta de los dos días faltantes en julio.
Cómo Podemos Solucionar Este Problema
Antes de abordar el tema de TVF, hablemos sobre mi método para resolver este tipo de problema y por qué lo incluí en un TVF.
Para resolver este problema, creo lo que me gusta llamar un Eje de Fechas. Esta columna de fechas/semanas/meses, cualquier período que necesites, se construye por separado del conjunto de datos que estás analizando. Esto asegura que las fechas sean independientes y no dependan de la existencia de los datos.
Crear el eje de fechas es bastante sencillo, aunque puede ser una tarea tediosa si necesitas crearlo con frecuencia.
A continuación, se muestra un ejemplo sencillo que genera fechas entre el 19 de junio y el 16 de julio de 2023.
WITH date_axis AS (SELECT datesFROM UNNEST(generate_date_array("2023-06-19","2023-07-16")) AS dates)SELECT datesFROM date_axis
La función generate_date_array es la parte clave de esto, pero como su nombre indica, la salida se devuelve como un array. Por lo tanto, debemos desanidar (aplanar) este array para el siguiente paso.
El eje de fechas existe en una CTE ya que necesitamos tratarlo como una tabla separada para hacer una unión izquierda con nuestros datos reales y la lista de fechas.
WITH date_axis as (SELECT datesFROM UNNEST(generate_date_array("2023-06-19","2023-07-16")) as dates)SELECT dates, responses as original_responses, ifnull(responses,0) as new_responsesFROM date_axis as axisLEFT JOIN `spreadsheep-20220603.Case_Studies.survey_responses` as survey ON axis.dates = survey.date
Como se puede ver arriba, tenemos valores nulos para el 1 y 2 de julio en nuestra tabla de respuestas de encuestas porque esas fechas no existen. Usando el eje de fechas, podemos identificar fácilmente estos valores nulos y manejarlos apropiadamente, en este escenario, los valores nulos se reemplazan con 0.
Al volver a trazar nuestros datos actualizados, ahora capturamos la falta de respuestas a principios de julio.
¿Qué es exactamente una TVF?
TVF es una abreviatura de Table-Valued Function. Al igual que las UDF (User-Defined Functions), te permiten especificar una serie de tareas que se ejecutarán cada vez que se llame a tu función personalizada.
La diferencia entre ambas es que la UDF devuelve un resultado para cada fila en tu conjunto de datos, mientras que la TVF devuelve una tabla completa.
Tal vez te preguntes cuál es el punto si el enfoque de CTE hace el trabajo perfectamente. Bueno, en una TVF, podemos ampliar las características y reusabilidad de la función de eje de fechas y simplificar nuestro código.
Existen muchas formas creativas y útiles de usar las TVF, y en este artículo, utilizaremos una para generar un eje de fechas.
Crear una TVF
CREATE OR REPLACE TABLE FUNCTION `spreadsheep-20220603.Case_Studies.generate_dates`(start_date DATE, end_date DATE)AS (SELECT datesFROM UNNEST(generate_date_array(start_date,end_date)) as dates)Crear la TVF es fácil y sencillo; comienza con create or replace table functionseguido de dónde quieres guardar tu TVF en tu proyecto. Luego, puedes agregar parámetros, en este ejemplo hemos agregado dos.
start_date DATE, end_date DATE
Como se muestra a continuación, estos dos parámetros reemplazan los valores estáticos que agregamos a la función generate_date_array.
unnest(generate_date_array(start_date,end_date)) as dates
Cuando se crea tu TVF, puedes llamar a tu nueva función como si fuera una tabla. Ten en cuenta que he agregado paréntesis al final de la cláusula FROM para especificar qué valores quiero que la TVF utilice, con el 1 de julio como fecha de inicio y el 7 de julio como fecha de fin.
SELECT dates FROM `spreadsheep-20220603.Case_Studies.generate_dates`("2023-07-01", "2023-07-07")
Ahora podemos actualizar nuestra consulta original para usar la nueva TVF.
WITH date_axis as (SELECT dates FROM `spreadsheep-20220603.Case_Studies.generate_dates`("2023-06-19", "2023-07-16"))SELECT dates, responses as original_responses, ifnull(responses,0) as new_responsesFROM date_axis as axisLEFT JOIN `spreadsheep-20220603.Case_Studies.survey_responses` as survey ON axis.dates = survey.dateAmpliando el TVF
Hasta ahora, la función es bastante limitada porque solo proporciona fechas. ¿Y si quisiéramos fechas de inicio de semana donde la semana comienza los domingos, o queremos fechas de inicio y fin trimestrales para los últimos años?
Aunque podríamos agregar esa lógica en nuestra CTE que llama al TVF, vamos a manejar eso en el TVF en su lugar para que esté ahí siempre que lo necesitemos.
Mi versión final agrega algunas otras posibilidades dependiendo de si necesitas rangos de fechas semanales, mensuales o trimestrales.
CREATE OR REPLACE FUNCTION `spreadsheep-20220603.Case_Studies.generate_dates`(start_date DATE, end_date DATE) OPTIONS (description = "Generar una tabla de fechas") AS ((select date, format_date("%a", date) as day_of_week, date_trunc(date, week(monday)) as week_start_monday, date_trunc(date, week(monday)) + 6 as week_end_monday, date_trunc(date, week(sunday)) as week_start_sunday, date_trunc(date, week(sunday)) + 6 as week_end_sunday, date_trunc(date, month) as month_start, date_add(date_trunc(date, month), interval 1 month) - 1 as month_end, date_trunc(date, quarter) as quarter_start, date_add(date_trunc(date, quarter), interval 1 quarter) - 1 as quarter_end, from unnest(generate_date_array(start_date, end_date)) as date));Esto nos da la salida que vimos al principio del artículo, donde una sola línea de consulta puede generar años de fechas, junto con sus partes semanales, mensuales y trimestrales.
Como bonificación, esta función que creamos no consulta ningún dato real. Lo que significa que se puede ejecutar de forma completamente gratuita y también es extremadamente rápida.
Incluso generar fechas desde 1820 hasta la actualidad solo tomó 1 segundo.
SELECT * FROM `spreadsheep-20220603.Case_Studies.generate_dates`("1820-07-01","2023-07-15")
Accediendo a mi TVF
Para ahorrar tiempo, no es necesario crear este TVF en tu proyecto; puedes usar la versión pública, que existe en el proyecto de código abierto BigFunctions.
Para agregar BigFunctions a tu proyecto, puedes utilizar la función de agregar desde el explorador y luego ‘marcar un proyecto por nombre’, como se muestra a continuación.
Estas funciones están disponibles en todas las regiones, y dentro de cada conjunto de datos, encontrarás generate_dates bajo Rutinas. ¡Prueba el siguiente código!
SELECT * FROM `bigfunctions.europe_west2.generate_dates`("2022-01-01", "2023-01-01");Puedes encontrar más detalles sobre BigFunctions aquí, que está repleto de excelentes funciones personalizadas, algunas de las cuales incluso utilizan Python para ejecutar todo tipo de cosas interesantes. Échale un vistazo si utilizas BigQuery en tu rol diario.
Esto concluye este artículo. Si tienes alguna pregunta, no dudes en comentar, y responderé lo antes posible.
Escribo frecuentemente artículos sobre BigQuery y Looker Studio. Si estás interesado, considera seguirme aquí en VoAGI para más contenido.
Todas las imágenes, a menos que se indique lo contrario, son del autor.
¡Mantente elegante, amigos! Tom
