如何在SQL查询中计算不同国家的累计去重计数？

我有一张表，包含用户id、用户所属国家（country）以及注册年份（year），示例数据如下：

我希望按年份计算不同国家的累计去重计数，示例的预期输出为：

我编写了如下SQL，但逻辑存在问题——查询的后半部分未实现去重计数：

with t1 as (
select year, count(distinct country) country_count
from data
group by 1 order by 1
)

select *, sum(country_count) over (order by year) AS cumulative_country_count
from t1

原SQL的问题在于：先按年份统计当年的去重国家数再累加，会重复计算跨年份出现的国家（比如USA在2010和2011都出现，原SQL会把2010的1和2011的1重复计入，导致结果错误）。

正确的核心思路是：统计到当前年份为止，所有首次出现的国家总数，避免重复计数。以下是几种可行的实现方式：

方法一：基于国家首次出现年份的累计统计 #

先找出每个国家最早的注册年份，再按年份统计有多少个国家的首次出现年份不晚于当前年份：

with first_appearance as (
    -- 得到每个国家首次出现的年份
    select country, min(year) as first_year
    from data
    group by country
),
year_list as (
    -- 提取所有存在数据的年份
    select distinct year from data order by year
)
select 
    yl.year,
    count(fa.country) as country_count
from year_list yl
left join first_appearance fa on fa.first_year <= yl.year
group by yl.year
order by yl.year;

方法二：支持窗口函数去重的简化写法 #

如果你的SQL引擎（如PostgreSQL 11+、BigQuery、Snowflake等）支持COUNT(DISTINCT ...)在窗口函数中使用，可以直接用以下简化语句：

select distinct
    year,
    count(distinct country) over (order by year) as country_count
from data
order by year;

方法三：条件聚合实现 #

通过笛卡尔积关联所有年份和国家，再用条件统计每个年份前出现的国家数：

with all_years as (
    select distinct year from data order by year
),
all_countries as (
    select distinct country from data
)
select
    ay.year,
    sum(case when exists (
        select 1 from data d 
        where d.country = ac.country and d.year <= ay.year
    ) then 1 else 0 end) as country_count
from all_years ay
cross join all_countries ac
group by ay.year
order by ay.year;

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