一個樹形聚集SQL問題

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

 

 

簡單的說，這個問題就是要實現從根節點到每個葉節點的樹形記錄實現連乘。

解決這個問題只有兩點相對比較麻煩，一個是實現連乘，關於這個問題的實現可以參考：http://yangtingkun.itpub.net/post/468/466369

第二個問題就是需要構造用來GROUP BY的欄位。因為表中並不存在一個可以標識不同路徑的欄位，因此要實現聚集功能，需要人為構造一個用來聚集的欄位。

首先構造一個例子：

SQL> CREATE TABLE T_LEVEL
  2  (
  3   LEVELS NUMBER,
  4   PARENT VARCHAR2(10),
  5   CHILD VARCHAR2(10),
  6   PARENT_QTY NUMBER,
  7   CHILD_QTY NUMBER
  8  );

表已建立。

SQL> INSERT INTO T_LEVEL VALUES (1, 'A', 'B', 1, 3);

已建立 1 行。

SQL> INSERT INTO T_LEVEL VALUES (2, 'B', 'C', 2, 3);

已建立 1 行。

SQL> INSERT INTO T_LEVEL VALUES (3, 'C', 'D', 5, 6);

已建立 1 行。

SQL> INSERT INTO T_LEVEL VALUES (4, 'D', 'E', 1, 2);

已建立 1 行。

SQL> INSERT INTO T_LEVEL VALUES (1, 'A', 'Z', 1, 3);

已建立 1 行。

SQL> COMMIT;

提交完成。

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

希望得到的結果是A包括10.8個E和3個Z。根據上面給出的連乘方法，實現連乘是很簡單的：

SQL> SELECT POWER(10, SUM(LOG(10, CHILD_QTY/PARENT_QTY))) FROM T_LEVEL;

POWER(10,SUM(LOG(10,CHILD_QTY/PARENT_QTY)))
-------------------------------------------
                                       32.4

這樣很輕易的實現了所有記錄的連乘，不過這裡需要根據根節點到不同的葉節點的不同路徑實現連乘。現有的欄位沒有可以用來表示這種不同的路徑，因此需要構造這樣的欄位。

下面是10g中解決這個問題的SQL：

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          E                10.8
A          Z                   3

之所以說是10g的解法，是因為裡面包含了10g的新功能，樹形查詢操作CONNECT_BY_PATH，利用這個操作可以獲取根節點的欄位。

當然，如果是9i，也可以使用其他方法來代替這個操作，只不過SQL略微麻煩一點而已：

SQL> SELECT SUBSTR(P, 2, 1) P,
  2   SUBSTR(MAX(D), LENGTH(MAX(D))) C,
  3   POWER(10, SUM(LOG(10, Q))) Q
  4  FROM
  5  (
  6  SELECT SYS_CONNECT_BY_PATH(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 SUBSTR(P, 2, 1), SUBSTR(D, 2, 1);

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