PostgreSQL中的实例化视图

本文概述

• 初始数据库设置
• 创建实例化视图
• 刷新实例化视图
• 查询实例化视图
• 删除实例化视图
• 总结

如果你广泛地或平均地使用SQL(最好是PostgreSQL用户)来查询数据库, 那么你可能已经编写了很多复杂的查询来找到关键问题的答案。另外, 有时会发生这种情况, 你需要在工作中经常使用这些查询的结果。多次运行复杂的查询可能会很昂贵。如果有一种方法可以缓存复杂查询的结果以提高性能呢？好吧, 实例化视图在那里为你服务。

你可能会认为也有一些简单的视图可以存储查询结果。但是, 简单的视图在多个方面与实例化视图不同。这是PostgreSQL的官方文档中关于简单视图的内容-

该视图未在实体上实现。而是, 每次在查询中引用视图时都运行查询。

该语句意味着简单视图不会物理存储在数据库中。每次在查询中使用视图时, 都会执行创建视图的查询。现在, 这使得简单视图在访问时间上效率低下。

另一方面, 实例化视图通过允许你物理上将视图保留在数据库中而具有很大的灵活性。你可以像在简单视图(但访问时间较短)的情况下一样, 在实例化视图上进行操作。在本教程的其余部分中, 你将研究PostgreSQL中的实例化视图。在他的一个网络研讨会中, srcmini的首席数据科学家David Robinson展示了他如何使用实例化视图有效地存储复杂查询的结果。

请注意, 为了跟随本教程, 你应该熟悉SQL中的联接数据课程中涵盖的内容。

初始数据库设置

最好在PostgreSQL中建立数据库并在其中创建一个基本表, 以便你可以有效地查询它。这也将在本教程的以下部分中为你提供帮助。你可以还原此数据库备份, 其中包含将在本教程中使用的表。如果要学习如何在PostgreSQL中还原数据库备份, 可以按照本教程的第一部分进行。

如果能够还原备份, 则应该在数据库中看到一个名为international_debt的表(如果没有该表, 则需要首先创建一个数据库)。让我们快速查看表的前几行(一个简单的select查询将帮助你完成此操作)-

该表包含有关本年度全球不同国家在不同类别中的债务统计信息(请参阅indicator_name, indicator_code列)。债务列显示特定国家在特定类别中的债务金额(美元)。数据属于经济学领域, 通常用于分析不同国家的经济状况。数据是从世界银行收集的。

既然你已经成功建立了数据库, 那么可以继续本教程的下一部分了。

创建实例化视图

让我们为以下查询创建实例化视图-

select country_name, sum(debt) as total_debt
from international_debt
group by country_name order by country_name;

查询返回的国家名称及其债务总额(按字母顺序)。查询的输出应如下所示：

现在, 实例化视图-

CREATE MATERIALIZED VIEW country_total_debt
as
  select country_name, sum(debt) as total_debt
  from international_debt
  group by country_name order by country_name;

你应该收到一条成功消息, 指出-查询成功返回：受影响的124行, 执行时间为242毫秒。

刷新实例化视图

你还可以通过指定WITH NO DATA选项来指示PostgreSQL在创建时不要将任何数据加载到实例化视图中-

CREATE MATERIALIZED VIEW country_total_debt_2
as
  select country_name, sum(debt) as total_debt
  from international_debt
  group by country_name order by country_name
WITH NO DATA;

你将收到一条消息, 提示-查询成功返回, 且在41毫秒内没有结果。注意两个输出消息之间的区别。如果附加WITH WITH DATA选项, 则会创建一个空的实例化视图。你无法查询该实例化视图。但是, 你可以执行以下命令来填充实例化视图：

刷新材料视图country_total_debt_2;

查询实例化视图

到目前为止, 你应该已经创建了两个实例化视图(country_total_debt, country_total_debt_2)。让我们使用这两种方法之一执行一个简单的选择查询：

select * from country_total_debt_2;

查询应返回类似-

输出与我们封装在实例化视图中的查询完全相同。你也可以使用聚合函数在实例化视图上运行查询-

select max(total_debt) from country_total_debt_2;

你应该得到-

删除实例化视图

可以删除实例化视图, 就像在PostgreSQL中删除表的方式一样。你只需要在放置后指定实体化视图-

Drop Materialized View country_total_debt_2;

总结

这就是本教程的全部内容。感谢你阅读到最后。在本教程中, 你必须了解PostgreSQL中的实例化视图, 以及如何在它们上进行操作。由于其有效性, 实例化视图被广泛用于数据库仓库。如果你有兴趣学习更复杂的查询, 可以查看以下srcmini课程-

• 中级SQL
• 用SQL报告