Funções de janela SQL no Django

O que são funções de janela?

Elas se relacionam às conhecidas funções de agregação: calculam sobre um conjunto de linhas e retornam um valor. Ao contrário delas, não agrupam linhas de entrada em uma única linha de saída e preservam as informações originais.

Fazem parte do padrão desde SQL:2003.

Preparar um ambiente de teste

Abra seu editor com suporte SQL e verifique se o sistema de gerenciamento de bancos de dados suporta funções de janela. Crie uma tabela com dados aleatórios para trabalhar com exemplos. Ela representará conexões de usuários ao sistema. Crie um banco com esta tabela:

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

Preencha com:

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)

Como usá-las

São declaradas como uma função de agregação seguida de OVER, que indica como as linhas são agrupadas e outros detalhes que veremos.

Podemos consultar a média de bytes transferidos por IP de origem nas conexões:

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

...com este resultado:

Mantemos as 9 linhas originais e acrescentamos o resultado como coluna. A função não alterou a entrada.

Usamos a conhecida AVG porque todas as funções de agregação existentes podem ser usadas como funções de janela. Há outras novas que só permitem essa forma.

PARTITION versus GROUP

O grupo de linhas ao qual se aplica chama-se “partição”. Na forma básica, é igual ao grupo de uma função de agregação: linhas consideradas “iguais” por um critério, sobre as quais se calcula um resultado. Porém, a função de janela é executada por linha, não uma vez por partição, e o resultado pode variar...

O “quadro da janela”

Se a partição não muda e a função se aplica a todas as linhas, como o resultado pode variar?

A primeira parte não é totalmente verdadeira: aplica-se a um subconjunto chamado “quadro da janela”. Antes, correspondia à partição inteira, mas podemos mudar:

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

Agora avg varia entre linhas da mesma partição porque o quadro é diferente. Com ORDER BY, contém as linhas do início à atual, incluindo posteriores iguais a ela conforme a cláusula ORDER BY. Lembre-se disso para evitar resultados que pareçam incoerentes. Ao entender, verá que é um recurso poderoso para definir exatamente quais linhas pode enxergar.

Nas primeiras 2 linhas, source_ip define a partição: as primeiras 3 pertencem a ela. Na primeira, o quadro só contém essa linha, e a média é seu bytes_transferred. Na segunda, abrange a primeira e a atual, e é calculada com ambas.

Na linha #7, o quadro vai da #4, primeira da partição, até a #8, posterior à atual, porque seu bytes_transferred coincide conforme ORDER BY. Por isso, #7 e #8 têm o mesmo quadro e avg.

Também pode ser definido por uma “cláusula de quadro”, que não detalharemos. A documentação do PostgreSQL explica. Sem essa cláusula nem ORDER BY, o quadro é a partição inteira.

Ordem de execução

São executadas depois de WHERE, HAVING e GROUP BY. Não podemos usar seus resultados nessas cláusulas sem subconsultas; elas também afetam as linhas que formam as partições.

Funções de agregação também são executadas antes. Não podemos usar resultados de janela dentro de uma agregação, mas podemos fazer o inverso.

Funções de janela versus consultas SQL típicas

Para obter a última conexão de cada usuário com todas as colunas, há alternativas. Vamos vê-las:

Alternativa 1: subconsulta com agregação

A subconsulta retorna o máximo connection_timestamp do usuário e o compara ao connection_timestamp da linha atual. É executada por linha e causa problemas de desempenho com muitos dados: ~20s no meu PostgreSQL com 13k linhas, contra ~35ms na solução de janela 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 a tabela a si mesma

Unimos linhas com o mesmo user_id em que a da “esquerda” tem um connection_timestamp menor que a da “direita”. Retorna conexões com outra mais recente do mesmo usuário. Escolhemos as que não estão nesse conjunto e obtemos o desejado.

A desvantagem é a legibilidade e não poder obter dinamicamente as “N” últimas conexões: seria preciso unir a tabela tantas vezes quanto as conexões desejadas.

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
)

Com funções de janela

Com row_number e uma subconsulta, alcançamos o 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 retorna o índice da linha na partição a partir de 1. Pedimos as primeiras conexões ao ordenar pelo maior connection_timestamp, particionando por user_id. Como não podemos usar row_number em WHERE, envolvemos tudo em uma subconsulta e filtramos fora.

Django está no título, mas você não fala dele

Verdade! A sintaxe cabe em uma única expressão da lista SELECT, como qualquer coluna, ou em ORDER BY, onde também é permitida. Não precisamos de suporte especial para usá-las no ORM do Django.

Basta chamar annotate com uma expressão 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)',
    []
))

Mais leituras

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