PostgreSQL如何基于日度病例数据计算每周平均病例数

核心逻辑 #

先基于date字段生成唯一周标识（避免不同年份同周数冲突），再根据你原有查询的输出粒度选择对应方案： #

方案1：原有查询为日度/明细粒度，需每行附带对应周的日均病例数 #

无需大幅修改原有逻辑，外层嵌套窗口函数计算即可，示例如下：

SELECT 
    t.*,
    AVG(t.daily_case_cnt) OVER (PARTITION BY t.week_unique_id) AS weekly_avg_cases
FROM (
    -- 此处直接替换为你现有接近预期的查询语句，仅需补充周标识字段即可
    SELECT 
        date,
        status,
        COUNT(caseID) AS daily_case_cnt,
        -- 生成年+周的唯一标识，以下为MySQL语法，其他数据库可替换为对应逻辑：
        -- PostgreSQL: TO_CHAR(date, 'IYYY-IW')
        -- SQL Server: CONCAT(DATEPART(YEAR, date), '-', DATEPART(WEEK, date))
        DATE_FORMAT(date, '%Y-%u') AS week_unique_id
    FROM Table1
    -- 你原有查询的WHERE、GROUP BY、JOIN等逻辑保持不变
    GROUP BY date, status
) t

方案2：需单独输出每周维度的周均病例数 #

直接按周聚合计算即可，可根据业务需求调整分母规则：

SELECT 
    week_unique_id,
    -- 规则1：周总病例 / 当周有上报数据的天数
    SUM(daily_case_cnt) / COUNT(DISTINCT date) AS weekly_avg_cases
    -- 规则2：周总病例 / 固定7天，替换为下方写法即可
    -- SUM(daily_case_cnt) / 7 AS weekly_avg_cases
FROM (
    SELECT 
        DATE_FORMAT(date, '%Y-%u') AS week_unique_id,
        date,
        COUNT(caseID) AS daily_case_cnt
    FROM Table1
    GROUP BY week_unique_id, date
) t
GROUP BY week_unique_id

注意事项 #

• 周统计规则需对齐业务要求：不同数据库默认的周起始日（周一/周日）、年度首周判定逻辑存在差异，可调整周标识生成函数适配
• 若需按病例状态分别计算周均值，在PARTITION BY或GROUP BY子句中补充status字段即可

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