基于年份统计多列唯一国家总数的SQL查询问题

需求 #

查询返回每年的唯一国家总数，以及整张表中的唯一国家总数。

表结构 #

Country | Year | State | V1 | V2 |
 US       2020      NY      9    2
 US       2020      MA      3    6
 CA       2020      MAN     2    8
 CA       2020      ONT     5    1
 AU       2020      TAS     7    2
 AU       2020      VIC     3    3
 US       2021      NY      2    0
 US       2021      MA      8    2
 AU       2021      TAS     4    1
 AU       2021      VIC     5    2

期望结果 #

2020  |  2021 | Total_Unique_Countries
3         2               3

错误尝试及结果 #

用户原SQL语句：

SELECT
   SUM(CASE WHEN YEAR=2020 THEN 1 ELSE 0 END) AS "2020",
   SUM(CASE WHEN YEAR=2021 THEN 1 ELSE 0 END) AS "2021",
   COUNT(DISTINCT COUNTRY) AS Total_Unique_Countries
FROM MYTABLE GROUP BY YEAR

得到错误结果：

2020 | 2021 | Total_Unique_Countries
6       0               3
0       4               2

问题分析 #

1. GROUP BY YEAR 会将结果按年份拆分为两行，不符合期望的单行输出。
2. SUM(CASE...) 统计的是对应年份的行数，而非去重后的国家数量。
3. COUNT(DISTINCT COUNTRY) 在 GROUP BY YEAR 后，仅统计当前年份的唯一国家数，而非整张表的总数。

正确解法 #

方法一：先去重年份-国家组合再聚合 #

SELECT
  SUM(CASE WHEN year = 2020 THEN 1 ELSE 0 END) AS "2020",
  SUM(CASE WHEN year = 2021 THEN 1 ELSE 0 END) AS "2021",
  (SELECT COUNT(DISTINCT Country) FROM MYTABLE) AS Total_Unique_Countries
FROM (
  -- 先获取每个年份下的唯一国家列表
  SELECT DISTINCT Country, Year FROM MYTABLE
) t

方法二：使用COUNT(DISTINCT CASE...)直接计算 #

SELECT
  -- 统计2020年的唯一国家数，CASE不匹配时返回NULL，COUNT会忽略NULL
  COUNT(DISTINCT CASE WHEN Year = 2020 THEN Country END) AS "2020",
  COUNT(DISTINCT CASE WHEN Year = 2021 THEN Country END) AS "2021",
  -- 统计整张表的唯一国家数
  COUNT(DISTINCT Country) AS Total_Unique_Countries
FROM MYTABLE

两种方法都能得到符合期望的单行结果，其中方法二更简洁高效。

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