Início / dba / Perguntas / 331277
Accepted
Edgxxar
Asked: 2023-09-16 21:54:53 +0800 CST

SELECT / DELETE linhas onde a contagem consecutiva de valores nulos é maior que N

  • 772

Eu tenho uma tabela com a seguinte estrutura, onde todas as colunas, exceto a última, são não nulas:

EU IA receptor hora_envio recv_time
1 A 00:00:00 00:00:01
2 A 00:00:01 NULO
3 A 00:00:02 NULO
4 A 00:00:03 NULO
5 A 00:00:04 00:00:05
6 B 00:00:00 00:00:01
7 B 00:00:01 NULO
8 B 00:00:02 00:00:03
9 A 00:00:05 NULO
10 B 00:00:03 NULO
11 A 00:00:06 00:00:07

Quero selecionar (e, finalmente, excluir, mas acho que selecionar seria o primeiro passo) todas as linhas, onde a recv_timecoluna é NULLpara n ou mais linhas consecutivas, particionadas pela receivercoluna e ordenadas pela send_timecoluna.

Portanto, no exemplo acima, se n=2 , desejo selecionar/excluir as linhas com ID 2, 3 e 4, mas não as linhas 7, 9 ou 10.

Acho que é necessária uma contagem com partição, mas não consigo descobrir como "redefinir" a contagem de valores nulos assim que uma linha não nula for encontrada. Isso é o que eu tenho até agora:

SELECT 
    id, receiver, send_time, recv_time, 
    COUNT(CASE WHEN recv_time IS NULL THEN 1 END) OVER (PARTITION BY receiver ORDER BY send_time)
FROM test 
ORDER BY id

o que me dá:

EU IA receptor hora_envio recv_time contar
1 A 00:00:00 00:00:01 0
2 A 00:00:01 NULO 1
3 A 00:00:02 NULO 2
4 A 00:00:03 NULO 3
5 A 00:00:04 00:00:05 3
6 B 00:00:00 00:00:01 0
7 B 00:00:01 NULO 1
8 B 00:00:02 00:00:03 1
9 A 00:00:05 NULO 4
10 B 00:00:03 NULO 2
11 A 00:00:06 00:00:07 4

Mas quero que a contagem seja reiniciada nas linhas 5, 8, 11. Também não tenho certeza de como proceder depois disso, porque quero capturar todas as linhas do "grupo" de linhas NULL consecutivas, mas obviamente a contagem para a linha 2 neste exemplo é menor que n .

postgresql

1 respostas

  1. Best Answer

    Para apenas selecionar:

    SELECT *
FROM  (
   SELECT *, count(*) OVER (PARTITION BY receiver, grp) AS grp_count
   FROM  (
      SELECT *, count(recv_time) OVER (PARTITION BY receiver ORDER BY send_time) AS grp
      FROM   test
      ) sub
   ) d
WHERE  d.grp_count > 3
AND    d.recv_time IS NULL;

    Basicamente, exatamente o oposto do que você tentou: queremos que a contagem pare nas linhas com nullvalores. Ou seja, você estava perto, com um passo na lógica invertida.

    O “truque” é fazer uma corrida count(recv_time). Valores nulos não contam, portanto a contagem é interrompida para essas linhas. Os candidatos à remoção acabam em grupos ( grp) com mais de 3 membros.

    Deletar:

    DELETE FROM test t
USING (  -- same as above
   SELECT *, count(*) OVER (PARTITION BY receiver, grp) AS grp_count
   FROM  (
      SELECT *, count(recv_time) OVER (PARTITION BY receiver ORDER BY send_time) AS grp
      FROM   test
      ) sub
   ) d
WHERE  d.grp_count > 3
AND    d.recv_time IS NULL
AND    t.id = d.id;

    violino

    Relacionado:

    • 1

relate perguntas