Результаты Duckdb sql и postgres различаются при использовании unnest

Что ж, при использовании SQL вам нужно помнить, что в целом набор записей SQL представляет собой неупорядоченный набор строк. По сути, это означает, что не существует гарантированного порядка строк при их выборе без предложения order by. Это одно из ключевых отличий от современных коллекций строк DataFrame и запросов «связанных выражений».

В вашем случае это означает, что запуск row_number() над невложенным массивом может быть совершенно случайным - после того, как массив не вложен, у вас больше нет порядка элементов массива. В принципе, это здорово, что версия Postgres работает стабильно, но неудивительно, что DuckDB не работает.

Чтобы ваш код был более готов к работе, я бы предложил использовать предложение с порядковым номером в Postgres, которое будет нумеровать строки набора результатов функции:

select
    txn_id,
    max(case when nr%4=1 then elem end) as cncy_cd,
    max(case when nr%4=2 then elem end) as txn_cncy_amt,
    max(case when nr%4=3 then elem end) as txn_exch_rate,
    max(case when nr%4=0 then elem end) as txn_nzd_amt
from test as t
    left join lateral unnest(string_to_array(t.description, '@')) with ordinality as a(elem, nr) on true  
group by
    txn_id,
    (nr-1)/4 ;

db<>рабочий пример

А в DuckDB вы можете использовать функцию generate_subscripts, которая будет генерировать индексы по N-му (в вашем случае - первому и единственному) измерению массива:

duckdb.sql("""
    drop table if exists test;

    create table test (txn_id int, description varchar(200));
    
    insert into test
    values
        (3332654,'[email protected]@0.9397@$10.64@[email protected]@23.8235@$6.30@KRW@36,[email protected]@$41.84@[email protected]@1.5711@$12.73@[email protected]@0.6013@$8.32@[email protected]@10.6013@$18.32'),
        (3332655,'[email protected]@0.8197@$11.64@[email protected]@21.8135@$61.30@KRW@36,[email protected]@$411.84@[email protected]@11.5711@$11.73');
        
    select
        txn_id,
        max(case when nr%4=1 then elem end) cncy_cd,
        max(case when nr%4=2 then elem end) txn_cncy_amt,
        max(case when nr%4=3 then elem end) txn_exch_rate,
        max(case when nr%4=0 then elem end) txn_nzd_amt
    from test t
        left join lateral (select string_to_array(t.description, '@')) as a(data) on true
        left join lateral (select unnest(a.data), generate_subscripts(a.data, 1)) as b(elem, nr) on true
    group by
        txn_id,
        (nr-1)//4;
""")

┌─────────┬─────────┬──────────────┬───────────────┬─────────────┐
│ txn_id  │ cncy_cd │ txn_cncy_amt │ txn_exch_rate │ txn_nzd_amt │
│  int32  │ varchar │   varchar    │    varchar    │   varchar   │
├─────────┼─────────┼──────────────┼───────────────┼─────────────┤
│ 3332655 │ CAD     │ 11.00        │ 0.8197        │ $11.64      │
│ 3332654 │ THB     │ 150.00       │ 23.8235       │ $6.30       │
│ 3332655 │ FJD     │ 21.00        │ 11.5711       │ $11.73      │
│ 3332654 │ FJD     │ 20.00        │ 1.5711        │ $12.73      │
│ 3332655 │ KRW     │ 36,001.00    │ 861.3722      │ $411.84     │
│ 3332654 │ EUR     │ 5.00         │ 0.6013        │ $8.32       │
│ 3332654 │ SGP     │ 15.00        │ 10.6013       │ $18.32      │
│ 3332655 │ THB     │ 110.00       │ 21.8135       │ $61.30      │
│ 3332654 │ CAD     │ 10.00        │ 0.9397        │ $10.64      │
│ 3332654 │ KRW     │ 36,000.00    │ 860.3722      │ $41.84      │
├─────────┴─────────┴──────────────┴───────────────┴─────────────┤
│ 10 rows                                              5 columns │
└────────────────────────────────────────────────────────────────┘

Возможно, стоит взглянуть на list_slice(), функцию DuckDB (см. здесь).

В документации DuckDB для unnest() говорится: «Когда несколько списков не вложены в одно и то же предложение SELECT, списки не вложены рядом». Я предполагаю, что это означает, что заказы остаются прежними, и это так, но неясно, гарантировано ли это.

Я использовал dplyr (пакет R) для генерации следующего SQL:

SELECT
  txn_id,
  unnest(list_slice(description, 1, length, 4)) AS cncy_cd,
  unnest(list_slice(description, 2, length, 4)) AS txn_cncy_amt,
  unnest(list_slice(description, 3, length, 4)) AS txn_exch_rate,
  unnest(list_slice(description, 4, length, 4)) AS txn_nzd_amt
FROM (
  SELECT q01.*, len(description) AS length
  FROM (
    SELECT txn_id, string_to_array(description, '@') AS description
    FROM df
  ) q01
) q01

Р-код:

library(tidyverse)
library(DBI)

db <- dbConnect(duckdb::duckdb())

df <- tribble(
  ~txn_id, ~description,
  3332654, "[email protected]@0.9397@$10.64@[email protected]@23.8235@$6.30@KRW@36,[email protected]@$41.84@[email protected]@1.5711@$12.73@[email protected]@0.6013@$8.32@[email protected]@10.6013@$18.32",
  3332655, "[email protected]@0.8197@$11.64@[email protected]@21.8135@$61.30@KRW@36,[email protected]@$411.84@[email protected]@11.5711@$11.73"
) |> copy_to(db, df = _, name = "df", overwrite = TRUE)

res <-
  df |>
  mutate(description = string_to_array(description, '@'),
         length = len(description)) |>
  mutate(cncy_cd = unnest(list_slice(description, 1L, length, 4L)),
         txn_cncy_amt = unnest(list_slice(description, 2L, length, 4L)),
         txn_exch_rate = unnest(list_slice(description, 3L, length, 4L)),
         txn_nzd_amt = unnest(list_slice(description, 4L, length, 4L))) |>
  select(-description, -length) |>
  collapse()

res |> show_query()
#> <SQL>
#> SELECT
#>   txn_id,
#>   unnest(list_slice(description, 1, length, 4)) AS cncy_cd,
#>   unnest(list_slice(description, 2, length, 4)) AS txn_cncy_amt,
#>   unnest(list_slice(description, 3, length, 4)) AS txn_exch_rate,
#>   unnest(list_slice(description, 4, length, 4)) AS txn_nzd_amt
#> FROM (
#>   SELECT q01.*, len(description) AS length
#>   FROM (
#>     SELECT txn_id, string_to_array(description, '@') AS description
#>     FROM df
#>   ) q01
#> ) q01

res |> collect() |> knitr::kable("pipe")

^{Created on 2024-06-29 with reprex v2.1.0}