【sql】PostgreSQL物化视图表使用案例

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版,通用型 2核8GB 50GB
简介: 【sql】PostgreSQL物化视图表使用案例

代码

创建物化视图表


create materialized view sel_eth_txn_info_recent_30_day as
select
  *
from
  sel_eth_txn_info set2  
where
  date(to_timestamp(block_time / 1000)::text) >= CURRENT_DATE - interval '30 days'
with no data;


指定索引

CREATE UNIQUE INDEX sel_eth_txn_info_recent_30_day_id ON sel_eth_txn_info_recent_30_day(id);

更新数据

REFRESH MATERIALIZED VIEW CONCURRENTLY sel_eth_txn_info_recent_30_day; -- 边刷新、边查
REFRESH MATERIALIZED VIEW sel_eth_txn_info_recent_30_day;

建立索引 & 建立触发器

---
CREATE INDEX  ON public.sel_eth_txn_info_recent_30_day USING btree (block_time);
CREATE INDEX  ON public.sel_eth_txn_info_recent_30_day USING btree (contract_address);
CREATE INDEX  ON public.sel_eth_txn_info_recent_30_day USING btree (from_address);
CREATE INDEX  ON public.sel_eth_txn_info_recent_30_day USING btree (to_address);
CREATE INDEX  ON public.sel_eth_txn_info_recent_30_day USING btree (transaction_hash);
CREATE INDEX  ON public.sel_eth_txn_info_recent_30_day USING btree (transaction_type);
CREATE INDEX  ON public.sel_eth_txn_info_recent_30_day USING btree (value);
CREATE INDEX  ON public.sel_eth_txn_info_recent_30_day USING btree (block_number);
---
CREATE OR REPLACE FUNCTION update_sel_eth_txn_info_recent_30_day()
RETURNS TRIGGER AS $$
BEGIN
  IF (TG_OP = 'INSERT') THEN
    IF (NEW.block_time / 1000 >= CURRENT_DATE - INTERVAL '30 days') THEN
      INSERT INTO sel_eth_txn_info_recent_30_day VALUES (NEW.*);
    END IF;
    -- delete expired data
    DELETE FROM sel_eth_txn_info_recent_30_day WHERE block_time / 1000 < CURRENT_DATE - INTERVAL '30 days';
  ELSIF (TG_OP = 'UPDATE') THEN
    IF (NEW.block_time / 1000 >= CURRENT_DATE - INTERVAL '30 days') THEN
      UPDATE sel_eth_txn_info_recent_30_day SET block_time = NEW.block_time, 
    block_number = NEW.block_number,
    transaction_hash = NEW.transaction_hash,
    contract_address = NEW.contract_address,
    from_address = NEW.from_address,
    value = NEW.value,
    transaction_type = NEW.transaction_type WHERE id = NEW.id;
    ELSE
      DELETE FROM sel_eth_txn_info_recent_30_day WHERE id = NEW.id;
    END IF;
  ELSIF (TG_OP = 'DELETE') THEN
    DELETE FROM sel_eth_txn_info_recent_30_day WHERE id = OLD.id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;
---
CREATE TRIGGER update_sel_eth_txn_info_recent_30_day
AFTER INSERT OR UPDATE OR DELETE ON sel_eth_txn_info
FOR EACH ROW EXECUTE PROCEDURE update_sel_eth_txn_info_recent_30_day();
---
REFRESH MATERIALIZED VIEW sel_eth_txn_info_recent_30_day;



相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍如何基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
11天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
|
11天前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格:MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型,包括关系型与NoSQL,主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起,支持按需付费与优惠套餐,适用于各类应用场景。
|
6月前
|
SQL 数据库 数据安全/隐私保护
数据库数据恢复——sql server数据库被加密的数据恢复案例
SQL server数据库数据故障: SQL server数据库被加密,无法使用。 数据库MDF、LDF、log日志文件名字被篡改。 数据库备份被加密,文件名字被篡改。
|
17天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
|
4月前
|
SQL 关系型数据库 MySQL
Go语言数据库编程:使用 `database/sql` 与 MySQL/PostgreSQL
Go语言通过`database/sql`标准库提供统一数据库操作接口,支持MySQL、PostgreSQL等多种数据库。本文介绍了驱动安装、连接数据库、基本增删改查操作、预处理语句、事务处理及错误管理等内容,涵盖实际开发中常用的技巧与注意事项,适合快速掌握Go语言数据库编程基础。
289 62
|
4月前
|
SQL 关系型数据库 PostgreSQL
CTE vs 子查询:深入拆解PostgreSQL复杂SQL的隐藏性能差异
本文深入探讨了PostgreSQL中CTE(公共表表达式)与子查询的选择对SQL性能的影响。通过分析两者底层机制,揭示CTE的物化特性及子查询的优化融合优势,并结合多场景案例对比执行效率。最终给出决策指南,帮助开发者根据数据量、引用次数和复杂度选择最优方案,同时提供高级优化技巧和版本演进建议,助力SQL性能调优。
330 1
|
SQL 数据库
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
SQL Server附加数据库出现错误823,附加数据库失败。数据库没有备份,无法通过备份恢复数据库。 SQL Server数据库出现823错误的可能原因有:数据库物理页面损坏、数据库物理页面校验值损坏导致无法识别该页面、断电或者文件系统问题导致页面丢失。
253 13
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
|
6月前
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
8月前
|
SQL 数据库
数据库数据恢复—SQL Server报错“错误 823”的数据恢复案例
SQL Server数据库附加数据库过程中比较常见的报错是“错误 823”,附加数据库失败。 如果数据库有备份则只需还原备份即可。但是如果没有备份,备份时间太久,或者其他原因导致备份不可用,那么就需要通过专业手段对数据库进行数据恢复。
|
8月前
|
SQL 关系型数据库 OLAP
云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
189 2

推荐镜像

更多