DuckDB GROUP BY查询中复用聚合结果实现嵌套聚合的问题求助

你的问题核心是SQL的执行逻辑限制：在同一个SELECT子句中，聚合函数和列别名是同一阶段计算的，你无法直接引用刚定义的mean别名作为另一个聚合函数的参数——数据库还没完成这个别名的计算，自然无法复用它。

另外你原SQL里还有两处语法错误：first(price) AS start和sum(size) AS vol后面都缺少逗号，这也会导致查询执行失败。

下面给出两种可行的解决方案，适配不同的需求场景：

方案1：用数学公式转换，避免复用别名 #

你可以通过数学恒等式把sum(price - mean)转换为不需要直接引用mean别名的形式，或者直接重复mean的计算逻辑：

比如sum(price - mean)等价于sum(price) - mean * count(*)，而mean本身是sum(price*size)/sum(size)，代入后可以写成：

df = duckdb.sql(f"""
                    SELECT timestamp,
                      first(price) AS start,
                      sum(size) AS vol,
                      sum(price * size) / sum(size) AS mean,
                      sum(price) - (sum(price * size) / sum(size)) * count(*) AS subtract_mean
                    FROM '{file_raw_parquet}'
                    GROUP BY timestamp
                """)

如果你的实际需求是计算加权的(price-mean)总和（和成交量加权的mean对应），那应该写成sum( (price - mean) * size )，这个可以进一步简化为sum(price*size) - mean * sum(size)——不过因为mean = sum(price*size)/sum(size)，这个式子的结果会是0，所以要确认你的真实需求是否如此。

方案2：使用CTE/子查询提前计算聚合结果，再关联计算 #

如果觉得重复写聚合表达式太冗余，或者你的需求更复杂无法用数学转换，你可以先通过CTE（公共表表达式）计算出每个timestamp的基础聚合结果（包括mean），再基于这个结果计算最终需要的subtract_mean：

df = duckdb.sql(f"""
                    WITH group_agg AS (
                        SELECT 
                            timestamp,
                            first(price) AS start,
                            sum(size) AS vol,
                            sum(price * size) / sum(size) AS mean,
                            sum(price) AS total_price,
                            count(*) AS row_count
                        FROM '{file_raw_parquet}'
                        GROUP BY timestamp
                    )
                    SELECT 
                        ga.timestamp,
                        ga.start,
                        ga.vol,
                        ga.mean,
                        ga.total_price - ga.mean * ga.row_count AS subtract_mean
                    FROM group_agg ga
                """)

如果你的subtract_mean必须基于原始行的price和组内mean逐行计算后再求和，那需要将CTE和原表关联后再聚合：

df = duckdb.sql(f"""
                    WITH group_agg AS (
                        SELECT 
                            timestamp,
                            sum(price * size) / sum(size) AS mean
                        FROM '{file_raw_parquet}'
                        GROUP BY timestamp
                    )
                    SELECT 
                        t.timestamp,
                        first(t.price) AS start,
                        sum(t.size) AS vol,
                        ga.mean,
                        sum(t.price - ga.mean) AS subtract_mean
                    FROM '{file_raw_parquet}' t
                    JOIN group_agg ga ON t.timestamp = ga.timestamp
                    GROUP BY t.timestamp, ga.mean
                """)

额外说明 #

你提到的“先计算mean到另一个表再JOIN”的思路完全可行，这就是方案2的核心思想——把需要复用的聚合结果提前计算好，再和原数据关联使用。

另外，DuckDB对CTE和子查询的支持很好，小数据量下性能无压力；大数据量场景下，方案1的数学转换会更高效，因为只需要对原表做一次聚合扫描。你提到的Arrow UDF和当前问题无关，这里的问题是SQL基础执行逻辑的限制，不需要用到UDF来解决。

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