一道Postgresql遞迴樹題

funnyZpC發表於2020-09-26
SQL遞迴

轉載請註明出處: https://www.cnblogs.com/funnyzpc/p/13698249.html

也是偶然的一次，群友出了一道題考考大家,當時正值疫情最最嚴重的三月(藉口...)，披著外套，天氣也不是很好(藉口...)，耐著性子花了5分鐘理解了下題，
第一個5分鐘...無解，再第二個5分鐘。。。無解，還第三個5分鐘。。。終究無解(之所以如此可能是題目太吸引我了吧)，之後又忙於各種瑣事，一直到離職後重新找工作，
一再想想這事兒還是不能再拖了，終於 就到了今天...接下來開始表演了???

chapter One:題目

  1. 將下表源資料排列成指定順序(看完題目請先思考幾分鐘)

    • 源資料
    id p_id name
    1 0 防控點級別
    2 0 道路標準
    3 0 應急響應等級
    10000 1 一級
    10001 1 二級
    10002 1 三級
    10003 1 四級
    10004 1 五級
    10005 2 主幹路
    10006 2 次幹路
    10007 2 支路
    10008 2 城市下立交區
    • 排列結果
    id p_id name
    1 防控點級別 0
    10000 一級 1
    10001 二級 1
    10002 三級 1
    10003 四級 1
    10004 五級 1
    2 道路標準 0
    10005 主幹路 2
    10011 邊支路1 10005
    10012 邊支路2 10005
    10006 次幹路 2
    10007 支路 2
    10008 城市下立交區 2
    3 應急響應等級 0

chapter Two:亮出SQL

思考完畢，想必這會兒可以小試牛刀了，這就提供下SQL

  • 以下為create 及 必要的insert語句
DROP TABLE IF EXISTS "dicts";
CREATE TABLE "public"."dicts" (
"id" int8 NOT NULL,
"p_id" int8,
"name" varchar(50) COLLATE "pg_catalog"."default"
);

INSERT INTO "dicts" VALUES (10000, 10013, '一級');
INSERT INTO "dicts" VALUES (10001, 10013, '二級');
INSERT INTO "dicts" VALUES (10002, 10013, '三級');
INSERT INTO "dicts" VALUES (10003, 10013, '四級');
INSERT INTO "dicts" VALUES (10004, 10013, '五級');
INSERT INTO "dicts" VALUES (10005, 10011, '主幹路');
INSERT INTO "dicts" VALUES (10006, 10011, '次幹路');
INSERT INTO "dicts" VALUES (10007, 10011, '支路');
INSERT INTO "dicts" VALUES (10008, 10011, '城市下立交區');
INSERT INTO "dicts" VALUES (10011, 99999, '防控點級別');
INSERT INTO "dicts" VALUES (10012, 99999, '應急響應等級');
INSERT INTO "dicts" VALUES (10013, 99999, '道路標準');

chapter Three:再思考

可能很多IT朋友都很急不可耐想知道答案哈哈?，答案或許重要或許也不重要，首先您得有一個思考的過程，一開始我的思考過程是這樣的：

  1. 思考一：這可能就是個普通排序，我直接建一個數字列標記下不久得了(to simple...，如果真這樣還用考嘛)
  2. 思考二：拿出首列作為一個表和剩餘列(也作為一個表)做聯合查詢。。。不行不行，SQL太複雜，後面也沒法排序這是個問題
  3. 思考三：使用遞迴函式，但是遞迴通常只取出指定記錄下級及分支級記錄，如果整體取出SQL太複雜(涉及到迴圈排序)。。。這是個思路，但不完美

思考結果：
我仔細的分析了題目，得出如下結論: 這是一顆帶有遞迴結構(思路)的遞迴樹，之所以特意註明遞迴結構是因為遞迴出來的資料必須有一個帶有樹結構的欄位，
不然之後無法使用排序生成最終結果
雖說遞迴解決了問題的第一步，後面我又碰到了問題的下一個重點：如何實現樹結構欄位列，終於我從實踐中找到了三個解決方案：

  • 方案一: 將遞迴後的結果按虛擬列(遞迴順序列)及p_id列排序，這樣貌似很簡單，SQL走起

    • 以下為SQL語句
    -- SQL語句
with recursive tmp as
(
    select id, name, p_id,id::text as pcode from dicts where p_id=0
    union all
    select origin.id, origin.name, tmp.id as p_id, tmp.id::text as pcode from tmp
    join dicts as origin on origin.p_id = tmp.id
)
select * from tmp order by pcode asc,p_id asc;

    • 以下為執行結果

      id p_id name pcode
      1 防控點級別 0 1
      10004 五級 1 1
      10000 一級 1 1
      10001 二級 1 1
      10002 三級 1 1
      10003 四級 1 1
      2 道路標準 0 2
      10005 主幹路 2 2
      10006 次幹路 2 2
      10007 支路 2 2
      10008 城市下立交區 2 2
      3 應急響應等級 0 3

  • 結果也對，但是 如果id列是varchr(字串)型別，這個排列順序結果就有問題，例如這樣

    id p_id name pcode
    1 防控點級別 0 1
    10004 五級 1 1
    10000 一級 1 1
    10001 二級 1 1
    10002 三級 1 1
    10003 四級 1 1
    10012 邊支路2 10005 10005
    10011 邊支路1 10005 10005
    2 道路標準 0 2
    10005 主幹路 2 2
    10006 次幹路 2 2
    10007 支路 2 2
    10008 城市下立交區 2 2
    3 應急響應等級 0 3

    (注：我將id及p_id改為varchar型別並插入兩行記錄10012及10011)

chapter Four:最終解決

可以看到，以上預設遞迴排序在id及p_id為數字時是符合題目答案的，不過即使這倆欄位是數字在這兩種情況下也是有問題的:

  1. 這棵樹有三級及更多級時
  2. 手動ID大小反序時
  3. 遞迴相關欄位為字元時(上文已提到)

對於這頭兩個種情況這裡不做深入，各位自行測試哈哈哈?
下面我就放出個人覺得合適的方案

  • 方案二

    • 使用遞迴+array函式將每次迴圈時產生的depth(虛擬欄位)及id欄位放進path(虛擬欄位)並按其排序

      • SQL實現語句
          WITH RECURSIVE tt (ID, NAME, p_id, PATH, DEPTH) AS (
    SELECT ID, NAME, p_id, ARRAY[ID] AS PATH, 1 AS DEPTH FROM dicts WHERE p_id='0'
    UNION ALL
    SELECT  D.ID, D.NAME, D.p_id, tt.PATH||D.ID, tt.DEPTH + 1 AS DEPTH
    FROM dicts D
    JOIN tt ON D.p_id = tt.ID
    )
    SELECT ID, NAME, p_id,DEPTH,PATH FROM tt
    ORDER BY PATH;
      • SQL輸出結果
      id name p_id depth path
      1 防控點級別 0 1 {1}
      10000 一級 1 2 {1,10000}
      10001 二級 1 2 {1,10001}
      10002 三級 1 2 {1,10002}
      10003 四級 1 2 {1,10003}
      10004 五級 1 2 {1,10004}
      2 道路標準 0 1 {2}
      10005 主幹路 2 2 {2,10005}
      10011 邊支路1 10005 3 {2,10005,10011}
      10012 邊支路2 10005 3 {2,10005,10012}
      10006 次幹路 2 2 {2,10006}
      10007 支路 2 2 {2,10007}
      10008 城市下立交區 2 2 {2,10008}
      3 應急響應等級 0 1 {3}

  • 方案二

  • 使用__遞迴__+__視窗函式__將每次迴圈時產生的depth(虛擬欄位)及視窗函式產生的序列放進path(虛擬欄位)並按其排序

  • SQL實現語句

    WITH RECURSIVE T (id, name, p_id,path,DEPTH) AS (
SELECT ID, NAME, p_id, row_number() over(order by id asc)::text AS PATH, 1 AS DEPTH FROM dicts WHERE p_id=0
UNION ALL
SELECT  D.ID, D.NAME, D.p_id, T.PATH||'-'||row_number() over(PARTITION by t.path order by d.id asc), T.DEPTH + 1 AS DEPTH FROM dicts D
JOIN T ON D.p_id = T.ID
)
SELECT ID, NAME,p_id,DEPTH,PATH FROM T
ORDER BY PATH asc;

  • SQL輸入結果

    id name p_id depth path
    1 防控點級別 0 1 1
    10000 一級 1 2 1-1
    10001 二級 1 2 1-2
    10002 三級 1 2 1-3
    10003 四級 1 2 1-4
    10004 五級 1 2 1-5
    2 道路標準 0 1 2
    10005 主幹路 2 2 2-1
    10011 邊支路1 10005 3 2-1-1
    10012 邊支路2 10005 3 2-1-2
    10006 次幹路 2 2 2-2
    10007 支路 2 2 2-3
    10008 城市下立交區 2 2 2-4
    3 應急響應等級 0 1 3

    可以看到方案三的輸出結構性更好(純個人摸索出來的);另~,ARRAY的方案 得感謝網友的博文?

finally：總結

首先，得說這是一道很好的SQL題，可不是嘛???，讓我忙活了好一會兒呢。。。,值得一提的是這道題可以考遞迴、排序、ARRAY(高階特性)、型別及型別轉換、當然還有視窗函式,
如果真有某面試官考這個，可就真坑...(￣y▽,￣)╭

另外,若內容有些許謬誤懇請指正哈，各位週末愉快，同時預祝各位一線碼農中秋快樂?

相關文章