Funciones de ventana en SQLite: OVER y PARTITION BY

Una función de ventana añade una columna sin colapsar las filas

GROUP BY reduce muchas filas a una sola. Las funciones de ventana en SQLite hacen algo distinto: calculan un valor sobre un conjunto de filas relacionadas, pero conservan todas las filas originales en el resultado. Así obtienes el detalle fila por fila y el agregado, uno al lado del otro.

La estructura es siempre la misma: una función, seguida de OVER (...).

CREATE TABLE sales (
    employee TEXT,
    region   TEXT,
    amount   INTEGER
);

INSERT INTO sales VALUES
    ('Ada',   'EU', 100),
    ('Boris', 'EU', 250),
    ('Cleo',  'EU', 175),
    ('Dan',   'US', 300),
    ('Eve',   'US', 150);

SELECT employee, region, amount,
       SUM(amount) OVER () AS total_all
FROM sales;

La columna total_all muestra el gran total de todas las filas, repetido en cada línea. Las filas originales quedan intactas. Compáralo con SELECT SUM(amount) FROM sales: el número es el mismo, pero solo te devuelve una fila. Con las funciones de ventana en SQLite obtienes ambas vistas a la vez.

PARTITION BY: agregar dentro de grupos

Un OVER () vacío agrega sobre toda la tabla. Si añades PARTITION BY, agregas dentro de grupos, parecido a GROUP BY, pero sin colapsar las filas.

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

Cada fila trae el total de su región y la porción que le corresponde de ese total. Con un GROUP BY clásico perderías el detalle por empleado. Ese es justo el gran punto a favor de las funciones de ventana en SQLite: te dan el detalle y el agregado en una sola consulta.

Ranking en SQLite: ROW_NUMBER, RANK y DENSE_RANK

La familia de funciones de ranking numera las filas siguiendo el ORDER BY que pongas dentro del OVER. Las tres variantes se diferencian en cómo tratan los empates.

CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES
    ('Ada', 90), ('Boris', 85), ('Cleo', 90),
    ('Dan', 70), ('Eve', 85);

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS rn,
       RANK()       OVER (ORDER BY points DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dr
FROM scores;

Al leer el resultado:

• ROW_NUMBER() siempre devuelve valores únicos: los empates se rompen de forma arbitraria. Úsalo cuando necesites un número estable y distinto para cada fila.
• RANK() asigna el mismo puesto a las filas empatadas, pero luego se salta los números siguientes. Si dos jugadores empatan en el puesto 1, el siguiente recibe el puesto 3.
• DENSE_RANK() también respeta los empates, pero no se salta posiciones. El siguiente puesto sería el 2.

Para sacar los "top N por grupo", combina la función de ranking con PARTITION BY y filtra desde una consulta externa, ya que WHERE no puede referenciar funciones de ventana directamente:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT *
FROM (
    SELECT employee, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
WHERE rn <= 2;

Los dos que más facturan en cada región.

LAG y LEAD: mirar filas vecinas

LAG(col) devuelve el valor de col de la fila anterior dentro de la ventana. LEAD(col), en cambio, mira hacia adelante. Ambas funciones vienen de maravilla cuando quieres analizar cómo cambian los datos en el tiempo.

CREATE TABLE temps (day INTEGER, celsius INTEGER);
INSERT INTO temps VALUES (1, 18), (2, 21), (3, 19), (4, 24), (5, 22);

SELECT day, celsius,
       LAG(celsius)  OVER (ORDER BY day) AS ayer,
       celsius - LAG(celsius) OVER (ORDER BY day) AS diferencia
FROM temps;

En la primera fila, ayer es NULL porque no hay nada antes. Puedes definir un valor por defecto: LAG(celsius, 1, celsius) OVER (ORDER BY day) usaría el valor de hoy cuando no exista una fila anterior.

LEAD hace justo lo contrario. Si combinas ambas con PARTITION BY, obtienes secuencias por grupo, como comparar las ventas de este mes con las del mes anterior dentro de cada región.

Total acumulado en SQLite con window functions

Si añades ORDER BY dentro de OVER, las funciones de agregación como SUM, AVG o COUNT empiezan a calcularse de forma acumulativa:

CREATE TABLE deposits (day INTEGER, amount INTEGER);
INSERT INTO deposits VALUES (1, 50), (2, 30), (3, 70), (4, 20), (5, 40);

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3day
FROM deposits;

Dos detalles que conviene notar:

• SUM(amount) OVER (ORDER BY day) es un total acumulado. Cuando escribes ORDER BY sin definir un marco explícito, el marco por defecto va "desde el inicio de la ventana hasta la fila actual".
• La segunda columna sí define un marco explícito: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Esa es una ventana deslizante de 3 filas, o sea, una media móvil.

El modelo mental para los marcos: cada función de ventana se evalúa sobre un marco de filas definido respecto a la fila actual. Algunos marcos típicos:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: total acumulado (es el valor por defecto implícito).
• ROWS BETWEEN N PRECEDING AND CURRENT ROW: ventana móvil hacia atrás.
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: toda la partición.

ROWS cuenta filas físicas. También existe RANGE, que agrupa por valor: viene muy bien cuando hay empates en la columna del ORDER BY y quieres tratarlos como un único paso.

FIRST_VALUE, LAST_VALUE y NTILE

Hay algunas funciones de ventana más que vale la pena conocer:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT employee, region, amount,
       FIRST_VALUE(employee) OVER (PARTITION BY region ORDER BY amount DESC) AS top_earner,
       NTILE(2) OVER (ORDER BY amount DESC) AS half
FROM sales;

• FIRST_VALUE y LAST_VALUE devuelven el primer o el último valor dentro del marco. Con LAST_VALUE hay que prestar atención al marco: por defecto termina en CURRENT ROW, así que normalmente vas a querer ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING para obtener de verdad el último valor de la partición.
• NTILE(n) reparte las filas en n cubos de tamaño parecido — muy útil para cuartiles, percentiles o particiones tipo A/B.

Nombrar una ventana con WINDOW

Cuando varias columnas comparten la misma cláusula OVER (...), repetirla una y otra vez se vuelve pesado. SQLite te permite nombrar una ventana una sola vez y reutilizarla:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount)   OVER region_window AS region_total,
       AVG(amount)   OVER region_window AS region_avg,
       RANK()        OVER region_window AS region_rank
FROM sales
WINDOW region_window AS (PARTITION BY region ORDER BY amount DESC);

Misma consulta, mucho menos ruido. La cláusula WINDOW va después de WHERE/GROUP BY/HAVING y antes de ORDER BY.

Window functions vs GROUP BY en SQLite

Ambas opciones implican agregación, pero responden a preguntas distintas:

• GROUP BY reduce. Una fila por grupo. Úsalo cuando solo te interese el resumen.
• Las funciones de ventana conservan las filas. Cada fila de entrada sobrevive, con columnas calculadas extra al lado.

Si alguna vez te ves haciendo un GROUP BY y luego volviendo a unir los agregados con la tabla original, es una señal clarísima de que una función de ventana resolvería todo en una sola consulta.

Un par de tropiezos habituales

• WHERE no puede referenciar funciones de ventana. Los filtros se aplican antes de calcular las ventanas. Envuelve la consulta en una subconsulta o CTE y filtra desde fuera.
• Los marcos implícitos engañan. SUM(x) OVER (ORDER BY y) produce un total acumulado porque el marco por defecto es RANGE UNBOUNDED PRECEDING. Si lo que querías era la suma completa de la partición, escribe OVER (PARTITION BY ...) sin ORDER BY, o define el marco de forma explícita.
• LAST_VALUE sorprende a todo el mundo la primera vez. Como el marco por defecto termina en la fila actual, devuelve el valor actual y no el último de la partición. Toca redefinir el marco.
• Las funciones de ventana requieren SQLite 3.25+ (publicado en 2018). Cualquier instalación medianamente moderna las soporta, pero algunos entornos embebidos van con retraso.

Lo que viene: columnas generadas

Las funciones de ventana son cálculo en tiempo de consulta. La siguiente página trata el cálculo en tiempo de almacenamiento: las columnas generadas, donde el valor de la columna se define mediante una expresión y se actualiza automáticamente conforme cambian los datos subyacentes.