物化视图
物化视图 DDL 命令用于创建、刷新、修改和删除预计算存储结果的物化视图对象。
本章内容
常用操作
创建物化视图
-- 创建自动刷新的物化视图
CREATE MATERIALIZED VIEW IF NOT EXISTS public.mv_daily_sales AS
SELECT DATE_TRUNC('day', created_at) AS sale_date,
SUM(amount) AS total_amount,
COUNT(*) AS order_count
FROM public.orders
GROUP BY 1;
-- 指定刷新间隔
CREATE MATERIALIZED VIEW public.mv_category_stats
REFRESH INTERVAL 30 MINUTE
AS
SELECT category, COUNT(*) AS cnt, AVG(price) AS avg_price
FROM public.products
GROUP BY category;
手动刷新
-- 手动触发刷新
REFRESH MATERIALIZED VIEW public.mv_daily_sales;
修改刷新配置
-- 修改刷新间隔（需使用 CREATE OR REPLACE）
CREATE OR REPLACE MATERIALIZED VIEW public.mv_daily_sales
REFRESH INTERVAL 60 MINUTE VCLUSTER DEFAULT
AS SELECT date_trunc('day', order_date) AS day, SUM(amount) AS total FROM orders GROUP BY 1;
-- 暂停自动刷新
ALTER MATERIALIZED VIEW public.mv_daily_sales SUSPEND;
-- 恢复自动刷新
ALTER MATERIALIZED VIEW public.mv_daily_sales RESUME;
查看与管理
-- 查看所有物化视图
SHOW MATERIALIZED VIEWS;
-- 查看物化视图状态
DESC MATERIALIZED VIEW public.mv_daily_sales;
-- 查看建表语句
SHOW CREATE MATERIALIZED VIEW public.mv_daily_sales;
-- 删除物化视图
DROP MATERIALIZED VIEW IF EXISTS public.mv_daily_sales;
相关文档
