成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

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

IT那活兒 / 1595人閱讀
遷移到PG 遞歸函數(shù)沒有了Level偽列怎么辦

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


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


由于開發(fā)的表比較復(fù)雜，我們這里來造一個cats表，說明一下這個問題。我們先看看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;


我們在Oracle中執(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īng)理級別，一共有三個經(jīng)理，分別是jones、blake、clark，然后以此往下推。


這里Oracle的語法：

  • CONNECT BY ：定義了父級別和子級別之間的關(guān)系。

  • PRIOR：則指定了父級

  • START WITH：定義我們希望查詢開始的記錄。

  • level：指示層次結(jié)構(gòu)級別的偽列。


那么在PostgreSQL中有兩種實(shí)現(xiàn)的方法，第一種叫CTE（commontable expressions），簡稱公用表表達(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)鍵字來表示，這是一個遞歸的CTE(公用表達(dá)式)。然后第一部分叫AnchorPart，翻譯過來就是錨點(diǎn)。這個錨點(diǎn)我覺得就代表了樹形查詢的一個展開的點(diǎn)，比如你要從我們CATS表的ITMGR這個節(jié)點(diǎn)查詢，那么這個條件就是錨點(diǎn)。

接下來就是Recursivepart，遞歸的部分。這里會告訴我們每個遞歸的步驟將要做什么。每次執(zhí)行出來的結(jié)果，就會存放到結(jié)果表中，一直到整個遞歸結(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òu)造一個的。

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偽列功能實(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，這個插件是軟件自帶的。安裝很簡單直接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

關(guān)鍵字段

parent_keyid_fld

父鍵的關(guān)鍵字段

orderby_fld

排序同級別字段（可選）

start_with

起始行的鍵值

max_depth

要向下展開的最大深度，零表示無限深度

branch_delim

在分支輸出中用于分隔鍵值的字符串（可

選


接下來就是見證這個函數(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)然這里有一個問題是我們沒辦法通過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低的。


終于有人喊出了口號：

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

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

轉(zhuǎn)載請注明本文地址：http://systransis.cn/yun/130088.html

相關(guān)文章

  • 升級 postgresql

    摘要：但如果涉及到跨大版本升級比如升級到，這種直接替換軟件就不行了，因?yàn)榭绨姹镜膬?nèi)部存儲形式發(fā)生了變化官方對于升級提供了種方法，這里遷移我使用了來進(jìn)行處理細(xì)節(jié)可以參考官方文檔。 1 場景描述 最近使用 postgresql 命令行比較多，就找了個類似 mycli 工具，pgcli，這個是針對 postgresql 的，興沖沖的安裝了 brew install pgcli 沒想到這貨自動幫我...

    learn_shifeng 評論0 收藏0

  • 詳解Ceph的殺手級技術(shù)CRUSH

    摘要：下圖是的代碼段，我喜歡叫它攪拌攪拌再攪拌得出一個隨機(jī)數(shù)如果看到這里你已經(jīng)被攪暈了，那讓我再簡單梳理下選擇一個時(shí)做的事情給出一個，作為的輸入。，，得出一個隨機(jī)數(shù)重點(diǎn)是隨機(jī)數(shù)，不是。對于所有的用他們的權(quán)重乘以每個對應(yīng)的隨機(jī)數(shù)，得到乘積。前言前文回顧：《開源社區(qū)的明星項(xiàng)目—Ceph談》、《史上最全的Ceph構(gòu)件及組件分析》、關(guān)于Ceph主題，這一節(jié)將詳細(xì)介紹Ceph ?CRUSH。Ceph CRU...

    newtrek 評論0 收藏0

  • 阿里云如何打破Oracle遷移上云的壁壘

    摘要：摘要第九屆中國數(shù)據(jù)庫技術(shù)大會，阿里云數(shù)據(jù)庫產(chǎn)品專家蕭少聰帶來以阿里云如何打破遷移上云的壁壘為題的演講。于是，阿里云給出了上面的解決方案。 摘要： 2018第九屆中國數(shù)據(jù)庫技術(shù)大會，阿里云數(shù)據(jù)庫產(chǎn)品專家蕭少聰帶來以阿里云如何打破Oracle遷移上云的壁壘為題的演講。Oracle是指數(shù)據(jù)庫管理系統(tǒng)，面對Oracle遷移上云的壁壘，阿里云如何能夠打破它呢？本文提出了Oracle 到云數(shù)據(jù)庫P...

    chavesgu 評論0 收藏0

  • 私有云搭建-私有云搭建之存儲虛擬化

    摘要：平臺采用分布式存儲系統(tǒng)作為虛擬化存儲，用于對接虛擬化計(jì)算及通用數(shù)據(jù)存儲服務(wù)，消除集中式網(wǎng)關(guān)，使客戶端直接與存儲系統(tǒng)進(jìn)行交互，并以多副本糾刪碼多級故障域數(shù)據(jù)重均衡故障數(shù)據(jù)重建等數(shù)據(jù)保護(hù)機(jī)制，確保數(shù)據(jù)安全性和可用性。云計(jì)算平臺通過硬件輔助的虛擬化計(jì)算技術(shù)最大程度上提高資源利用率和業(yè)務(wù)運(yùn)維管理的效率，整體降低 IT 基礎(chǔ)設(shè)施的總擁有成本，并有效提高業(yè)務(wù)服務(wù)的可用性、可靠性及穩(wěn)定性。在解決計(jì)算資源的...

    ernest.wang 評論0 收藏0

發(fā)表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<