在PostgreSQL中使用ltree处理层次结构数据的方法

2024-04-02 19:04:59 803人浏览薄情痞子

摘要

目录什么是ltree？为什么选择ltree？初始数据概述在本文中，我们将学习如何使用postgresql的ltree模块，该模块允许以分层的树状结构存储数据。什么是ltree？ L

什么是ltree？

Ltree是Postgresql模块。它实现了一种数据类型ltree，用于表示存储在分层树状结构中的数据的标签。提供了用于搜索标签树的广泛工具。

为什么选择ltree？

ltree实现了一个物化路径，对于INSERT / UPDATE / DELETE来说非常快，而对于SELECT操作则较快
通常，它比使用经常需要重新计算分支的递归CTE或递归函数要快
如内置的查询语法和专门用于查询和导航树的运算符
索引！！！

初始数据

首先，您应该在数据库中启用扩展。您可以通过以下命令执行此操作：


CREATE EXTENSioN ltree;

让我们创建表并向其中添加一些数据：


CREATE TABLE comments (user_id integer, description text, path ltree);
INSERT INTO comments (user_id, description, path) VALUES ( 1, md5(random()::text), '0001');
INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0001.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0001.0001.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 1, md5(random()::text), '0001.0001.0001.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 5, md5(random()::text), '0001.0001.0001.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0002.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 8, md5(random()::text), '0001.0003.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 11, md5(random()::text), '0001.0003.0002.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0003.0002.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 5, md5(random()::text), '0001.0003.0002.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 7, md5(random()::text), '0001.0003.0002.0002.0001');
INSERT INTO comments (user_id, description, path) VALUES ( 20, md5(random()::text), '0001.0003.0002.0002.0002');
INSERT INTO comments (user_id, description, path) VALUES ( 31, md5(random()::text), '0001.0003.0002.0002.0003');
INSERT INTO comments (user_id, description, path) VALUES ( 22, md5(random()::text), '0001.0003.0002.0002.0004');
INSERT INTO comments (user_id, description, path) VALUES ( 34, md5(random()::text), '0001.0003.0002.0002.0005');
INSERT INTO comments (user_id, description, path) VALUES ( 22, md5(random()::text), '0001.0003.0002.0002.0006');

另外，我们应该添加一些索引：


CREATE INDEX path_GISt_comments_idx ON comments USING GIST(path);
CREATE INDEX path_comments_idx ON comments USING btree(path);

正如您看到的那样，我建立comments表时带有path字段，该字段包含该表的tree全部路径。如您所见，对于树分隔符，我使用4个数字和点。

让我们在commenets表中找到path以‘0001.0003'的记录：


$ SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
 user_id |   path
---------+--------------------------
  6 | 0001.0003
  8 | 0001.0003.0001
  9 | 0001.0003.0002
  11 | 0001.0003.0002.0001
  2 | 0001.0003.0002.0002
  5 | 0001.0003.0002.0003
  7 | 0001.0003.0002.0002.0001
  20 | 0001.0003.0002.0002.0002
  31 | 0001.0003.0002.0002.0003
  22 | 0001.0003.0002.0002.0004
  34 | 0001.0003.0002.0002.0005
  22 | 0001.0003.0002.0002.0006
(12 rows)

让我们通过EXPLaiN命令检查这个SQL：


$ EXPLAIN ANALYZE SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
            QUERY PLAN
----------------------------------------------------------------------------------------------------
 Seq Scan on comments (cost=0.00..1.24 rows=2 width=38) (actual time=0.013..0.017 rows=12 loops=1)
 Filter: (path <@ '0001.0003'::ltree)
 Rows Removed by Filter: 7
 Total runtime: 0.038 ms
(4 rows)

让我们禁用seq scan进行测试：


$ SET enable_seqscan=false;
SET
$ EXPLAIN ANALYZE SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using path_gist_comments_idx on comments (cost=0.00..8.29 rows=2 width=38) (actual time=0.023..0.034 rows=12 loops=1)
 Index Cond: (path <@ '0001.0003'::ltree)
 Total runtime: 0.076 ms
(3 rows)

现在SQL慢了，但是能看到SQL是怎么使用index的。
第一个SQL语句使用了sequence scan，因为在表中没有太多的数据。

我们可以将select “path <@ ‘0001.0003'” 换种实现方法：


$ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*';
user_id |   path
---------+--------------------------
  6 | 0001.0003
  8 | 0001.0003.0001
  9 | 0001.0003.0002
  11 | 0001.0003.0002.0001
  2 | 0001.0003.0002.0002
  5 | 0001.0003.0002.0003
  7 | 0001.0003.0002.0002.0001
  20 | 0001.0003.0002.0002.0002
  31 | 0001.0003.0002.0002.0003
  22 | 0001.0003.0002.0002.0004
  34 | 0001.0003.0002.0002.0005
  22 | 0001.0003.0002.0002.0006
(12 rows)

你不应该忘记数据的顺序，如下的例子：


$ INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0001');
$ INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0002');
$ INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0003');
$ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*';
user_id |   path
---------+--------------------------
  6 | 0001.0003
  8 | 0001.0003.0001
  9 | 0001.0003.0002
  11 | 0001.0003.0002.0001
  2 | 0001.0003.0002.0002
  5 | 0001.0003.0002.0003
  7 | 0001.0003.0002.0002.0001
  20 | 0001.0003.0002.0002.0002
  31 | 0001.0003.0002.0002.0003
  22 | 0001.0003.0002.0002.0004
  34 | 0001.0003.0002.0002.0005
  22 | 0001.0003.0002.0002.0006
  9 | 0001.0003.0001.0001
  9 | 0001.0003.0001.0002
  9 | 0001.0003.0001.0003
(15 rows)

现在进行排序：


$ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*' ORDER by path;
 user_id |   path
---------+--------------------------
  6 | 0001.0003
  8 | 0001.0003.0001
  9 | 0001.0003.0001.0001
  9 | 0001.0003.0001.0002
  9 | 0001.0003.0001.0003
  9 | 0001.0003.0002
  11 | 0001.0003.0002.0001
  2 | 0001.0003.0002.0002
  7 | 0001.0003.0002.0002.0001
  20 | 0001.0003.0002.0002.0002
  31 | 0001.0003.0002.0002.0003
  22 | 0001.0003.0002.0002.0004
  34 | 0001.0003.0002.0002.0005
  22 | 0001.0003.0002.0002.0006
  5 | 0001.0003.0002.0003
(15 rows)

可以在lquery的非星号标签的末尾添加几个修饰符，以使其比完全匹配更匹配：
“ @”-不区分大小写匹配，例如a @匹配A
“ *”-匹配任何带有该前缀的标签，例如foo *匹配foobar
“％”-匹配以下划线开头的单词


$ SELECT user_id, path FROM comments WHERE path ~ '0001.*{1,2}.0001|0002.*' ORDER by path;
 user_id |   path
---------+--------------------------
  2 | 0001.0001.0001
  2 | 0001.0001.0001.0001
  1 | 0001.0001.0001.0002
  5 | 0001.0001.0001.0003
  6 | 0001.0002.0001
  8 | 0001.0003.0001
  9 | 0001.0003.0001.0001
  9 | 0001.0003.0001.0002
  9 | 0001.0003.0001.0003
  9 | 0001.0003.0002
  11 | 0001.0003.0002.0001
  2 | 0001.0003.0002.0002
  7 | 0001.0003.0002.0002.0001
  20 | 0001.0003.0002.0002.0002
  31 | 0001.0003.0002.0002.0003
  22 | 0001.0003.0002.0002.0004
  34 | 0001.0003.0002.0002.0005
  22 | 0001.0003.0002.0002.0006
  5 | 0001.0003.0002.0003
(19 rows)

我们来为parent ‘0001.0003'找到所有直接的childrens，见下：


$ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*{1}' ORDER by path;
 user_id |  path
---------+----------------
  8 | 0001.0003.0001
  9 | 0001.0003.0002
(2 rows)

为parent ‘0001.0003'找到所有的childrens，见下：


$ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*' ORDER by path;
 user_id |   path
---------+--------------------------
  6 | 0001.0003
  8 | 0001.0003.0001
  9 | 0001.0003.0001.0001
  9 | 0001.0003.0001.0002
  9 | 0001.0003.0001.0003
  9 | 0001.0003.0002
  11 | 0001.0003.0002.0001
  2 | 0001.0003.0002.0002
  7 | 0001.0003.0002.0002.0001
  20 | 0001.0003.0002.0002.0002
  31 | 0001.0003.0002.0002.0003
  22 | 0001.0003.0002.0002.0004
  34 | 0001.0003.0002.0002.0005
  22 | 0001.0003.0002.0002.0006
  5 | 0001.0003.0002.0003
(15 rows)

为children ‘0001.0003.0002.0002.0005'找到parent:


$ SELECT user_id, path FROM comments WHERE path = subpath('0001.0003.0002.0002.0005', 0, -1) ORDER by path;
 user_id |  path
---------+---------------------
  2 | 0001.0003.0002.0002
(1 row)

如果你的路径不是唯一的，你会得到多条记录。

概述

可以看出，使用ltree的物化路径非常简单。在本文中，我没有列出ltree的所有可能用法。它不被视为全文搜索问题ltxtquery。但是您可以在PostgreSQL官方文档(Http://www.postgresql.org/docs/current/static/ltree.html)中找到它。

了解更多PostgreSQL热点资讯、新闻动态、精彩活动，请访问中国PostgreSQL官方网站：www.postgresqlchina.com

解决更多PostgreSQL相关知识、技术、工作问题，请访问中国PostgreSQL官方问答社区：www.pgfans.cn

下载更多PostgreSQL相关资料、工具、插件问题，请访问中国PostgreSQL官方下载网站：www.postgreshub.cn

到此这篇关于在PostgreSQL中使用ltree处理层次结构数据的文章就介绍到这了,更多相关PostgreSQL层次结构数据内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！

点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: 在PostgreSQL中使用ltree处理层次结构数据的方法

本文链接: https://www.lsjlt.com/news/122603.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档

去做题

猜你喜欢

在PostgreSQL中使用ltree处理层次结构数据的方法

目录什么是ltree？为什么选择ltree？初始数据概述在本文中，我们将学习如何使用PostgreSQL的ltree模块，该模块允许以分层的树状结构存储数据。什么是ltree？ L...

99+

2024-04-02
如何在PostgreSQL中利用ltree处理层次结构数据

本篇文章给大家分享的是有关如何在PostgreSQL中利用ltree处理层次结构数据，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。什么是ltree？Ltree是PostgreS...

99+

2023-06-14
JS使用reduce()方法处理树形结构数据

目录定义语法实例1. 没有传递初始值init2. 传递初始值的情况下3. 数组去重4. 利用 reduce 对数组中的 Object 对象进行分组及合并5. 利用 reduce 处理...

99+

2024-04-02
redis五种数据结构的底层实现方法

本篇内容主要讲解“redis五种数据结构的底层实现方法”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“redis五种数据结构的底层实现方法”吧!实现方法：1、每种数据结构都有自己底层的内部编码实现...

99+

2023-06-20
如何在C++中处理非结构化数据和半结构化数据？

非常抱歉，由于您没有提供文章标题，我无法为您生成一篇高质量的文章。请您提供文章标题，我将尽快为您生成一篇优质的文章。...

99+

2024-05-16
如何在Pig中处理复杂的数据结构

在Pig中处理复杂的数据结构通常涉及到使用嵌套数据类型，如map、bag、tuple等。以下是一些处理复杂数据结构的示例：使用M...

99+

2024-03-08

Pig
k8s 使用 OwnerRef 获取集群中的所有 pod 层次结构

php小编香蕉今天给大家介绍一种使用 OwnerRef 获取 Kubernetes 集群中所有 Pod 层次结构的方法。Kubernetes 是一个流行的容器编排平台，可以帮助我们管理...

99+

2024-02-08

容器化应用
PHP 数组分组函数在创建层级结构中的使用

php 数组分组函数可用于创建层级结构，通过将数组元素根据指定键分组，生成具有多个嵌套级别的数组。代码示例使用 array_group_by() 分组数据，然后通过递归函数 create...

99+

2024-05-01

php 数组分组
java中hashmap的底层数据结构与实现原理

目录Hash结构HashMap实现原理为何HashMap的数组长度一定是2的次幂？重写equals方法需同时重写hashCode方法总结Hash结构 HashMap根据名称可知，其实...

99+

2024-04-02
在QlikView中使用Neo4j处理图形数据的方法

这篇文章主要介绍在QlikView中使用Neo4j处理图形数据的方法，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！　　图形数据库是使用带有节点、边缘和属性的图结构来对数据进行存储和展...

99+

2024-04-02
PHP底层的高性能数据结构与实现方法

PHP底层的高性能数据结构与实现方法，需要具体代码示例随着互联网应用的不断发展，PHP已经成为了一种广泛使用的服务器端脚本语言。然而，在大规模的Web应用中，PHP的性能问题成为了一个不容忽视的问题，很多大型网站都出现了性能瓶颈和系统崩溃的...

99+

2023-11-09

数据结构高性能 PHP底层
Java 中数据结构LinkedList的用法

LinkList 链表（Linked list）是一种常见的基础数据结构，是一种线性表，但是并不会按线性的顺序存储数据，而是在每一个节点里存到下一个节点的地址。链表可分为单向链表和双向链表。一个单向链表包含两个值: 当前节点的值和一个指...

99+

2023-08-30

java 数据结构 windows
PostgreSQL执行聚合函数所使用的数据结构有哪些

这篇文章主要讲解了“PostgreSQL执行聚合函数所使用的数据结构有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“PostgreSQL执行聚合函数所使...

99+

2024-04-02
DBus数据库表结构变更处理方法是什么

这篇文章主要讲解了“DBus数据库表结构变更处理方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“DBus数据库表结构变更处理方法是什么”吧！一、感知...

99+

2024-04-02
处理数据缺失的结构化解决办法

数据缺失是数据科学家在处理数据时经常遇到的问题，本文作者基于不同的情境提供了相应的数据插补解决办法。没有完美的数据插补法，但总有一款更适合当下情况。我在数据清理与探索性分析中遇到的...

99+

2024-04-02
在 Go 中处理解耦的最佳方法是在两个不同的包中使用类似的结构，但结构中的子项使其变得困难？

在 Go 中处理解耦的最佳方法是使用类似结构但具有不同子项的两个不同包。这种方法可以有效地将代码分离，提高可维护性和模块化程度。然而，当结构中的子项变得复杂时，这种解耦方法可能会变得困...

99+

2024-02-09
PostgreSQL在执行逻辑优化中相关的数据结构是什么

这篇文章主要介绍“PostgreSQL在执行逻辑优化中相关的数据结构是什么”，在日常操作中，相信很多人在PostgreSQL在执行逻辑优化中相关的数据结构是什么问题上存在疑惑，小编查阅了各式资料，整理出简单...

99+

2024-04-02
PostgreSQL在执行逻辑优化中相关的数据结构有哪些

这篇文章主要介绍“PostgreSQL在执行逻辑优化中相关的数据结构有哪些”，在日常操作中，相信很多人在PostgreSQL在执行逻辑优化中相关的数据结构有哪些问题上存在疑惑，小编查阅了各式资料，整理出简单...

99+

2024-04-02
Java结构化数据处理开源库SPL怎么使用

本篇内容主要讲解“Java结构化数据处理开源库SPL怎么使用”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“Java结构化数据处理开源库SPL怎么使用”吧!现代Java应用架构越来越强调数据存储和...

99+

2023-06-30
运用数据结构优化 PHP 函数处理数据的效率

利用数据结构优化php函数处理数据的效率：选择合适的数据结构：数组、哈希表、链表、堆栈、队列优化数组排序：使用二叉树优化冒泡排序优化哈希表查找：利用哈希表自身特性优化查找复杂度优化链表插...

99+

2024-04-11

效率 php 键值对冒泡排序