Às vezes, ao analisar dados, surge o
problema de
distinguir “cadeias” em uma amostra - ou seja,
sequências ordenadas de registros, para cada uma das quais
uma determinada condição é atendida .
Isso pode ser uma condição nos dados do próprio registro ou uma expressão complexa em relação a um ou mais registros anteriores - por exemplo, a duração do intervalo entre amostras de tempo próximo.
As soluções tradicionais oferecem opções diferentes de "auto-junção", quando a amostra se conecta a si mesma, ou o uso de certos fatos "fora dos dados" - por exemplo, que os registros devem ter uma etapa estritamente definida (N + 1, "para todos os dias", ... )
A primeira opção geralmente leva à
complexidade quadrática do algoritmo em termos de número de registros, o que é
inaceitável em grandes amostras , e a segunda
pode facilmente "desmoronar" se de repente não houver amostras nos dados de origem.
Mas esta tarefa nos ajudará a resolver efetivamente as
funções da janela no PostgreSQL.
Tarefa: contar o dinheiro de outras pessoas
Considere o caso mais simples de uma cadeia - quando a condição de continuidade é determinada pelos dados do próprio registro.
Todas as operações adicionais não precisam ser realizadas separadamente. Mas, para maior clareza do algoritmo, eu o dividirei em etapas sucessivas e,
no final, mostrarei o que e como otimizar .
Vamos imaginar que temos um pequeno banco que mantém saldos nas contas dos clientes na tabela. Assim que a transação de recebimento e despesa ocorrer, essa data também fixará o valor total da fatura no final do dia.
Após um longo feriado de Ano Novo, o banco decidiu recompensar seus clientes - e cada pessoa que abriu uma conta este ano acumula adicionalmente + 1% do saldo diário médio pelo período contínuo mais longo em que a conta não foi "redefinida" .
Aqui está o nosso critério para a continuidade da "cadeia". Bem, a ordem dos dados será determinada pelas datas dos saldos.
Eles nos trouxeram esse CSV e pediram para calcular rapidamente quem e quanto essa generosidade do banco deveria receber:
date;client;balance 01.01.2020;;150 01.01.2020;;100 02.01.2020;;100 02.01.2020;;150 03.01.2020;;200 05.01.2020;;0 06.01.2020;;50 08.01.2020;;0 08.01.2020;;200 09.01.2020;;0 09.01.2020;;0 10.01.2020;;5
Observe alguns fatos que são perceptíveis nesses dados:
- 01.01 foi feriado e o banco não funcionou. Portanto, nenhum dos clientes possui registros de alterações no saldo naquele dia, mas eles têm dinheiro em suas contas. Ou seja, algoritmos de "força bruta" que iteram por dia não funcionarão normalmente.
- 04.01 Alice não realizou nenhuma operação, portanto não há entrada. Porém, antes de 05.01, o valor em sua conta era diferente de zero - isso deve ser levado em consideração na análise.
- Realizamos a análise em 01.01-12.01, mas o saldo da conta de Alice no final deste período é diferente de zero. Também levamos em conta a necessidade de limitar o período.
CSV para tabela
A melhor maneira de importar do CSV é
usar o operador COPY . Mas tentaremos fazer isso através de expressões regulares para aquecer:
CREATE TEMPORARY TABLE tbl AS SELECT to_date(prt[1], 'DD.MM.YYYY') dt , prt[2] client , prt[3]::numeric(32,2) balance FROM ( SELECT regexp_split_to_array(str, ';') prt FROM ( SELECT regexp_split_to_table( $$ date;client;balance 01.01.2020;;150 01.01.2020;;100 02.01.2020;;100 02.01.2020;;150 03.01.2020;;200 05.01.2020;;0 06.01.2020;;50 08.01.2020;;0 08.01.2020;;200 09.01.2020;;0 09.01.2020;;0 10.01.2020;;5 $$ , E'\\n') str ) T WHERE str <> '' OFFSET 1 ) T;
Este é um método "desonesto", no sentido de que não será digerido corretamente, por exemplo, protegendo um separador no corpo de um campo. Mas para a maioria das aplicações simples - adequado.
Etapa 1: corrigir a condição do aplicativo
No nosso caso, a condição de continuidade da cadeia é um saldo diferente de zero. Exibimos como um campo separado, para maior clareza, ordenando cronologicamente o cliente:
SELECT * , balance > 0 cond FROM tbl ORDER BY client, dt;
dt | client | balance | cond ------------------------------------ 2020-01-01 | | 150.00 | t 2020-01-02 | | 100.00 | t 2020-01-03 | | 200.00 | t 2020-01-05 | | 0.00 | f 2020-01-06 | | 50.00 | t 2020-01-08 | | 0.00 | f 2020-01-09 | | 0.00 | f 2020-01-10 | | 5.00 | t 2020-01-01 | | 100.00 | t 2020-01-02 | | 150.00 | t 2020-01-08 | | 200.00 | t 2020-01-09 | | 0.00 | f
Etapa 2: Calcular a falta
Observe que o valor de Bob não mudou de 02,01 para 08,01. E de acordo com as condições do problema, devemos calcular a
média diária restante - ou seja, precisamos de informações sobre esses dias "perdidos". Ou pelo menos o número de dias em que o valor permaneceu o mesmo:
coalesce(lead(dt) OVER(PARTITION BY client ORDER BY dt), '2020-01-12') - dt days
dt | client | balance | cond | days ------------------------------------------- 2020-01-01 | | 150.00 | t | 1 2020-01-02 | | 100.00 | t | 1 2020-01-03 | | 200.00 | t | 2 2020-01-05 | | 0.00 | f | 1 2020-01-06 | | 50.00 | t | 2 2020-01-08 | | 0.00 | f | 1 2020-01-09 | | 0.00 | f | 1 2020-01-10 | | 5.00 | t | 2 2020-01-01 | | 100.00 | t | 1 2020-01-02 | | 150.00 | t | 6 2020-01-08 | | 200.00 | t | 1 2020-01-09 | | 0.00 | f | 3
Usando
a função da janela lead (), aprendemos a data do
próximo registro em ordem e, por meio da
coalescência, limitamos o intervalo do último. Ao mesmo tempo, eles usaram a propriedade útil de que a
diferença de duas datas no PostgreSQL retorna um número inteiro de dias entre elas.
Como um bônus quase gratuito, obtivemos todas as mesmas informações para registros com saldo zero. Mas, se houver muitas linhas com uma condição não satisfatória que não nos interessam, faz sentido
conduzir esses
cálculos no CASE para economizar recursos do servidor.
Etapa 3: encontre os pontos de interrupção
O início de cada cadeia em que estamos interessados é o ponto em que o valor da condição calculada anteriormente muda em relação ao registro
anterior . Usaremos a função
lag () para encontrar esses pontos:
lag(cond) OVER(PARTITION BY client ORDER BY dt) IS DISTINCT FROM cond chain_start
dt | client | balance | cond | days | chain_start --------------------------------------------------------- 2020-01-01 | | 150.00 | t | 1 | t 2020-01-02 | | 100.00 | t | 1 | f 2020-01-03 | | 200.00 | t | 2 | f 2020-01-05 | | 0.00 | f | 1 | t 2020-01-06 | | 50.00 | t | 2 | t 2020-01-08 | | 0.00 | f | 1 | t 2020-01-09 | | 0.00 | f | 1 | f 2020-01-10 | | 5.00 | t | 2 | t 2020-01-01 | | 100.00 | t | 1 | t 2020-01-02 | | 150.00 | t | 6 | f 2020-01-08 | | 200.00 | t | 1 | f 2020-01-09 | | 0.00 | f | 3 | t
Usando o operador
IS DISTINCT FROM em vez de <>, evitamos os problemas de comparação com NULL para os primeiros registros de cada cliente. Assim, todas as linhas em que o valor TRUE é o início de uma nova cadeia e FALSE é a continuação.
Etapa 4: amarre os links
Para agrupar dados em cada cadeia individual, é mais fácil atribuir
o mesmo identificador a todos os seus registros. O número de série da cadeia em si é perfeito para isso. E é exatamente igual ao
número de "começos" de cadeias que foram encontradas mais na amostra.
Eles podem ser calculados através do somatório “window” da soma dos valores booleanos ({boolean} :: integer), ou contando o número de registros correspondentes à condição count (*) FILTER (WHERE {boolean}). Usaremos a segunda opção:
count(*) FILTER(WHERE chain_start) OVER(PARTITION BY client ORDER BY dt) grpid
dt | client | balance | cond | days | chain_start | grpid ----------------------------------------------------------------- 2020-01-01 | | 150.00 | t | 1 | t | 1 2020-01-02 | | 100.00 | t | 1 | f | 1 2020-01-03 | | 200.00 | t | 2 | f | 1 2020-01-06 | | 50.00 | t | 2 | t | 2 2020-01-10 | | 5.00 | t | 2 | t | 3 2020-01-01 | | 100.00 | t | 1 | t | 1 2020-01-02 | | 150.00 | t | 6 | f | 1 2020-01-08 | | 200.00 | t | 1 | f | 1
Nesta etapa, já sabemos o comprimento de todos os links em cada cadeia, não precisamos mais de registros "desinteressantes", então apenas os filtramos.
Etapa 5: Colocando Correntes
Para calcular a média de todos os dias em uma cadeia, precisamos do número total de dias e do saldo "integral":
SELECT client , min(dt) chain_dt , sum(days * balance) balance , sum(days) days FROM ... GROUP BY 1, grpid ORDER BY 1, grpid;
client | chain_dt | balance | days ------------------------------------- | 2020-01-01 | 650.00 | 4 | 2020-01-06 | 100.00 | 2 | 2020-01-10 | 10.00 | 2 | 2020-01-01 | 1200.00 | 8
Etapa 6: procurando elevações aplicadas
Usando
DISTINCT ON, deixaremos um único registro (com um valor máximo de dias) para cada cliente:
SELECT DISTINCT ON(client) * FROM ... ORDER BY client, days DESC;
client | chain_dt | balance | days ------------------------------------- | 2020-01-01 | 650.00 | 4 | 2020-01-01 | 1200.00 | 8
Na verdade, é tudo, tudo o que resta é ...
Combinamos e otimizamos
Pedido de resumo WITH step123 AS ( SELECT * , CASE WHEN cond THEN lag(cond) OVER(w) IS DISTINCT FROM cond END chain_start , CASE WHEN cond THEN coalesce(lead(dt) OVER(w), '2020-01-12') - dt END days FROM tbl , LATERAL(SELECT balance > 0 cond) T WINDOW w AS (PARTITION BY client ORDER BY dt) ) , step4 AS ( SELECT * , count(*) FILTER(WHERE chain_start) OVER(PARTITION BY client ORDER BY dt) grpid FROM step123 WHERE cond ) SELECT DISTINCT ON(client) client , sum(days) OVER(w) days , min(dt) OVER(w) chain_dt , sum(days * balance) OVER(w) balance FROM step4 WINDOW w AS (PARTITION BY client, grpid) ORDER BY 1, 2 DESC;
Aqui, combinamos e otimizamos as três primeiras etapas:
- A subconsulta LATERAL nos permitiu calcular um campo adicional sem passar desnecessariamente pela seleção e usá-lo imediatamente na função
- a remoção de uma definição geral no WINDOW ajuda o PostgreSQL a não fazer a classificação dupla para formar uma “janela” e calcular as duas funções em um nó WindowAgg
- O cálculo da função “preguiçoso” no CASE reduz o número de operações executadas
Da mesma forma, combinamos as duas etapas a seguir. Mas a ordem da “janela” de cálculo de agregados (cliente, grpid) e exclusividade (cliente, soma (dias)) não coincidiu, portanto, ainda existem dois nós de classificação no último bloco - antes do WindowAgg e antes do Unique.
[veja em explicar.tensor.ru]Observo que, ao numerar cadeias
, a condição WHERE é atendida pela primeira vez ; portanto, os números gerados pela
função window acabam sendo sequenciais.