Presto中是否有等价于pyspark.sql.functions.posexplode()的函数？

Presto中可以通过UNNEST ... WITH ORDINALITY语法实现和Spark posexplode()等价的功能——展开数组的同时获取元素在原数组中的位置索引。

核心用法说明 #

• UNNEST(数组列) 负责展开数组中的每个元素
• WITH ORDINALITY 会为每个展开的元素生成一个从1开始的序号（对应元素在原数组中的位置）
• 若需要和Spark一致的0-based索引，只需将序号减1即可

基础示例 #

假设我们有包含用户和其喜爱水果的数据集：

WITH sample_data AS (
    SELECT 
        'user1' AS user_id,
        ARRAY('apple', 'banana', 'cherry') AS fruits
    UNION ALL
    SELECT 
        'user2' AS user_id,
        ARRAY('orange', 'grape') AS fruits
)
SELECT 
    user_id,
    fruit,
    ordinal - 1 AS element_index, -- 转换为0-based索引
    ordinal AS original_1based_ordinal
FROM sample_data
CROSS JOIN UNNEST(fruits) WITH ORDINALITY AS t(fruit, ordinal);

执行后输出结果：

复杂类型数组示例 #

如果数组元素是结构体，同样可以用该方法展开并保留索引：

WITH order_data AS (
    SELECT 
        'order_001' AS order_id,
        ARRAY(
            CAST(ROW('shirt', 29.99) AS ROW(product_name VARCHAR, price DOUBLE)),
            CAST(ROW('pants', 49.99) AS ROW(product_name VARCHAR, price DOUBLE)),
            CAST(ROW('socks', 9.99) AS ROW(product_name VARCHAR, price DOUBLE))
        ) AS products
)
SELECT 
    order_id,
    product.product_name,
    product.price,
    ordinal - 1 AS element_index
FROM order_data
CROSS JOIN UNNEST(products) WITH ORDINALITY AS t(product, ordinal);

内容的提问来源于stack exchange，提问作者Olivier Maxwell