sql行数据据扩展总结

有如下需求：
A用户有1笔交易
B用户有3笔交易
C用户有2笔交易
数据如下：
A 1
B 3
C 2

现要求将该表数据扩展，变成：
A 1
B 3
B 3
B 3
C 2
C 2

第一种：贪婪法
 这种方式是不管每行对应的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; A          B          C- ---------- ----------A          1          1B          3          1B          3          2B          3          3C          2          1C          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,  8         table(cast(multiset  9                    (select rownum from dual connect by rownum <= a.b) as 10                    sys.odcinumberlist)) b; A          B          C- ---------- ----------A          1          1B          3          1B          3          2B          3          3C          2          1C          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  7    from tmp  8  connect by prior b = b  9         and level <= b 10         and prior dbms_random.value is not null 11   order by 1, 2; A          B          C- ---------- ----------A          1          1B          3          2B          3          3B          3          1C          2          1C          2          2 6 rows selected

第四种：MODEL方法

SQL> WITH tmp AS (  2   SELECT  'A' a, 1 b FROM DUAL  3   UNION ALL SELECT 'B', 3 FROM DUAL  4   UNION ALL SELECT 'C', 2 FROM DUAL  5   )  6   SELECT a,b  7    FROM tmp  8   MODEL  9   PARTITION BY (a) 10   DIMENSION BY (1 n) 11   MEASURES (b) 12   RULES 13  ITERATE (100) UNTIL (ITERATION_NUMBER>=b[1]-1) 14     ( 15        b[ITERATION_NUMBER+1]=b[1] 16     ) 17   ORDER BY 1; A          B- ----------A          1B          3B          3B          3C          2C          2 6 rows selected

 第五种：xmltable法
原理同第二种类型，就是构建与指定行相同的行数来获得结果，大致使用方式如下：

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  7     from tmp a,  8          xmltable('1 to xs:integer($n)' passing a.b as "n" columns b for  9                   ordinality) x; A          B- ----------A          1B          3B          3B          3C          2C          2 6 rows selected

 

第六种 ：

SQL> WITH tmp AS (  2     SELECT  'A' a, 1 b FROM DUAL  3     UNION ALL SELECT 'B', 3 FROM DUAL  4     UNION ALL SELECT 'C', 2 FROM DUAL  5     ),  6   rdata (a, b, lv) as  7     (  8   select a, b, 1 as lv from tmp  9   union all 10   select a, b, lv + 1 as lv from rdata where lv + 1 <= b 11     ) 12     select * from rdata 13     order by a, lv; A          B         LV- ---------- ----------A          1          1B          3          1B          3          2B          3          3C          2          1C          2          2 6 rows selected

总结：
六种方法中，以第二种最为简单和实用，但是在几个函数的上的用法要比较清楚，以及有
sys.odcinumberlis的支持才行，第五种虽然原理上差不多，但是用法比较少见，理解上也比
较复杂点。第三种方法利用了connectby的自身递归特性，以一定的条件来结束无限制深入，
比较巧妙，但是这个在数据量大时，依赖于随机数的生成会降低性能。第一种最简单，也是
最容易想到的，但是中间结果集由最大N值决定，如果这个值过大，就会导致中间结果集非
常大，而实际可能需要的只要一点点。综合以上几种情况，如果数据量比较少时，可以使用
第一种方法。数据量大，且条件允许，可以使用第二种，或第五种，但是要求对xmltable有
比较清楚的认识。第三种也可以使用，但是随即数的生成影响了其性能，还需要斟酌。