一個樹形聚集SQL問題（二）

看到ITPUB上一個帖子，感覺樓主的需要比較有意思，於是嘗試了一下。問題源自：http://www.itpub.net/thread-1020586-1-1.html

上一篇給出了一個SQL實現，不過由於不是很清楚樓主的含義，加上測試資料過於簡單，沒有將問題完全展現，因此第一篇給出的SQL並不能完全滿足需要。這篇根據新的測試資料來構造求解的SQL。

一個樹形聚集SQL問題（一）：http://yangtingkun.itpub.net/post/468/466388

 

 

雖然上一篇給出的SQL能得到正確的結果，但是真正的情況比測試中要複雜很多，樹形的分叉並非只可能存在於第一層，而是在任一層都可能包括多個葉節點。

比如插入一條記錄：

SQL> SELECT * FROM T_LEVEL;

    LEVELS PARENT     CHILD      PARENT_QTY  CHILD_QTY
---------- ---------- ---------- ---------- ----------
         1 A          B                   1          3
         2 B          C                   2          3
         3 C          D                   5          6
         4 D          E                   1          2
         1 A          Z                   1          3

SQL> INSERT INTO T_LEVEL VALUES (4, 'D', 'F', 1, 3);

已建立 1 行。

SQL> SELECT P,
  2   SUBSTR(MAX(D), LENGTH(MAX(D))) C,
  3   POWER(10, SUM(LOG(10, Q))) Q
  4  FROM
  5  (
  6  SELECT CONNECT_BY_ROOT(PARENT) P,
  7   SYS_CONNECT_BY_PATH(CHILD, '/') D,
  8   CHILD_QTY/PARENT_QTY Q
  9  FROM T_LEVEL
 10  START WITH LEVELS = 1
 11  CONNECT BY PRIOR CHILD = PARENT
 12  )
 13  GROUP BY P, SUBSTR(D, 2, 1);

P          C                   Q
---------- ---------- ----------
A          F                32.4
A          Z                   3

現在得到的結果顯然是不正確的，這裡應該得到3條記錄，A到E、A到F和A到Z。

雖然SQL得到的結果是不正確的，不過解題的思想並沒有變，仍然是透過SUM解決連乘問題，透過構造來獲取聚集的GROUP BY欄位。

為了簡單這個問題，可以將樹形查詢反過來進行，從所有的葉節點出發，彙總資料到根節點，具體實現SQL如下：

SQL> SELECT
  2   SUBSTR(MAX(D), INSTR(MAX(D), '/', -1) + 1) C,
  3   P,
  4   POWER(10, SUM(LOG(10, Q))) Q
  5  FROM
  6  (
  7   SELECT CONNECT_BY_ROOT(CHILD) P,
  8    SYS_CONNECT_BY_PATH(PARENT, '/') D,
  9    CHILD_QTY/PARENT_QTY Q
 10   FROM T_LEVEL
 11   START WITH CHILD IN
 12   (
 13    SELECT CHILD FROM
 14    (
 15     SELECT CHILD, CONNECT_BY_ISLEAF LEAF FROM T_LEVEL
 16     START WITH LEVELS = 1
 17     CONNECT BY PRIOR CHILD = PARENT
 18    )
 19    WHERE LEAF = 1
 20   )
 21   CONNECT BY PRIOR PARENT = CHILD
 22  )
 23  GROUP BY P
 24  ;

C                              P                                       Q
------------------------------ ------------------------------ ----------
A                              Z                                       3
A                              E                                    10.8
A                              F                                    16.2

這種方式仍然是10g的方法，因為使用了CONNECT_BY_ISLEAF偽列，而這個偽列是10g的新特性，由來標識當前的記錄是否是葉節點。

如果在9i中，則需要使用別的方法來實現這個功能，實現的SQL如下：

SQL> SELECT
  2   SUBSTR(MAX(D), INSTR(MAX(D), '/', -1) + 1) C,
  3   SUBSTR(P, 2, INSTR(P, '/', 1, 2) - 2) P,
  4   POWER(10, SUM(LOG(10, Q))) Q
  5  FROM
  6  (
  7   SELECT SYS_CONNECT_BY_PATH(CHILD, '/') || '/' P,
  8    SYS_CONNECT_BY_PATH(PARENT, '/') D,
  9    CHILD_QTY/PARENT_QTY Q
 10   FROM T_LEVEL
 11   START WITH CHILD IN
 12   (
 13    SELECT CHILD FROM
 14    (
 15     SELECT CHILD,
 16      CASE WHEN LEAD(LEVELS) OVER(ORDER BY ID) > LEVELS THEN 0 ELSE 1 END LEAF
 17     FROM
 18     (
 19      SELECT ROWNUM ID, LEVELS, PARENT, CHILD
 20      FROM T_LEVEL
 21      START WITH LEVELS = 1
 22      CONNECT BY PRIOR CHILD = PARENT
 23      ORDER SIBLINGS BY LEVELS
 24     )
 25    )
 26    WHERE LEAF = 1
 27   )
 28   CONNECT BY PRIOR PARENT = CHILD
 29  )
 30  GROUP BY SUBSTR(P, 2, INSTR(P, '/', 1, 2) - 2);

C                              P                                       Q
------------------------------ ------------------------------ ----------
A                              Z                                       3
A                              E                                    10.8
A                              F                                    16.2