遷移到PG 遞歸函數(shù)沒有了Level偽列怎么辦

IT那活兒發(fā)布于2023-01-11 13:20 / 1667人閱讀

今天又遇到一個(gè)問題，在Oracle數(shù)據(jù)庫中有Connectby這類的遞歸SQL。結(jié)果遷移到PostgreSQL中，發(fā)現(xiàn)沒有l(wèi)evel偽列。

作為去O實(shí)力派，沒辦法只能安排。

由于開發(fā)的表比較復(fù)雜，我們這里來造一個(gè)cats表，說明一下這個(gè)問題。我們先看看Oracle中的查詢結(jié)果。

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;

我們?cè)贠racle中執(zhí)行層次查詢得到下列結(jié)果。

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是老大，他的下面一級(jí)是經(jīng)理級(jí)別，一共有三個(gè)經(jīng)理，分別是jones、blake、clark，然后以此往下推。

這里Oracle的語法：

CONNECT BY ：定義了父級(jí)別和子級(jí)別之間的關(guān)系。
PRIOR：則指定了父級(jí)
START WITH：定義我們希望查詢開始的記錄。
level：指示層次結(jié)構(gòu)級(jí)別的偽列。

那么在PostgreSQL中有兩種實(shí)現(xiàn)的方法，第一種叫CTE（commontable expressions），簡(jiǎn)稱公用表表達(dá)式。第二種是安裝自帶插件，使用PG的connectby函數(shù)。

CTE(common tableexpressions)實(shí)現(xiàn)

CTE又叫commontable expressions，它隸屬于SQL:1999標(biāo)準(zhǔn)，在Oracle11gR2版本、MySQL8.0版本、PostgreSQL9.4以上版本都支持。因?yàn)槭菢?biāo)準(zhǔn)的語法，我們這里用mariadb官方文檔（最容易懂）的圖來說明一下原理。

首選我們要使用recursive關(guān)鍵字來表示，這是一個(gè)遞歸的CTE(公用表達(dá)式)。然后第一部分叫AnchorPart，翻譯過來就是錨點(diǎn)。這個(gè)錨點(diǎn)我覺得就代表了樹形查詢的一個(gè)展開的點(diǎn)，比如你要從我們CATS表的ITMGR這個(gè)節(jié)點(diǎn)查詢，那么這個(gè)條件就是錨點(diǎn)。

接下來就是Recursivepart，遞歸的部分。這里會(huì)告訴我們每個(gè)遞歸的步驟將要做什么。每次執(zhí)行出來的結(jié)果，就會(huì)存放到結(jié)果表中，一直到整個(gè)遞歸結(jié)束。

下面的圖詳細(xì)的說明了遞歸到結(jié)果集這一過程。

首選查詢錨點(diǎn)，取出name=‘Alex’的記錄放入到結(jié)果表。

通過錨點(diǎn)查詢的數(shù)據(jù)和原來的數(shù)據(jù)表關(guān)聯(lián)，查詢出錨點(diǎn)的下一層數(shù)據(jù)。比如這里查的是Alex的father和mother。

這里將上面查出來的數(shù)據(jù)Dad和Mom存到結(jié)果表中。

接下來繼續(xù)根據(jù)上面查詢的Dad和Mom，繼續(xù)查詢他們的father和mother。

這里將上面查出來的數(shù)據(jù)GrandpaBill存到結(jié)果表中

就這樣一直查，直到?jīng)]有結(jié)果為止。

上面的原理和語法介紹完了，我們可以在PG中寫同樣的SQL實(shí)現(xiàn)上述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

可以看到結(jié)果類似，但是缺少像Oracle中的偽列l(wèi)evel。這個(gè)偽列是需要我們自己構(gòu)造一個(gè)的。

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

手動(dòng)增加了一個(gè)列，就可以把level偽列功能實(shí)現(xiàn)了，至此我們就解決了開發(fā)的問題。

connectby實(shí)現(xiàn)

上面介紹了比較標(biāo)準(zhǔn)的CTE表達(dá)式，是在各種數(shù)據(jù)庫都已經(jīng)兼容的語法。而PG也有他自己獨(dú)有的一種方法叫connectby，PG的connectby和Oracle中的connectby使用方式有很大的不同。

首先我們要安裝插件tablefunc，這個(gè)插件是軟件自帶的。安裝很簡(jiǎn)單直接createextension tablefunc就可以了。

這個(gè)插件有很多功能，我們這里只用最后一個(gè)功能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	關(guān)鍵字段
parent_keyid_fld	父鍵的關(guān)鍵字段
orderby_fld	排序同級(jí)別字段（可選）
start_with	起始行的鍵值
max_depth	要向下展開的最大深度，零表示無限深度
branch_delim	在分支輸出中用于分隔鍵值的字符串（可選

接下來就是見證這個(gè)函數(shù)魅力的時(shí)候了。

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

可以看到使用起來非常方便，直接就輸入?yún)?shù)就行了。當(dāng)然這里有一個(gè)問題是我們沒辦法通過connectby函數(shù)展示全部的列，它的函數(shù)參數(shù)寫死了類型。如果我們要展示cats表中的catname，就需要我們把當(dāng)前結(jié)果集和原表cats在做一次關(guān)聯(lián)。

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的結(jié)果集完全一致了。

誰的效率高？

如果拿我們想要的結(jié)果來看的話。通過執(zhí)行計(jì)劃來看，很明顯是CTE快一些。

就算我們不取catname，取消掉關(guān)聯(lián)，connecyby函數(shù)執(zhí)行的效率也是比CTE低的。

終于有人喊出了口號(hào)：

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云服務(wù)器云服務(wù)器購買了云服務(wù)器怎么沒有了購買了云服務(wù)器怎么沒有購買了云服務(wù)器怎么沒有實(shí)例怎么把文件遷移到云服務(wù)器

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://m.hztianpu.com/yun/130088.html

發(fā)表評(píng)論

登陸后可評(píng)論

0條評(píng)論

IT那活兒

男|高級(jí)講師

我要關(guān)注我要私信

TA的文章

消息中間件故障分析一例

閱讀 1493·2023-01-11 13:20
RAC雙節(jié)點(diǎn)crash回復(fù)一例

閱讀 1851·2023-01-11 13:20
ORA-600處理一例

閱讀 1290·2023-01-11 13:20
雙節(jié)點(diǎn)RAC實(shí)例2 HANG 故障分析一例

閱讀 2041·2023-01-11 13:20
RAC集群節(jié)點(diǎn)1重啟分析一例

閱讀 4243·2023-01-11 13:20
CRS啟動(dòng)報(bào)錯(cuò)CRS-1656處理分享

閱讀 2948·2023-01-11 13:20
oracle 12CR2打補(bǔ)丁報(bào)錯(cuò)處理一例

閱讀 1581·2023-01-11 13:20
分布式緩存組件故障分析及監(jiān)控優(yōu)化

閱讀 3853·2023-01-11 13:20

成人无码视频,亚洲精品久久久久av无码,午夜精品久久久久久毛片,亚洲中文字幕日韩无码

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長(zhǎng)期優(yōu)惠，快來選購！

遷移到PG 遞歸函數(shù)沒有了Level偽列怎么辦

今天又遇到一個(gè)問題，在Oracle數(shù)據(jù)庫中有Connectby這類的遞歸SQL。結(jié)果遷移到PostgreSQL中，發(fā)現(xiàn)沒有l(wèi)evel偽列。