有如下需求:
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有比较清楚的认识。第三种也可以使用,但是随即数的生成影响了其性能,还需要斟酌。
3、connect by自循环2:
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有比较清楚的认识。第三种也可以使用,但是随即数的生成影响了其性能,还需要斟酌。
还有三种方法:
1、递归with;
with tt 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),
t(a,
b) as
(select a, 1
from tt
group by a
union all
select tt.a, t.b + 1
from t, tt
where t.a = tt.a
and t.b <= tt.b)
select * from t order by 1, 2
(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),
t(a,
b) as
(select a, 1
from tt
group by a
union all
select tt.a, t.b + 1
from t, tt
where t.a = tt.a
and t.b <= tt.b)
select * from t order by 1, 2
2、connect by自循环;
WITH data AS (
SELECT 'A' name, 2 cnt FROM DUAL
UNION ALL SELECT 'B', 4 FROM DUAL
UNION ALL SELECT 'C', 1 FROM DUAL
)
select * from data
start with name = name
connect by name = name and rownum <= cnt + 1
SELECT 'A' name, 2 cnt FROM DUAL
UNION ALL SELECT 'B', 4 FROM DUAL
UNION ALL SELECT 'C', 1 FROM DUAL
)
select * from data
start with name = name
connect by name = name and rownum <= cnt + 1
3、connect by自循环2:
WITH data AS (
SELECT 'A' name, 2 cnt FROM DUAL
UNION ALL SELECT 'B', 4 FROM DUAL
UNION ALL SELECT 'C', 1 FROM DUAL
)
SELECT 'A' name, 2 cnt FROM DUAL
UNION ALL SELECT 'B', 4 FROM DUAL
UNION ALL SELECT 'C', 1 FROM DUAL
)
SELECT name,cnt
FROM data
CONNECT BY NAME=connect_by_root(NAME) AND LEVEL<=cnt
FROM data
CONNECT BY NAME=connect_by_root(NAME) AND LEVEL<=cnt
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12932950/viewspace-717314/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12932950/viewspace-717314/