pg创建物化视图--实现离线数据分析 之增量处理

CREATE TABLE "public"."book" (
  "book_id" varchar(32)  NOT NULL,
  "book_name" varchar(255) ,
  "book_price" float8,
  "book_type" varchar(255) ,
  "recordtime" timestamp(6),
  CONSTRAINT "book_pkey" PRIMARY KEY ("book_id")
)
;
INSERT INTO "public"."book"("book_id", "book_name", "book_price", "book_type", "recordtime") VALUES ('2000', '高中语文', 5.8, '教材', '2019-12-10 11:32:45');
INSERT INTO "public"."book"("book_id", "book_name", "book_price", "book_type", "recordtime") VALUES ('2001', '高中语文', 5.8, '教材', '2019-12-10 11:32:45');
INSERT INTO "public"."book"("book_id", "book_name", "book_price", "book_type", "recordtime") VALUES ('2002', '高中数学', 7.5, '教材', '2019-12-10 17:32:45');
INSERT INTO "public"."book"("book_id", "book_name", "book_price", "book_type", "recordtime") VALUES ('2003', '高中英语', 10.5, '教材', '2019-12-10 17:33:45');
INSERT INTO "public"."book"("book_id", "book_name", "book_price", "book_type", "recordtime") VALUES ('2004', '高中物理', 10, '教材', '2019-12-10 17:34:47');
INSERT INTO "public"."book"("book_id", "book_name", "book_price", "book_type", "recordtime") VALUES ('2005', '高中化学', 10, '教材', '2019-12-10 17:34:47');
-- 创建物化视图 根据实际逻辑替换下面 select sql即可  SELECT * FROM 函数;
CREATE MATERIALIZED VIEW book_mv as SELECT * FROM PUBLIC.book;
--必须要创建唯一索引才能增量刷新
-- 在实际业务中 新增自增主键即可用于此: id serial8
CREATE UNIQUE INDEX book_mv_index ON PUBLIC.book_mv(book_id);
SELECT *FROM PUBLIC.book_mv;
-- 源表新增一行
INSERT INTO "public"."book"("book_id", "book_name", "book_price", "book_type", "recordtime") VALUES ('2006', '高中生物', 23, '教材', '2019-12-26 14:11:05');
-- 不带CONCURRENTLY即为全量刷新，带CONCURRENTLY即为增量刷新
-- 全量刷新速度较快，但是刷新时会阻塞对book_mv的查询，增量刷新相反
-- 实际业务中选增量刷新，定时执行refresh即可
refresh MATERIALIZED VIEW CONCURRENTLY PUBLIC.book_mv;

SELECT *FROM PUBLIC.book_mv;
-- 删除物化视图
DROP MATERIALIZED VIEW PUBLIC.book_mv;


           

遗憾的是:gp不支持物化视图