DuckDB 一招鲜：WITH ORDINALITY —— UNNEST 数组时自动带索引，告别子查询

CREATE TABLE orders AS
SELECT * FROM (VALUES
    (1, ARRAY['iPhone', 'case', 'charger']),
    (2, ARRAY['laptop', 'mouse', 'keyboard', 'pad']),
    (3, ARRAY['tablet'])
) AS t(order_id, items);

SELECT 
    o.order_id,
    unnest_item AS item,
    rn AS position
FROM orders o,
LATERAL (
    SELECT 
        unnest(o.items) AS unnest_item,
        ROW_NUMBER() OVER () AS rn
) sub;

SELECT 
    order_id,
    item,
    position
FROM orders,
UNNEST(items) AS t(item, position) WITH ORDINALITY;

-- 电商：提取每个产品的标签优先级
SELECT 
    product_id,
    tag,
    tag_rank
FROM products,
UNNEST(tags) WITH ORDINALITY AS t(tag, tag_rank);

-- 老系统导出的逗号分隔数据
CREATE TABLE legacy_data AS
SELECT * FROM (VALUES
    (1, 'apple,banana,cherry'),
    (2, 'dog,cat,fish'),
    (3, 'red,green,blue,yellow')
) AS t(id, values);

SELECT 
    id,
    element,
    position
FROM legacy_data,
UNNEST(string_split(values, ',')) WITH ORDINALITY AS t(element, position);

-- 当数组中的位置承载业务含义时
-- （如：关键词优先级：主关键词、次关键词等）
SELECT 
    order_id,
    item,
    CASE position
        WHEN 1 THEN 'primary'
        WHEN 2 THEN 'secondary'
        ELSE 'tertiary'
    END AS item_type,
    position
FROM orders,
UNNEST(items) WITH ORDINALITY AS t(item, position);

-- WITH ORDINALITY：保留原列 + 追加位置
UNNEST(items) AS t(item, position) WITH ORDINALITY

-- FOR ALL：并行展开多个数组，无位置信息
UNNEST(items, other_items) FOR ALL AS t(item, other)

-- ✅ 正确：position 是最后一个别名
UNNEST(items) AS t(item, position) WITH ORDINALITY

-- ❌ 错误："position" 变成 item 名，序数列获得通用名
UNNEST(items) AS t(position, item) WITH ORDINALITY

-- 位置反映原始数组顺序，不是排序后的顺序
SELECT order_id, item, position
FROM orders,
UNNEST(items) WITH ORDINALITY AS t(item, position)
ORDER BY position;  -- position 仍按原始数组为 1,2,3

SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY item) AS sorted_pos
FROM (
    SELECT order_id, item, position
    FROM orders,
    UNNEST(items) WITH ORDINALITY AS t(item, position)
) base;