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

我想知道某个政党家族(保守党、社会民主党等)在选举中的表现如何.要做到这一点很容易:

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

我想知道某个政党家族在几十年的选举中表现如何，即.1980-1989年、1990-1999年及以后各年的投票率是多少？幸运的是，Postgres有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 |
--------------------------------

根据 comments ，我提供了输入数据:

+---------+------+------------+
| 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 --  here I divide by 2 because there were two elections (1983, 1986)