在DuckDB中如何基于另一张表的行列值对目标表执行特定SQL聚合操作

嘿，我来给你捋捋在DuckDB里怎么干这个事儿！首先得贴合你的场景：你已经在DuckDB里加载了CDI、Population两张事实表和好几张维度表，而且已经分别把这两张事实表和对应的维度表关联好了对吧？

比如关联后的CDI表应该包含了像地区、时间这类维度字段，还有CDI的核心业务指标；关联后的Population表也有对应的维度字段和人口相关数据。现在要基于Population表的行列值来对CDI表做聚合，核心其实就是把两张关联后的表再次关联，然后基于共同的维度字段做聚合。

给你几个具体的SQL例子，你可以照着改：

• 基础分组聚合：假设两张表都有region（地区）和year（年份）这两个共同维度，你想按这两个维度分组，计算CDI指标的总和，同时带上对应维度的人口数：

SELECT
    c.region,
    c.year,
    SUM(c.cdi_value) AS total_cdi,
    p.total_population
FROM
    merged_cdi c
JOIN
    merged_population p
ON
    c.region = p.region
    AND c.year = p.year
GROUP BY
    c.region, c.year, p.total_population
ORDER BY
    c.year, c.region;

• 带筛选条件的聚合：如果只想对人口数超过10万的地区计算CDI的平均值：

SELECT
    c.region,
    c.year,
    AVG(c.cdi_value) AS avg_cdi
FROM
    merged_cdi c
JOIN
    merged_population p
ON
    c.region = p.region
    AND c.year = p.year
WHERE
    p.total_population > 100000
GROUP BY
    c.region, c.year
ORDER BY
    c.year, c.region;

• 加权聚合：用人口数作为权重计算CDI的加权平均值：

SELECT
    c.region,
    c.year,
    SUM(c.cdi_value * p.total_population) / SUM(p.total_population) AS weighted_avg_cdi
FROM
    merged_cdi c
JOIN
    merged_population p
ON
    c.region = p.region
    AND c.year = p.year
GROUP BY
    c.region, c.year
ORDER BY
    c.year, c.region;

总的来说就是先通过共同维度把两张表连起来，再根据你的需求选合适的聚合函数，搭配GROUP BY就能实现你要的操作啦。

内容来源于stack exchange