Oracle11GR2的递归WITH子查询-Oracle

Oracle 21NN 3周前 (06-27) 16次浏览 未收录 0个评论 扫描二维码

Oracle11GR2的递归WITH子查询。

SQL> with emp_data(ename,empno,mgr,l) 2 as 3 (select ename, empno, mgr, 1 lvl from emp where mgr is null 4 union all 5 select emp.ename, emp.empno, emp.mgr, ed.l+1 6 from emp, emp_data ed 7 where emp.mgr = ed.empno 8 ) 9 SEARCH DEPTH FIRST BY ename SET order_by 10 select l, 11 lpad(‘*’ ,2*l, ‘*’)||ename nm 12 from emp_data 13 order by order_by 14 /

L NM —- ————— 1 **KING 2 ****BLAKE 3 ******ALLEN 3 ******JAMES 3 ******MARTIN 3 ******TURNER 3 ******WARD 2 ****CLARK 3 ******MILLER 2 ****JONES 3 ******FORD 4 ********SMITH 3 ******SCOTT 4 ********ADAMS

14 rows selected.

不知道真用起来怎么样,按我的想象可以比原来的SYS_CONNECT_BY_PATH多玩出很多新花样,比如按路径累加,更灵活的剪枝条件,

WITH子查询也称为CTE (Common Table Expression),是ANSI SQL-99标准的一部分。ORACLE从9i开始引入WITH子查询,把它被称作SUBQUERY FACTORING(分解子查询)。

WITH子查询的作用类似于内联视图(INLINE VIEW)。内联视图的定义写作SQL的FROM 后面,只能够引用一次;而WITH子查询需要在引用之前先定义,一旦定义了在整个查询的后续部分就可以按名称来反复引用,从这点来看又很像临时表。

从版本11GR2开始,ORACLE支持递归的WITH, 即允许在WITH子查询的定义中对自身引用。这不是什么新鲜事,其他数据库如DB2, Firebird, Microsoft SQL Server, PostgreSQL 都先于ORACLE支持这一特性。但对于ORACLE用户来说,这一递归特性还是很令人期待的,利用它可以轻易实现以往做不到的、或者很难做到的许多新功能。这一章我们就来探索这一令人兴奋的新特性,并把它和以往的实现手段(主要是CONNECT BY层次查询)作比较。

我们先来看看这个递归WITH子查询的语法: WITH ① query_name ([c_alias [, c_alias]…]) ② AS (subquery) ③ [search_clause] ④ [cycle_clause] ⑤ [,query_name ([c_alias [, c_alias]…]) AS (subquery) [search_clause] [cycle_clause]]…

①这是子查询的名称,和以往不同的是,必须在括号中把这个子查询的所有列名写出来。 ②AS后面的subquery就是查询语句,递归部分就写在这里。 ③遍历顺序子句,可以指定深度优先或广度优先遍历顺序。 ④循环子句,用于中止遍历中出现的死循环。 ⑤如果还有其他递归子查询,定义同上。

subquery部分由两个成员组成:anchor member(锚点成员) 和 recursive member(递归成员)。它们之间必须用union all联合起来,anchor member 必须写在recursive member前面。 anchor member用来定位递归的入口,锚点成员是一个SELECT语句,它不可以包含自身名称(query_name)。这相当于CONNECT BY查询中的START WITH,典型写法就是: SELECT … FROM 要遍历的表 WHERE … (起始条件)

递归成员也是一个SELECT语句,用于定义上下级的关系,它必须包含自身名称(即query_name),而且仅仅只能引用一次。递归正是体现在对于自身的引用。典型的做法就是把query_name和其他表(一般来说就是你要遍历的表)做一个连接,连接条件表明了上下级的关系。必须注意,在这个query_name中,并不是截止目前为止的所有数据都是可见的,可见的只是上次递归新加入的最近的一层数据。对query_name列的引用相当于CONNECT BY中的PRIOR操作符。当找不到满足条件的下级,遍历就会停止;如果你还有其他的递归出口条件,也可以一起写在WHERE中,当WHERE不满足时,遍历就会停止,这就是在遍历树、图时候的剪枝操作。越早停止则效率越高。

这个递归成员就是程序员发挥创造力的地方,以往在CONNECT BY中做不到的事情,比如沿路径求和、求积等运算,现在都轻而易举。而SYS_CONNECT_BY_PATH也很容易用字符串拼接’||’来实现。

搜索子句(search_clause)和循环子句(cycle_clause)我们后面的例子中会见到。

下面我们就来看看递归WITH子查询的用法实例。

例1: 先来一个简单例子,从scott/tiger的emp表来查找上下级关系:

传统的CONNECT BY写法: SELECT empno ,ename ,job ,mgr ,deptno ,level ,SYS_CONNECT_BY_PATH(ename,’\’) AS path ,CONNECT_BY_ROOT(ename) AS top_manager FROM EMP START WITH mgr IS NULL — mgr列为空,表示没有上级,该员工已经是最高级别。这是层次查询的起点 CONNECT BY PRIOR empno= mgr;

新的递归WITH写法: WITH T(empno, ename, job, mgr, deptno, the_level, path,top_manager) AS ( —- 必须把结构写出来 SELECT empno, ename, job, mgr, deptno —- 先写锚点查询,用START WITH的条件 ,1 AS the_level —- 递归起点,第一层 ,’\’||ename —- 路径的第一截 ,ename AS top_manager —- 原来的CONNECT_BY_ROOT FROM EMP WHERE mgr IS NULL —- 原来的START WITH条件 UNION ALL —- 下面是递归部分 SELECT e.empno, e.ename, e.job, e.mgr, e.deptno —- 要加入的新一层数据,来自要遍历的emp表 ,1 + t.the_level —- 递归层次,在原来的基础上加1。这相当于CONNECT BY查询中的LEVEL伪列 ,t.path||’\’||e.ename —- 把新的一截路径拼上去 ,t.top_manager —- 直接继承原来的数据,因为每个路径的根节点只有一个 FROM t, emp e —- 典型写法,把子查询本身和要遍历的表作一个连接 WHERE t.empno = e.mgr —- 原来的CONNECT BY条件 ) —- WITH定义结束 SELECT * FROM T ;

查询结果: EMPNO ENAME JOB MGR DEPTNO THE_LEVEL PATH TOP_MANAGE —— ———- ——— —— ——- ———- ————————– ———- 7839 KING PRESIDENT 10 1 \KING KING 7566 JONES MANAGER 7839 20 2 \KING\JONES KING 7698 BLAKE MANAGER 7839 30 2 \KING\BLAKE KING 7782 CLARK MANAGER 7839 10 2 \KING\CLARK KING 7499 ALLEN SALESMAN 7698 30 3 \KING\BLAKE\ALLEN KING 7521 WARD SALESMAN 7698 30 3 \KING\BLAKE\WARD KING 7654 MARTIN SALESMAN 7698 30 3 \KING\BLAKE\MARTIN KING 7788 SCOTT ANALYST 7566 20 3 \KING\JONES\SCOTT KING 7844 TURNER SALESMAN 7698 30 3 \KING\BLAKE\TURNER KING 7900 JAMES CLERK 7698 30 3 \KING\BLAKE\JAMES KING 7902 FORD ANALYST 7566 20 3 \KING\JONES\FORD KING 7934 MILLER CLERK 7782 10 3 \KING\CLARK\MILLER KING 7369 SMITH CLERK 7902 20 4 \KING\JONES\FORD\SMITH KING 7876 ADAMS CLERK 7788 20 4 \KING\JONES\SCOTT\ADAMS KING

14 rows selected.

从结果集的THE_LEVEL和PATH列可以清楚地看到数据是如何被一层一层叠加上去的。

例2: 构造等差数列:

CONNECT BY写法: 这是一个非常特殊的用法,因为没有上下级关系,只有遍历的终止条件。像这类CONNECT BY我强烈推荐在只有一行的结果集上运行(比如FROM DUAL, 比如从一个聚合后的子查询),在多行的集合上运行比较难以控制,头脑必须很清醒。

(以下ROWNUM全部可以改成 LEVEL,效果一样): SELECT ROWNUM n ,ROWNUM*2 n2 ,DATE ‘2010-1-1’+ROWNUM-1 dt ,ADD_MONTHS(DATE ‘2010-1-1’, ROWNUM-1) mon FROM DUAL CONNECT BY ROWNUM<=10;

结果: N N2 DT MON ———- ———- ———– ———– 1 2 2010-01-01 2010-01-01 2 4 2010-01-02 2010-02-01 3 6 2010-01-03 2010-03-01 4 8 2010-01-04 2010-04-01 5 10 2010-01-05 2010-05-01 6 12 2010-01-06 2010-06-01 7 14 2010-01-07 2010-07-01 8 16 2010-01-08 2010-08-01 9 18 2010-01-09 2010-09-01 10 20 2010-01-10 2010-10-01

10 rows selected.

这个简洁优雅的写法最早由Mikito Harakiri(从名字看是个日本人)在asktom网站(https://asktom.oracle.com)发表,现在已经风靡全世界的ORACLE社区。在这个方法被发现之前,一般采用的是从一个大的集合(表或视图)中获取ROWNUM的方法:

SELECT ROWNUM n, ROWNUM*2 n2, DATE ‘2010-1-1’+ROWNUM-1 dt, ADD_MONTHS(DATE ‘2010-1-1’, ROWNUM-1) mon FROM ALL_OBJECTS —- ALL_OBJECTS是个很大的系统视图,它包含的行数足够满足一般的序列构造 WHERE ROWNUM<=10;

下面尝试用递归WITH的写法: WITH t(n,n2,dt,mon) AS ( SELECT 1, 2,TO_DATE(‘2010-1-1′,’YYYY-MM-DD’),TO_DATE(‘2010-1-1′,’YYYY-MM-DD’) FROM DUAL — 先构造第一个 UNION ALL SELECT t.n+1 —- 递增1 ,t.n2+2 —- 递增2 ,dt+1 —- 下一日 ,ADD_MONTHS(mon,1) —- 下个月 FROM t —- 没有任何连接,因为不需要,所有数据都可以从锚点成员中衍生出来 WHERE t.n<10 ) SELECT * FROM T;

一切都按规矩来,竟然还是出错了: ,ADD_MONTHS(mon,1) —- 下个月 * ERROR at line 6: ORA-01790: expression must have same datatype as corresponding expression

改为字符串型看看: WITH t(n,n2,dt,mon) AS ( SELECT 1, 2,’2010-01-01′,’2010-01-01′ FROM DUAL —- 用字符串来表示日期 UNION ALL SELECT t.n+1 —- 递增1 ,t.n2+2 —- 递增2 ,TO_CHAR(TO_DATE(t.dt,’YYYY-MM-DD’)+1,’YYYY-MM-DD’) —- 先转换为日期型,计算后换回字符串型 ,TO_CHAR(ADD_MONTHS(TO_DATE(t.mon,’YYYY-MM-DD’),1),’YYYY-MM-DD’) —- 计算下个月,方法同上 FROM t WHERE t.n<10 ) SELECT * FROM T;

我很惊奇地看到这个结果: N N2 DT MON ———- ———- ———- ———- 1 2 2010-01-01 2010-01-01 2 4 2009-12-31 2010-02-01 —– DT竟然是递减的! 3 6 2009-12-30 2010-03-01 4 8 2009-12-29 2010-04-01 5 10 2009-12-28 2010-05-01 6 12 2009-12-27 2010-06-01 7 14 2009-12-26 2010-07-01 8 16 2009-12-25 2010-08-01 9 18 2009-12-24 2010-09-01 10 20 2009-12-23 2010-10-01

10 rows selected.

这是ORACEL 11.2.0.1.0版本的BUG,后续版本应该会改正。

没办法,只好想其他招数绕过去: WITH t(n) AS ( SELECT 1 FROM DUAL — 先构造第一个 UNION ALL SELECT t.n+1 —- 仅仅是整数序列 FROM t WHERE t.n<10 ) SELECT n ,n*2 n2 ,DATE ‘2010-1-1’+n-1 dt —- 在最终的查询中进行日期运算 ,ADD_MONTHS(DATE ‘2010-1-1’, n-1) mon FROM T; 这下子对了: N N2 DT MON ———- ———- ———– ———– 1 2 2010-01-01 2010-01-01 2 4 2010-01-02 2010-02-01 3 6 2010-01-03 2010-03-01 4 8 2010-01-04 2010-04-01 5 10 2010-01-05 2010-05-01 6 12 2010-01-06 2010-06-01 7 14 2010-01-07 2010-07-01 8 16 2010-01-08 2010-08-01 9 18 2010-01-09 2010-09-01 10 20 2010-01-10 2010-10-01

10 rows selected.

看来对日期的运算有BUG。解决办法就是先构造整数序列,然后在最终的查询中再利用这个整数序列来构造日期序列。

从一个单行结果集CONNECT BY的例子: SELECT ROWNUM rn,cnt FROM (SELECT COUNT(*) cnt FROM emp) —- 经过聚合的只有一行的结果集 CONNECT BY ROWNUM<=cnt;

结果: RN CNT ———- ———- 1 14 2 14 3 14 4 14 5 14 6 14 7 14 8 14 9 14 10 14 11 14 12 14 13 14 14 14

14 rows selected.

递归WITH写法: WITH t(n,cnt) AS ( SELECT 1,COUNT(*) cnt FROM EMP — 先构造第一个 UNION ALL SELECT t.n+1 —- 递增1 ,t.cnt —- 这个cnt列不做任何修改,从第一层得来 FROM t —- 没有任何连接,因为不需要 WHERE t.n 结果同上(略)。

例3: 独立事件的排列组合:一个布袋中装有数量相同的四种颜色的小球。随机从布袋中取四次,每次取完都放回去。现在问四次结果总颜色数等于3的概率是多少?

传统的CONNECT BY写法: WITH t AS ( SELECT ROWNUM rn — 先构造一个1,2,3,4的结果集,每个rn表示一种颜色 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2 AS ( —- 集合t2模拟独立取四次的动作,最终结果会有4*4*4*4=256行 SELECT ROWNUM id —- 构造唯一ID供下面拆分用 ,REPLACE(SYS_CONNECT_BY_PATH(rn,’@’),’@’) path —- 用一个特殊字符@来作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt —- 利用分析函数算出总行数并把它作为一个列返回 FROM t —- 这个是有四行的集合 WHERE LEVEL=4 —- 我们需要的仅仅是最后一层的结果。在PATH里面已经包含了取四次的所有结果组合 CONNECT BY LEVEL<=4 —- 没有任何条件,前后都是独立的 ) ,t3 AS ( —- 集合t3把t2中的PATH包含的颜色组合拆开为四行 SELECT id,cnt,SUBSTR(PATH,rn,1) color FROM t2,t —- 笛卡儿积,用于把t2中的一行变为四行 ) SELECT COUNT(COUNT(*))/MAX(cnt) AS prob FROM t3 GROUP BY id,cnt HAVING COUNT(DISTINCT color)=3 — 每一个id中包含三种颜色 ;

结果: PROB ———- .5625

这个例子展示了CONNECT BY来模拟排列组合的技巧。每一层遍历表示一次抽取的动作,因为每次都是完全独立的,在CONNECT BY 里面仅仅限制了抽取次数(遍历层数)而没有其他条件。SYS_CONNECT_BY_PATH可以把截至当前为止所访问到的各层次的数据串起来,在LEVEL=N就包含了前N层的排列组合情况。你可以用这个查询来看看中间生成的结果集t2:

WITH t AS ( SELECT ROWNUM rn — 先构造一个1,2,3,4的结果集,每个rn表示一种颜色 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2 AS ( —- 集合t2模拟独立取四次的动作,最终结果会有4*4*4*4=256行 SELECT ROWNUM id —- 构造唯一ID供下面拆分用 ,REPLACE(SYS_CONNECT_BY_PATH(rn,’@’),’@’) path —- 用一个特殊字符@来作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt —- 利用分析函数算出总行数并把它作为一个列返回 FROM t —- 这个是有四行的集合 WHERE LEVEL=4 —- 我们需要的仅仅是最后一层的结果。在PATH里面已经包含了取四次的所有结果组合 CONNECT BY LEVEL<=4 —- 没有任何条件,前后都是独立的 ) SELECT * FROM t2;

ID PATH CNT ———- ———- ———- 1 1111 256 2 1112 256 3 1113 256 4 1114 256 5 1121 256 6 1122 256 7 1123 256 8 1124 256 9 1131 256 10 1132 256 11 1133 256 ……(其余结果略)

256 rows selected.

由此看到PATH列已经包含了四次抽取的所有可能结果,每个结果都被赋予一个唯一的编号ID。

如果你好奇的话可以看看下一步的结果集t3: WITH t AS ( SELECT ROWNUM rn — 先构造一个1,2,3,4的结果集,每个rn表示一种颜色 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2 AS ( —- 集合t2模拟独立取四次的动作,最终结果会有4*4*4*4=256行 SELECT ROWNUM id —- 构造唯一ID供下面拆分用 ,REPLACE(SYS_CONNECT_BY_PATH(rn,’@’),’@’) path —- 用一个特殊字符@来作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt —- 利用分析函数算出总行数并把它作为一个列返回 FROM t —- 这个是有四行的集合 WHERE LEVEL=4 —- 我们需要的仅仅是最后一层的结果。在PATH里面已经包含了取四次的所有结果组合 CONNECT BY LEVEL<=4 —- 没有任何条件,前后都是独立的 ) ,t3 AS ( —- 集合t3把t2中的PATH包含的颜色组合拆开为四行 SELECT id,cnt,SUBSTR(PATH,rn,1) color FROM t2,t —- 笛卡儿积,用于把t2中的一行变为四行 ) SELECT * FROM t3;

ID CNT COLO ———- ———- —- 1 256 1 1 256 1 1 256 1 1 256 1 2 256 1 2 256 1 2 256 1 2 256 2 3 256 1 3 256 1 3 256 1 3 256 3 4 256 1 4 256 1 4 256 1 4 256 4 ……(其余结果略)

1024 rows selected. 可以看到t2集合中的每一行都被拆成了四行,这是为了后面的聚合运算。

最后看看算概率的主查询: SELECT COUNT(COUNT(*))/MAX(cnt) AS prob FROM t3 GROUP BY id,cnt HAVING COUNT(DISTINCT color)=3;

COUNT(DISTINCT color)可以算出每个ID中包含不重复的颜色数目,放在HAVING中过滤了数目不为3的那些ID。

GROUP BY id,cnt 表示按照id来分组。因为所有行的cnt都是一样的(都等于256),我们在分组加入它并不会改变分组的结果,加入cnt的目的是为了在查询中引用。 最后的连续两层COUNT函数的意思是要把分组结果再聚合为一行,算出满足条件的id的行数。除以cnt就得到了我们要的概率。

本例是一个在多行的结果集上进行无条件遍历的例子,前面说过了要特别小心,因为没有上下级关系,随着层数递增,数据量的增长十分可观。

递归WITH写法:

WITH T AS ( SELECT ROWNUM rn — 还是先构造一个1,2,3,4的结果集 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2(distinct_colors,lvl) AS ( — 两个列:所有不重复颜色,层次 SELECT ‘\’||rn,1 —- 第一层就是最基础的四种颜色的表 FROM t UNION ALL SELECT CASE WHEN INSTR(t2.distinct_colors||’\’,’\’||t.rn||’\’)=0 — 这个颜色没有出现过 THEN t2.distinct_colors||’\’||t.rn — 拼上去 ELSE t2.distinct_colors —- 颜色已经出现,保持原来的 END ,t2.lvl+1 — 层数递增 FROM t, t2 WHERE t2.lvl<4 — 递归出口的条件:次数达到限制 ) SELECT COUNT(CASE WHEN LENGTH(distinct_colors) – LENGTH(REPLACE(distinct_colors,’\’))=3 THEN 1 END) — 出现三个斜杠 /COUNT(*) FROM t2 WHERE lvl=4 —- 同CONNECT BY类似,我们只需观察最后一层的数据,在这里面已经包含了所有层次的颜色 ;

在递归WITH子查询t2中,我们看到它用了一个CASE表达式把以前没出现过的颜色拼接到distinct_colors中。这个CASE是递归WITH的妙处,用SYS_CONNECT_BY_PATH没办法做到有条件的拼接。

而最后在计算颜色数的时候用了一个技巧,把颜色数转换为斜杠的个数,因为我们构造数据的时候每种颜色前面都带一个斜杠。为了求出字符串中某字符出现的次数,我们用了这样的办法: 先求出字符串的总长度; 用REPLACE函数从串中去除这个字符,然后再求一次长度; 两个长度之差就是被去除的字符个数。 CASE函数把出现满足条件的标记置为1,不满足则为NULL, 那么再套一个COUNT函数就能算出满足条件的行数,因为NULL是不被COUNT计入的。 COUNT和CASE的嵌套使用,也是在聚合运算中常用的技巧。


21NN.CN , 版权所有丨如未注明 , 均为原创丨转载请注明原文链接:Oracle11GR2的递归WITH子查询-Oracle
喜欢 (0)
[1353713598@qq.com]
分享 (0)
关于作者:
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址