如何在list_transform中使用SQL计算生成的列表？

我想在list_transform中使用SQL查询得到的列表数据，替代原代码里的generate_series(1, 6)。

原可行代码 #

SELECT filename, 
min(tpep_pickup_datetime), 
max(tpep_pickup_datetime), 
avg(total_amount)
FROM read_parquet(
    list_transform(
        generate_series(1, 6),
        n -> format('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-{:02d}.parquet', n) 
    ), filename=true
)
GROUP BY filename
ORDER BY filename;

尝试替换后的报错代码 #

with a as (select [1,6] as a_list) -- << 正常情况下，[1,6]应来自数据查询结果
SELECT filename, 
min(tpep_pickup_datetime), 
max(tpep_pickup_datetime), 
avg(total_amount)
FROM read_parquet(
    list_transform(
        a.a_list, -- << 此处修改后无法运行
        n -> format('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-{}.parquet', n) 
    ), filename=true
)
GROUP BY filename
ORDER BY filename;

解决方案 #

问题核心是直接引用CTE中的列表对象无法被list_transform迭代处理，需要先将列表拆分为单个元素的序列，再传入list_transform。以下是两种可行修改方案：

方案1：展开列表后重新聚合为序列 #

适用于从查询获取列表的场景（比如从表中读取存储的数组）：

WITH a AS (
    -- 模拟从查询获取的列表，实际场景替换为你的查询逻辑
    SELECT unnest([1,2,3,4,5,6]) AS num
)
SELECT filename, 
       min(tpep_pickup_datetime), 
       max(tpep_pickup_datetime), 
       avg(total_amount)
FROM read_parquet(
    list_transform(
        (SELECT array_agg(num) FROM a),
        n -> format('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-{:02d}.parquet', n)
    ), filename=true
)
GROUP BY filename
ORDER BY filename;

方案2：直接用unnest展开列表传入 #

如果列表已经在CTE中定义，直接用unnest拆分后传入list_transform：

WITH a AS (
    SELECT [1,2,3,4,5,6] AS a_list
)
SELECT filename, 
       min(tpep_pickup_datetime), 
       max(tpep_pickup_datetime), 
       avg(total_amount)
FROM read_parquet(
    list_transform(
        unnest(a.a_list),
        n -> format('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-{:02d}.parquet', n)
    ), filename=true
)
GROUP BY filename
ORDER BY filename;

关键注意点 #

• unnest函数负责将列表拆分为多行独立值，让list_transform可以逐个迭代处理
• 保留format中的{:02d}格式符，确保月份格式为两位数字（如01、02），与原代码的文件名规则一致
• 如果你的列表是从其他查询动态获取的，先通过unnest展开，再用array_agg重新聚合为序列即可

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