DuckDB不支持Postgres的WITH ORDINALITY，求替代实现方案

DuckDB暂未支持WITH ORDINALITY语法，但可以通过**窗口函数row_number()**模拟生成拆分后元素的序号，完全复现你原查询的逻辑。

修改后的完整查询 #

WITH suffixes AS (
    SELECT 
        ordrnbr,
        (nr - 1) / 2 AS N,
        MAX(CASE WHEN nr % 2 = 1 THEN elem END) AS Nkey,
        CAST(MAX(CASE WHEN nr % 2 = 0 THEN elem END) AS INT) AS Nval
    FROM (
        SELECT 
            t.ordrnbr,
            unnest(string_to_array(t.description, '@')) AS elem,
            ROW_NUMBER() OVER(PARTITION BY t.ordrnbr ORDER BY 1) AS nr
        FROM test t
    ) AS split_data
    GROUP BY ordrnbr, (nr - 1) / 2
)

SELECT 
    CONCAT(t.ordrnbr, '-', ROW_NUMBER() OVER(PARTITION BY t.ordrnbr ORDER BY s.N)) AS ordrnbr,
    vehiclename,
    description,
    id,
    totprice,
    COALESCE(s.Nval, totprice) AS ind_price
FROM test t
LEFT JOIN suffixes s ON s.ordrnbr = t.ordrnbr
WHERE s.Nval >= 10000 OR (s.Nval IS NULL AND totPrice >= 10000)

关键修改说明 #

• 把原查询中LEFT JOIN LATERAL unnest(...) WITH ORDINALITY的逻辑，替换为子查询内部先拆分字符串，再用ROW_NUMBER() OVER(PARTITION BY t.ordrnbr ORDER BY 1)生成每个元素的序号nr，确保每个ordrnbr下的拆分元素序号从1开始递增，和WITH ORDINALITY的效果完全一致。
• 其余逻辑（分组聚合、关联过滤、生成新序号）都和原查询保持一致，保证输出结果与原PostgreSQL查询完全相同。

内容的提问来源于stack exchange，提问作者Balaji Venkatachalam