SQL Academy · Lección

Escritura de consultas analíticas

Segmente, desglose y consolide las métricas.

Escritura de consultas analíticas es una lección gratuita de SQL Academy en CoddyKit. Esta es la lección 4 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de SQL Academy, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de SQL Academy incluye 4 lecciones en total.

¿Qué son las consultas analíticas?

Las consultas analíticas van más allá de las simples búsquedas de filas. En lugar de preguntar ¿qué pedido hizo el cliente 42?, preguntan ¿cuáles son los ingresos totales por región y trimestre? o ¿cómo se compara este mes con el anterior?

En un almacén de datos basado en un esquema de estrella, las consultas analíticas segmentan (filtran una dimensión), seleccionan (filtran varias dimensiones) y agregan (agrupan con una granularidad más general) los hechos para obtener información útil para el negocio.

Repaso del esquema de estrella

Un esquema de estrella tiene una tabla de hechos central (por ejemplo, fact_sales) rodeada de tablas de dimensiones (por ejemplo, dim_date, dim_product y dim_store). Las consultas analíticas unen la tabla de hechos con las dimensiones necesarias para el análisis actual.

SELECT
    s.store_name,
    d.year,
    d.quarter,
    SUM(f.revenue)   AS total_revenue,
    SUM(f.units_sold) AS total_units
FROM fact_sales f
JOIN dim_store  s ON s.store_id  = f.store_id
JOIN dim_date   d ON d.date_id   = f.date_id
GROUP BY
    s.store_name,
    d.year,
    d.quarter
ORDER BY
    d.year,
    d.quarter,
    s.store_name;

Segmentación: filtrar una dimensión

Segmentar significa restringir el conjunto de resultados a un único valor de una dimensión; por ejemplo, consultar únicamente los datos del año 2024. La cláusula WHERE es la herramienta para segmentar.

Al segmentar al principio se reduce el número de filas que la base de datos debe agregar, lo que mantiene rápidas las consultas sobre tablas de hechos grandes.

-- Slice: only year 2024
SELECT
    p.category,
    SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY p.category
ORDER BY total_revenue DESC;

Selección: filtrar varias dimensiones

Seleccionar significa aplicar filtros sobre dos o más dimensiones al mismo tiempo; por ejemplo, consultar las ventas de productos electrónicos de la región norte durante el primer trimestre. Cada condición WHERE adicional delimita un cubo de datos más pequeño.

-- Dice: category = 'Electronics', region = 'North', Q1
SELECT
    d.month,
    SUM(f.revenue)    AS revenue,
    SUM(f.units_sold) AS units
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE
    p.category  = 'Electronics'
    AND s.region = 'North'
    AND d.year   = 2024
    AND d.quarter = 1
GROUP BY d.month
ORDER BY d.month;

Agregación: alcanzar una granularidad mayor

Agregar significa pasar de una granularidad detallada (ventas diarias por tienda) a una granularidad más general (ventas mensuales por región). Para ello, se eliminan las columnas de nivel inferior de GROUP BY y se vuelven a agregar los datos.

El modificador ROLLUP permite generar subtotales y totales generales en una sola consulta, en lugar de escribir varios bloques UNION ALL.

-- Roll up from store/month to region/quarter with subtotals
SELECT
    s.region,
    d.quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date  d ON d.date_id  = f.date_id
WHERE d.year = 2024
GROUP BY ROLLUP(s.region, d.quarter)
ORDER BY s.region NULLS LAST, d.quarter NULLS LAST;

Comparaciones entre períodos con LAG

Uno de los patrones analíticos más habituales consiste en comparar una métrica con la misma métrica de un período anterior. La función de ventana LAG() permite incorporar directamente el valor de la fila anterior a la fila actual sin utilizar una autocombinación.

Aquí se calcula el crecimiento de los ingresos mes a mes como porcentaje.

WITH monthly AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY year, month) AS prev_month_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY year, month))
             / NULLIF(LAG(revenue) OVER (ORDER BY year, month), 0),
    2) AS mom_growth_pct
FROM monthly
ORDER BY year, month;

Totales acumulados con SUM OVER

Un total acumulado suma el valor de cada fila al total de todas las filas anteriores en un orden definido. Es perfecto para hacer un seguimiento de los ingresos acumulados durante un año o controlar la reducción progresiva de un presupuesto.

La cláusula de marco ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW hace que la ventana sea explícita y no dé lugar a ambigüedades.

SELECT
    d.year,
    d.month,
    SUM(f.revenue)                                      AS monthly_revenue,
    SUM(SUM(f.revenue)) OVER (
        PARTITION BY d.year
        ORDER BY d.month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                                   AS ytd_revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
ORDER BY d.year, d.month;

Clasificación de dimensiones con DENSE_RANK

La clasificación permite encontrar los valores con mejor o peor rendimiento dentro de un grupo. DENSE_RANK() asigna rangos consecutivos sin saltos cuando hay empates, por lo que es la opción preferida para las clasificaciones de los informes de BI.

Al envolver el resultado clasificado en una CTE y filtrar por rango, el patrón de los primeros N resultados queda limpio y fácil de leer.

WITH ranked_products AS (
    SELECT
        p.product_name,
        p.category,
        SUM(f.revenue) AS revenue,
        DENSE_RANK() OVER (
            PARTITION BY p.category
            ORDER BY SUM(f.revenue) DESC
        ) AS rnk
    FROM fact_sales f
    JOIN dim_product p ON p.product_id = f.product_id
    JOIN dim_date   d ON d.date_id     = f.date_id
    WHERE d.year = 2024
    GROUP BY p.product_name, p.category
)
SELECT *
FROM ranked_products
WHERE rnk <= 3
ORDER BY category, rnk;

Porcentaje de contribución con SUM de ventana

Conocer los ingresos absolutos de un producto es útil, pero saber que aporta el 38 % de los ingresos de su categoría resulta más práctico. Un SUM() de ventana sobre toda la partición proporciona el denominador sin una combinación con una subconsulta.

SELECT
    p.category,
    p.product_name,
    SUM(f.revenue)                               AS product_revenue,
    SUM(SUM(f.revenue)) OVER (PARTITION BY p.category) AS category_revenue,
    ROUND(
        100.0 * SUM(f.revenue)
             / SUM(SUM(f.revenue)) OVER (PARTITION BY p.category),
    1)                                           AS pct_of_category
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id     = f.date_id
WHERE d.year = 2024
GROUP BY p.category, p.product_name
ORDER BY p.category, pct_of_category DESC;

Medias móviles para suavizar tendencias

Las cifras de ventas diarias o semanales contienen mucho ruido. Una media móvil suaviza las fluctuaciones a corto plazo para que pueda observar la tendencia subyacente. Aquí se calcula una media móvil de tres meses mediante un marco de ventana deslizante.

WITH monthly_rev AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY year, month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
    2) AS moving_avg_3m
FROM monthly_rev
ORDER BY year, month;

CUBE para todas las combinaciones de dimensiones

CUBE amplía ROLLUP al calcular subtotales para todas las combinaciones posibles de las dimensiones indicadas, no solo para la ruta jerárquica de agregación. Esto produce el resumen completo entre dimensiones en una sola pasada, lo que resulta útil para paneles multidimensionales en los que los usuarios pueden cambiar libremente la perspectiva.

NULL en una columna de agrupación significa todos los valores de esa dimensión. Utilice GROUPING() para distinguir los NULL intencionados de los datos de los NULL generados por la agregación.

SELECT
    CASE WHEN GROUPING(s.region)   = 1 THEN 'ALL REGIONS'    ELSE s.region        END AS region,
    CASE WHEN GROUPING(p.category) = 1 THEN 'ALL CATEGORIES' ELSE p.category      END AS category,
    CASE WHEN GROUPING(d.quarter)  = 1 THEN 'ALL QUARTERS'   ELSE d.quarter::TEXT END AS quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY CUBE(s.region, p.category, d.quarter)
ORDER BY s.region NULLS LAST, p.category NULLS LAST, d.quarter NULLS LAST;

¿Qué operación restringe los resultados a un único valor de dimensión?

Compruebe cuánto entiende la terminología de las consultas analíticas utilizada en los almacenes de datos.

Resumen: escribir consultas analíticas

En esta lección exploró los patrones básicos para escribir consultas analíticas sobre un esquema de estrella:

Segmentación: filtrar una dimensión con WHERE para centrarse en un segmento específico.
Selección: filtrar varias dimensiones simultáneamente para delimitar un cubo de datos preciso.
Agregación: agrupar con una granularidad más general; utilizar ROLLUP o CUBE para obtener subtotales de varios niveles.
LAG / LEAD: comparar períodos sin autocombinaciones.
Totales acumulados y medias móviles: obtener métricas acumuladas y suavizadas mediante marcos de ventana.
DENSE_RANK: crear clasificaciones claras de los primeros N resultados dentro de las particiones.
Porcentaje de contribución: utilizar SUM de ventana como denominador para calcular proporciones.

La combinación de estos patrones cubre la gran mayoría de los requisitos de BI y generación de informes que encontrará en almacenes de datos de producción.

Preguntas frecuentes

¿La lección «Escritura de consultas analíticas» es gratis?

Sí — el texto completo de «Escritura de consultas analíticas» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de SQL Academy, actualiza a CoddyKit PRO. El curso de SQL Academy incluye 4 lecciones en total.

¿Qué aprenderé en «Escritura de consultas analíticas»?

Segmente, desglose y consolide las métricas. Practicas SQL Academy con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.

¿Necesito experiencia previa para empezar SQL Academy?

No se requiere experiencia previa. SQL Academy en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 4 de 4.

¿Cuánto tiempo toma la lección «Escritura de consultas analíticas»?

La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.

¿Puedo escribir y ejecutar código en esta lección de SQL Academy?

Sí. Cada lección de SQL Academy incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.

Todas las lecciones de este curso

← Volver a SQL Academy