Considere a seguinte tabela:
ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL
---+----------+-----------+-----------+-----
1 | 1 | 1 | (null) | 3
2 | 1 | 2 | (null) | 2
3 | 1 | 3 | (null) | 1
4 | 1 | 4 | 4 | 2
5 | 1 | 5 | (null) | 1
6 | 2 | 1 | (null) | 4
7 | 2 | 2 | 2 | 3
8 | 2 | 3 | (null) | 4
9 | 2 | 4 | (null) | 2
10 | 2 | 5 | (null) | 2
11 | 2 | 6 | (null) | 4
12 | 2 | 7 | 14 | 2
13 | 2 | 8 | (null) | 2
Para cada linha, preciso calcular a soma cumulativa de VAL
todas as linhas anteriores (ordenadas por ORDER_VAL
e agrupadas por GROUP_ID
), mas cada vez que um não- NULL
RESET_VAL
é encontrado, preciso usar esse valor para a soma. As linhas a seguir também precisam ser construídas em RESET_VAL
vez de usar a soma real. Observe que cada grupo pode ter vários valores de redefinição.
Este é o resultado que espero para a tabela acima:
ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL | CUMSUM
---+----------+-----------+-----------+-----+-------
1 | 1 | 1 | (null) | 3 | 0
2 | 1 | 2 | (null) | 2 | 3
3 | 1 | 3 | (null) | 1 | 5
4 | 1 | 4 | 4 | 2 | 4
5 | 1 | 5 | (null) | 1 | 6
6 | 2 | 1 | (null) | 4 | 0
7 | 2 | 2 | 2 | 3 | 2
8 | 2 | 3 | (null) | 4 | 5
9 | 2 | 4 | (null) | 2 | 9
10 | 2 | 5 | (null) | 2 | 11
11 | 2 | 6 | (null) | 4 | 13
12 | 2 | 7 | 14 | 2 | 14
13 | 2 | 8 | (null) | 2 | 16
Se não fosse pelos valores redefinidos, eu poderia usar uma consulta de janela:
SELECT temp.*,
COALESCE(SUM(val) OVER (PARTITION BY group_id ORDER BY order_val ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
0) AS cumsum
FROM temp;
Originalmente, pensei erroneamente que poderia apenas colocar RESET_VAL
no início do COALESCE
, mas isso não funcionará, pois não redefinirá o valor das linhas seguintes.
Eu também tentei esta solução , mas ela redefine apenas para zero, não para um valor na coluna. Adaptá-lo para fazer isso não é trivial porque esse valor deve se propagar para todas as linhas seguintes.
Uma consulta recursiva parece um ajuste natural, mas ainda não consegui descobrir como fazer isso.
Provavelmente devo mencionar que a tabela com a qual realmente tenho que lidar é muito maior (centenas de milhares a alguns milhões de linhas) do que o exemplo acima, portanto, mencione se houver alguma armadilha de desempenho com qualquer resposta.
O seguinte funciona, mas provavelmente há uma versão mais inteligente. Explicação da lógica da consulta:
Primeiro descobrimos quantas "reinicializações" foram feitas até e incluindo a linha atual contando os não nulos da
reset_val
coluna, para que possamos separar as linhas em subgrupos.Também usamos outra função de janela
LAST_VALUE()
comIGNORE NULLS
, para que possamos encontrar a últimareset_value
.Observe que ambas as funções de janela
COUNT()
eLAST_VALUE()
têm umORDER BY
, portanto, a janela padrãoROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
. Omitido na consulta, para deixar o código mais claro.Assumindo que
val
não é anulável, a outra função de janela também pode ser encurtada, de:(evitando o
COALESCE()
também) para:Finalmente, no segundo cte, usamos os subgrupos encontrados acima (usando
PARTITION BY group_id, reset_count
) para encontrar as somas cumulativas.Teste no SQLfiddle .
Outra variação, baseada na resposta recursiva de @Chris . (ligeiramente melhorado, trabalha com não consecutivos
order_val
, evita o finalGROUP BY
).Também funciona caso a primeira linha de um grupo tenha
reset_val
:Teste no SQLfiddle-2 .
Mais uma variação, usando a
CONNECT BY
sintaxe mais antiga (proprietária) para consultas recursivas. Mais compacto, mas acho mais difícil de escrever e ler do que a versão CTE:Testado em SQLfiddle-3 .
Como exercício acadêmico, implementei uma solução em Postgres. Agora, eu sei que era uma pergunta sobre Oracle, mas também foi enquadrada como uma pergunta de SQL! :) Se alguém estiver mais familiarizado com a sintaxe de consulta recursiva do Oracle, talvez encontre as alterações necessárias para fazer isso rodar no Oracle.
Consulta recursiva (no Postgres)
Nesta solução, faço uso de consultas recursivas, aplicando a sintaxe do Postgres
WITH RECURSIVE
no meu CTE. A consulta recursiva é a seguinte:Aqui está o SQL Fiddle correspondente .
Obviamente, a chave para o comportamento adequado dessa consulta é primeiro usar a parte recursiva da consulta para criar sua soma cumulativa, certificando-se de usar
COALESCE
para substituir em qualquer umRESET_VAL
que determine o valorCUMSUM
da linha. O últimoSELECT
simplesmente faz com que você filtre pelo valor máximo doCUMSUM
porque se a consulta foi construída corretamente, o máximo é idêntico à soma cumulativa.Editar: versão Oracle da consulta recursiva
Esta tradução para Oracle foi fornecida pelo ypercube :
Oracle SQLFiddle