SQL查询返回重复行问题排查及滚动累计实现方案

问题描述 #

需要实现随时间推移的滚动累计统计，但当前SQL查询返回重复行，导致new_vaccinations被重复累加，破坏统计结果。尝试过DISTINCT但无效，需要让每个日期仅保留一行以正确计算累计值。

原查询代码 #

Select 
    dea.continent, dea.location, dea.date, dea.population, 
    vac.new_vaccinations,
    sum(cast(vac.new_vaccinations as bigint)) over (partition by dea.location order by dea.location, dea.date)
from 
    PortfolioProject..CovidDeaths dea
join 
    PortfolioProject..CovidVaccinations vac on dea.location = vac.location
                                            and dea.date = vac.date
where 
    dea.continent is not null
order by 
    2,3

查询输出（重复行示例） #

Europe  Austria 2020-12-28 00:00:00.000 8922082 1345    2690
Europe  Austria 2020-12-28 00:00:00.000 8922082 1345    2690
Europe  Austria 2020-12-29 00:00:00.000 8922082 1694    6078
Europe  Austria 2020-12-29 00:00:00.000 8922082 1694    6078
Europe  Austria 2020-12-30 00:00:00.000 8922082 1429    8936
Europe  Austria 2020-12-30 00:00:00.000 8922082 1429    8936
Europe  Austria 2020-12-31 00:00:00.000 8922082 25      8986
Europe  Austria 2020-12-31 00:00:00.000 8922082 25      8986
Europe  Austria 2021-01-01 00:00:00.000 8922082 19      9024
Europe  Austria 2021-01-01 00:00:00.000 8922082 19      9024

解决方案 #

重复行的核心原因是CovidVaccinations表中同一location+date存在多条记录，关联时会将死亡表的单行匹配多次。需先处理疫苗表的重复数据，再进行关联和累计计算。

方法1：聚合疫苗表（推荐，若同一日期多条记录为分批次数据） #

通过GROUP BY将同一日期的疫苗数求和，确保每个location+date仅一行数据：

Select 
    dea.continent, dea.location, dea.date, dea.population, 
    vac.daily_vaccinations,
    sum(cast(vac.daily_vaccinations as bigint)) over (partition by dea.location order by dea.date) as rolling_total_vaccinations
from 
    PortfolioProject..CovidDeaths dea
join (
    select 
        location, date,
        sum(new_vaccinations) as daily_vaccinations
    from PortfolioProject..CovidVaccinations
    group by location, date
) vac on dea.location = vac.location
    and dea.date = vac.date
where 
    dea.continent is not null
order by 
    dea.location, dea.date

方法2：用ROW_NUMBER去重（若同一日期多条记录为重复数据） #

通过窗口函数给同一location+date的记录排名，仅保留第一行：

With RankedVaccinations as (
    select 
        location, date, new_vaccinations,
        ROW_NUMBER() over (partition by location, date order by (select null)) as rn
    from PortfolioProject..CovidVaccinations
)
Select 
    dea.continent, dea.location, dea.date, dea.population, 
    vac.new_vaccinations,
    sum(cast(vac.new_vaccinations as bigint)) over (partition by dea.location order by dea.date) as rolling_total_vaccinations
from 
    PortfolioProject..CovidDeaths dea
join RankedVaccinations vac on dea.location = vac.location
    and dea.date = vac.date
    and vac.rn = 1
where 
    dea.continent is not null
order by 
    dea.location, dea.date

说明 #

• DISTINCT无效的原因：窗口函数计算的累计值在重复行中已经是重复结果，即使去重也无法修正累计错误，必须先消除关联前的重复源。
• 选择哪种方法取决于疫苗表重复数据的性质：若为分批次的疫苗记录，用聚合求和更准确；若为完全重复的冗余数据，用ROW_NUMBER去重即可。

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