Início / coding / Perguntas / 79031606
Accepted
xiaodai
Asked: 2024-09-27 21:39:23 +0800 CST

No DuckDB, é possível alterar um valor com base no valor anterior ordenado por outra coluna sem usar UDFs?

  • 772

Tenho uma coluna Aque desejo incrementar ou decrementar de acordo com colunas Be C.

A lógica é esta:

  • se lag(A) = 0e se B= 1 entãoA=1
  • se lag(A) = 1e se C= 1 entãoA=0
  • o lag(A)é de encomendar porD

Não consigo descobrir como implementar essa lógica no DuckDB. Acho que o problema é que Adepende do valor anterior.

select
  unnest(array_agg(row(B, C) order by D)) as list1
from
  my_table

Imaginei que deveria ser algo para a versão não aninhada de list1e pensei list_reduceque poderia ajudar, mas não consigo descobrir como. Outro pensamento é que CTEs recursivos podem ajudar. Mas não consegui encontrar uma solução.

Talvez não seja possível no DuckDB e eu precise usar uma lista UDF.

Se for possível em qualquer outra variante do SQL, eu adoraria ouvir a resposta também.

Dados de exemplo

   B C A  D
1  0 0 0  1
2  1 0 1  2
3  0 0 1  3
4  1 0 1  4
5  0 1 0  5
6  1 0 1  6
7  1 0 1  7
8  0 1 0  8
9  0 0 0  9
10 0 1 0 10

Código R para gerar dados

fn = function(b, c) {
  l = length(b)
  A = vector(mode="integer", l)
  A[1] = 0
  for (i in 2:l) {
    A[i]= A[i-1]
    if((b[i] == 1) & (A[i-1] == 0)) {
      A[i] = 1
    } else if ((c[i] == 1) & (A[i-1] == 1)) {
      A[i] = 0
    }
  }
  A
}


d = data.frame(
  B = c(0, 1, 0, 1, 0, 1, 1, 0, 0, 0),
  C = c(0, 0, 0, 0, 1, 0, 0, 1, 0, 1)
) %>% dplyr::mutate(
  A = fn(B, C)
) %>% 
  mutate(D = row_number())

d
sql

1 respostas

  1. Best Answer

    Em geral, o SQL não é a melhor ferramenta para executar esses cálculos iterativos, mas você pode fazer isso com CTE recursivo :

    with recursive cte as (
    select b, c, d, 0 as a
    from df
    where d = 1

    union all

    select
        d.b, d.c, d.d,
        case
            when c.a = 0 and d.b = 1 then 1
            when c.a = 1 and d.c = 1 then 0
            else c.a
        end as a
    from df as d
        inner join cte as c on
            c.d = d.d - 1
)
select *
from cte

    ┌───────┬───────┬───────┬───────┐
│   b   │   c   │   d   │   a   │
│ int64 │ int64 │ int64 │ int32 │
├───────┼───────┼───────┼───────┤
│     0 │     0 │     1 │     0 │
│     1 │     0 │     2 │     1 │
│     0 │     0 │     3 │     1 │
│     1 │     0 │     4 │     1 │
│     0 │     1 │     5 │     0 │
│     1 │     0 │     6 │     1 │
│     1 │     0 │     7 │     1 │
│     0 │     1 │     8 │     0 │
│     0 │     0 │     9 │     0 │
│     0 │     1 │    10 │     0 │
├───────┴───────┴───────┴───────┤
│ 10 rows             4 columns │
└───────────────────────────────┘
    • 2

relate perguntas