PostgreSQL基于触发器自动更新物化视图实现RAG状态计算

需求概述 #

基于三张表构建自动更新的物化视图：

• tbl_results：存储指标结果数据
• tbl_ki：存储指标的附加元数据（名称、阈值等）
• tbl_calcs：存储每个指标对应的RAG状态计算规则（动态CASE语句）

物化视图需要包含：

• 月度聚合的指标结果
• 指标的附加字段
• 根据tbl_calcs中的规则和tbl_ki的阈值计算出的RAG状态（1=绿，2=黄，3=红，规则由各calc_id定义）

第一步：修正原SQL脚本错误 #

原脚本存在语法问题，以下是修正后的建表和数据插入语句：

-- 创建指标结果表
CREATE TABLE tbl_results (
    uid SERIAL PRIMARY KEY,
    kid VARCHAR(50) NOT NULL,
    dte DATE NOT NULL,
    res numeric(18,4) NULL,
    res_v numeric(18,4) NULL,
    dte_added DATE DEFAULT CURRENT_DATE NOT NULL,
    bsn VARCHAR(50) NULL,
    CONSTRAINT pk_results UNIQUE (uid)
);

-- 插入指标结果数据（修正VALUES语法）
INSERT INTO tbl_results
(uid, kid, dte, res, res_v, dte_added, bsn)
VALUES
(1, 'IN1', '2023-12-31', 1.2587, 2.665, CURRENT_DATE, ''),
(2, 'IN2', '2023-12-31', 2.15, 0.225, CURRENT_DATE, ''),
(3, 'IN3', '2023-12-31', 3.856, 1.9, CURRENT_DATE, ''),
(4, 'IN4', '2023-12-31', 1.978, 6.9, CURRENT_DATE, ''),
(5, 'IN5', '2023-12-31', 45.69, 7855, CURRENT_DATE, '');

-- 创建指标元数据表
CREATE TABLE tbl_ki (
    kid VARCHAR(50) NOT NULL,
    ki_nme varchar(100) NOT NULL,
    ki_desc varchar(150) NOT NULL,
    ki_purp varchar(150) NOT NULL,
    ki_req varchar(150) NOT NULL,
    ki_own varchar(150) NOT NULL,
    ki_jrny varchar(150) NOT NULL,
    calc_id varchar(10) NOT NULL,
    threshold_red float NOT NULL,
    threshold_amber float NOT NULL,
    PRIMARY KEY (kid)
);

-- 插入指标元数据（添加分号）
INSERT INTO tbl_ki (kid, ki_nme, ki_desc, ki_purp, ki_req, ki_own, ki_jrny, calc_id, threshold_red, threshold_amber) VALUES
('IN1', '知识库系统', '用于存储和检索知识文章的系统', '为用户提供集中式信息访问库', '数据库管理系统、Web服务器、用户界面', 'IT部门', '需求收集、开发、测试、部署', 'CALCID001', 0.8, 0.6),
('IN2', '库存管理系统', '用于跟踪和管理库存水平的系统', '优化库存水平并简化库存流程', '数据库管理系统、条码扫描器、用户界面', '仓库经理', '需求分析、设计、实现、维护', 'CALCID002', 0.9, 0.7),
('IN3', '客户关系管理系统', '用于管理与客户互动的系统', '改善客户服务并提高销售效率', '数据库管理系统、邮件集成、客户门户', '销售团队', '线索生成、销售流程管理、客户支持', 'CALCID003', 0.85, 0.65);

-- 创建计算规则表
CREATE TABLE tbl_calcs (
    calc_id VARCHAR(10) PRIMARY KEY,
    calc text NOT NULL,
    dte_added DATE DEFAULT CURRENT_DATE NOT NULL
);

-- 插入计算规则（替换HTML转义字符为实际符号）
INSERT INTO tbl_calcs (calc_id, calc) VALUES
('CALCID001', 'CASE WHEN res < threshold_red THEN 3 ELSE 1 END'),
('CALCID002', 'CASE WHEN res > threshold_amber THEN 1 WHEN res > threshold_red AND res <= threshold_amber THEN 2 ELSE 3 END'),
('CALCID003', 'CASE WHEN res >= threshold_red THEN 3 WHEN res > threshold_amber THEN 2 ELSE 1 END');

第二步：创建RAG状态计算函数 #

由于每个指标的计算规则存储在tbl_calcs中，需要动态执行这些规则，因此创建一个PL/pgSQL函数来处理：

CREATE OR REPLACE FUNCTION get_rag_status(p_kid VARCHAR(50), p_res NUMERIC(18,4))
RETURNS INTEGER AS $$
DECLARE
    v_calc TEXT;
    v_threshold_red FLOAT;
    v_threshold_amber FLOAT;
    v_rag INTEGER;
BEGIN
    -- 获取当前指标的阈值和计算规则
    SELECT k.threshold_red, k.threshold_amber, c.calc
    INTO v_threshold_red, v_threshold_amber, v_calc
    FROM tbl_ki k
    JOIN tbl_calcs c ON k.calc_id = c.calc_id
    WHERE k.kid = p_kid;

    -- 如果没有找到规则，返回NULL或默认值
    IF NOT FOUND THEN
        RETURN NULL;
    END IF;

    -- 动态执行计算规则
    EXECUTE format('SELECT %s', v_calc)
    INTO v_rag
    USING p_res AS res, v_threshold_red AS threshold_red, v_threshold_amber AS threshold_amber;

    RETURN v_rag;
END;
$$ LANGUAGE plpgsql STABLE;

第三步：创建物化视图 #

创建包含月度聚合、附加字段和RAG状态的物化视图，这里以每月最后一条指标记录为例（可根据需求调整聚合逻辑，如平均值、总和等）：

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_rag_results AS
SELECT
    DATE_TRUNC('month', r.dte) AS report_month,
    r.kid,
    k.ki_nme,
    k.ki_desc,
    k.ki_own,
    r.res,
    get_rag_status(r.kid, r.res) AS rag_status,
    k.threshold_red,
    k.threshold_amber,
    c.calc AS rag_calc_rule
FROM tbl_results r
LEFT JOIN tbl_ki k ON r.kid = k.kid
LEFT JOIN tbl_calcs c ON k.calc_id = c.calc_id
-- 筛选每月最后一条记录（可根据需求修改聚合逻辑）
WHERE r.dte = (
    SELECT MAX(dte)
    FROM tbl_results
    WHERE kid = r.kid AND DATE_TRUNC('month', dte) = DATE_TRUNC('month', r.dte)
)
WITH DATA;

-- 创建唯一索引，支持并发刷新
CREATE UNIQUE INDEX idx_mv_rag_results_month_kid ON mv_rag_results(report_month, kid);

第四步：创建物化视图刷新函数 #

创建用于刷新物化视图的函数：

CREATE OR REPLACE FUNCTION refresh_mv_rag_results()
RETURNS TRIGGER AS $$
BEGIN
    -- 使用CONCURRENTLY避免锁表（需要唯一索引）
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_rag_results;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

第五步：创建源表触发器 #

在三张源表上创建触发器，当数据变化时自动刷新物化视图：

-- 为tbl_results创建触发器
CREATE TRIGGER trigger_refresh_mv_after_results_change
AFTER INSERT OR UPDATE OR DELETE ON tbl_results
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_mv_rag_results();

-- 为tbl_ki创建触发器
CREATE TRIGGER trigger_refresh_mv_after_ki_change
AFTER INSERT OR UPDATE OR DELETE ON tbl_ki
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_mv_rag_results();

-- 为tbl_calcs创建触发器
CREATE TRIGGER trigger_refresh_mv_after_calcs_change
AFTER INSERT OR UPDATE OR DELETE ON tbl_calcs
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_mv_rag_results();

验证与说明 #

1. RAG状态对应：根据tbl_calcs中的规则，1=绿色（正常）、2=黄色（警告）、3=红色（异常），可根据需求调整返回值或映射为文本。
2. 聚合逻辑调整：如果需要其他月度聚合方式（如平均值），可修改物化视图中的查询，例如使用AVG(res)并按report_month, kid分组。
3. 性能考虑：REFRESH MATERIALIZED VIEW CONCURRENTLY会比普通刷新慢，但不会锁表。如果对实时性要求极高，可考虑使用普通刷新，或调整触发器为行级触发（但会增加性能开销）。
4. 异常处理：函数中添加了未找到规则时返回NULL的逻辑，可根据需求改为默认值或抛出错误。

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