如何在Postgres中按十年分组计算政党选举得票率平均值

问题 #

我在Postgres中有以下简化表：

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)
);

原本可以轻松查询特定政党家族的选举得票率，但现在希望按十年分组统计得票率平均值。我用date_trunc写的查询直接对得票率求和，结果不符合预期：

SELECT
    e.country_name,
    sum(e.vote_share) AS vote_share,
    extract(year FROM date_trunc('decade', e.e_date)) || 's' AS decade
    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,
    decade

错误结果：

|country_name|vote_share|decade|
|------------|----------|------|
|Austria     |8.2       |1980s |
|Austria     |26.3      |1990s |
|Austria     |31        |2000s |
|Austria     |36.4      |2010s |
--------------------------------

正确逻辑是：先计算十年内每次选举的得票率总和，再除以该十年的选举次数。比如示例输入数据：

+---------+------+------------+
| country | year | vote_share |
+---------+------+------------+
| Austria | 1983 |        1.4 |
| Austria | 1983 |        2.0 |
| Austria | 1986 |        4.8 |
| Austria | 1990 |        4.8 |
| Austria | 1990 |        2.0 |
| Austria | 1994 |        7.3 |
| Austria | 1995 |        4.8 |
| Austria | 1999 |        7.4 |
+---------+------+------------+

期望结果示例：

1980s: sum: 1.4 + 2 + 4.8 = 8.2
average vote share: 8.2 / 2 = 4.1 -- 此处除以2是因为该十年有2次选举（1983、1986）

请问如何编写正确的SQL查询？

解决方案 #

要实现需求，需要分两步计算：

1. 先按单次选举分组，计算该次选举中目标政党家族的得票率总和
2. 再按十年分组，对各次选举的得票总和求平均值

对应的SQL查询如下：

WITH election_year_totals AS (
    SELECT
        e.country_name,
        extract(year FROM e.e_date) AS election_year,
        sum(e.vote_share) AS election_total_vote
    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, election_year
)
SELECT
    country_name,
    avg(election_total_vote) AS avg_vote_share,
    extract(year FROM date_trunc('decade', (election_year || '-01-01')::date)) || 's' AS decade
FROM election_year_totals
GROUP BY country_name, decade
ORDER BY decade;

说明 #

• 第一步通过CTE（election_year_totals）先按选举年份（单次选举）聚合，得到每次选举的得票总和，比如1983年的得票总和是1.4+2.0=3.4，1986年是4.8
• 第二步基于CTE的结果，按十年分组，用avg()函数直接计算各次选举得票总和的平均值，这样就得到了十年内的平均得票率
• 也可以用sum(election_total_vote)/count(DISTINCT election_year)替代avg()，结果完全一致

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