SQL:计算每个国家的红黄牌数

Question

我有下表，名为 Players:

name      |    memberof   |    country   |   givento   |   cardtype
Ni             1               Australia     7             red
Ju             1               Australia     9             yellow
Is             1               Australia     11            red
Ji             3               Japan         2             red
Su             3               Japan         22            red
Sa             3               Japan         25            yellow
Ji             3               Japan         2             yellow
Ji             3               Japan         2             yellow
Li             4               Thailand      13            red
Li             4               Thailand      13            yellow
Ro             1               Australia     null          null
So             2               Malaysia      null          null

上表显示了球员和他们在足球比赛中获得的牌。name为玩家名称，memberof为队伍id，country为玩家所属国家，givento为玩家id，cardtype为红色或黄色。

我正在尝试编写一个查询来确定每个国家/地区收到的红牌和黄牌总数。如果一个国家的玩家收到一张卡片，则该国家/地区将被视为收到一张卡片。

查询的输出应该是:

teams      |   reds    |   yellows
Australia       2           1
Malaysia        0           0
Japan           2           3
Thailand        1           1

我有以下两种观点，它们分别给出了每个国家收到的黄牌和红牌的总数:

create or replace view yellow as select memberof, country, cardtype, 
count(cardtype) over (partition by country) 
as yellows from Players where cardtype = 'yellow';


 create or replace view red as select memberof, country, cardtype, 
 count(cardtype) over (partition by country) 
 as reds from Players where cardtype = 'red';

通过对上述两个查询的“memberof”结果集执行内部连接，我可以获得一个国家/地区收到的红牌和黄牌总数。

select distinct y.country, r.reds, y.yellows from yellow y inner join red r on 
y.memberof = r.memberof;

然而，零红牌和零黄牌的马来西亚不包括在输出中。

    country  |    reds    |   yellows
   Australia      2            1
   Japan          2            3
   Thailand       1            1

对于收到任何一种颜色的零张牌的国家，我如何计算红牌和黄牌？任何见解都值得赞赏。

Answer 1

使用filter 并只计划聚合:

select p.country,
       count(*) filter (where p.carttype = 'red') as reds,
       count(*) filter (where p.carttype = 'yellow') as yellows
from players p
group by p.country;

您使用两个 View 和窗口函数的方法似乎太复杂了。