Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

PostgreSQL 树状数据存储与查询(非递归) - Use ltree extension deal tree-like data type

4 minute read

背景

PostgreSQL 9.1里面的一个ltree extension.

由两位国外PostgreSQL贡献者共同开发

http://www.sai.msu.su/~megera/postgres/gist

这个extension带有四个数据类型，多个函数，支持多样化的操作符。

对于树形结构的数据处理可见一斑。

之前我的一位同事在设计数据结构的时候有用到这种树形数据，当时是用PostgreSQL的递归查询来满足的需求。

文章地址:

http://blog.163.com/digoal@126/blog/static/163877040201132843255911/

如果使用ltree extension的话，会方便很多。

简易模块加载步骤:

1. 安装PostgreSQL的时候

gmake world  
gmake install-world

2. 在需要新增此EXTENSION的数据库，使用超级用户新增。

digoal=# create extension ltree;  
CREATE EXTENSION  
  
digoal=# select * from pg_extension ;  
 extname  | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition   
----------+----------+--------------+----------------+------------+-----------+--------------  
 plpgsql  |       10 |           11 | f              | 1.0        |           |   
 file_fdw |       10 |         2200 | t              | 1.0        |           |   
 ltree    |       10 |         2200 | t              | 1.0        |           |   
(3 rows)  
  
digoal=# \dT+  
                         List of data types  
 Schema |    Name    | Internal name | Size | Elements | Description   
--------+------------+---------------+------+----------+-------------  
 public | lquery     | lquery        | var  |          |   
 public | ltree      | ltree         | var  |          |   
 public | ltree_gist | ltree_gist    | var  |          |   
 public | ltxtquery  | ltxtquery     | var  |          |   
(4 rows)

简单的介绍一下这几个类型 :

1. ltree (目前只支持A-Z,a-z,0-9,_作为label的合法字符)

树形结构类型,一个ltree被称为一个path,由1或多个LABEL组成,每个label由A-Z,a-z,0-9,_组成。

2. lquery

规则表达式,用于匹配ltree类型.

具体参考手册,需要注意的是%匹配的不是一个label,而是label里的一个单词(_为单词分隔符

3. ltxtquery

一般用于全文扫描，注意，只有ltxtquery类型是符号和匹配的内容是可以有空格隔开的,lquery和ltree不支持空格。

操作符介绍:

Table F-12. ltree Operators

Operator Returns Description
ltree @> ltree boolean is left argument an ancestor of right (or equal)?
ltree <@ ltree boolean is left argument a descendant of right (or equal)?
ltree ~ lquery boolean does ltree match lquery?
lquery ~ ltree boolean does ltree match lquery?
ltree ? lquery[] boolean does ltree match any lquery in array?
lquery[] ? ltree boolean does ltree match any lquery in array?
ltree @ ltxtquery boolean does ltree match ltxtquery?
ltxtquery @ ltree boolean does ltree match ltxtquery?
ltree || ltree ltree concatenate ltree paths
ltree || text ltree convert text to ltree and concatenate
text || ltree ltree convert text to ltree and concatenate
ltree[] @> ltree boolean does array contain an ancestor of ltree?
ltree <@ ltree[] boolean does array contain an ancestor of ltree?
ltree[] <@ ltree boolean does array contain a descendant of ltree?
ltree @> ltree[] boolean does array contain a descendant of ltree?
ltree[] ~ lquery boolean does array contain any path matching lquery?
lquery ~ ltree[] boolean does array contain any path matching lquery?
ltree[] ? lquery[] boolean does ltree array contain any path matching any lquery?
lquery[] ? ltree[] boolean does ltree array contain any path matching any lquery?
ltree[] @ ltxtquery boolean does array contain any path matching ltxtquery?
ltxtquery @ ltree[] boolean does array contain any path matching ltxtquery?
ltree[] ?@> ltree ltree first array entry that is an ancestor of ltree; NULL if none
ltree[] ?<@ ltree ltree first array entry that is a descendant of ltree; NULL if none
ltree[] ?~ lquery ltree first array entry that matches lquery; NULL if none
ltree[] ?@ ltxtquery ltree first array entry that matches ltxtquery; NULL if none

函数简单介绍:

Function Return Type Description Example Result
subltree(ltree, int start, int end) ltree subpath of ltree from position start to position end-1 (counting from 0) subltree(‘Top.Child1.Child2’,1,2) Child1
subpath(ltree, int offset, int len) ltree subpath of ltree starting at position offset, length len. If offset is negative, subpath starts that far from the end of the path. If len is negative, leaves that many labels off the end of the path. subpath(‘Top.Child1.Child2’,0,2) Top.Child1
subpath(ltree, int offset) ltree subpath of ltree starting at position offset, extending to end of path. If offset is negative, subpath starts that far from the end of the path. subpath(‘Top.Child1.Child2’,1) Child1.Child2
nlevel(ltree) integer number of labels in path nlevel(‘Top.Child1.Child2’) 3
index(ltree a, ltree b) integer position of first occurrence of b in a; -1 if not found index(‘0.1.2.3.5.4.5.6.8.5.6.8’,’5.6’) 6
index(ltree a, ltree b, int offset) integer position of first occurrence of b in a, searching starting at offset; negative offsetmeans start -offset labels from the end of the path index(‘0.1.2.3.5.4.5.6.8.5.6.8’,’5.6’,-4) 9
text2ltree(text) ltree cast text to ltree - -
ltree2text(ltree) text cast ltree to text - -
lca(ltree, ltree, …) ltree lowest common ancestor, i.e., longest common prefix of paths (up to 8 arguments supported) lca(‘1.2.2.3’,’1.2.3.4.5.6’) 1.2
lca(ltree[]) ltree lowest common ancestor, i.e., longest common prefix of paths lca(array[‘1.2.2.3’::ltree,’1.2.3’]) 1.2

实例:

digoal=> create table tbl_music(id serial4,song ltree not null);  
NOTICE:  CREATE TABLE will create implicit sequence "tbl_music_id_seq" for serial column "tbl_music.id"  
CREATE TABLE  
  
digoal=> insert into tbl_music (song) values ('GangTai.NanGeShou.LiuDeHua.AiNiYiWanNian');  
digoal=> insert into tbl_music (song) values ('GangTai.NanGeShou.LiuDeHua.JinTian');  
INSERT 0 1  
digoal=> insert into tbl_music (song) values ('GangTai.NanGeShou.LiuDeHua.WangQinShui');  
INSERT 0 1  
digoal=> insert into tbl_music (song) values ('GangTai.NanGeShou.ZhangXueYou.WenBie');  
INSERT 0 1  
digoal=> insert into tbl_music (song) values ('GangTai.NanGeShou.ZhangXueYou.QingShu');  
INSERT 0 1  
digoal=> insert into tbl_music (song) values ('GangTai.NvGeShou.ZhenXiuWen.MeiFeiSeWu');  
INSERT 0 1  
digoal=> insert into tbl_music (song) values ('GangTai.NvGeShou.ZhenXiuWen.ZhongShenMeiLi');  
INSERT 0 1  
digoal=> insert into tbl_music (song) values ('DaLu.NanGeShou.DaoLang.2002NianDeDiYiChangXue');  
INSERT 0 1  
digoal=> insert into tbl_music (song) values ('DaLu.NvGeShou.FanBinBin.FeiNiao');  
  
digoal=> select * from tbl_music;  
 id |                     song                        
----+-----------------------------------------------  
  2 | GangTai.NanGeShou.LiuDeHua.AiNiYiWanNian  
  3 | GangTai.NanGeShou.LiuDeHua.JinTian  
  4 | GangTai.NanGeShou.LiuDeHua.WangQinShui  
  5 | GangTai.NanGeShou.ZhangXueYou.WenBie  
  6 | GangTai.NanGeShou.ZhangXueYou.QingShu  
  7 | GangTai.NvGeShou.ZhenXiuWen.MeiFeiSeWu  
  8 | GangTai.NvGeShou.ZhenXiuWen.ZhongShenMeiLi  
  9 | DaLu.NanGeShou.DaoLang.2002NianDeDiYiChangXue  
 10 | DaLu.NvGeShou.FanBinBin.FeiNiao

检索刘德华的所有歌曲.

digoal=> select subltree(song,3,4) from tbl_music where subltree(song,2,3)='LiuDeHua';  
   subltree      
---------------  
 AiNiYiWanNian  
 JinTian  
 WangQinShui  
(3 rows)

查找与刘德华同一个区域（港台。男歌手）的歌手。

digoal=> select distinct subltree(song,2,3) from tbl_music where song <@ (select  subpath(song,0,2) from tbl_music where subltree(song,2,3)='LiuDeHua' limit 1);  
  subltree     
-------------  
 LiuDeHua  
 ZhangXueYou  
(2 rows)

其他不再举例.

参考

postgresql-9.1beta1/postgresql-9.1beta1/doc/src/sgml/html/ltree.html

with recursive

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...