Início / coding / Perguntas / 79224829
Accepted
alpacafondue
Asked: 2024-11-26 07:05:22 +0800 CST

CTE recursivo para remover duplicatas

  • 772

Estou procurando limpar dados de eventos que por acaso tenham linhas "duplicadas" para um determinado dia. Quero remover linhas de um dia que tenham mais de um status com base no contexto do statusvalor do dia seguinte. Atualmente, estou usando o BigQuery e várias etapas de CTE com self joins para iterar por dias com vários eventos para, eventualmente, "corrigir" todos os dias para ter um único statusvalor.

Tentei usar CTEs recursivos com self joins, várias funções de janela, etc. sem muita sorte. O BigQuery não permite funções analíticas em CTEs recursivos, incluindo GROUP BYs :(

Veja abaixo um exemplo de 2 iterações:

# data has multiple instances of days with more than one status (* = duplicate)
| date       | status   |
|------------|----------|
| 2024-11-01 | active   |*
| 2024-11-01 | inactive |*
| 2024-11-02 | inactive |
| 2024-11-03 | active   |*
| 2024-11-03 | inactive |*
| 2024-11-04 | active   |*
| 2024-11-04 | inactive |*
| 2024-11-05 | active   |

# first iteration with removed rows (**)
| date       | status   |
|------------|----------|
| 2024-11-01 | active   |** (2024-11-02 is inactive, so remove this row)
| 2024-11-01 | inactive |*
| 2024-11-02 | inactive |
| 2024-11-03 | active   |* (2024-11-04 has duplicates, so we can't derive yet)
| 2024-11-03 | inactive |* (2024-11-04 has duplicates, so we can't derive yet)
| 2024-11-04 | active   |*
| 2024-11-04 | inactive |** (2024-11-05 is active, so remove this row)
| 2024-11-05 | active   |

# second iteration with removed rows (***)
| date       | status   |
|------------|----------|
| 2024-11-01 | active   |**
| 2024-11-01 | inactive |*
| 2024-11-02 | inactive |
| 2024-11-03 | active   |*
| 2024-11-03 | inactive |*** (2024-11-04 has been deduped to active, so remove this row)
| 2024-11-04 | active   |*
| 2024-11-04 | inactive |**
| 2024-11-05 | active   |

# final desired set of deduplicated rows
| date       | status   |
|------------|----------|
| 2024-11-01 | inactive |
| 2024-11-02 | inactive |
| 2024-11-03 | active   |
| 2024-11-04 | active   |
| 2024-11-05 | active   |

Posso imaginar ter que iterar N vezes dado o tamanho dos dados. Existe uma abordagem recursiva para esse problema em SQL? Obrigado!

1 respostas

  1. Best Answer
    • CTE "a" define o status para NULLdatas com vários status.
    • CTE "b" é usado FIRST_VALUEpara encontrar o próximo status conhecido para datas com NULLstatus.
    WITH a AS (
  SELECT date, IF(COUNT(DISTINCT status) = 1, MIN(status), NULL) AS status
  FROM sample_data
  GROUP BY date
),
b AS (
  SELECT
    date,
    COALESCE(
      status,
      FIRST_VALUE(status IGNORE NULLS) OVER (
        ORDER BY date
        ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
      )
    ) AS final_status
  FROM a
)
SELECT date, final_status AS status
FROM b
ORDER BY date;

    Saída:

    data status
    2024-11-01 inativo
    2024-11-02 inativo
    2024-11-03 ativo
    2024-11-04 ativo
    2024-11-05 ativo
    • 1

relate perguntas