单表Over窗口+GroupBy场景下DuckDB比Polars慢3倍是否正常？

在Python环境下测试了同时涉及窗口计算与分组聚合的场景，DuckDB的运行速度比Polars慢近3倍，但二者输出结果完全一致。想搞清楚：

• 这一结果是否符合预期？毕竟DuckDB的设计定位更偏向跨数据框/表操作？
• 还是仅仅因为我的SQL查询写法存在低效问题？

测试代码 #

import time

import duckdb
import numpy as np
import polars as pl

## 测试数据集生成
rng = np.random.default_rng(1)

nrows = 10_000_000
df = pl.DataFrame(
    dict(
        id=rng.integers(1, 100, nrows),
        id2=rng.integers(1, 1_000, nrows),
        v1=rng.normal(0, 1, nrows),
        v2=rng.normal(0, 1, nrows),
        v3=rng.normal(0, 1, nrows),
        v4=rng.normal(0, 1, nrows),
    )
)

## Polars实现（耗时：~1.1s）
start = time.perf_counter()
res = (
    df.select(
        [
            "id",
            "id2",
            pl.col("v1") - pl.col("v1").mean().over(["id", "id2"]),
            pl.col("v2") - pl.col("v2").mean().over(["id", "id2"]),
            pl.col("v3") - pl.col("v3").mean().over(["id", "id2"]),
            pl.col("v4") - pl.col("v4").mean().over(["id", "id2"]),
        ]
    )
    .groupby(["id", "id2"])
    .agg(
        [
            (pl.col("v1") * pl.col("v2")).sum().alias("ans1"),
            (pl.col("v3") * pl.col("v4")).sum().alias("ans2"),
        ]
    )
)
time.perf_counter() - start
# 1.0977217499166727

## DuckDB实现（耗时：~3.5s）
start = time.perf_counter()
res2 = (
    duckdb.sql(
        """
        SELECT id, id2,
        v1 - mean(v1) OVER (PARTITION BY id, id2) as v1,
        v2 - mean(v2) OVER (PARTITION BY id, id2) as v2,
        v3 - mean(v3) OVER (PARTITION BY id, id2) as v3,
        v4 - mean(v4) OVER (PARTITION BY id, id2) as v4,
        FROM df
        """
    )
    .aggregate(
        "id, id2, sum(v1 * v2) as ans1, sum(v3 * v4) as ans2",
        "id, id2",
    )
    .pl()
)
time.perf_counter() - start
# 3.549897135235369

问题解答 #

核心结论 #

这种性能差异主要是DuckDB写法未做优化导致的，而非完全符合产品定位的结果。通过调整SQL写法，DuckDB的性能可以大幅接近甚至超过Polars。

1. 你的DuckDB写法的低效点 #

当前写法会先生成1000万行的全量窗口计算中间结果，再对中间结果做分组聚合，额外增加了内存开销和数据流转成本。而Polars的查询引擎会自动优化执行计划：它能识别到窗口计算后立即分组的逻辑，直接在分组计算均值的同时完成后续乘积求和，跳过了全量中间数据的生成。

2. DuckDB的优化写法 #

写法1：合并表达式，避免全量中间表

直接将窗口计算逻辑嵌入聚合步骤，让DuckDB可以在一次遍历中完成计算：

start = time.perf_counter()
res2 = duckdb.sql("""
    SELECT 
        id, id2,
        SUM( (v1 - AVG(v1) OVER (PARTITION BY id, id2)) * (v2 - AVG(v2) OVER (PARTITION BY id, id2)) ) AS ans1,
        SUM( (v3 - AVG(v3) OVER (PARTITION BY id, id2)) * (v4 - AVG(v4) OVER (PARTITION BY id, id2)) ) AS ans2
    FROM df
    GROUP BY id, id2
""").pl()
time.perf_counter() - start

写法2：数学公式展开，进一步减少窗口计算

利用统计公式展开SUM((x-avg_x)*(y-avg_y))，避免重复计算窗口均值，性能更优：

start = time.perf_counter()
res2 = duckdb.sql("""
    WITH group_stats AS (
        SELECT 
            id, id2,
            AVG(v1) AS avg_v1, AVG(v2) AS avg_v2,
            AVG(v3) AS avg_v3, AVG(v4) AS avg_v4,
            SUM(v1*v2) AS sum_v1v2, SUM(v1) AS sum_v1, SUM(v2) AS sum_v2,
            SUM(v3*v4) AS sum_v3v4, SUM(v3) AS sum_v3, SUM(v4) AS sum_v4,
            COUNT(*) AS cnt
        FROM df
        GROUP BY id, id2
    )
    SELECT 
        id, id2,
        sum_v1v2 - avg_v1*sum_v2 - avg_v2*sum_v1 + avg_v1*avg_v2*cnt AS ans1,
        sum_v3v4 - avg_v3*sum_v4 - avg_v4*sum_v3 + avg_v3*avg_v4*cnt AS ans2
    FROM group_stats
""").pl()
time.perf_counter() - start

3. 关于产品定位的补充 #

DuckDB确实更擅长跨多表复杂查询（如多表JOIN、嵌套子查询），但在单表窗口+聚合场景下，只要写法优化到位，性能不会比Polars差。你的测试结果更多是写法未利用DuckDB的优化空间，而非定位问题。

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