如何为月度排名物化视图添加上月排名列以对比升降？

你的代码无法运行的核心原因有两个：

1. 创建物化视图时不能引用自身（视图尚未创建完成）；
2. 没有存储历史排名数据，无法直接关联获取上月的排名结果。

根据你的数据存储情况，分两种最优方案：

方案一：scorelist已保存月度历史数据 #

如果scorelist表中每个项目（id）每月都有一条独立的score记录（带月份字段），可以直接用窗口函数LAG()获取上月排名：

CREATE MATERIALIZED VIEW public.monthlyranking
TABLESPACE pg_default
AS
SELECT 
    s.id,
    s.month AS current_month,
    s.score,
    -- 计算当月排名
    RANK() OVER (PARTITION BY s.month ORDER BY s.score DESC) AS ranking,
    -- 获取该项目上月的排名
    LAG(RANK() OVER (PARTITION BY s.month ORDER BY s.score DESC)) 
        OVER (PARTITION BY s.id ORDER BY s.month) AS ranking_old
FROM scorelist s
-- 只保留当月数据，按需调整
WHERE s.month = DATE_TRUNC('month', CURRENT_DATE)::DATE
WITH DATA;

每月刷新物化视图即可：

REFRESH MATERIALIZED VIEW public.monthlyranking;

方案二：scorelist仅保存当前最新数据 #

如果scorelist只有项目的最新score，需要先创建历史表存储每月的排名快照，再关联生成带上月排名的物化视图：

步骤1：创建历史排名存储表 #

用来持久化每个月的排名数据：

CREATE TABLE public.monthly_ranking_history (
    id INT,
    score NUMERIC,
    ranking INT,
    month DATE NOT NULL, -- 用每月第一天标识月度，如'2024-05-01'
    PRIMARY KEY (id, month)
);

步骤2：每月生成当月排名并写入历史表 #

可以用定时任务（如pg_cron）自动执行：

WITH current_month_rank AS (
    SELECT 
        s.id,
        s.score,
        RANK() OVER (ORDER BY s.score DESC) AS ranking,
        DATE_TRUNC('month', CURRENT_DATE)::DATE AS current_month
    FROM scorelist s
)
INSERT INTO monthly_ranking_history (id, score, ranking, month)
SELECT id, score, ranking, current_month
FROM current_month_rank
-- 重复计算时覆盖旧数据
ON CONFLICT (id, month) DO UPDATE 
SET score = EXCLUDED.score, ranking = EXCLUDED.ranking;

步骤3：创建带上月排名的物化视图 #

CREATE MATERIALIZED VIEW public.monthlyranking
TABLESPACE pg_default
AS
SELECT 
    curr.id,
    curr.score,
    curr.ranking,
    prev.ranking AS ranking_old,
    curr.month AS current_month
FROM monthly_ranking_history curr
-- 关联上月的排名数据
LEFT JOIN monthly_ranking_history prev
    ON curr.id = prev.id
    AND prev.month = (curr.month - INTERVAL '1 month')::DATE
-- 只显示当月数据
WHERE curr.month = DATE_TRUNC('month', CURRENT_DATE)::DATE
WITH DATA;

步骤4：每月刷新物化视图 #

REFRESH MATERIALIZED VIEW public.monthlyranking;

内容的提问来源于stack exchange，提问作者Omar Abdul'Azeez