Laboratorio 5: SQL Avanzado y Funciones de Ventana (Window Functions)

Objetivos del Laboratorio

• Diferenciar entre las agregaciones tradicionales de la cláusula GROUP BY y las operaciones analíticas continuas.
• Modularizar consultas complejas utilizando Expresiones de Tabla Comunes (CTEs) para mejorar la mantenibilidad del código SQL.
• Implementar funciones de ventana avanzadas para realizar un análisis de comportamiento secuencial de clientes.
• Crear y encapsular lógica de negocio reutilizable mediante Funciones Temporales Definidas por el Usuario (UDFs).

Herramientas y APIs Utilizadas

• Standard SQL (GoogleSQL): El dialecto de SQL optimizado que utiliza BigQuery para el análisis de datos a gran escala.
• bq CLI: Interfaz de comandos empleada para enviar las instrucciones analíticas complejas directamente al motor de cálculo.
• BigQuery API: API de Google Cloud encargada de procesar las consultas SQL asíncronas y gestionar el retorno estructurado de los datos.

Estructura del Código Desarrollado

• sales.csv: Datos de transacciones financieras secuenciales que incluyen el identificador del cliente, la fecha de compra y el importe.
• main.tf: Archivo de configuración de Terraform que aprovisiona el dataset lab05_dataset y la tabla nativa de ventas.
• 05_advanced_analytics.sql: Script SQL completo que incorpora una UDF temporal, una estructura CTE y múltiples funciones de ventana.

Paso a Paso del Despliegue

Fase 1: Preparación de Datos y Despliegue

• Creación de los datos históricos de ventas simulando la actividad diaria de múltiples clientes independientes.
• Ejecución de la infraestructura declarada en Terraform mediante los comandos de inicialización y aplicación física.
• Carga rápida de los datos del CSV local a la tabla nativa recién desplegada utilizando el comando bq load.

Fase 2: Estructuración del Análisis Avanzado

• Escritura de una UDF temporal con la sentencia CREATE TEMP FUNCTION para segmentar las ventas según el importe acumulado.
• Declaración de una CTE utilizando la cláusula WITH para aislar y secuenciar lógicamente las fases de agregación del informe.
• Configuración del operador de ventana OVER(PARTITION BY customer_id ORDER BY sale_date).

Fase 3: Implementación Analítica y Ejecución

• Uso de la función de análisis temporal ROW_NUMBER() para enumerar cronológicamente la secuencia de compras por cliente.
• Uso de la función de navegación LAG() para capturar el importe de la venta anterior y contrastar tendencias sin realizar auto-uniones.
• Uso de la función agregada acumulativa SUM() acotada al marco lógico de la fila actual y las anteriores mediante la directiva ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
• Lanzamiento del script analítico desde la terminal y auditoría del comportamiento de los registros secuenciales resultantes.