Oracle行資料擴充套件方法總結

有如下需求：
A使用者有1筆交易
B使用者有3筆交易
C使用者有2筆交易
資料如下：
A  1
B  3
C  2

現要求將該表資料擴充套件，變成：
A  1
B  3
B  3
B  3
C  2
C  2

這是一個常見的資料需求，在分析或業務展現中應用比較多。實現的方法有以下幾種（以下命名均是自創，如有雷同，不甚榮幸），假設該表有M行：
第一種：貪婪法
這種方式是不管每行對應的N是多少，首先取出最大的那個N值，然後構造N條記錄的虛擬表，該虛擬表有一個序列，值為1到N。與原表做關聯，條件是虛擬表的序列欄位不大於實際表的N值。即如下：
SQL> with tmp as(
  2  select 'A' a, 1 b from dual union all
  3  select 'B' a, 3 b from dual union all
  4  select 'C' a, 2 b from dual
  5  ),
  6  tmp2 as(select max(b) maxb from tmp)
  7  select a.a, a.b, b.r c
  8    from tmp a, (select rownum r from tmp2 connect by rownum <= maxb) b
  9   where b.r <= a.b
 10   order by a, c
 11  /
 
A          B          C
- ---------- ----------
A          1          1
B          3          1
B          3          2
B          3          3
C          2          1
C          2          2
 
6 rows selected
這裡用到的技術很簡單，就是有條件的部分笛卡爾積。由每個實際行延伸出的虛擬號的序列號不大於該行的N值即可。

第二種：動態笛卡爾積法
每條記錄，都有對應的一個展開因子N，只要能得到一個能根據這個N產生的N行偽記錄，進行笛卡爾積擴充套件，就能得到所要求的資料了，如下：
SQL> with tmp as(
  2  select 'A' a, 1 b from dual union all
  3  select 'B' a, 3 b from dual union all
  4  select 'C' a, 2 b from dual
  5  )
  6  select a.a, a.b, b.column_value c
  7    from tmp a, table(cast(multiset(select rownum from dual connect by rownum <= a.b) as sys.odcinumberlist)) b
  8  /
 
A          B          C
- ---------- ----------
A          1          1
B          3          1
B          3          2
B          3          3
C          2          1
C          2          2
 
6 rows selected
其中用到了以下幾個重要方法：
1、table函式。用於構建虛擬表，並且可以套用同個from子句下其他表的值。為構建虛擬行的基礎。
2、cast型別轉換函式。用於將資料轉換成行。
3、multiset函式。用於將資料轉換成結果集的形式。
4、sys.odcinumberlist型別。告訴cast將子查詢以什麼型別返回結果。
這種方式產生的中間資料結果集即為最終結果集：b欄位的和，即sum(b)。

第三種：自身遞迴法
這種方法是利用了oracle的connectby查詢特性，比較巧妙。將自身與自身為遞迴條件，往無限制層次查詢。於是下面增加了level不大於N值，就控制住了遞迴的層數。但是因為這樣的查詢明顯是迴圈的（prior b=b，再prior b=b），所以要有dbms_random.value來打破這個迴圈。查詢語句如下：
SQL> with tmp as(
  2  select 'A' a, 1 b from dual union all
  3  select 'B' a, 3 b from dual union all
  4  select 'C' a, 2 b from dual
  5  )
  6  select a, b, level c from tmp
  7  connect by prior b = b
  8  and level<=b
  9  and prior dbms_random.value is not null
 10  order by 1,2;
 
A          B          C
- ---------- ----------
A          1          1
B          3          2
B          3          3
B          3          1
C          2          1
C          2          2
 
6 rows selected
 
第四種：xmltable法
原理同第二種型別，就是構建與指定行相同的行數來獲得結果，大致使用方式如下：
with tmp as(
select 'A' a, 1 b from dual union all
select 'B' a, 3 b from dual union all
select 'C' a, 2 b from dual
)
select a.a, a.b
from tmp a, xmltable('1 to integer($n)' passing a.b as n columns b for ordinality) x

四種方法中，以第二種最為簡單和實用，但是在幾個函式的上的用法要比較清楚，以及有sys.odcinumberlis的支援才行，第四種雖然原理上差不多，但是用法比較少見，理解上也比較複雜點。第三種方法利用了connectby的自身遞迴特性，以一定的條件來結束無限制深入，比較巧妙，但是這個在資料量大時，依賴於隨機數的生成會降低效能。第一種最簡單，也是最容易想到的，但是中間結果集由最大N值決定，如果這個值過大，就會導致中間結果集非常大，而實際可能需要的只要一點點。綜合以上幾種情況，如果資料量比較少時，可以使用第一種方法。資料量大，且條件允許，可以使用第二種，或第四種，但是要求對xmltable有比較清楚的認識。第三種也可以使用，但是隨即數的生成影響了其效能，還需要斟酌。