获取月末人口数据并计算月均温、月度死亡总数的SQL查询需求

假设你的现有查询已经能正确筛选出月末的人口数据，我们可以通过**子查询/CTE（公共表表达式）**来实现需求中的两个统计项，以下是完整的实现代码：

WITH MonthlyPopulation AS (
    -- 你的现有查询，获取月末当日的人口数据，同时提取年月用于关联
    SELECT 
        Country,
        [City Abbr],
        [Area Code],
        Date,
        -- 提取年月（SQL Server写法，其他数据库可调整）
        DATEFROMPARTS(YEAR(Date), MONTH(Date), 1) AS MonthStart
    FROM Population
    -- 保留你原有的月末筛选逻辑，示例用EOMONTH判断当日为月末
    WHERE Date = EOMONTH(Date)
),
MonthlyAvgTemp AS (
    -- 计算每个地区当月的平均温度
    SELECT 
        Country,
        [City Abbr],
        [Area Code],
        DATEFROMPARTS(YEAR(Date), MONTH(Date), 1) AS MonthStart,
        AVG(Temperature) AS MonthlyAvgTemperature
    FROM Population
    GROUP BY Country, [City Abbr], [Area Code], YEAR(Date), MONTH(Date)
),
MonthlyDeadCount AS (
    -- 关联Dead表，统计每个地区当月的死亡总数
    SELECT 
        p.Country,
        p.[City Abbr],
        p.[Area Code],
        DATEFROMPARTS(YEAR(d.DeathDate), MONTH(d.DeathDate), 1) AS MonthStart,
        COUNT(d.DeathID) AS MonthlyDeadTotal -- 假设Dead表用DeathID作为唯一标识
    FROM Dead d
    JOIN MonthlyPopulation p 
        ON d.Country = p.Country
        AND d.[City Abbr] = p.[City Abbr]
        AND d.[Area Code] = p.[Area Code]
        AND YEAR(d.DeathDate) = YEAR(p.Date)
        AND MONTH(d.DeathDate) = MONTH(p.Date)
    GROUP BY p.Country, p.[City Abbr], p.[Area Code], YEAR(d.DeathDate), MONTH(d.DeathDate)
)
-- 最终关联所有结果
SELECT 
    mp.Country,
    mp.[City Abbr],
    mp.[Area Code],
    mp.Date AS MonthEndDate,
    mat.MonthlyAvgTemperature,
    mdc.MonthlyDeadTotal
FROM MonthlyPopulation mp
LEFT JOIN MonthlyAvgTemp mat 
    ON mp.Country = mat.Country
    AND mp.[City Abbr] = mat.[City Abbr]
    AND mp.[Area Code] = mat.[Area Code]
    AND mp.MonthStart = mat.MonthStart
LEFT JOIN MonthlyDeadCount mdc 
    ON mp.Country = mdc.Country
    AND mp.[City Abbr] = mdc.[City Abbr]
    AND mp.[Area Code] = mdc.[Area Code]
    AND mp.MonthStart = mdc.MonthStart;

关键说明： #

• 月末数据适配：如果使用非SQL Server数据库，月末判断逻辑需调整：MySQL用LAST_DAY(Date)，Oracle用TRUNC(Date, 'MM') + INTERVAL '1' MONTH - INTERVAL '1' DAY。
• 月均温计算：通过按Country、City Abbr、Area Code和年月分组，用AVG(Temperature)得到整月温度平均值。
• 死亡统计适配：如果Dead表没有DeathID，可改用非空的业务主键字段做COUNT，避免重复计数；若部分地区当月无死亡数据，LEFT JOIN会保留月末人口数据，死亡总数显示为NULL。

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