I have the following tables (simplified) in 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)
);

I like to know how a certain political party performs in elections. To do so I wrote a simple query:

我想知道某个政党在选举中的表现如何。为此，我编写了一个简单的查询：

SELECT
    e.country_name,
    extract(year FROM e.e_date) AS year,
    e.party_name,
    round(e.vote_share, 1) AS vote_share
FROM
    election e
    LEFT 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 = 'Luxembourg'
    AND e.e_date >= '1980-01-01'::date
    AND e.e_date < '2020-01-01'::date
GROUP BY
    e.country_name,
    e.e_date,
    e.party_name,
    e.vote_share
ORDER BY
    e.country_name,
    year;

This gives the following result:

这将产生以下结果：

+------------+------+-----------------------------------+-----+
| Luxembourg | 1984 | Déi Gréng                         | 5.2 |
| Luxembourg | 1989 | Déi Gréng                         | 4.2 |
| Luxembourg | 1989 | Greng Lëscht Ekologesch Initiativ | 4.2 |
+------------+------+-----------------------------------+-----+

I'd like to add one more information: the rank of each political party in election as measured by its vote share. I can do this for a single election (see result below) but my code won't work for the above written query. Please find below my working query for single election:

我还想补充一条信息：以得票率衡量的每个政党在选举中的排名。我可以对一次选举执行此操作(请参见下面的结果)，但我的代码不能用于上面的书面查询。请在下面找到我对单一选举的工作查询：

SELECT
    extract(year FROM e.e_date) AS year,
    e.party_name,
    e.country_name,
    round(e.vote_share, 1) AS vote_share,
    RANK() OVER (PARTITION BY e.id ORDER BY e.vote_share DESC) vote_rank_number
FROM
    election e
WHERE
    e.id = 1;

and the result:

结果是：

+------+----------------------------------+-------------+------+---+
| 1972 | Labour Party                     | New Zealand | 48.4 | 1 |
| 1972 | National Party                   | New Zealand | 41.5 | 2 |
| 1972 | Social Credit | Democratic Party | New Zealand |  6.7 | 3 |
| 1972 | Values Party                     | New Zealand |  2.0 | 4 |
+------+----------------------------------+-------------+------+---+

I use Postgres 14 but I can install 15 if needed.

我使用的是Postgres 14，但如果需要，我可以安装15。

UPDATE

更新

Please have a look at the following data. It shows election result for one year only. Déi Gréng ranks fourth in this election.

请看一下以下数据。它只显示一年的选举结果。Déi Gréng在这次选举中排名第四。

+------------+------+-----------------------------------------------+----------------------------------------------+------------+------+
|  country   | year |                  party name                   |             party name original              | vote_share | rank |
+------------+------+-----------------------------------------------+----------------------------------------------+------------+------+
| Luxembourg | 1984 | Chrëschtlech Sozial Vollekspartei             | Christian Social People's Party              |       34.9 |    1 |
| Luxembourg | 1984 | Lëtzebuerger Sozialistesch Aarbechterpartei   | Luxembourg Socialist Workers' Party          |       33.6 |    2 |
| Luxembourg | 1984 | Demokratesch Partei                           | Democratic Party                             |       18.7 |    3 |
| Luxembourg | 1984 | Déi Gréng                                     | The Greens                                   |        5.2 |    4 |
| Luxembourg | 1984 | Kommunistesch Partei Lëtzebuerg               | Communist Party of Luxembourg                |        5.0 |    5 |
| Luxembourg | 1984 | Jean Gremling List – Socialistes Indépendants | Jean Gremling List -- Independent Socialists |        2.5 |    6 |
+------------+------+-----------------------------------------------+----------------------------------------------+------------+------+

No let's back to my initial query. Here I want to add rank().

不，让我们回到我最初的疑问。这里我想添加rank（）。

SELECT
    e.country_name,
    extract(year FROM e.e_date) AS year,
    e.party_name,
    round(e.vote_share, 1) AS vote_share,
    RANK() OVER (PARTITION BY e.id ORDER BY e.vote_share DESC) vote_rank_number
FROM
    election e
    LEFT 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 = 'Luxembourg'
    AND e.e_date >= '1980-01-01'::date
    AND e.e_date < '2020-01-01'::date
GROUP BY
    e.id,
    e.country_name,
    e.e_date,
    e.party_name,
    e.vote_share
ORDER BY
    e.country_name,
    year;

The query gives the following result:

该查询得到以下结果：

+------------+------+-----------------------------------+------------+------+
|  country   | year |            party name             | vote_share | rank |
+------------+------+-----------------------------------+------------+------+
| Luxembourg | 1984 | Déi Gréng                         |        5.2 |    1 |
| Luxembourg | 1989 | Greng Lëscht Ekologesch Initiativ |        4.2 |    1 |
| Luxembourg | 1989 | Déi Gréng                         |        4.2 |    2 |
| Luxembourg | 1994 | Déi Gréng                         |       10.2 |    1 |
| Luxembourg | 1999 | Greng Lëscht Ekologesch Initiativ |        1.1 |    2 |
| Luxembourg | 1999 | Déi Gréng                         |        9.1 |    1 |
| Luxembourg | 2004 | Déi Gréng                         |       11.5 |    1 |
| Luxembourg | 2009 | Déi Gréng                         |       11.7 |    1 |
| Luxembourg | 2013 | Déi Gréng                         |       10.1 |    1 |
| Luxembourg | 2018 | Déi Gréng                         |       14.1 |    1 |
+------------+------+-----------------------------------+------------+------+

It wrongly outputs 1 for 1984 election year. It should be 4 as shown earlier. I hope I'm more clear know.

它错误地输出了1984选举年的1。如前面所示，它应该是4。我希望我能更清楚地知道。

If you want to include rank of each political part's vote share in every election year, you can modify your query like this:

如果您想要包括每个选举年每个政治部分的选票份额排名，您可以这样修改您的查询：

SELECT
    e.country_name,
    extract(year FROM e.e_date) AS year,
    e.party_name,
    round(e.vote_share, 1) AS vote_share,
    RANK() OVER (PARTITION BY extract(year FROM e.e_date) ORDER BY e.vote_share DESC) AS vote_rank_number
FROM
    election e
    LEFT 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 = 'Luxembourg'
    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),
    e.party_name,
    e.vote_share
ORDER BY
    e.country_name,
    year;

SELECT * FROM

(SELECT
    e.country_name,
    extract(year FROM e.e_date) AS year,
    e.party_name,
    p.family_name,
    round(e.vote_share, 1) AS vote_share,
    RANK() OVER (PARTITION BY extract(year FROM e.e_date) ORDER BY e.vote_share DESC) AS vote_rank_number
FROM
    election e
    LEFT JOIN party p ON e.party_id = p.id
WHERE
    e.e_type = 'parliament'
    AND e.country_name = 'Luxembourg'
    AND e.e_date >= '1980-01-01'::date
    AND e.e_date < '2020-01-01'::date
ORDER BY
    e.country_name,
    year
) 

WHERE family_name IN ('Green/Ecologist')