如何在DuckDB中对列表列进行归一化处理？

嘿，我来帮你搞定这个DuckDB列表归一化的问题！你在Polars里的实现很顺畅，但DuckDB对列表的运算规则有点不同，咱们一步步来调整。

首先先还原你的场景，你一开始用Polars创建了带列表列的DataFrame：

import polars as pl
import duckdb

df = pl.DataFrame({'a':[1,1,2], 'b': [4,5,6]}).with_columns(c=pl.concat_list('a', 'b'))
print(df)

输出是：

shape: (3, 3)
┌─────┬─────┬───────────┐
│ a   ┆ b   ┆ c         │
│ --- ┆ --- ┆ ---       │
│ i64 ┆ i64 ┆ list[i64] │
╞═════╪═════╪═══════════╡
│ 1   ┆ 4   ┆ [1, 4]    │
│ 1   ┆ 5   ┆ [1, 5]    │
│ 2   ┆ 6   ┆ [2, 6]    │
└─────┴─────┴───────────┘

在Polars里你用pl.col('c') / pl.col('c').list.sum()就能轻松归一化，但DuckDB直接这么写会报错，就像你遇到的：

duckdb.sql("""
from df
select c / list_sum(c)
""")

报错信息：

BinderException: Binder Error: No function matches the given name and argument types '/(BIGINT[], HUGEINT)'. You might need to add explicit type casts.
        Candidate functions:
        /(FLOAT, FLOAT) -> FLOAT
        /(DOUBLE, DOUBLE) -> DOUBLE
        /(INTERVAL, BIGINT) -> INTERVAL

问题原因 #

这个报错的核心是：DuckDB里list_sum(c)返回的是单个标量值（每个列表的总和），但DuckDB不会自动把标量和列表的每个元素做除法运算，而且类型也不匹配——列表是整数数组，总和是HUGEINT类型，没有直接的除法函数支持这种组合。

解决方法 #

我们需要用DuckDB的list_transform函数，遍历列表里的每个元素，分别除以该列表的总和，同时要把总和转换成浮点数，避免整数除法导致的精度丢失：

result = duckdb.sql("""
from df
select 
    a, b, c,
    list_transform(c, x -> x::DOUBLE / list_sum(c)::DOUBLE) as c_normalised
""").pl()
print(result)

运行这个代码后，输出就和Polars里的结果一致了：

shape: (3, 4)
┌─────┬─────┬───────────┬──────────────────────┐
│ a   ┆ b   ┆ c         ┆ c_normalised         │
│ --- ┆ --- ┆ ---       ┆ ---                  │
│ i64 ┆ i64 ┆ list[i64] ┆ list[f64]            │
╞═════╪═════╪═══════════╪══════════════════════╡
│ 1   ┆ 4   ┆ [1, 4]    ┆ [0.2, 0.8]           │
│ 1   ┆ 5   ┆ [1, 5]    ┆ [0.166667, 0.833333] │
│ 2   ┆ 6   ┆ [2, 6]    ┆ [0.25, 0.75]         │
└─────┴─────┴───────────┴──────────────────────┘

补充说明 #

list_transform(c, x -> x::DOUBLE / list_sum(c)::DOUBLE) 里的x代表列表c中的每个元素，我们把每个元素和总和都转成DOUBLE类型，确保除法是浮点数运算，避免出现整数除法截断结果的问题。

备注：内容来源于stack exchange，提问作者ignoring_gravity