为什么这个查询返回两行

Question

随着时间的推移，我正试图运行滚动计数，因此第二行(重复(再次添加相同的值，并将计数搞砸。我该如何阻止这种情况的发生？我只希望每个日期一行，然后添加下一个日期。我已经尝试了不同的命令，但第二行计数仍然出现，所以我想我需要一个不同的命令。

请参阅底部的输出代码，最后一列是合计，倒数第二列是要添加到总中的新金额

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

Answer 1

子句分区不会减少返回的行。只需将聚合数据添加到行中。由于CovidVaccinations表上的汇总，您假设有许多vac.location+vac.date的记录。

因此，你应该首先准备新冠疫苗接种的数据；

SELECT vac.location, vac.date, sum( vac.new_vaccinations )
FROM portfolioproject..CovidVaccinations vac
GROUP BY vac.location, vac.date

然后再加入CovidDeaths，没有聚集，就像那样；

WITH vac
( SELECT vac.location, vac.date, sum( vac.new_vaccinations ) as total
FROM portfolioproject..CovidVaccinations vac
GROUP BY vac.location, vac.date
)
SELECT vac.total, ... FROM CovidDeaths dea, vac
WHERE dea.location = vac.location
AND dea.date = vac.date

如果CovidDeaths表的每个dea.contation、dea.location、dea.date、dea.population也有许多记录，则将其数据分组，然后加入其他结果