如何在DuckDB/SQL中按分组计算Spearman秩相关系数？

你原来的代码报错是因为SQL不允许在聚合函数（比如corr）里直接嵌套窗口函数（比如rank()）——窗口函数是逐行处理生成列，聚合函数是对分组做统计，顺序不能反。

正确的做法是先在每个分组内算出a和b的秩，再基于这些秩计算皮尔逊相关系数（Spearman秩相关本质就是变量秩的皮尔逊相关）。

修改后的完整代码：

import duckdb
import pandas as pd

df = pd.DataFrame(
    {
        "a": [1, 1, 2, 2, 6, 1, 3, 6, 3],
        "b": [4, 2, 6, 4, 3, 1, 6, 4, 8],
        "c": [1, 1, 1, 1, 2, 2, 2, 2, 2],
    }
)

result = duckdb.sql(
    """
    WITH ranked_data AS (
        SELECT 
            c,
            rank() OVER (PARTITION BY c ORDER BY a) AS rank_a,
            rank() OVER (PARTITION BY c ORDER BY b) AS rank_b
        FROM df
    )
    SELECT 
        c,
        corr(rank_a, rank_b) AS spearman_corr
    FROM ranked_data
    GROUP BY c
    """
).df()

print(result)

运行后会输出你期望的结果：

c  spearman_corr
0  1       0.707107
1  2       0.105409

这里关键是用PARTITION BY c让rank()的计算范围限定在每个分组内，先得到每个分组里a和b的秩列，再对分组后的秩列计算corr，就符合SQL的执行逻辑了。

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