Funciones de ventana SQL en Django

¿Qué son las funciones de ventana?

Están relacionadas con las conocidas funciones de agregación: calculan sobre un conjunto de filas y devuelven un valor. A diferencia de ellas, no agrupan las filas de entrada en una sola fila de salida, y conservan la información original.

Forman parte del estándar desde SQL:2003.

Preparar un entorno de prueba

Abre tu editor con soporte SQL y verifica que el sistema de gestión de bases de datos soporte funciones de ventana. Crea una tabla con datos aleatorios para trabajar con ejemplos. Representará conexiones de usuarios al sistema. Crea una base de datos con esta tabla:

CREATE TABLE connections (
  id INT,
  user_id INT,
  source_ip VARCHAR(15), 
  connection_timestamp TIMESTAMP,
  bytes_transferred INT
)

Llénala con:

INSERT INTO connections VALUES
(1, 1, '192.168.1.100', '2016-10-01T13:30:00', 15720000),
(2, 1, '192.168.1.100', '2016-10-01T00:40:16', 19500000),
(3, 2, '192.168.1.100', '2016-10-01T19:09:48', 9840000),
(4, 3, '192.168.1.200', '2016-10-01T22:40:58', 10000),
(5, 3, '192.168.1.200', '2016-10-01T13:15:03', 20310000),
(6, 3, '192.168.1.200', '2016-10-01T10:10:48', 2010000),
(7, 4, '192.168.1.200', '2016-10-01T23:25:21', 20310000),
(8, 4, '192.168.1.200', '2016-10-01T04:06:49', 810000),
(9, 1, '192.168.1.200', '2016-10-01T16:07:10', 91280000)

Cómo usarlas

Se declaran como una función de agregación seguida de OVER, que indica cómo se agrupan las filas y otros detalles que veremos.

Podemos consultar el promedio de bytes transferidos por IP de origen en las conexiones:

SELECT *, AVG(bytes_transferred) OVER (PARTITION BY source_ip) FROM connections

...con este resultado:

Conservamos las 9 filas originales y añadimos el resultado como columna. La función no alteró la entrada.

Usamos la conocida AVG porque todas las funciones de agregación existentes pueden usarse como funciones de ventana. Hay otras nuevas que solo admiten esta forma.

PARTITION frente a GROUP

El grupo de filas al que se aplica se llama «partición». En su forma básica es igual al grupo de una función de agregación: filas consideradas «iguales» según un criterio, sobre las que se calcula un resultado. Sin embargo, la función de ventana se ejecuta por fila, no una vez por partición, y el resultado puede variar...

El «marco de ventana»

Si la partición no cambia y la función se aplica a todas sus filas, ¿cómo puede variar el resultado?

La primera parte no es totalmente cierta: se aplica a un subconjunto llamado «marco de ventana». Antes coincidía con toda la partición, pero podemos cambiarlo:

SELECT *, AVG(bytes_transferred) OVER (PARTITION BY source_ip ORDER BY bytes_transferred) FROM connections

Ahora avg varía entre filas de la misma partición porque el marco es distinto. Con ORDER BY, contiene las filas desde el comienzo hasta la actual, incluidas las posteriores iguales a ella según la cláusula ORDER BY. Recuerda esto para evitar resultados que parezcan incoherentes. Al entenderlo verás que es una función potente para definir exactamente qué filas puede ver.

En las primeras 2 filas, source_ip define la partición: las primeras 3 pertenecen a ella. En la primera, el marco solo contiene esa fila y el promedio es su bytes_transferred. En la segunda, abarca la primera y la actual, y se calcula con ambas.

En la fila #7, el marco va de la #4, primera de su partición, hasta la #8, posterior a la actual, porque su bytes_transferred coincide según ORDER BY. Por eso #7 y #8 tienen el mismo marco y avg.

También puede definirse mediante una «cláusula de marco», que no detallaremos. La documentación de PostgreSQL lo explica. Sin esa cláusula ni ORDER BY, el marco es toda la partición.

Orden de ejecución

Se ejecutan después de WHERE, HAVING y GROUP BY. No podemos usar sus resultados en esas cláusulas sin subconsultas; además, las cláusulas afectan a las filas que forman las particiones.

Las funciones de agregación también se ejecutan antes. No podemos usar resultados de ventana dentro de una agregación, pero sí al revés.

Funciones de ventana frente a consultas SQL típicas

Para obtener la última conexión de cada usuario con todas las columnas, hay alternativas. Veámoslas:

Alternativa 1: subconsulta con agregación

La subconsulta devuelve el máximo connection_timestamp del usuario y lo compara con el connection_timestamp de la fila actual. Se ejecuta por fila y causa problemas de rendimiento con muchos datos: ~20s en mi PostgreSQL con 13k filas, frente a ~35ms con la solución de ventana final.

SELECT * FROM connections WHERE connection_timestamp = (
  SELECT max(connection_timestamp) FROM connections AS sub
  WHERE sub.user_id = connections.user_id
)

Alternativa 2: unir la tabla consigo misma

Unimos filas con el mismo user_id donde la de la «izquierda» tiene un connection_timestamp menor que la de la «derecha». Devuelve conexiones con otra más reciente del mismo usuario. Elegimos las que no están en ese conjunto y obtenemos lo deseado.

La desventaja es la legibilidad y no poder obtener dinámicamente las «N» últimas conexiones: habría que unir la tabla tantas veces como conexiones queremos.

SELECT * FROM connections WHERE id NOT IN (
  SELECT c1.id
  FROM connections AS c1
  JOIN connections AS c2 USING (user_id)
  WHERE c1.connection_timestamp < c2.connection_timestamp
)

Con funciones de ventana

Con row_number y una subconsulta logramos el objetivo:

SELECT * FROM (
  SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY connection_timestamp DESC)
  FROM connections
) AS sub WHERE row_number = 1

row_number devuelve el índice de la fila en su partición desde 1. Pedimos las primeras conexiones al ordenar por el mayor connection_timestamp, particionando por user_id. Como no podemos usar row_number en WHERE, envolvemos todo en una subconsulta y filtramos fuera.

Django está en el título, pero no hablas de él

¡Cierto! La sintaxis cabe en una sola expresión de la lista SELECT, como cualquier columna, o en ORDER BY, donde también se permite. No necesitamos soporte especial para usarlas en el ORM de Django.

Basta llamar a annotate con una expresión RawSQL.

from django.db.models.expressions import RawSQL
from connections.models import Connection
Connection.objects.annotate(row_number=RawSQL(
    'row_number() OVER (PARTITION BY user_id ORDER BY connection_timestamp DESC)',
    []
))

Más lecturas

• https://www.postgresql.org/docs/current/static/tutorial-window.html
• http://tapoueh.org/blog/2013/08/20-Window-Functions