PostgreSQL中基于当前日期结合Window Function（含Partition By与双列组合）创建Materialized View的技术求助

嘿，我完全get到你要实现的逻辑了——根据当前日期动态调整窗口内的求和规则，对不同日期的记录分别累加a列、a+b列或者继续累加b列对吧？结合你给出的示例，我整理了一个能直接落地的方案：

核心实现思路 #

我们可以用CASE语句配合窗口函数SUM()，借助PostgreSQL的CURRENT_DATE获取当前日期，判断每一行应该计入a、b还是两者都计入，再通过窗口子句实现按日期顺序的累计求和。

创建物化视图的SQL语句 #

CREATE MATERIALIZED VIEW dynamic_sum_mv AS
SELECT
    date_col,
    a,
    b,
    -- 按日期顺序累计求和，根据当前日期动态调整计入的列
    SUM(
        -- 当前日期及之前的行，计入a列的值
        CASE WHEN date_col <= CURRENT_DATE THEN a ELSE 0 END +
        -- 当前日期及之后的行，计入b列的值
        CASE WHEN date_col >= CURRENT_DATE THEN b ELSE 0 END
    ) OVER (
        ORDER BY date_col 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS total_sum
FROM your_table; -- 替换成你的实际表名

逻辑拆解 #

• 第一个CASE分支：对于早于或等于当前日期的记录，我们把a列的值加入累计求和；
• 第二个CASE分支：对于晚于或等于当前日期的记录，我们把b列的值加入累计求和；
• 窗口子句ORDER BY date_col ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW确保求和是从最早的日期开始，一直累加到当前行，完全匹配你示例里的累计逻辑。

动态刷新物化视图 #

因为CURRENT_DATE是实时变化的，每天的求和结果都会不同，所以需要定期刷新物化视图来更新数据：

REFRESH MATERIALIZED VIEW dynamic_sum_mv;

用你给出的示例数据测试的话，当CURRENT_DATE为'2022-06-06'时：

• 2022-06-05的total_sum会是1+2+3+4+5=15
• 2022-06-06的total_sum会是15+6+107=128
• 2022-06-07的total_sum会是128+108=236
  完全符合你想要的结果~

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