物化视图（Materialized View）是通过预先计算来存储查询结果的数据库对象，物化视图将结果集物理化存储在实体表中，查询时直接读取预存数据，大幅提升复杂查询的响应速度，适用于读多写少的场景。

物化视图既在pg_matviews存储视图定义信息，也在pg_class存储实体表定义信息，对应一张物理表，存储物化视图数据。

查询重写时，根据pg_matviews中的视图定义，检查是否可以从物化视图中直接读取预计算数据。

基表数据写入后，可手动或者定时刷新物化视图数据。

名词解释

基表：物化视图中SQL查询的表。

失效：基表发生数据变化后，物化视图会被标记成失效状态。可直查，不可重写。

刷新：将物化视图的SQL重新全量或增量执行，更新物化视图数据。

重写：数据库优化器对SQL的查询重写阶段，将用户SQL等价改写成更高效的形式。

原理介绍

基本原理

1. 数据预计算：基于指定的基础查询语句（可包含关联、聚合、过滤等操作），在物化视图创建/刷新时，将查询结果一次性计算并存储为物理表，数据与基础表解耦。

2. 数据一致性：基础表数据发生变更（增删改）时，物化视图的预存数据会过期，需通过刷新机制同步基础表变化，平衡查询性能与数据实时性。

3. 透明加速：当用户SQL与物化视图SQL匹配时，查询重写会自动选择从物化视图读取数据。无需修改业务SQL，即可实现复杂查询的响应时间，降低系统负载。

简单来说，物化视图是“空间换时间”的典型优化方案，以额外的存储开销和少量的刷新性能损耗，换取复杂查询的毫秒级响应。

功能特点

• 声明式编程：通过物化视图的SQL完成数据加工，不必再编写复杂的存储过程。
• 增量计算：除了表级全量刷新，分区全量刷新，还支持表级增量刷新，分区级增量刷新。对于增量很少的场景，会比全量刷新性能更好。
• pipleline流式加工：将物化试图嵌套使用，即可数据的层层向上流转，完成不同主题域的数据自动加工。
• 跨源查询：物化视图可以从多个不同数据源，将数据定时汇总到视图中。
• 数仓分层：将明细层（DWD），基础层（DWB），汇总层（DWS）和应用服务层，可借用物化视图进行分层管理。

技术优势

• 失效轻量化：基表数据变化后触发物化视图失效，不影响基表并发写入，不重复失效。
• 锁轻量化：基表写入不堵塞刷新，刷新也不堵塞业务查询。
• 可靠失效：CN故障不影响物化视图的失效和刷新。
• 合理级联锁：对失效、刷新和基表DDL，进行统一设计，任意交叉依赖和嵌套，无死锁。
• 自动级联刷新：自底向上级联刷新每一个物化视图，全链路保障数据实时一致。

功能约束

全量刷新

刷新物化视图时，重新执行视图定义中的SQL，将根据最新数据计算的全量结果，替换物化视图中的旧数据。

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] materialized_view_name
[ ( column_name [, ...] ) ] --可选，自定义各列别名
[ BUILD { DEFERRED | IMMEDIATE } ] --可选，创建后再刷新或创建时立即刷新数据
[ REFRESH [ [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ] [ COMPLETE | FAST ] [ ON DEMAND ] 
[ [ START WITH (timestamptz) ] | [ EVERY (interval) ] ] ] --定义数据的定时或级联刷新方式
[ { ENABLE | DISABLE } QUERY REWRITE ] --可选，是否用于查询重写
[ WITH ( {storage_parameter = value} [, ... ] ) ] --表格式相关参数，推荐WITH(orientation=COLUMN, enable_hstore_opt=ON)
[ TABLESPACE tablespace_name ]
[ DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } } ]
[PARTITION BY { 
        {RANGE (partition_key) ( partition_less_than_item [, ... ] )} |
        {RANGE (partition_key) ( partition_start_end_item [, ... ] )}]
AS query;

创建样例：

CREATE MATERIALIZED VIEW test_mv1
REFRESH COMPLETE EVERY (interval '00:01:00') --每分钟刷新一次
ENABLE QUERY REWRITE --开启查询重写
WITH(orientation=COLUMN, enable_hstore_opt=ON) --使用列存hstore_opt存储格式
AS Your_Select;

全量刷新对业务SQL没有限制，刷新时内部通过insert overwrite完成数据写入，仅在最后交换数据文件的瞬间加锁与查询互斥。

数据量本身不大，秒级完成的SQL，建议优先使用全量刷新。

增量刷新

刷新物化视图时，从基表的binlog中抽取增量数据，并根据增量算法计算出增量结果，替换物化视图中的部分数据。

创建样例：

CREATE MATERIALIZED VIEW test_mv1
REFRESH FAST EVERY (interval '00:00:30') --每30秒刷新一次
ENABLE QUERY REWRITE --开启查询重写
WITH(orientation=COLUMN, enable_hstore_opt=ON) --使用列存hstore_opt存储格式
AS Your_Select;

不适用增量刷新的场景

subquery, union all, window agg, cte。

增量刷新会自动转全量的场景

• 基表发生DDL
• 无group by的全表聚合
• 手动强制restrict刷新
• 集群扩容重分布期间的刷新
• 开启了GTM Free模式

增量算法

根据多表JOIN计算出增量合并的部分，不限制JOIN表的数量。

查询重写

单表的点查，可以创建索引进行查询加速；

多表的复杂查询，还可以创建物化视图，实现透明加速，无需改造业务。

相当于省去了SQL的执行时间，直接从物化视图中取数据，大大加速业务。

1. 支持全文匹配的查询重写。

2. 支持查询条件的代数逻辑推理，如果物化视图中的数据能满足查询，则优先使用物化视图的数据。

3. 支持对子查询的物化视图重写。

查询重写原理

选型策略

选择适合物化视图的场景

物化视图本质上，通过“相似归一”的思想，减少重复计算。

一般我们可以从TOPSQL中分析历史SQL，总结归纳相似计算。

• 通过unique_sql_id统计同类语句使用频率，将同类语句中的公共部分提取为物化视图。
• 通过具体大表，按query字段like该表上的SQL，分析近似计算。

往往把物化视图提取的更基础，更简单，复用频率更高，越能带来更大的收益。

设计合理，减少了重复计算，可以降本增效；反之，可能增加系统负担。

通过pv_matview_detail视图，可以评估物化视图使用效率。

选择合理的物化视图定义

系统会根据物化视图SQL查询的表头，自动推导物化视图的列名，但还是建议给每列指定列名，避免后面调优需要修改列名。

物化视图表级属性

• bitmap_columns 指定bitmap列，可以给物化视图创建bitmap索引，加速使用视图的查询。
• excluded_inactive_tables 指定哪些表的修改，不触发物化视图失效。例如一些维度表的变化，如果对视图结果无影响，可以进行设置。
• mv_analyze_mode; 控制物化视图刷新后自动analyze方式，一般自动做，且不堵塞查询
• mv_computing_resource 给物化视图刷新指定的弹性资源池 
• mv_incremental_mode; 指定增量物化视图的格式
• mv_pck_column; --指定pck列，给物化视图创建PCK，加速使用视图的查询。
• mv_refresh_guc; --刷新使用的GUC参数，可以设定各种GUC参数，确保刷新的计划稳定，例如禁止nestloop等。
• mv_support_function_type; --开启物化视图创建时查询语句中可以使用的函数属性

如果对物化视图上的查询性能要求较高，可以利用mv_pck_column，bitmap_columns进行优化。

如果需要使用不稳定函数，可以设置mv_support_function_type

如果需要给物化视图单独设置GUC参数，可以使用mv_refresh_guc

选择合适的存储格式

存储格式

根据业务使用场景，选择行存或列存。

分布方式

根据物化视图的结果选择合理的分布方式。

设计合理的刷新方式

刷新方式

选全量刷新，还是增量刷新？

自动刷新间隔

可根据业务实际的实时性要求，按天，小时，30min，分钟，秒的粒度安排物化视图的自动刷新间隔。

• 跑批业务可以手动刷新，或者按小时级刷新。
• 实时业务可以按分钟或秒级刷新。
  

  

  
  

最好能错峰刷新，防止集中刷新导致CPU过高。可以通过START WITH和EVERY进行控制。

实时性需要与业务需求相匹配，过度频繁的刷新会消耗太多系统资源。

刷新级联

嵌套使用的物化视图，建议级联刷新的方式，自底向上一次完成整个链路的刷新。减少各层非必要的重复刷新。

底层物化视图设置cascade和erery定时，上层物化视图不设置定时即可。

刷新稳定性

如果SQL比较复杂，存在计划不稳定的可能性，建议通过hint将计划固定住。防止刷新的计划跳变导致刷新时间超过刷新间隔，出现积压。

注意事项总结

• 防CPU突高：刷新任务错峰编排，避免集中刷新。由START WITH和EVERY进行设定。
• 防计划跳变：执行计划尽量hint固定，将查询计划调至最优，减少计划跳变对轮询刷新的影响。 
• 防重复刷新：按数据流管道级联刷新，减少重复刷新。CASCADE BACKWARD控制。
• 最佳的性能：建议使用hstore_opt格式
• 合理的间隔：顶层视图刷新间隔，必须大于下层最大间隔，避免数据快照不一致。

下层mv1每5分钟刷新一次，上层mv2每2分钟刷新一次，上层刷新间隔小于下层，上层即使刷新看到的也是下层旧的数据。

实践案例

业务场景

按不同的业务场景，可以分为以下几类物化视图。

聚合物化视图

主要解决聚合加速问题。

CREATE MATERIALIZED VIEW mv_order_region_stats
AS
SELECT
    region,                          -- 聚合维度：地区
    COUNT(*) AS order_count,         -- 指标1：该地区订单总数
    SUM(order_amount) AS total_amount -- 指标2：该地区订单总金额
FROM t_order
GROUP BY region; -- 单表聚合，无多表关联

JOIN物化视图

主要解决多表关联加速问题。

CREATE MATERIALIZED VIEW mv_user_order_stats
AS
SELECT
    u.user_level,
    u.region,
    COUNT(o.order_id) AS order_count,
    SUM(o.order_amount) AS total_amount
FROM t_order o
INNER JOIN t_user u ON o.user_id = u.user_id
GROUP BY u.user_level, u.region;

分区物化视图

主要解决超大事实表的查询加速问题。

--创建事实表
CREATE TABLE fact_table (start_time TIMESTAMPTZ NOT NULL, id int)
PARTITION BY RANGE(start_time) (
    PARTITION p1 VALUES LESS THAN ('2024-01-01'),
    PARTITION p2 VALUES LESS THAN ('2024-02-01'),
    PARTITION p3 VALUES LESS THAN ('2024-03-01'),
    PARTITION p4 VALUES LESS THAN ('2024-04-01'),
    PARTITION p5 VALUES LESS THAN ('2024-05-01'),
    PARTITION p6 VALUES LESS THAN ('2024-06-01')
);

--创建维度表
CREATE TABLE dimen_table (start_time TIMESTAMPTZ NOT NULL, id INT);

--等比例方式，创建与事实表分区完全一样的分区物化视图
CREATE MATERIALIZED VIEW level_normal enable query rewrite DISTRIBUTE BY HASH(start_time) PARTITION BY start_time
AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;

--按天上卷方式，根据事实表start_time分区键，创建分区物化视图
CREATE MATERIALIZED VIEW level_day enable query rewrite DISTRIBUTE BY HASH(start_time) PARTITION BY date_trunc('day', start_time)
AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;

--按月上卷方式，根据事实表start_time分区键，创建分区物化视图
CREATE MATERIALIZED VIEW level_month enable query rewrite DISTRIBUTE BY HASH(start_time) PARTITION BY date_trunc('month', start_time)
AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;

--按年上卷方式，根据事实表start_time分区键，创建分区物化视图
CREATE MATERIALIZED VIEW level_year enable query rewrite DISTRIBUTE BY HASH(start_time) PARTITION BY date_trunc('year', start_time)
AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;

刷新时无需指定分区，内核会自动识别需要刷新的失效分区。

为了解决分区刷新实时性问题，可以设置 mv_refresh_parts_per_trans 由近及远分批刷新提交，快速完成最近分区的刷新。

如果需要手动快速干预，也可以直接手动指定分区刷新。手动与自动不冲突，一方在进行另一方会自动跳过，一方完成另一方拿到锁后也会自动跳过，防止重复浪费资源。

嵌套物化视图

主要解决实时数据流或相似查询统一问题。

CREATE TABLE t_user (
    user_id BIGSERIAL PRIMARY KEY, 
    user_name VARCHAR(32) NOT NULL,
    user_level VARCHAR(10) NOT NULL,
    region VARCHAR(20) NOT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE t_order (
    order_id BIGSERIAL PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT NOT NULL,
    order_amount NUMERIC(10,2) NOT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT NOW(),
);

第一层物化视图，按照用户等级和地区，计算订单总数和总金额

CREATE MATERIALIZED VIEW mv_user_order_stats
AS
SELECT
    u.user_level,
    u.region,
    COUNT(o.order_id) AS order_count,
    SUM(o.order_amount) AS total_amount
FROM t_order o
INNER JOIN t_user u ON o.user_id = u.user_id
GROUP BY u.user_level, u.region;

第二层物化视图，按照用户等级全局聚合

CREATE MATERIALIZED VIEW mv_user_level_global_stats
AS
SELECT
    user_level,
    SUM(order_count) AS total_order_count,
    SUM(total_amount) AS total_order_amount
FROM mv_user_order_stats
GROUP BY user_level;

第二层物化视图，按照地区全局聚合

CREATE MATERIALIZED VIEW mv_region_global_stats
AS
SELECT
    region,
    SUM(order_count) AS total_order_count,
    SUM(total_amount) AS total_order_amount
FROM mv_user_order_stats
GROUP BY region;

既需要按地区统计订单总数和总金额，又需要按用户统计订单总数和总金额。

先创建一个按照“地区和用户”聚合的中间层物化视图，再分别按地区和用户聚合，这样省去了基表的重复关联计算。

行业案例

游戏行业战力排名

-- 游戏玩家战力核心表（500万级数据，优化字段+基础索引）
CREATE TABLE game_player_combat (
    player_id BIGSERIAL PRIMARY KEY,       -- 玩家唯一ID（主键，自增）
    player_name VARCHAR(32) NOT NULL,      -- 玩家角色名（不可重复）
    server_id INT NOT NULL,                -- 服务器ID（1~20服，区分不同服务器）
    profession VARCHAR(20) NOT NULL,       -- 职业（战士/法师/刺客/牧师/弓手）
    combat_power BIGINT NOT NULL,          -- 战力值（排名核心，1万~200万）
    title VARCHAR(20) NOT NULL DEFAULT '见习', -- 头衔（按战力梯度分配）
    level INT NOT NULL,                    -- 玩家等级（1~150级）
    update_source VARCHAR(30) NOT NULL,    -- 战力更新来源（装备强化/技能升级/宠物进阶）
    last_update_time TIMESTAMP NOT NULL DEFAULT NOW(), -- 战力最后更新时间
    is_online BOOLEAN NOT NULL DEFAULT FALSE -- 是否在线（运营统计用）
);

-- 玩家战力通用物化视图（支撑玩家榜单+运营统计，单表聚合，精简字段）
CREATE MATERIALIZED VIEW mv_game_player_combat_all
AS
SELECT
    player_id,          -- 玩家ID（主键，用于增量刷新、个人排名查询）
    player_name,        -- 角色名（榜单展示）
    server_id,          -- 服务器ID（分服查询、分段统计）
    profession,         -- 职业（可选：分职业榜单扩展）
    combat_power,       -- 战力值（排名核心、分段统计核心）
    title,              -- 头衔（榜单展示、头衔统计）
    last_update_time,   -- 战力更新时间（玩家查看、运营排查延迟）
    -- 新增：战力分段（提前预计算，避免运营查询时重复计算，核心优化）
    CASE
        WHEN combat_power < 100000 THEN '10万以下'
        WHEN combat_power < 300000 THEN '10-30万'
        WHEN combat_power < 500000 THEN '30-50万'
        WHEN combat_power < 800000 THEN '50-80万'
        ELSE '80万以上'
    END AS combat_segment -- 战力分段（运营统计直接使用）
FROM game_player_combat;

-- 全服战力TOP100（游戏首页榜单，展示核心信息，按战力降序）
SELECT
    ROW_NUMBER() OVER(ORDER BY combat_power DESC) AS all_server_rank, -- 全服排名
    player_name,
    server_id,
    combat_power,
    title
FROM mv_game_player_combat_all
ORDER BY combat_power DESC
LIMIT 100;

广告行业精准投流

业务诉求：广告行业的核心痛点是 海量日志数据的高频多维度聚合查询—— 广告曝光、点击、转化日志单日可达亿级，直接基于原始日志表做「广告主 / 渠道 / 时段 / 地域」的多维度统计，会触发全表扫描，查询耗时长达分钟级，无法支撑实时投放优化、报表展示等业务需求。

解决方案：物化视图的核心价值是 提前预计算 + 物理存储聚合结果，将多维度统计查询的响应时间从分钟级降至毫秒级，完美适配广告行业「准实时统计 + 高频报表」的核心需求。

基础日志表

-- 广告曝光点击转化日志表（核心基表，亿级数据量）
CREATE TABLE ad_imp_click_log (
    log_id BIGSERIAL PRIMARY KEY,       -- 日志唯一ID
    ad_id BIGINT NOT NULL,              -- 广告ID
    advertiser_id BIGINT NOT NULL,      -- 广告主ID
    channel_id BIGINT NOT NULL,         -- 投放渠道ID（如抖音/微信/头条）
    region VARCHAR(20) NOT NULL,        -- 投放地域（省/市）
    imp_time TIMESTAMP NOT NULL,        -- 曝光时间
    click_time TIMESTAMP NULL,          -- 点击时间（NULL表示未点击）
    convert_time TIMESTAMP NULL,        -- 转化时间（NULL表示未转化）
    cost DECIMAL(10,2) NOT NULL,        -- 单条曝光消耗（元）
    device_type VARCHAR(10) NOT NULL    -- 设备类型（安卓/ios/pc）
);

场景一 广告主实时查看广告指标（单表聚合物化视图）

-- 广告主日投放效果物化视图（单表聚合，核心指标预计算）
CREATE MATERIALIZED VIEW mv_advertiser_daily_stats
AS
SELECT
    advertiser_id,                      -- 广告主ID
    DATE(imp_time) AS stat_date,        -- 统计日期
    COUNT(*) AS imp_count,              -- 曝光量
    COUNT(click_time) AS click_count,   -- 点击量
    COUNT(convert_time) AS convert_count, -- 转化量
    SUM(cost) AS total_cost,            -- 总消耗（元）
    ROUND(COUNT(click_time)::FLOAT / NULLIF(COUNT(*), 0) * 100, 2) AS ctr,  -- 点击率（避免除以0，用NULLIF）
    ROUND(COUNT(convert_time)::FLOAT / NULLIF(COUNT(click_time), 0) * 100, 2) AS cvr  -- 转化率（点击→转化）
FROM ad_imp_click_log
GROUP BY advertiser_id, DATE(imp_time); -- 聚合维度：广告主+日期

--使用物化视图
--广告主（ID=10）查询近 7 天投放效果，直接从物化视图获取，无需扫描亿级日志
SELECT
    stat_date,
    imp_count,
    click_count,
    convert_count,
    total_cost,
    ctr || '%' AS ctr,
    cvr || '%' AS cvr
FROM mv_advertiser_daily_stats
WHERE advertiser_id = 10 AND stat_date >= CURRENT_DATE - 7
ORDER BY stat_date DESC;

场景二 广告运营监控投放效果优化决策（JOIN物化视图）

-- 渠道信息表（维度表）
CREATE TABLE ad_channel_info (
    channel_id BIGINT PRIMARY KEY,      -- 渠道ID
    channel_name VARCHAR(50) NOT NULL,  -- 渠道名称（如抖音/微信/头条）
    channel_type VARCHAR(20) NOT NULL   -- 渠道类型（信息流/搜索/社交）
);

-- 渠道-地域-设备多维度效果物化视图（双表JOIN+多维度聚合）
CREATE MATERIALIZED VIEW mv_channel_region_device_stats
AS
SELECT
    c.channel_id,
    c.channel_name,
    c.channel_type,
    l.region,
    l.device_type,
    DATE(l.imp_time) AS stat_date,
    COUNT(*) AS imp_count,
    COUNT(l.click_time) AS click_count,
    SUM(l.cost) AS total_cost
FROM ad_imp_click_log l
INNER JOIN ad_channel_info c ON l.channel_id = c.channel_id
GROUP BY c.channel_id, c.channel_name, c.channel_type, l.region, l.device_type, DATE(l.imp_time);

--运营查询抖音渠道在北京市的安卓设备近 3 天投放效果：
SELECT
    stat_date,
    imp_count,
    click_count,
    total_cost,
    ROUND(click_count::FLOAT / imp_count * 100, 2) AS ctr
FROM mv_channel_region_device_stats
WHERE channel_name = '抖音' AND region = '北京' AND device_type = '安卓' AND stat_date >= CURRENT_DATE - 3
ORDER BY stat_date DESC;

场景三 广告投放周度趋势分析（嵌套物化视图）

-- 广告主-渠道周度投放趋势物化视图（嵌套视图，基于日统计视图汇总）
CREATE MATERIALIZED VIEW mv_advertiser_channel_weekly_stats
AS
SELECT
    a.advertiser_id,
    l.channel_id,
    c.channel_name,
    DATE_TRUNC('week', a.stat_date) AS stat_week, -- 按周聚合（周一为每周第一天）
    SUM(a.imp_count) AS weekly_imp_count,
    SUM(a.click_count) AS weekly_click_count,
    SUM(a.total_cost) AS weekly_total_cost
FROM mv_advertiser_daily_stats a
INNER JOIN ad_imp_click_log l ON a.advertiser_id = l.advertiser_id AND a.stat_date = DATE(l.imp_time)
INNER JOIN ad_channel_info c ON l.channel_id = c.channel_id
GROUP BY a.advertiser_id, l.channel_id, c.channel_name, DATE_TRUNC('week', a.stat_date);

--广告主（ID=10）查询在抖音渠道近 4 周的投放趋势：
SELECT
    TO_CHAR(stat_week, 'YYYY-MM-DD') AS week_start,
    weekly_imp_count,
    weekly_click_count,
    weekly_total_cost
FROM mv_advertiser_channel_weekly_stats
WHERE advertiser_id = 10 AND channel_name = '抖音'
ORDER BY stat_week DESC
LIMIT 4;

金融领域风控

--交易流水表（核心事实表，亿级数据），存储用户所有支付 / 转账交易记录，是反欺诈、交易监控的核心数据源。
CREATE TABLE risk_transaction (
    trans_id BIGSERIAL PRIMARY KEY,      -- 交易唯一ID
    user_id BIGINT NOT NULL,             -- 用户ID
    merchant_id BIGINT NOT NULL,         -- 商户ID
    trans_amount DECIMAL(15,2) NOT NULL, -- 交易金额
    trans_type VARCHAR(20) NOT NULL,     -- 交易类型：消费/转账/提现
    trans_time TIMESTAMP NOT NULL,       -- 交易时间
    trans_region VARCHAR(20) NOT NULL,   -- 交易地域（省/市/境外）
    device_id VARCHAR(64) NOT NULL,      -- 交易设备ID
    is_success BOOLEAN NOT NULL,         -- 交易是否成功
    risk_label VARCHAR(10) DEFAULT 'normal' -- 风险标签：normal/abnormal/suspicious
);

--用户信贷信息表（维度表），存储用户信贷资质、额度、逾期记录，支撑信贷审批风险评分。
CREATE TABLE risk_user_credit (
    user_id BIGSERIAL PRIMARY KEY,       -- 用户ID
    credit_score INT NOT NULL,           -- 征信评分：0~1000
    loan_limit DECIMAL(15,2) NOT NULL,   -- 授信额度
    overdue_count INT DEFAULT 0,         -- 逾期次数
    overdue_amount DECIMAL(15,2) DEFAULT 0.00, -- 逾期金额
    update_time TIMESTAMP NOT NULL DEFAULT NOW() -- 信息更新时间
);

场景一 实时交易反欺诈监控（单表聚合物化视图）

-- 用户交易监控物化视图：预计算用户1小时粒度的交易指标
CREATE MATERIALIZED VIEW mv_risk_user_trans_stats
AS
SELECT
    user_id,
    DATE_TRUNC('hour', trans_time) AS trans_hour, -- 按小时聚合
    trans_region,
    device_id,
    COUNT(*) AS trans_count,              -- 该时段交易次数
    SUM(trans_amount) AS total_amount,    -- 该时段交易总金额
    MAX(trans_amount) AS max_single_amount, -- 单笔最大金额
    COUNT(CASE WHEN is_success = TRUE THEN 1 END) AS success_count -- 成功交易次数
FROM risk_transaction
GROUP BY user_id, DATE_TRUNC('hour', trans_time), trans_region, device_id;

-- 假设新交易用户ID=10086，交易时间=当前时间，地域=上海，设备ID=device_12345
WITH user_current_stats AS (
    SELECT
        trans_count,
        total_amount,
        max_single_amount
    FROM mv_risk_user_trans_stats
    WHERE user_id = 10086 
      AND trans_hour = DATE_TRUNC('hour', NOW())
      AND trans_region = '上海'
), user_credit AS (
    SELECT loan_limit FROM risk_user_credit WHERE user_id = 10086
)
SELECT
    CASE 
        WHEN ucs.trans_count > 10 THEN '高频交易异常'
        WHEN ucs.total_amount > uc.loan_limit * 0.5 THEN '大额交易异常'
        ELSE '正常'
    END AS risk_result
FROM user_current_stats ucs, user_credit uc;

 场景二 用户信贷审批风险评分（JOIN 物化视图）

-- 用户信贷风险物化视图：关联用户信贷表和交易表，预计算风险指标
CREATE MATERIALIZED VIEW mv_risk_user_credit_score
AS
SELECT
    uc.user_id,
    uc.credit_score,
    uc.overdue_count,
    uc.overdue_amount,
    uc.loan_limit,
    -- 近7天提现总金额
    COALESCE(SUM(CASE WHEN t.trans_type = '提现' AND t.trans_time >= NOW() - INTERVAL '7 days' THEN t.trans_amount ELSE 0 END), 0) AS recent_7d_cash_amount,
    -- 近7天交易总次数
    COALESCE(COUNT(t.trans_id), 0) AS recent_7d_trans_count,
    -- 预计算风险评分
    ROUND(
        uc.credit_score * 0.6 - uc.overdue_count * 50 - (COALESCE(SUM(CASE WHEN t.trans_type = '提现' THEN t.trans_amount ELSE 0 END), 0) / NULLIF(uc.loan_limit, 0)) * 200
    ) AS final_risk_score,
    NOW() AS calc_time -- 评分计算时间
FROM risk_user_credit uc
LEFT JOIN risk_transaction t ON uc.user_id = t.user_id
GROUP BY uc.user_id, uc.credit_score, uc.overdue_count, uc.overdue_amount, uc.loan_limit;

-- 查询用户ID=10086的信贷风险评分
SELECT
    user_id,
    credit_score,
    overdue_count,
    recent_7d_cash_amount,
    final_risk_score,
    -- 风险等级判定
    CASE
        WHEN final_risk_score >= 800 THEN '低风险（通过）'
        WHEN final_risk_score >= 600 THEN '中风险（人工审核）'
        ELSE '高风险（拒绝）'
    END AS approval_result
FROM mv_risk_user_credit_score
WHERE user_id = 10086;

场景三 商户违规趋势分析（嵌套物化视图）

-- 商户交易基础物化视图：按商户+日+交易类型聚合
CREATE MATERIALIZED VIEW mv_risk_merchant_daily_stats
AS
SELECT
    merchant_id,
    DATE(trans_time) AS trans_date,
    trans_type,
    COUNT(*) AS trans_count,
    SUM(trans_amount) AS total_amount,
    -- 异常交易占比（风险标签为abnormal的交易）
    ROUND(COUNT(CASE WHEN risk_label = 'abnormal' THEN 1 END)::FLOAT / NULLIF(COUNT(*), 0) * 100, 2) AS abnormal_rate
FROM risk_transaction
GROUP BY merchant_id, DATE(trans_time), trans_type;

-- 商户周度违规趋势嵌套物化视图
CREATE MATERIALIZED VIEW mv_risk_merchant_weekly_trend
AS
SELECT
    merchant_id,
    DATE_TRUNC('week', trans_date) AS trans_week, -- 按周聚合
    trans_type,
    SUM(trans_count) AS weekly_trans_count,
    SUM(total_amount) AS weekly_total_amount,
    AVG(abnormal_rate) AS weekly_avg_abnormal_rate -- 周度平均异常率
FROM mv_risk_merchant_daily_stats
GROUP BY merchant_id, DATE_TRUNC('week', trans_date), trans_type;

--运营查询某商户（ID=1234）近 4 周的套现风险趋势：
SELECT
    TO_CHAR(trans_week, 'YYYY-MM-DD') AS week_start,
    trans_type,
    weekly_trans_count,
    weekly_avg_abnormal_rate || '%' AS abnormal_rate,
    -- 风险趋势判定
    CASE
        WHEN weekly_avg_abnormal_rate > LAG(weekly_avg_abnormal_rate) OVER(PARTITION BY merchant_id ORDER BY trans_week) THEN '上升'
        WHEN weekly_avg_abnormal_rate < LAG(weekly_avg_abnormal_rate) OVER(PARTITION BY merchant_id ORDER BY trans_week) THEN '下降'
        ELSE '持平'
    END AS trend
FROM mv_risk_merchant_weekly_trend
WHERE merchant_id = 1234 AND trans_type = '提现'
ORDER BY trans_week DESC
LIMIT 4;

物联网设备监控

解决方案：物化视图通过预计算设备采集核心指标（在线率、平均采集值、异常频次、能耗总量），将查询响应时间降至毫秒级，同时结合物联网设备数据的「时序性」特点，设计专属刷新策略，完美适配物联网「高并发采集、低延迟查询、准实时统计」的业务需求。本案例覆盖工业物联网最典型的3个场景，所有SQL可直接复制运行，适配10万~1000万级设备规模。

-- 物联网设备信息表（维度表，存储设备基础信息，更新频率低）
CREATE TABLE iot_device_info (
    device_id BIGSERIAL PRIMARY KEY,       -- 设备唯一ID（主键，自增）
    device_name VARCHAR(64) NOT NULL,      -- 设备名称（如温度传感器-车间A-01）
    device_type VARCHAR(30) NOT NULL,      -- 设备类型（温度传感器/电压传感器/能耗仪表）
    area VARCHAR(50) NOT NULL,             -- 设备部署区域（如车间A/办公楼B/仓库C）
    threshold_min DECIMAL(10,2),           -- 采集指标最小值阈值（异常判断用）
    threshold_max DECIMAL(10,2),           -- 采集指标最大值阈值（异常判断用）
    install_time TIMESTAMP NOT NULL,       -- 设备安装时间
    is_active BOOLEAN NOT NULL DEFAULT TRUE, -- 设备是否启用
    last_online_time TIMESTAMP             -- 设备最后在线时间
);

-- 物联网传感器采集数据表（事实表，时序数据，按采集时间分区）
CREATE TABLE iot_sensor_collect (
    collect_id BIGSERIAL PRIMARY KEY,      -- 采集记录唯一ID
    device_id BIGINT NOT NULL,             -- 关联设备ID
    collect_time TIMESTAMP NOT NULL,       -- 采集时间（时序核心字段）
    collect_value DECIMAL(10,2) NOT NULL,  -- 采集指标值（如温度25.5℃、电压220.3V）
    signal_strength INT NOT NULL,          -- 信号强度（0~100，越低越差）
    is_abnormal BOOLEAN NOT NULL DEFAULT FALSE, -- 是否异常（采集值超阈值）
    collect_status VARCHAR(20) NOT NULL DEFAULT 'success', -- 采集状态（success/fail）
    -- 关联设备信息表，提前标记异常（简化物化视图计算）
    FOREIGN KEY (device_id) REFERENCES iot_device_info (device_id)
) PARTITION BY RANGE (DATE(collect_time)); -- 按采集日期分区（IOT时序数据核心优化）

场景一 设备实时状态监控（单表聚合物化视图）

-- 设备实时状态物化视图（按设备+小时聚合，时序核心视图）
CREATE MATERIALIZED VIEW mv_iot_device_real_time
AS
SELECT
    device_id,
    DATE_TRUNC('hour', collect_time) AS collect_hour, -- 按小时聚合（适配实时监控）
    COUNT(*) AS collect_count,              -- 该小时采集次数
    SUM(CASE WHEN collect_status = 'success' THEN 1 END) AS success_count, -- 采集成功次数
    SUM(CASE WHEN is_abnormal = TRUE THEN 1 END) AS abnormal_count, -- 异常采集次数
    AVG(collect_value) AS avg_collect_value, -- 平均采集值
    MAX(collect_value) AS max_collect_value, -- 最大采集值
    MIN(collect_value) AS min_collect_value, -- 最小采集值
    AVG(signal_strength) AS avg_signal_strength, -- 平均信号强度
    -- 设备在线状态（采集成功次数≥该小时理论采集次数的80%，视为在线）
    CASE WHEN SUM(CASE WHEN collect_status = 'success' THEN 1 END) / COUNT(*) >= 0.8 THEN 'online' ELSE 'offline' END AS device_status
FROM iot_sensor_collect
GROUP BY device_id, DATE_TRUNC('hour', collect_time);

-- 示例：查询设备ID=1001（能耗仪表）近24小时的实时状态和采集指标
SELECT
    TO_CHAR(collect_hour, 'YYYY-MM-DD HH24:00') AS 采集时段,
    collect_count AS 采集次数,
    success_count AS 成功次数,
    abnormal_count AS 异常次数,
    ROUND(avg_collect_value, 2) AS 平均能耗,
    device_status AS 设备状态
FROM mv_iot_device_real_time
WHERE device_id = 1001
  AND collect_hour >= NOW() - INTERVAL '24 hours'
ORDER BY collect_hour DESC;

场景二 区域设备能耗统计（JOIN聚合物化视图）

-- 区域设备能耗统计物化视图（关联设备表和采集表，仅统计能耗仪表数据）
CREATE MATERIALIZED VIEW mv_iot_area_energy_stats
AS
SELECT
    d.area,                  -- 设备区域
    d.device_type,           -- 设备类型（仅能耗仪表）
    DATE_TRUNC('hour', c.collect_time) AS collect_hour, -- 按小时聚合
    COUNT(DISTINCT d.device_id) AS device_count, -- 该区域该类型设备数量
    SUM(c.collect_value) AS total_energy, -- 该小时能耗总量
    AVG(c.collect_value) AS avg_energy_per_device, -- 单设备平均能耗
    -- 能耗同比（与昨日同一时段对比）
    SUM(c.collect_value) - LAG(SUM(c.collect_value), 24) OVER(PARTITION BY d.area, d.device_type ORDER BY DATE_TRUNC('hour', c.collect_time)) AS energy_yoy
FROM iot_sensor_collect c
INNER JOIN iot_device_info d ON c.device_id = d.device_id
WHERE d.device_type = '能耗仪表' -- 仅统计能耗仪表
GROUP BY d.area, d.device_type, DATE_TRUNC('hour', c.collect_time);

-- 示例：查询车间A近1小时的严重异常设备（critical等级）
SELECT
    TO_CHAR(warning_hour, 'YYYY-MM-DD HH24:00') AS 预警时段,
    abnormal_device_count AS 严重异常设备数,
    total_abnormal_count AS 总异常次数,
    abnormal_device_ratio || '%' AS 异常设备占比
FROM mv_iot_abnormal_warning_summary
WHERE area = '车间A'
  AND abnormal_level = 'critical'
  AND warning_hour = DATE_TRUNC('hour', NOW() - INTERVAL '1 hour');

场景三 设备异常预警统计（嵌套物化视图）

-- 第一步：底层异常明细视图（基于实时状态视图，聚合异常数据）
CREATE MATERIALIZED VIEW mv_iot_device_abnormal_detail
AS
SELECT
    device_id,
    collect_hour,
    abnormal_count,
    avg_collect_value,
    device_status,
    -- 异常等级（按异常次数划分）
    CASE
        WHEN abnormal_count / collect_count >= 0.5 THEN 'critical' -- 严重异常（50%以上采集异常）
        WHEN abnormal_count / collect_count >= 0.2 THEN 'warning' -- 警告（20%~50%采集异常）
        ELSE 'normal' -- 正常（＜20%采集异常）
    END AS abnormal_level
FROM mv_iot_device_real_time
WHERE abnormal_count > 0; -- 仅保留有异常的记录

-- 第二步：上层异常预警汇总视图（嵌套聚合，按区域+异常等级汇总）
CREATE MATERIALIZED VIEW mv_iot_abnormal_warning_summary
AS
SELECT
    d.area,
    ad.abnormal_level,
    DATE_TRUNC('hour', ad.collect_hour) AS warning_hour,
    COUNT(DISTINCT ad.device_id) AS abnormal_device_count, -- 异常设备数量
    SUM(ad.abnormal_count) AS total_abnormal_count, -- 总异常次数
    -- 异常设备占比（该区域总设备数）
    ROUND(COUNT(DISTINCT ad.device_id)::FLOAT / (SELECT COUNT(*) FROM iot_device_info WHERE area = d.area) * 100, 2) AS abnormal_device_ratio
FROM mv_iot_device_abnormal_detail ad
INNER JOIN iot_device_info d ON ad.device_id = d.device_id
GROUP BY d.area, ad.abnormal_level, DATE_TRUNC('hour', ad.collect_hour);

-- 示例：查询所有区域今日（00:00至今）的能耗统计，按能耗总量排序
SELECT
    area AS 区域,
    SUM(total_energy) AS 今日总能耗,
    ROUND(AVG(avg_energy_per_device), 2) AS 单设备平均能耗,
    ROUND(AVG(energy_yoy), 2) AS 平均能耗同比
FROM mv_iot_area_energy_stats
WHERE collect_hour >= DATE_TRUNC('day', NOW())
GROUP BY area
ORDER BY 今日总能耗 DESC;