Funciones window básicas para analítica

Antes de empezar, imagina que estás currando con una tabla de miles de filas de ventas. Tu tarea: averiguar quién es el vendedor número uno en cada categoría, quién es el segundo, y así sucesivamente. O, por ejemplo, necesitas numerar todas las filas en una consulta para seguir el orden. Todo esto se hace fácil usando funciones window.

Las funciones window son funciones SQL que trabajan con un subconjunto de filas (vamos a llamarlo "ventana") de un conjunto de datos. A diferencia de las funciones agregadas, que agrupan filas en una sola (por ejemplo, SUM() o AVG()), las funciones window dejan las filas tal cual, añadiendo valores calculados a cada una.

Diferencia con funciones agregadas

Las funciones agregadas "comprimen" los datos, agrupando filas:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Resultado: solo un par de filas, según el número de departamentos.

Comparado con una función window — aquí las filas se quedan en su sitio, pero se añade un nuevo campo, por ejemplo, ROW_NUMBER():

SELECT employee_name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_within_department
FROM employees;

Aquí vas a tener todas las mismas filas, pero con una columna extra rank_within_department, donde a cada empleado se le asigna un número dentro de su departamento.

Funciones window principales

Sintaxis de OVER()

La parte más importante de cada función window es la palabra mágica OVER(). Esto define con qué "ventana" de datos va a currar la función. Dentro de OVER() puedes poner división en grupos (PARTITION BY) y/o orden de clasificación (ORDER BY).

Sintaxis general:

<función_window>() OVER (
   [PARTITION BY <grupo>] 
   [ORDER BY <orden>]
)

Componentes:

• PARTITION BY: Divide las filas en grupos. Por ejemplo, "divide los datos por departamentos".
• ORDER BY: Indica el orden de procesamiento de las filas. Por ejemplo, "ordena los empleados por salario de mayor a menor".

Función ROW_NUMBER()

La función ROW_NUMBER() numera las filas, empezando desde 1, dentro de la "ventana" indicada. A veces viene bien para crear un número de fila en una tabla temporal o para saber la posición de un registro.

Ejemplo. Tabla sales (ventas):

Consulta:

SELECT seller_name, product_category, revenue,
       ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS row_number
FROM sales;

Resultado:

Cómo funciona esto:

1. Los datos se dividen en grupos por product_category.
2. Cada grupo se ordena por revenue (de mayor a menor).
3. Las filas dentro de cada grupo reciben un número de orden.

Función RANK()

La función RANK() se usa para rankear filas. A diferencia de ROW_NUMBER(), tiene en cuenta los valores iguales y salta números (ranks) si hay empates.

Ejemplo:

SELECT seller_name, product_category, revenue,
       RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
FROM sales;

Resultado:

Función DENSE_RANK()

DENSE_RANK() es parecida a RANK(), salvo por un detalle: no salta números de rank si hay valores iguales.

Ejemplo. Añadimos una venta con el mismo revenue:

Consulta:

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Resultado:

Ejemplos de uso: numeración de filas

Tarea: numerar todos los pedidos en la tabla orders, ordenados por fecha.

SELECT order_id, customer_name, order_date,
       ROW_NUMBER() OVER (ORDER BY order_date) AS order_number
FROM orders;

Resultado: tienes la lista de pedidos con numeración según el orden de realización.

Ejemplos de uso: top-3 vendedores en cada categoría

Tarea: sacar los tres mejores vendedores en cada categoría de productos.

WITH ranked_sales AS (
    SELECT seller_name, product_category, revenue,
           RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
    FROM sales
)
SELECT seller_name, product_category, revenue
FROM ranked_sales
WHERE rank <= 3;

Ejemplos de uso: detectar valores iguales

Tarea: averiguar si hay vendedores con el mismo revenue en cada categoría.

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Ahora puedes ver los ranks donde los valores "se quedan pegados".