迁移到PG 递归函数没有了Level伪列怎么办

IT那活儿发布于2023-01-11 13:20 / 1557人阅读

今天又遇到一个问题，在Oracle数据库中有Connectby这类的递归SQL。结果迁移到PostgreSQL中，发现没有level伪列。

作为去O实力派，没办法只能安排。

由于开发的表比较复杂，我们这里来造一个cats表，说明一下这个问题。我们先看看Oracle中的查询结果。

create table hbdx_zhaoyou.cats(

catno number(4,0),

catname varchar2(15),

job varchar2(15),

mgr number(4,0),

constraint pk_emp primary key (catno)

);

insert into hbdx_zhaoyou.cats values (0, king, ceo, null );

insert into hbdx_zhaoyou.cats values (1, jones, cio, 0);

insert into hbdx_zhaoyou.cats values (2, blake, cfo, 0);

insert into hbdx_zhaoyou.cats values (3, clark, hr exec, 0);

insert into hbdx_zhaoyou.cats values (4, scott, it mgr, 1);

insert into hbdx_zhaoyou.cats values (5, turner, architect,1);

insert into hbdx_zhaoyou.cats values (6, adams, fin.mgr,2 );

insert into hbdx_zhaoyou.cats values (7, james, hr.mgr,3);

insert into hbdx_zhaoyou.cats values (8, ford, it support,4);

insert into hbdx_zhaoyou.cats values (9, miller, developer, 4);

insert into hbdx_zhaoyou.cats values (10, smith, accountant,6);

insert into hbdx_zhaoyou.cats values (11, allen, payroll clerk,6);

insert into hbdx_zhaoyou.cats values (12, ward, hr officer,7 );

commit;

我们在Oracle中执行层次查询得到下列结果。

SQL> SELECT catno,catname,job,level FROM hbdx_zhaoyou.cats CONNECT BY PRIOR catno = mgr START WITH mgr IS NULL order by level ;

CATNO CATNAME JOB LEVEL

---------- --------------- --------------- ----------

0 king ceo 1

1 jones cio 2

2 blake cfo 2

3 clark hr exec 2

6 adams fin.mgr 3

7 james hr.mgr 3

5 turner architect 3

4 scott it mgr 3

8 ford it support 4

11 allen payroll clerk 4

12 ward hr officer 4

9 miller developer 4

10 smith accountant 4

类似于上图，我们知道King是老大，他的下面一级是经理级别，一共有三个经理，分别是jones、blake、clark，然后以此往下推。

这里Oracle的语法：

CONNECT BY ：定义了父级别和子级别之间的关系。
PRIOR：则指定了父级
START WITH：定义我们希望查询开始的记录。
level：指示层次结构级别的伪列。

那么在PostgreSQL中有两种实现的方法，第一种叫CTE（commontable expressions），简称公用表表达式。第二种是安装自带插件，使用PG的connectby函数。

CTE(common tableexpressions)实现

CTE又叫commontable expressions，它隶属于SQL:1999标准，在Oracle11gR2版本、MySQL8.0版本、PostgreSQL9.4以上版本都支持。因为是标准的语法，我们这里用mariadb官方文档（最容易懂）的图来说明一下原理。

首选我们要使用recursive关键字来表示，这是一个递归的CTE(公用表达式)。然后第一部分叫AnchorPart，翻译过来就是锚点。这个锚点我觉得就代表了树形查询的一个展开的点，比如你要从我们CATS表的ITMGR这个节点查询，那么这个条件就是锚点。

接下来就是Recursivepart，递归的部分。这里会告诉我们每个递归的步骤将要做什么。每次执行出来的结果，就会存放到结果表中，一直到整个递归结束。

下面的图详细的说明了递归到结果集这一过程。

首选查询锚点，取出name=‘Alex’的记录放入到结果表。

通过锚点查询的数据和原来的数据表关联，查询出锚点的下一层数据。比如这里查的是Alex的father和mother。

这里将上面查出来的数据Dad和Mom存到结果表中。

接下来继续根据上面查询的Dad和Mom，继续查询他们的father和mother。

这里将上面查出来的数据GrandpaBill存到结果表中

就这样一直查，直到没有结果为止。

上面的原理和语法介绍完了，我们可以在PG中写同样的SQL实现上述Oracle中connectby功能。

with recursive cte as (

select catno, catname, mgr from cats where mgr is null

union all

select e.catno, e.catname, e.mgr from cte c join cats e on e.mgr = c.catno

)

select * from cte;

catno | catname | mgr

-------+---------+-----

0 | king |

1 | jones | 0

2 | blake | 0

3 | clark | 0

4 | scott | 1

5 | turner | 1

6 | adams | 2

7 | james | 3

8 | ford | 4

9 | miller | 4

10 | smith | 6

11 | allen | 6

12 | ward | 7

可以看到结果类似，但是缺少像Oracle中的伪列level。这个伪列是需要我们自己构造一个的。

with recursive cte as (

select catno, catname, mgr,1 AS level from cats where mgr is null

union all

select e.catno, e.catname, e.mgr,c.level + 1 from cte c join cats e on e.mgr = c.catno )

select * from cte;

catno | catname | mgr | level

-------+---------+-----+-------

0 | king | | 1

1 | jones | 0 | 2

2 | blake | 0 | 2

3 | clark | 0 | 2

4 | scott | 1 | 3

5 | turner | 1 | 3

6 | adams | 2 | 3

7 | james | 3 | 3

8 | ford | 4 | 4

9 | miller | 4 | 4

10 | smith | 6 | 4

11 | allen | 6 | 4

12 | ward | 7 | 4

手动增加了一个列，就可以把level伪列功能实现了，至此我们就解决了开发的问题。

connectby实现

上面介绍了比较标准的CTE表达式，是在各种数据库都已经兼容的语法。而PG也有他自己独有的一种方法叫connectby，PG的connectby和Oracle中的connectby使用方式有很大的不同。

首先我们要安装插件tablefunc，这个插件是软件自带的。安装很简单直接createextension tablefunc就可以了。

这个插件有很多功能，我们这里只用最后一个功能connectby。

connectby(text relname, text keyid_fld, text parent_keyid_fld

[, text orderby_fld ], text start_with, int max_depth

[, text branch_delim ])

relname	源表的名称
keyid_fld	关键字段
parent_keyid_fld	父键的关键字段
orderby_fld	排序同级别字段（可选）
start_with	起始行的键值
max_depth	要向下展开的最大深度，零表示无限深度
branch_delim	在分支输出中用于分隔键值的字符串（可选

接下来就是见证这个函数魅力的时候了。

SELECT * FROM connectby(cats, catno, mgr, 0, 0, ->)

AS t(keyid numeric, parent_keyid numeric, level int, branch text)

order by level asc;

keyid | parent_keyid | level | branch

-------+--------------+-------+-------------

0 | | 0 | 0

1 | 0 | 1 | 0->1

2 | 0 | 1 | 0->2

3 | 0 | 1 | 0->3

4 | 1 | 2 | 0->1->4

6 | 2 | 2 | 0->2->6

7 | 3 | 2 | 0->3->7

5 | 1 | 2 | 0->1->5

9 | 4 | 3 | 0->1->4->9

8 | 4 | 3 | 0->1->4->8

10 | 6 | 3 | 0->2->6->10

11 | 6 | 3 | 0->2->6->11

12 | 7 | 3 | 0->3->7->12

可以看到使用起来非常方便，直接就输入参数就行了。当然这里有一个问题是我们没办法通过connectby函数展示全部的列，它的函数参数写死了类型。如果我们要展示cats表中的catname，就需要我们把当前结果集和原表cats在做一次关联。

select keyid,catname,parent_keyid,level+1 from connectby(cats, catno, mgr, 0, 0, ->) AS t(keyid numeric, parent_keyid numeric, level int, branch text)

inner join cats on catno = keyid

order by level,mgr asc;

keyid | catname | parent_keyid | ?column?

-------+---------+--------------+----------

0 | king | | 1

1 | jones | 0 | 2

2 | blake | 0 | 2

3 | clark | 0 | 2

5 | turner | 1 | 3

4 | scott | 1 | 3

6 | adams | 2 | 3

7 | james | 3 | 3

9 | miller | 4 | 4

8 | ford | 4 | 4

10 | smith | 6 | 4

11 | allen | 6 | 4

12 | ward | 7 | 4

那么这么写就和我前面的withrecursive的结果集完全一致了。

谁的效率高？

如果拿我们想要的结果来看的话。通过执行计划来看，很明显是CTE快一些。

就算我们不取catname，取消掉关联，connecyby函数执行的效率也是比CTE低的。

终于有人喊出了口号：

CONNECTBY Is Dead,

LongLive CTE!

参考文档：

RecursiveCommon Table Expressions Overview

https://mariadb.com/kb/en/recursive-common-table-expressions-overview/

Hierarchicaland recursive queries in SQL

https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression

F.38.tablefunc

https://www.postgresql.org/docs/12/tablefunc.html

云服务器 GPU云服务器购买了云服务器怎么没有了购买了云服务器怎么没有购买了云服务器怎么没有实例怎么把文件迁移到云服务器

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/130088.html

升级 postgresql

摘要：但如果涉及到跨大版本升级比如升级到，这种直接替换软件就不行了，因为跨版本的内部存储形式发生了变化官方对于升级提供了种方法，这里迁移我使用了来进行处理细节可以参考官方文档。 1 场景描述最近使用 postgresql 命令行比较多，就找了个类似 mycli 工具，pgcli，这个是针对 postgresql 的，兴冲冲的安装了 brew install pgcli 没想到这货自动帮我...

learn_shifeng 2019-07-25 12:33 评论0 收藏0
详解Ceph的杀手级技术CRUSH

摘要：下图是的代码段，我喜欢叫它搅拌搅拌再搅拌得出一个随机数如果看到这里你已经被搅晕了，那让我再简单梳理下选择一个时做的事情给出一个，作为的输入。，，得出一个随机数重点是随机数，不是。对于所有的用他们的权重乘以每个对应的随机数，得到乘积。前言前文回顾：《开源社区的明星项目—Ceph谈》、《史上最全的Ceph构件及组件分析》、关于Ceph主题，这一节将详细介绍Ceph CRUSH。Ceph CRU...

newtrek 2019-04-29 15:50 评论0 收藏0
阿里云如何打破Oracle迁移上云的壁垒

摘要：摘要第九届中国数据库技术大会，阿里云数据库产品专家萧少聪带来以阿里云如何打破迁移上云的壁垒为题的演讲。于是，阿里云给出了上面的解决方案。摘要： 2018第九届中国数据库技术大会，阿里云数据库产品专家萧少聪带来以阿里云如何打破Oracle迁移上云的壁垒为题的演讲。Oracle是指数据库管理系统，面对Oracle迁移上云的壁垒，阿里云如何能够打破它呢？本文提出了Oracle 到云数据库P...

chavesgu 2019-06-25 18:39 评论0 收藏0
私有云搭建-私有云搭建之存储虚拟化

摘要：平台采用分布式存储系统作为虚拟化存储，用于对接虚拟化计算及通用数据存储服务，消除集中式网关，使客户端直接与存储系统进行交互，并以多副本纠删码多级故障域数据重均衡故障数据重建等数据保护机制，确保数据安全性和可用性。云计算平台通过硬件辅助的虚拟化计算技术最大程度上提高资源利用率和业务运维管理的效率，整体降低 IT 基础设施的总拥有成本，并有效提高业务服务的可用性、可靠性及稳定性。在解决计算资源的...

ernest.wang 2022-06-28 15:50 评论0 收藏0