oracle 每日一题-分析函数row_number

最新推荐文章于 2021-10-28 12:14:47 发布

zhq200902

最新推荐文章于 2021-10-28 12:14:47 发布

阅读量567

点赞数 1

分类专栏： oracle 文章标签： oracle

oracle 专栏收录该内容

19 篇文章 1 订阅

订阅专栏

转自http://www.itpub.net/thread-2084317-1-1.html 感谢newkid

原始出处：
http://www.plsqlchallenge.com/

作者：Kim Berg Hansen

运行环境：SQLPLUS, SERVEROUTPUT已打开

我有一张表保存着每个人的姓名，出生日期（DOB），以及父亲：

create table qz_men (
man_id integer primary key
, name    varchar2(10)
, dob    date
, father_id integer references qz_men
)
/

insert into qz_men values (10, 'Zebadiah', date '1950-01-10', null)
/
insert into qz_men values (15, 'William' , date '1970-01-15', 10  )
/
insert into qz_men values (16, 'Stephen' , date '1972-01-16', 10  )
/
insert into qz_men values (20, 'Roger' , date '1990-01-20', 15  )
/
insert into qz_men values (21, 'Perry' , date '1992-01-21', 15  )
/
insert into qz_men values (25, 'Louis' , date '1993-01-25', 16  )
/
insert into qz_men values (26, 'Jerry' , date '1991-01-26', 16  )
/
commit
/

我想要一张以出生日期排序的清单，显示每个人是他父亲的第几个儿子。

哪些选项包含的查询能够产生如下的输出：

MAN_ID NAME    DOB       PARENTAGE
---------- ---------- ---------- --------------------
      10 Zebadiah 1950-01-10 Unknown
      15 William 1970-01-15 1. son of Zebadiah
      16 Stephen 1972-01-16 2. son of Zebadiah
      20 Roger    1990-01-20 1. son of William
      26 Jerry    1991-01-26 1. son of Stephen
      21 Perry    1992-01-21 2. son of William
      25 Louis    1993-01-25 2. son of Stephen

(A)
select m.man_id
   , m.name
   , m.dob
   , nvl2(
      m.father_id
      , row_number() over (
         partition by PRIOR m.man_id
         order by m.dob
      ) || '. son of ' || PRIOR m.name
      , 'Unknown'
   ) as parentage
  from qz_men m
start with m.father_id is null
connect by m.father_id = prior m.man_id
order by m.dob
/

(B)
select m.man_id
   , m.name
   , m.dob
   , nvl2(
      m.father_id
      , row_number() over (
         partition by PRIOR m.man_id
         order SIBLINGS by m.dob
      ) || '. son of ' || PRIOR m.name
      , 'Unknown'
   ) as parentage
  from qz_men m
start with m.father_id is null
connect by m.father_id = prior m.man_id
order by m.dob
/

(C)
select m.man_id
   , m.name
   , m.dob
   , nvl2(
      m.father_id
      , row_number() over (
         partition by m.father_id
         order by m.dob
      ) || '. son of ' || (
         select name
            from qz_men f
            where f.man_id = m.father_id
      )
      , 'Unknown'
   ) as parentage
  from qz_men m
start with m.father_id is null
connect by m.father_id = prior m.man_id
order by m.dob
/

(D)
select m.man_id
   , m.name
   , m.dob
   , nvl2(
      m.father_id
      , row_number() over (
         partition by m.father_id
         order by m.dob
      ) || '. son of ' || (
         select name
            from qz_men f
            where f.man_id = m.father_id
      )
      , 'Unknown'
   ) as parentage
  from qz_men m
order by m.dob
/

(E)
select s2.man_id
   , s2.name
   , s2.dob
   , nvl2(
      s2.father_id
      , row_number() over (
         partition by s2.father_id
         order by s2.rn
      ) || '. son of ' || s2.father_name
      , 'Unknown'
   ) as parentage
  from (
select s1.*
      , rownum as rn
   from (
   select m.*
         , prior m.name as father_name
      from qz_men m
   start with m.father_id is null
   connect by m.father_id = prior m.man_id
   order SIBLINGS by m.dob
   ) s1
  ) s2
order by s2.dob
/

(F)
select s1.man_id
   , s1.name
   , s1.dob
   , nvl2(
      s1.father_id
      , row_number() over (
         partition by s1.father_id
         order by s1.rn
      ) || '. son of ' || s1.father_name
      , 'Unknown'
   ) as parentage
  from (
select m.*
      , prior m.name as father_name
      , row_number() over (
         order SIBLINGS by m.dob
      ) as rn
   from qz_men m
start with m.father_id is null
connect by m.father_id = prior m.man_id
  ) s1
order by s1.dob
/

A: （推荐）
在分析函数row_number中我们按照父亲分区，按照每个父亲的孩子的出生日期排序，这样就给出了我们所需的每个孩子的序号。

B: 在CONNECT BY里面，在ORDER BY中使用SIBLING子句，就可以在保留层次顺序的同时，以特定顺序获得树中的兄弟节点，这常常是很有用的。然而在分析函数中是不允许的，所以这个选项会报错：
ORA-30929: ORDER SIBLINGS BY clause not allowed here.
此外，在这个例子中SIBLINGS是不需要的，因为我们是按父亲分区（如同前一选项）

C: （不推荐）
在分区部分，我们在此处用了FATHER_ID而不是选项A那样的PRIOR用法，这是可以的，会得到相同结果。但是选项A同样用了PRIOR来得到父亲的名字，而此处我们用了一个标量子查询，这样也行，但对于数据库而言是不必要的工作。

D: 如果用了标量子查询而不是PRIOR子句，我们就可以完全去除CONNECT BY，因为所需的结果也不是按层次的顺序排列。选项A用了CONNECT BY来通过PRIOR得到父亲的名字, 这样在遍历树的时候也完成了FATHER_ID的查找。这个选项不需要遍历树，但是在MAN_ID上做查找，这样也许会受益于标量子查询的缓存，这取决于数据。大多数情况下，选项A的性能是最好的，但是取决于树的深度和宽度以及索引情况，在某些特殊情况下这个选项也可能很好。这需要测试。
E: （不推荐）
此处我们在两层的内联视图中用了SIBLINGS（因为ROWNUM是在ORDER之前产生的，我们需要两个层次的内联视图）来在RN列中生成我们需要的序号，然后就可以在我们的分析函数中使用。它没有问题，但是对这个例子是小题大做，因为以父亲分区就不需要层次顺序了。对于其它用例，这可能是个有用的技巧，可以用来保留层次的顺序。
F: 这是在尝试将前一选项简化为一个内联视图而不是双层视图，用的是分析函数ROW_NUMBER而不是ROWNUM。ROW_NUMBER经常是ROWNUM的一个很好的替身，但是在这个例子中它行不通，因为分析函数的ORDER BY不允许SIBLINGS子句，会报错：
ORA-30929: ORDER SIBLINGS BY clause not allowed here.