PostgreSQL查询聚合问题：按年份汇总政党家族得票率

表结构 #

CREATE TABLE party(
    id int PRIMARY KEY,
    family_name varchar(50) NOT NULL
);

CREATE TABLE election(
    id int,
    country_name varchar(50) NOT NULL,
    e_type election_type NOT NULL,
    e_date date NOT NULL,
    vote_share numeric,
    seats int,
    seats_total int NOT NULL,
    party_name_short varchar(10) NOT NULL,
    party_name varchar(255) NOT NULL,
    party_name_english varchar(255) NOT NULL,
    party_id int REFERENCES party(id)
);

原查询及问题 #

原查询试图统计奥地利1980-2020年间议会选举中绿党/生态党家族的得票率：

SELECT
    e.country_name,
    extract(year FROM e.e_date) AS year,
    sum(e.vote_share) AS vote_share
FROM
    election e
    JOIN party p ON e.party_id = p.id
WHERE
    e.e_type = 'parliament'
    AND p.family_name IN ('Green/Ecologist')
    AND e.country_name = 'Austria'
    AND e.e_date >= '1980-01-01'::date
    AND e.e_date < '2020-01-01'::date
GROUP BY
    e.country_name,
    e.e_date,
    vote_share;

原查询返回结果（未按年份汇总，保留了每个政党的单独得票率）：

需求 #

需要按年份汇总，将同一年的多个得票率以值1 + 值2的形式展示，预期结果：

修改后的查询 #

要实现这个需求，需使用STRING_AGG函数拼接同一年的得票率，并调整分组字段：

SELECT
    e.country_name,
    EXTRACT(YEAR FROM e.e_date) AS year,
    STRING_AGG(e.vote_share::TEXT, ' + ') AS vote_share
FROM
    election e
    JOIN party p ON e.party_id = p.id
WHERE
    e.e_type = 'parliament'
    AND p.family_name = 'Green/Ecologist'
    AND e.country_name = 'Austria'
    AND e.e_date >= '1980-01-01'::date
    AND e.e_date < '2020-01-01'::date
GROUP BY
    e.country_name,
    EXTRACT(YEAR FROM e.e_date);

关键调整说明 #

1. 替换聚合函数：用STRING_AGG替代SUM，它能将分组内的字段值按指定分隔符拼接成字符串，这里用' + '作为分隔符。
2. 修正分组逻辑：原查询错误将e.e_date和vote_share加入分组，导致每个单独得票率成为独立分组。现在只按国家和年份分组，确保同一年的所有得票率被合并。
3. 类型转换：vote_share是numeric类型，需通过::TEXT转换为字符串才能参与拼接。

如果需要同时展示明细拼接和总得票率，可在查询中同时保留两种聚合结果：

SELECT
    e.country_name,
    EXTRACT(YEAR FROM e.e_date) AS year,
    STRING_AGG(e.vote_share::TEXT, ' + ') AS vote_share_details,
    SUM(e.vote_share) AS total_vote_share
FROM
    election e
    JOIN party p ON e.party_id = p.id
WHERE
    e.e_type = 'parliament'
    AND p.family_name = 'Green/Ecologist'
    AND e.country_name = 'Austria'
    AND e.e_date >= '1980-01-01'::date
    AND e.e_date < '2020-01-01'::date
GROUP BY
    e.country_name,
    EXTRACT(YEAR FROM e.e_date);

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