postgres创建递归视图

原创 已于 2022-10-28 15:11:11 修改 · 336 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#java

于 2022-10-28 11:45:21 首次发布
  • 递归功能：把所有继承关系的id都放到一个新的数级字段中（当前的元素id+父id+父的父id+…)
CREATE OR REPLACE RECURSIVE VIEW VW_BUSINESS_CAPABILITY_MODEL_HIERARCHY (relation_id, sub_id, bcm_id, bcm_name, bcm_type, path) AS
	SELECT sbcm.id,
		sbcm2.id AS sub_id,
		sbcm2.bcm_id,
		sbcm2.bcm_name,
		sbcm2.bcm_type,
		ARRAY [cast(sbcm2.id as character varying)]
		FROM solaris_business_capability_model sbcm
		LEFT JOIN solaris_business_capability_model sbcm2 ON sbcm.id = sbcm2.parent_id
		WHERE sbcm.parent_id is null AND sbcm.bcm_industry_id = 'I_CROSS'
	UNION
		SELECT vw.sub_id as id,
		sbcm3.id as sub_id,
		sbcm3.bcm_id,
		sbcm3.bcm_name,
		sbcm3.bcm_type,
		vw.path || cast(sbcm3.id as character varying)
		FROM vw_business_capability_model_hierarchy vw
		JOIN solaris_business_capability_model sub ON sbcm3.parent_id = vw.sub_id
  • 把字符串 字段 放在一个数组类型的新字段 path中
    • ARRAY [cast(sbcm2.id as character varying)]
  • 新数组字段添加 字符串值 元素进来
    • vw.path || cast(sbcm3.id as character varying)
lanxing_thk
关注
Postgres、hgdb物化视图实验
lk的窝
01-25 516
以下步骤依postgres 9.6版本为例： postgres9.6@[local]:5432 postgres# select version();                                                  version                                                  ------------------...
怎样在 PostgreSQL 中优化复杂的递归查询性能？
2401_86074221的博客
07-08 1769
优化 PostgreSQL 中的复杂递归查询性能需要综合考虑多个因素，包括限制递归深度、创建合适的索引、优化查询逻辑、缓存中间结果以及分阶段处理等。通过针对具体的业务场景和数据特点进行分析和优化，可以显著提高递归查询的性能，使其能够在合理的时间内返回准确的结果。请注意，每一个实际的应用场景都有其独特性，需要根据具体情况选择最合适的优化策略。同时，定期对数据库性能进行监测和优化是保持系统高效运行的重要措施。🎉相关推荐🍅关注博主🎗️带你畅游技术世界，不错过每一次成长机会！
PostgreSQL树形结构的递归查询
沈荣荣的博客
07-06 5412
背景 处理不确定深度的层级结构，比如组织机构，一个常用的设计是在一张表里面保存 ID 和 Parent_ID ，并且通过自联结的办法构造一颗树。这种方式对写数据的过程很友好，但是查询过程就变得相对复杂。在不引入MPTT模型的前提下，必须通过递归算法来查询某个节点和下级子节点。 Oracle提供的connect by扩展语法，简单好用。但是其他的RDBMS就没这么人性化了（或者我不知道）。最近在项目中使用PostgreSQL来查询树形数据，记录一下。 构造样本数据 drop table if...
递归显示视图层次结构
浓浓的巧克力
05-04 849
// Recursively travel down the view tree, increasing the indentation level for children - (void) dumpView: (UIView *) aView atIndent: (int) indent into:(NSMutableString *) outstring {     for (int
Mysql 递归、视图、GROUP_CONCAT
洪君的博客
05-26 675
DROP FUNCTION IF EXISTS `getUserChild`; CREATE FUNCTION `getUserChild`( userId INT ) RETURNS varchar(4000) CHARSET utf8 COLLATE utf8_bin BEGIN DECLARE oTemp VARCHAR ( 4000 ); DECLARE oTempChild VARCHAR ( 4000 ); SET oTemp = ''; SET oTempChild = CAST(.
oracle、sqlServer、postgreSQL、greenplum递归查询、递归视图
shufangreal的博客
12-30 782
sqlServer、postgreSQL、greenplum递归查询 参考博客：https://blog.youkuaiyun.com/wenzhihui_2010/article/details/43935019 递归查询通常可用作将有层级关系的数据进行扁平化展示,如组织机构，包含关系等 1 测试数据创建 --创建表 create table tb(id varchar(3) , pid varchar(3) , name varchar(10)); --插入测试数据 insert into tb value
视图和递归查询
for_lover的博客
11-10 288
1、递归查询 SELECT * FROMtableName START WITH BAE024 = '410222040505' CONNECT BY PRIOR AAB301 = BAE024 union all select '410422' from dual select aab301, aae044 fromtableName start with aab301 = '410222' connect by prior aab301 = bae024 2、视图 create or rep..
SQL之递归查询
Alkali!的博客
01-25 5630
SQL之递归查询
basics-postgres
03-29
Postgres支持许多高级特性，如JSON和XML数据类型、全文搜索、窗口函数、递归查询、并行查询等。此外，还有丰富的社区开发的扩展，如PostGIS（地理空间数据）和pg_stat_statements（性能监控）。 九、监控与日志 ...
postgres数据库中文手册
12-03
在语言特性方面，Postgres支持SQL标准，包括DML（数据操纵语言）如INSERT、UPDATE、DELETE，以及DDL（数据定义语言）如CREATE、ALTER、DROP等用于创建和修改数据库对象的命令。此外，Postgres还扩展了SQL，提供了...
postgres_practice
02-20
5. **视图与存储过程**：掌握创建和使用视图以简化复杂查询，以及编写和调用存储过程以封装复杂的业务逻辑。 6. **安全性与权限管理**：了解如何设置用户账户，管理角色权限，以及使用GRANT和REVOKE命令控制数据...
常见问题解决技巧：递归和回溯
新华编程特战队
01-14 1734
这正是递归背后的基本概念——递归函数将任何问题分解为更小的子问题，其答案要么是已知的，要么可以通过使用另一种算法找到。在回溯中，我们尝试解决一个子问题，如果没有达到所需的解决方案，则撤消为解决该子问题所做的一切，并尝试解决另一个子问题，直到找到该问题的最佳解决方案（如果有）。在那里你可以看到你的图像，在你的图像中，在你的图像中等等。为我们提供了一种解决复杂问题的优雅方法，它将复杂问题分解为更小的问题，并且比迭代需要更少的代码行。然而，这是没有终止情况的递归，因为你无法找到这些图像结束的点。
【数据库】PostgreSQL递归
野生Java小菜鸟的博客
08-27 6145
PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS），是以加州大学计算机系开发的POSTGRES，4.2版本为基础的对象关系型数据库管理系统。 在 PostgreSQL 中，WITH 子句提供了一种编写辅助语句的方法，以便在更大的查询中使用。 WITH 子句有助于将复杂的大型查询分解为更简单的表单，便于阅读。 CTE（Common Table Express， CTE）: 这些语句通常称为通用表表达式，也可以当做一个为查询而存在的临时表。 WITH 子句允许我们在查询
使用递归方法获取某个视图的所有子视图
番薯大佬的专栏
06-05 1397
使用递归方法获取某个子视图的所有子视图，并有层次的打印出来。代码示例// 子视图 UIView *view0 = [[UIView alloc] initWithFrame:CGRectMake(10.0, 10.0, 10.0, 10.0)]; [self.view addSubview:view0]; // UIView *view1 = [[UIView alloc] initWithFra...
在视图中使用递归生成树状结构
weixin_34122604的博客
09-27 272
在开发过程中往往会有一个需求，就是将一个树状的数据结构在视图中表示出来。例如最传统的多级分类，系统中有一系列根分类，每个分类中又带有一些子分类，而我们的目标便是在页面上生成一个由ul和li嵌套组成的HTML结构。这个问题看似简单，但是如何让实现变的轻松、易于使用也是一个值得讨论的问题。这次就来谈谈这部分的情况。 实现目标 首先来明确一下实现目标。例如我们有一个Category对象，表示一个类别：p...
PostgreSQL的递归查询(with recursive)
weixin_34124577的博客
04-25 1125
为什么80%的码农都做不了架构师？>>> ...
postgres use with
最新发布
11-25
<think> 我们正在讨论的是PostgreSQL中的WITH子句（也称为公共表表达式，CTE）。根据用户提供的引用，我们可以总结WITH子句的关键点，并给出详细的使用方法和示例。用户引用的内容主要涉及WITH子句的基本用法、优点、缺点以及一些示例。 ### 1. **WITH子句的基本概念** WITH子句允许我们在一个查询中定义一个或多个临时结果集，这些结果集可以在后续的主查询中被引用。它类似于临时视图，但只在当前查询中有效。 ### 2. **WITH子句的语法** ```sql WITH cte_name (column_list) AS ( CTE_query_definition ) main_query; ``` - `cte_name`：临时结果集的名称。 - `column_list`：可选，指定CTE中列的别名。如果省略，则使用查询中的列名。 - `CTE_query_definition`：定义CTE的查询。 - `main_query`：使用CTE的主查询。 ### 3. **使用场景** #### a) 提高可读性（引用[1][2]） 将复杂查询拆分为多个CTE，使查询逻辑更清晰。 **示例**：引用[3]中的简单CTE ```sql WITH cte AS ( SELECT * FROM company ) SELECT * FROM cte; ``` #### b) 避免重复子查询（引用[2][5]） 当同一个子查询在多个地方被使用时，使用CTE可以避免重复计算。 **示例**：引用[4]中，用CTE计算一段时间内的充值总额，然后在主查询中与其他表连接 ```sql WITH u AS ( SELECT id, SUM(amount) AS num FROM pay WHERE pay_time >= 1493568000 AND pay_time < 1494172800 GROUP BY id ) SELECT u.id, pinfo.sid, u.num FROM u JOIN pinfo ON u.id = pinfo.id; ``` #### c) 递归查询（引用[2][5]） 递归CTE用于处理树形或层次结构数据，如组织结构、目录树等。 **递归CTE语法**： ```sql WITH RECURSIVE cte_name AS ( -- 非递归部分（初始查询） SELECT ... UNION [ALL] -- 递归部分（引用CTE自身） SELECT ... FROM cte_name ... ) SELECT ... FROM cte_name; ``` **示例**：查询员工及其所有下属（假设表employees有id, name, manager_id） ```sql WITH RECURSIVE subordinates AS ( SELECT id, name, manager_id FROM employees WHERE id = 1 -- 假设要查询id为1的员工及其所有下属 UNION SELECT e.id, e.name, e.manager_id FROM employees e INNER JOIN subordinates s ON s.id = e.manager_id ) SELECT * FROM subordinates; ``` #### d) 数据修改（引用[2][5]） 在WITH子句中可以使用INSERT、UPDATE、DELETE等数据修改语句，然后在主查询中返回修改后的数据。 **示例**：删除数据并返回被删除的行 ```sql WITH deleted_rows AS ( DELETE FROM employees WHERE salary < 3000 RETURNING * ) SELECT * FROM deleted_rows; -- 返回被删除的员工记录 ``` ### 4. **注意事项（引用[5]）** - **性能开销**：CTE的结果集通常存储在内存中，对于大数据集可能导致性能下降。 - **递归深度**：递归CTE可能因递归过深而出现性能问题。 - **索引**：不能在CTE上创建索引，因此对于需要索引的场景，可能需要考虑临时表。 - **可读性**：虽然CTE能提高可读性，但过度嵌套或复杂的CTE可能使查询更难理解。 ### 5. **使用建议** - 对于复杂查询，使用多个CTE逐步构建，提高可读性。 - 避免在CTE中处理过大的数据集，尤其是递归查询。 - 在需要多次引用同一个子查询时，使用CTE可以减少重复计算。 ### 总结 WITH子句（CTE）是PostgreSQL中处理复杂查询的强大工具，它通过模块化查询逻辑、支持递归查询和数据修改操作，显著提升SQL代码的可读性和可维护性。但需注意其潜在的性能影响，合理使用。 §§相关问题§§ 1. 如何在递归CTE中避免无限循环? 2. 在哪些场景下使用CTE比使用临时表更合适? 3. 如何优化递归CTE的性能? </think> 以下是关于 PostgreSQL 中 `WITH` 子句（Common Table Expressions，CTE）的详细使用指南，结合核心功能和实际场景： --- ### 一、`WITH` 子句的核心功能 1. **简化复杂查询** 将大型查询拆分为逻辑模块（引用[1][2]）： ```sql WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ) SELECT region, total_sales FROM regional_sales WHERE total_sales > 100000; ``` 2. **支持递归查询** 处理树形结构数据（引用[2][5]）： ```sql WITH RECURSIVE employee_hierarchy AS ( SELECT id, name, manager_id -- 初始查询（根节点） FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id -- 递归部分 FROM employees e JOIN employee_hierarchy eh ON e.manager_id = eh.id ) SELECT * FROM employee_hierarchy; ``` 3. **数据修改操作** 在单一语句中实现增删改查（引用[5]）： ```sql WITH deleted_orders AS ( DELETE FROM orders WHERE order_date < '2023-01-01' RETURNING order_id, amount -- 返回被删除的数据 ) INSERT INTO archive_orders SELECT * FROM deleted_orders; ``` --- ### 二、关键使用场景与示例 #### 场景1：数据分步处理（引用[4]） ```sql WITH user_payments AS ( SELECT user_id, SUM(amount) AS total_paid FROM payments WHERE payment_time BETWEEN '2023-05-01' AND '2023-05-08' GROUP BY user_id ) SELECT u.user_id, p.sid, u.total_paid FROM user_payments u JOIN profiles p ON u.user_id = p.user_id; ``` #### 场景2：避免重复子查询 ```sql WITH top_products AS ( SELECT product_id, AVG(rating) AS avg_rating FROM reviews GROUP BY product_id HAVING COUNT(*) > 50 -- 只统计超过50条评论的商品 ) SELECT p.name, tp.avg_rating FROM products p JOIN top_products tp ON p.id = tp.product_id; ``` --- ### 三、性能优化与注意事项 1. **优点**（引用[2][5]）： - 📊 **可读性提升**：将复杂逻辑拆解为命名模块 - 🔁 **代码重用**：避免重复子查询 - ⚡ **优化器支持**：PostgreSQL 会对 CTE 进行优化 2. **缺点**（引用[5]）： - ⚠️ **内存消耗**：大型结果集可能占用过多内存 - ⏱️ **递归深度风险**：深层递归可能导致性能下降 - 🔒 **无索引支持**：CTE 结果集无法创建索引 3. **性能建议**： ```sql WITH cte_materialized AS MATERIALIZED ( -- 强制物化结果（PostgreSQL 12+） SELECT * FROM large_table ) SELECT ... FROM cte_materialized; ``` --- ### 四、典型错误示例 **错误：** 在 CTE 中执行不必要的数据扫描 ```sql -- 低效写法 (全表扫描两次) WITH all_orders AS (SELECT * FROM orders) SELECT * FROM all_orders WHERE amount > 100; ``` **优化方案：** ```sql -- 高效写法 (减少扫描范围) WITH filtered_orders AS ( SELECT * FROM orders WHERE amount > 100 -- 提前过滤 ) SELECT * FROM filtered_orders; ``` --- ### 总结 `WITH` 子句是 PostgreSQL 处理复杂查询的核心工具，尤其适用于： 1. 多步骤数据加工 2. 递归层级数据处理 3. 需要数据修改的原子操作 使用时需平衡可读性与性能，对大数据集优先考虑物化策略（`MATERIALIZED`）。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值