PostgreSQL按日输出Gr1数据填充最近历史值计算日均的SQL实现问题

你可以用窗口函数LAST_VALUE搭配IGNORE NULLS参数实现缺失值向前填充，完整SQL如下：

WITH all_dates AS (
    -- 取出表中所有去重日期，转为日期类型用于后续关联
    SELECT DISTINCT TO_DATE("Date", 'DD/MM/YYYY') AS stat_date
    FROM your_table_name
    ORDER BY stat_date DESC
),
gr1_raw AS (
    -- 过滤出Gr1分组的原始有效记录
    SELECT
        TO_DATE("Date", 'DD/MM/YYYY') AS stat_date,
        "Group",
        "Count",
        "Days"
    FROM your_table_name
    WHERE "Group" = 'Gr1'
)
SELECT
    TO_CHAR(t.stat_date, 'DD/MM/YYYY') AS "Date",
    'Gr1' AS "Group",
    t."Count",
    t."Days",
    (t."Count"::numeric / t."Days") AS "Avg Count Per Days"
FROM (
    SELECT
        ad.stat_date,
        -- 向前填充最近的非空Count值
        LAST_VALUE(gr."Count") IGNORE NULLS OVER (ORDER BY ad.stat_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Count",
        -- 向前填充最近的非空Days值
        LAST_VALUE(gr."Days") IGNORE NULLS OVER (ORDER BY ad.stat_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Days"
    FROM all_dates ad
    LEFT JOIN gr1_raw gr ON ad.stat_date = gr.stat_date
) t
ORDER BY t.stat_date DESC;

注意事项 #

• 请将代码中的your_table_name替换为你实际使用的表名
• 若你需要生成超出表中已有日期范围的连续记录，可将all_dates部分替换为generate_series生成指定区间的连续日期，示例：

  SELECT generate_series('2021-01-01'::date, '2021-01-31'::date, '1 day'::interval)::date AS stat_date
  

• 代码中对字段加双引号是为了适配PostgreSQL对大写字段名、关键字字段的识别规则，可根据你实际的表结构调整

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