Muitas vezes eles perguntam se existem análogos de funções analíticas (janela) no MySQL.
Nota No momento da redação deste texto, não havia tais análogos, mas o artigo ainda é de interesse acadêmico em termos de análise da abordagem original do uso de variáveis no MySQL.Para substituir funções analíticas, consultas de conexão automática, subconsultas complexas e mais são frequentemente usadas. A maioria dessas soluções é ineficaz em termos de desempenho.
Também no MySQL não há recursão. No entanto, algumas das tarefas que geralmente são resolvidas por funções analíticas ou recursivas podem ser tratadas pelas ferramentas do MySQL.
Uma dessas ferramentas é única e incomum para outro mecanismo DBMS de trabalhar com variáveis dentro de uma consulta SQL. Podemos declarar uma variável dentro da consulta, alterar seu valor e substituí-la em SELECT pela saída. Além disso, a ordem de processamento das linhas na solicitação e, como resultado, a ordem de atribuição de valores às variáveis podem ser definidas na classificação personalizada!
Advertência O artigo pressupõe que o processamento de expressões na cláusula SELECT seja realizado da esquerda para a direita, no entanto, não há confirmação oficial dessa ordem de processamento na documentação do MySQL. Isso deve ser lembrado ao alterar a versão do servidor. Para garantir consistência, você pode usar a instrução CASE ou IF dummy.
Analógico de recursão
Considere um exemplo simples que gera uma sequência de Fibonacci (na sequência de Fibonacci, cada termo é igual à soma dos dois anteriores e os 2 primeiros são iguais a um):
SELECT IF(X=1, Fn_1, Fn_2) F FROM( SELECT @I := @I + @J Fn_1, @J := @I + @J Fn_2 FROM (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)a, (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)b, (SELECT @I := 1, @J := 1)IJ )T, (SELECT 1 X UNION ALL SELECT 2)X;
Esta consulta gera 18 números de Fibonacci, sem contar os dois primeiros:
2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597,2584,4181,6765
Agora vamos ver como funciona.
Nas linhas 5) 6) 9 registros são gerados. Nada incomum aqui.
Na linha 7) declaramos duas variáveis @I, @J e as atribuímos 1.
Na linha 3), acontece o seguinte: primeiro, a variável @I recebe a soma das duas variáveis. Em seguida, atribuímos o mesmo à variável @J, levando em consideração o fato de que o valor de @I já foi alterado.
Em outras palavras, os cálculos no SELECT são executados da esquerda para a direita - veja também a observação no início do artigo.
Além disso, a mudança de variáveis é realizada em cada um de nossos 9 registros, ou seja, ao processar cada nova linha, as variáveis @I e @J conterão os valores calculados ao processar a linha anterior.
Para resolver o mesmo problema com a ajuda de outros DBMSs, teríamos que escrever uma
consulta recursiva!Nota:As variáveis devem ser declaradas em uma subconsulta separada (linha 7), se declararmos uma variável na cláusula SELECT, ela provavelmente será avaliada apenas 1 vez (embora o comportamento específico dependa da versão do servidor). O tipo de uma variável é determinado pelo valor pelo qual é inicializada. Este tipo pode mudar dinamicamente. Se você definir a variável como NULL, seu tipo será BLOB.A ordem na qual as linhas são processadas no SELECT, conforme mencionado acima, depende da classificação personalizada. Um exemplo simples de numeração de linha em uma determinada ordem:
SELECT val, @I:=@I+1 Num FROM (SELECT 30 val UNION ALL SELECT 20 UNION ALL SELECT 10 UNION ALL SELECT 50)a, (SELECT @I := 0)I ORDER BY val;
Val Num 10 1 20 2 30 3 50 4
Análogos de funções analíticas
Variáveis também podem ser usadas para substituir funções analíticas. A seguir estão alguns exemplos. Por uma questão de simplicidade, assumimos que todos os campos NÃO são NULL e a classificação e o particionamento (PARTITION BY) ocorrem em um campo. O uso de valores NULL e classificações mais complexas tornará os exemplos mais pesados, mas a essência não será alterada.
Para exemplos, crie a tabela TestTable:
CREATE TABLE TestTable( group_id INT NOT NULL, order_id INT UNIQUE NOT NULL, value INT NOT NULL );
onde
group_id - identificador de grupo (análogo da janela da função analítica);
order_id - um campo exclusivo para classificação;
value é algum valor numérico.
Preencha nossa tabela com dados de teste:
INSERT TestTable(order_id, group_id, value) SELECT * FROM( SELECT 1 order_id, 1 group_id, 1 value UNION ALL SELECT 2, 1, 2 UNION ALL SELECT 3, 1, 2 UNION ALL SELECT 4, 2, 1 UNION ALL SELECT 5, 2, 2 UNION ALL SELECT 6, 2, 3 UNION ALL SELECT 7, 3, 1 UNION ALL SELECT 8, 3, 2 UNION ALL SELECT 9, 4, 1 UNION ALL SELECT 11, 3, 2 )T;
Exemplos de substituição de algumas funções analíticas.
1) ROW_NUMBER () OVER (ORDER BY order_id)
SELECT T.*, @I:=@I+1 RowNum FROM TestTable T,(SELECT @I:=0)I ORDER BY order_id;
group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 4
2 5 2 5
2 6 3 6
3 7 1 7
3 8 2 8
4 9 1 9
3 11 2 10
2) ROW_NUMBER () OVER (PARTITION BY group_id ORDER BY order_id)
SELECT group_id, order_id, value, RowNum FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNum, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T;
group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 1
2 5 2 2
2 6 3 3
3 7 1 1
3 8 2 2
3 11 2 3
4 9 1 1
3) SUM (valor) ACIMA (PARTITION BY group_id ORDER BY order_id)
SELECT group_id, order_id, value, RunningTotal FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+value, @I:=value) RunningTotal, @last_group_id := group_id FROM TestTable T, (SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T;
group_id order_id value RunningTotal
1 1 1 1
1 2 2 3
1 3 2 5
2 4 1 1
2 5 2 3
2 6 3 6
3 7 1 1
3 8 2 3
3 11 2 5
4 9 1 1
4) LAG (valor) OVER (PARTITION BY group_id ORDER BY order_id)
SELECT group_id, order_id, value, LAG FROM( SELECT T.*, IF(@last_group_id = group_id, @last_value, NULL) LAG, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value LAG
1 1 1 NULL
1 2 2 1
1 3 2 2
2 4 1 NULL
2 5 2 1
2 6 3 2
3 7 1 NULL
3 8 2 1
3 11 2 2
4 9 1 NULL
Para LEAD, tudo é igual, apenas você precisa alterar a classificação para ORDER BY group_id, order_id DESC
Para as funções COUNT, MIN, MAX, tudo é um pouco mais complicado, porque até analisarmos todas as linhas do grupo (janela), não conseguiremos descobrir o valor da função. O MS SQL, por exemplo, "coloca em spool" uma janela para esses fins (coloca temporariamente as linhas da janela em uma tabela de buffer oculta para acessá-las novamente), no MySQL não existe essa possibilidade. Mas podemos calcular o valor da função na última linha de cada janela para uma determinada classificação (ou seja, depois de analisar a janela inteira) e, em seguida, classificando as linhas na janela na ordem inversa, coloque o valor calculado em toda a janela.
Então, precisamos de duas classificações. Para que a classificação final permaneça a mesma dos exemplos acima, primeiro classificamos pelos campos group_id ASC, order_id DESC e, em seguida, pelos campos group_id ASC, order_id ASC.
5) COUNT (*) ACIMA (PARTITION BY group_id)
No primeiro tipo, simplesmente numeramos as entradas. No segundo, atribuímos o número máximo a todas as linhas da janela, o que corresponderá ao número de linhas na janela.
SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxRowNum, @MaxRowNum := RowNumDesc) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNumDesc, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id DESC )T,(SELECT @last_group_id:=NULL, @MaxRowNum:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value Cnt
1 1 1 3
1 2 2 3
1 3 2 3
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 3
3 8 2 3
3 11 2 3
4 9 1 1
As funções MAX e MIN são calculadas por analogia. Vou dar apenas um exemplo para o MAX:
6) MAX (valor) ACIMA (PARTITION BY group_id)
SELECT group_id, order_id, value, MaxVal FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxVal, @MaxVal := MaxVal) MaxVal, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, GREATEST(@MaxVal, value), @MaxVal:=value) MaxVal, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id DESC )T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value MaxVal
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1
7) COUNT (valor DISTINCT) ACIMA (PARTITION BY group_id)
Uma coisa interessante que não está disponível no MS SQL Server, mas pode ser calculada com uma subconsulta usando o MAX do RANK. Faremos o mesmo aqui. No primeiro tipo, calculamos RANK () OVER (PARTITION BY group_id ORDER BY valor DESC) e, no segundo tipo, colocamos o valor máximo para todas as linhas em cada janela:
SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @Rank, @Rank := Rank) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, IF(@last_value = value, @Rank, @Rank:=@Rank+1) , @Rank:=1) Rank, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL, @Rank:=0)I ORDER BY group_id, value DESC, order_id DESC )T,(SELECT @last_group_id:=NULL, @Rank:=NULL)I ORDER BY group_id, value, order_id )T;
group_id order_id value Cnt
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1
Desempenho
Para começar, comparamos o desempenho da numeração de linhas em uma consulta usando a associação automática e variáveis.
1) A maneira clássica com auto-conexão
SELECT COUNT(*)N, T1.* FROM TestTable T1 JOIN TestTable T2 ON T1.order_id >= T2.order_id GROUP BY T1.order_id;
O que para 10000 registros na tabela TestTable produz:
Duração / busca
16,084 s / 0,016 s
2) Usando variáveis:
SELECT @N:=@N+1 N, T1.* FROM TestTable T1, (SELECT @N := 0)M ORDER BY T1.order_id;
Produz:
Duração / busca
0,016 seg / 0,015 seg
O resultado fala por si. No entanto, deve-se entender que os valores calculados usando variáveis não são utilizados de maneira ideal nas condições de filtragem. A classificação e o cálculo ocorrerão para TODAS as linhas, apesar de, no final, precisarmos apenas de uma pequena parte delas.
Vamos considerar com mais detalhes o exemplo de uma tarefa:
Imprima as 2 primeiras linhas da tabela TestTable para cada valor de group_id, classificado por order_id.Aqui está como essa tarefa seria resolvida em um DBMS com suporte para funções analíticas:
SELECT group_id, order_id, value FROM( SELECT *, ROW_NUMBER()OVER(PARTITION BY group_id ORDER BY order_id) RowNum FROM TestTable )T WHERE RowNum <= 2;
No entanto, o otimizador do MySQL não sabe nada sobre as regras pelas quais calculamos o campo RowNum. Ele terá que numerar TODAS as linhas e só então selecionar as necessárias.
Agora imagine que temos 1 milhão de registros e 20 valores únicos de group_id. I.e. para selecionar 40 linhas, o MySQL calculará o valor RowNum para um milhão de linhas! Não há uma solução bonita para esse problema com uma única consulta no MySQL. Mas você pode primeiro obter uma lista de valores únicos de group_id, assim:
SELECT DISTINCT group_id FROM TestTable;
Em seguida, usando qualquer outra linguagem de programação, gere uma consulta no formulário:
SELECT * FROM TestTable WHERE group_id=1 ORDER BY order_id LIMIT 2 UNION ALL SELECT * FROM TestTable WHERE group_id=2 ORDER BY order_id LIMIT 2 UNION ALL … SELECT * FROM TestTable WHERE group_id=20 ORDER BY order_id LIMIT 2;
20 consultas fáceis funcionarão muito mais rápido do que calcular o RowNum para um milhão de linhas.