Funciones de ventana de SQL que realmente necesitas para ciencia de datos

1. Por qué son importantes las funciones de ventana: el problema que resuelven

¿Calcular un total acumulado en SQL? Un GROUP BY estándar no te servirá para esto. Funciona como un compactador de basura: entran muchas filas y sale una. Obtienes el total, pero los detalles individuales de la transacción desaparecen.

Esto es lo que sucede cuando intentamos mostrar cada compra junto con el gasto total usando un enfoque ingenuo.

import pandas as pd
import sqlite3

# Let's set up a quick in-memory database to simulate SQL
conn = sqlite3.connect(':memory:')

data = pd.DataFrame({
    'user_id': [1, 1, 1, 2, 2],
    'amount': [10, 20, 30, 50, 10]
})
data.to_sql('sales', conn, index=False)

# The Naive Approach: This fails to show row-level detail
query_naive = """
SELECT user_id, SUM(amount) as total_spend
FROM sales
GROUP BY user_id
"""
print("Naive Output (Rows are collapsed):")
print(pd.read_sql(query_naive, conn))

# The Window Function Approach: Aggregation without collapsing
query_window = """
SELECT user_id, amount, 
       SUM(amount) OVER(PARTITION BY user_id) as total_user_spend
FROM sales
"""
print("\nWindow Function Output (Row detail preserved):")
print(pd.read_sql(query_window, conn))

Lo que esto significa realmente: El primer resultado nos da solo dos filas. Hemos perdido el contexto de cuándo se gastó el dinero. El segundo resultado es diferente: la función de ventana SUM(...) OVER(...) calculó el total, pero conservó las cinco filas originales. El enfoque ingenuo no pudo mostrarnos el árbol y el bosque al mismo tiempo.

2. La idea central: partición y ordenamiento

Las funciones de ventana crean una “ventana” de filas para cada registro. Para cada fila de tu tabla, la base de datos examina un subconjunto específico de otras filas para realizar un cálculo.

• PARTITION BY: Piénsalo como GROUP BY, pero para ventanas. Le indica a SQL que solo examine las filas que comparten el mismo valor que la fila actual (por ejemplo, el mismo user_id).
• ORDER BY: Esto define la secuencia. Para un total acumulado, el orden es importante.

# Let's look at how ordering changes the calculation
query_partition = """
SELECT user_id, amount, 
       SUM(amount) OVER(PARTITION BY user_id ORDER BY amount) as running_total
FROM sales
"""
print("Partitioned and Ordered Running Total:")
print(pd.read_sql(query_partition, conn))

En esta salida, la columna running_total se reinicia para el Usuario 2. Dentro de la partición del Usuario 1, los números se suman secuencialmente (10, 30, 60). La ventana se “desliza” fila por fila.

3. ROW_NUMBER, RANK y DENSE_RANK: numeración de filas

Esto confunde a muchos candidatos. Las tres funciones parecen idénticas hasta que aparece un empate. Digamos que tres productos vendieron exactamente 100 unidades cada uno.

• ROW_NUMBER(): Un contador estricto. Los empates no importan — da 1, 2, 3.
• RANK(): Los empates obtienen el mismo número, pero el siguiente se salta. Entonces 1, 1, 3.
• DENSE_RANK(): Los empates obtienen el mismo número, sin saltarse ninguno. Entonces 1, 1, 2.

ties_data = pd.DataFrame({
    'product': ['A', 'B', 'C', 'D'],
    'sales': [100, 100, 50, 10]
})
ties_data.to_sql('products', conn, index=False, if_exists='replace')

query_ranks = """
SELECT product, sales,
       ROW_NUMBER() OVER(ORDER BY sales DESC) as row_num,
       RANK() OVER(ORDER BY sales DESC) as rank_num,
       DENSE_RANK() OVER(ORDER BY sales DESC) as dense_rank_num
FROM products
"""
print(pd.read_sql(query_ranks, conn))

Interpretación: Observa el producto ‘C’. RANK lo llama el elemento 3 porque dos elementos empataron en el lugar 1. DENSE_RANK lo llama el elemento 2 porque solo cuenta los valores únicos por encima de él. Si un entrevistador pide el “top 3”, vale la pena preguntar cómo desea que se manejen los empates.

4. LAG y LEAD: consultando filas anteriores y siguientes

LAG obtiene la fila anterior a la actual. LEAD obtiene la fila que le sigue. Estas dos funciones son la base del análisis de series de tiempo.

# Calculate day-over-day change
daily_data = pd.DataFrame({
    'day': [1, 2, 3, 4],
    'revenue': [100, 150, 130, 200]
})
daily_data.to_sql('daily_rev', conn, index=False)

query_lag = """
SELECT day, revenue,
       LAG(revenue) OVER(ORDER BY day) as prev_rev,
       revenue - LAG(revenue) OVER(ORDER BY day) as diff
FROM daily_rev
"""
print(pd.read_sql(query_lag, conn))

Lo que ocurre: El Día 2, LAG toma el 100 del Día 1, y la columna diff muestra 50. El Día 1, prev_rev es None (o NULL) — no hay un día anterior de donde tomar el dato. Así es como puedes saber si el gasto de un usuario va en aumento o en disminución a lo largo del tiempo.

5. Agregaciones acumulativas: SUM, AVG, COUNT sobre una ventana

También puedes controlar el tamaño de la ventana con ROWS BETWEEN.

• UNBOUNDED PRECEDING: Desde el inicio.
• CURRENT ROW: Se detiene en la fila actual.
• 7 PRECEDING: Retrocede exactamente 7 filas — útil para una media móvil de 7 días.

query_moving_avg = """
SELECT day, revenue,
       SUM(revenue) OVER(ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total,
       AVG(revenue) OVER(ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as two_day_avg
FROM daily_rev
"""
print(pd.read_sql(query_moving_avg, conn))

Interpretación: El two_day_avg para el Día 2 es 125 (promedio de 100 y 150). Este suavizado reduce el ruido diario para que la tendencia subyacente sea más fácil de leer.

6. FIRST_VALUE y LAST_VALUE: anclaje al inicio o al final

A veces necesitas comparar cada fila contra una línea base. ¿Cuál fue el precio de la primera compra del usuario?

query_first = """
SELECT user_id, amount,
       FIRST_VALUE(amount) OVER(PARTITION BY user_id ORDER BY amount ASC) as cheapest_ever
FROM sales
"""
print(pd.read_sql(query_first, conn))

Ahora cada fila del Usuario 1 muestra ‘10’ en la columna cheapest_ever. Esto facilita calcular cosas como el “porcentaje de aumento desde la primera compra”.

7. NTILE: agrupación en cuantiles

NTILE(n) divide tus datos en n cubos. ¿Quieres cuartiles? Usa NTILE(4).

# Segment users into two halves based on spending
query_ntile = """
SELECT user_id, SUM(amount) as total,
       NTILE(2) OVER(ORDER BY SUM(amount) DESC) as spend_tier
FROM sales
GROUP BY user_id
"""
print(pd.read_sql(query_ntile, conn))

Interpretación: El Usuario 1 y el Usuario 2 gastaron 60 en total, pero NTILE(2) aún tiene que dividir 2 filas en 2 cubos de igual tamaño. Entonces, un usuario cae en el nivel 1 y el otro en el nivel 2 — quién obtiene el nivel 1 depende de cómo la base de datos resuelva el empate, ya que ORDER BY SUM(amount) DESC por sí solo no determina completamente el orden de las filas cuando los valores son iguales. Esto es ideal para segmentos de “Valor Alto” frente a “Valor Bajo”. Simplemente añade una columna de desempate a ORDER BY si necesitas una asignación de cubos reproducible.

8. Poniéndolo todo junto: una pregunta real de entrevista

Pregunta: “Para cada registro de ingresos diarios, muestra el total acumulado, el cambio respecto al día anterior y el rango de los ingresos de ese día en comparación con todos los demás días.”

full_query = """
SELECT 
    day, 
    revenue,
    SUM(revenue) OVER(ORDER BY day) as running_total,
    revenue - LAG(revenue) OVER(ORDER BY day) as dod_change,
    RANK() OVER(ORDER BY revenue DESC) as rev_rank
FROM daily_rev
"""
print(pd.read_sql(full_query, conn))

En una entrevista, lo plantearías de manera sencilla: “Estoy usando SUM con un ORDER BY para el total acumulado, LAG para obtener la fila anterior correspondiente al cambio diario, y RANK para encontrar nuestros días de mayores ingresos.”

9. Errores comunes y cómo evitarlos

1. Olvidar ORDER BY: Si escribes SUM(amount) OVER(PARTITION BY user_id), obtienes el total de ese usuario en cada fila, no un total acumulado. Necesitas ORDER BY para que calcule el acumulado.
2. Mezclar con GROUP BY: No puedes usar una función de ventana en una columna que no esté en tu GROUP BY a menos que la envuelvas correctamente. Yo optaría por agregar primero en un CTE y luego aplicar las funciones de ventana; suele ser el enfoque más seguro.
3. Valores NULL en LAG: La primera fila siempre tendrá un LAG NULL. Usa COALESCE(LAG(revenue), 0) para manejarlo.

10. Práctica: tres preguntas estilo entrevista

1. Clasificar productos por categoría: RANK() OVER(PARTITION BY category ORDER BY sales DESC)
2. Identificar a los ‘Súper usuarios’: NTILE(10) divide a los usuarios en deciles — el grupo superior marca el 10% de los que más gastan.
3. Encontrar brechas de inactividad: LAG(purchase_date) obtiene la fecha de compra anterior, para que puedas medir los días entre las compras de un usuario.

11. Próximos pasos

Has pasado de “SQL es para aplastar datos” a “SQL es para analizar secuencias.” Las funciones de ventana son más rápidas que los self-joins, y el código se lee más limpio.

• Resumen: PARTITION para agrupar, ORDER para secuenciar, LAG/LEAD para viajar en el tiempo.
• Siguiente parte: Combinaremos estas con Expresiones de Tabla Comunes (CTEs) para construir pipelines de datos complejos.

Comprueba tu comprensión

Las preguntas a continuación van desde la simple recordación hasta el diseño de formato abierto, siguiendo aproximadamente la Taxonomía de Bloom.

Recordar ¿Cuál es la diferencia clave entre RANK() y DENSE_RANK() cuando hay un empate?

Comprender En tus propias palabras, explica por qué SUM(amount) OVER(PARTITION BY user_id) sin un ORDER BY da el mismo total en cada fila para ese usuario, en lugar de un total acumulado.

Aplicar Usando la sintaxis ROWS BETWEEN del artículo, ¿qué definición de marco de ventana escribirías para calcular un promedio móvil de 3 días (hoy más los dos días anteriores)?

Analizar La trampa #3 del artículo advierte que LAG produce NULL para la primera fila de cada partición. Explica paso a paso qué le ocurriría a un cálculo dod_change (revenue - LAG(revenue)) en esa primera fila si no usaras COALESCE, y por qué obtener NULL de forma silenciosa allí es más seguro que obtener un número incorrecto de forma silenciosa.

Evaluar El ejemplo de NTILE del artículo muestra que un empate entre dos usuarios con el mismo total se rompe arbitrariamente por la base de datos. Critica el uso de NTILE para una segmentación de clientes de “Alto Valor” frente a “Bajo Valor” sin una columna explícita de desempate: ¿qué riesgo de negocio crea este desempate arbitrario si la asignación de segmentos conlleva un tratamiento diferente (como distintas ofertas de descuento)?

Crear Diseña una consulta con funciones de ventana (siguiendo el patrón de la “pregunta real de entrevista” de la Sección 8 del artículo) para un nuevo escenario: para cada cliente, muestra el monto de su pedido, su rango entre sus propios pedidos (no los de todos los clientes) y la cantidad de días desde su pedido anterior. Esboza el SQL usando PARTITION BY, RANK() y LAG() juntos.

---

Aplica lo que aprendiste

**Resumen**: Estás entregando un servicio de extracción de características para un modelo de abandono de usuarios (churn). El servicio lee de una tabla `sales` (`user_id`, `amount`, `day`) y debe producir características de ML por transacción utilizando funciones de ventana SQL — totales acumulados, deltas día a día, rangos de transacciones por usuario y cubos de niveles de gasto. Tres trampas del artículo te afectarán en producción si no las manejas: (1) omitir `ORDER BY` de `SUM ... OVER(PARTITION BY ...)` da un total estático en lugar de un total acumulado, (2) `LAG` devuelve `NULL` en la primera fila de cada partición y corrompe silenciosamente `dod_change` a menos que uses `COALESCE`, y (3) `NTILE` sin una columna de desempate asigna cubos arbitrarios cuando hay empates en los totales — segmentos no reproducibles significan diferentes ofertas de descuento para usuarios con valor idéntico.

Entregable: Completa las cinco funciones TODO en el archivo base. Cada función tiene un comentario PASS CRITERION que describe cómo debería ser el resultado correcto. Ejecuta python window_feature_service.py para verificar que las cinco consultas se ejecuten y produzcan los conteos de filas y valores esperados.

Archivo inicial: projects/sql-window-functions-you-actually-need-for-data-science/window_feature_service.py

Rúbrica:

• compute_running_total usa SUM(amount) OVER(PARTITION BY user_id ORDER BY day) — el ORDER BY day es lo que hace que sea “acumulado”; sin esto, cada fila muestra 60 para el Usuario 1 (trampa #1 del artículo)
• compute_dod_change envuelve LAG(amount) en COALESCE(..., 0) para que la primera fila por usuario muestre 0, no NULL (trampa #3 del artículo)
• compute_user_rank usa RANK() y no ROW_NUMBER() — las dos transacciones de amount=25 del Usuario 3 deben compartir el rango 2, y amount=15 obtiene el rango 4 (Sección 3 del artículo: empates)
• compute_spend_tiers añade user_id como desempate en NTILE(3) OVER(ORDER BY total_spend DESC, user_id) para que la asignación de cubos sea reproducible cuando el Usuario 1 y el Usuario 2 sumen ambos 60 (Sección 7 del artículo)
• build_feature_table combina las cuatro funciones de ventana en una consulta que devuelve 9 filas (una por transacción, sin colapso de GROUP BY) con las columnas: user_id, day, amount, running_total, dod_change, txn_rank, cheapest_ever