如何在SQL中获取每个国家首次新冠疫苗接种的匹配记录？

要获取每个国家满足条件的第一条疫苗接种记录，可以通过以下两种高效的SQL方案实现：

方案一：使用窗口函数ROW_NUMBER() #

窗口函数可以按国家分组并对日期排序，直接标记出每个国家的第一条记录：

WITH RankedVaccinations AS (
    SELECT 
        dea.location,
        dea.date,
        vac.new_vaccinations,
        -- 按国家分组，日期升序排名，排名为1的就是首次接种记录
        ROW_NUMBER() OVER (PARTITION BY dea.location ORDER BY dea.date ASC) AS rn
    FROM PortfolioProject..CovidDeaths dea
    JOIN PortfolioProject..CovidVaccinations vac
        ON dea.location = vac.location AND dea.date = vac.date
    WHERE 
        dea.continent IS NOT NULL 
        AND vac.new_vaccinations IS NOT NULL
)
SELECT location, date, new_vaccinations
FROM RankedVaccinations
WHERE rn = 1
-- 若仅需指定国家，添加如下条件
-- AND location IN ('Canada', 'Israel', 'Bahrain')
ORDER BY date;

方案二：分组查询+关联原表 #

先通过子查询找到每个国家的最早接种日期，再关联原表获取对应接种数据：

SELECT 
    dea.location,
    dea.date,
    vac.new_vaccinations
FROM PortfolioProject..CovidDeaths dea
JOIN PortfolioProject..CovidVaccinations vac
    ON dea.location = vac.location AND dea.date = vac.date
JOIN (
    -- 子查询获取每个国家的首次接种日期
    SELECT location, MIN(date) AS first_vaccine_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 
        AND vac.new_vaccinations IS NOT NULL
    GROUP BY location
) AS FirstDates
    ON dea.location = FirstDates.location AND dea.date = FirstDates.first_vaccine_date
-- 若仅需指定国家，添加如下条件
-- WHERE dea.location IN ('Canada', 'Israel', 'Bahrain')
ORDER BY date;

注意事项 #

• 如果某个国家在首次接种日有多条记录，方案一的ROW_NUMBER()会只返回其中一条；若需保留同一天的所有记录，可将ROW_NUMBER()替换为RANK()。
• 若只需查询特定国家，取消注释代码中的AND location IN (...)条件即可。

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