oracle中求差,ORACLE确定当前记录和下一条记录之间的差值

转自：http://blog.csdn.net/wh62592855/article/details/4988336

例如说吧，对DEPTNO 10中的每个员工，确定聘用他们的日期及聘用下一个员工(可能是其他部门的员工)的日期之间相差的天数。

SQL> select ename,hiredate,deptno from emp order by hiredate;

ENAME      HIREDATE            DEPTNO

---------- --------------- ----------

SMITH      17-DEC-80               20

ALLEN      20-FEB-81               30

WARD       22-FEB-81               30

JONES      02-APR-81               20

BLAKE      01-MAY-81               30

CLARK      09-JUN-81               10

TURNER     08-SEP-81               30

MARTIN     28-SEP-81               30

KING       17-NOV-81               10

JAMES      03-DEC-81               30

FORD       03-DEC-81               20

ENAME      HIREDATE            DEPTNO

---------- --------------- ----------

MILLER     23-JAN-82               10

SCOTT      19-APR-87               20

ADAMS      23-MAY-87               20

14 rows selected.

SQL> select ename,hiredate,next_hd,

2  next_hd-hiredate diff

3  from

4  (

5  select deptno,ename,hiredate,

6  lead(hiredate) over(order by hiredate) next_hd

7  from emp

8  )

9  where deptno=10;

ENAME      HIREDATE        NEXT_HD               DIFF

---------- --------------- --------------- ----------

CLARK      09-JUN-81       08-SEP-81               91

KING       17-NOV-81       03-DEC-81               16

MILLER     23-JAN-82       19-APR-87             1912

这里的LEAD OVER非常有用，它能够访问“未来的”行(“未来的”行相对于当前行，由ORDER BY子句决定)。这种无需添加联接就能够访问当前行附近行的功能，提高了代码的可读性和有效性。在采用窗口函数时，一定要记住，它在WHERE子句之后求值，因此在该解决方案中，需要使用内联视图。如果把对DEPTNO的筛选移到内联视图，则结果会发生改变(仅考虑了DETPNO 10中的HIREDATE)。

所以下面的结果是错误的：

SQL> select ename,hiredate,next_hd,

2  next_hd-hiredate diff

3  from

4  (

5  select deptno,ename,hiredate,

6  lead(hiredate) over(order by hiredate) next_hd

7  from emp

8  where deptno=10

9  );

ENAME      HIREDATE        NEXT_HD               DIFF

---------- --------------- --------------- ----------

CLARK      09-JUN-81       17-NOV-81              161

KING       17-NOV-81       23-JAN-82               67

MILLER     23-JAN-82

对于ORACLE的LEAD和LAG函数还需要特别注意，它们的结果中可能会有重复。在上面的例子中表EMP内不包含重复的HIREDATE，所以“看起来”似乎没有什么问题。下面我们向表中插入4个重复值来看看

SQL> insert into emp(empno,ename,deptno,hiredate)

2  values(1,'a',10,to_date('17-NOV-1981'));

1 row created.

SQL> insert into emp(empno,ename,deptno,hiredate)

2  values(2,'b',10,to_date('17-NOV-1981'));

1 row created.

SQL> insert into emp(empno,ename,deptno,hiredate)

2  values(3,'c',10,to_date('17-NOV-1981'));

1 row created.

SQL> insert into emp(empno,ename,deptno,hiredate)

2  values(4,'d',10,to_date('17-NOV-1981'));

1 row created.

SQL> select ename,hiredate

2  from emp

3  where deptno=10

4  order by 2;

ENAME      HIREDATE

---------- ---------------

CLARK      09-JUN-81

b          17-NOV-81

c          17-NOV-81

a          17-NOV-81

d          17-NOV-81

KING       17-NOV-81

MILLER     23-JAN-82

7 rows selected.

现在还是用以前那个查询语句来试试

SQL> select ename,hiredate,next_hd,

2  next_hd-hiredate diff

3  from

4  (

5  select deptno,ename,hiredate,

6  lead(hiredate) over(order by hiredate) next_hd

7  from emp

8  )

9  where deptno=10;

ENAME      HIREDATE        NEXT_HD               DIFF

---------- --------------- --------------- ----------

CLARK      09-JUN-81       08-SEP-81               91

d          17-NOV-81       17-NOV-81                0

c          17-NOV-81       17-NOV-81                0

a          17-NOV-81       17-NOV-81                0

b          17-NOV-81       17-NOV-81                0

KING       17-NOV-81       03-DEC-81               16

MILLER     23-JAN-82       19-APR-87             1912

7 rows selected.

可以看到其中有4个员工的DIFF列值都是0，这是错误的，同一天聘用的所有员工都应该跟下一个聘用其他员工的HIREDATE进行计算。

幸运的是ORACLE针对这类情况提供了一个非常简单的措施：当调用LEAD函数时，可以给LEAD传递一个参数，以便准确的指定“未来的”行(是下一行？10行之后？等等)。

select ename,hiredate,next_hd,

next_hd-hiredate diff

from

(

select deptno,ename,hiredate,

lead(hiredate,cnt-rn+1) over(order by hiredate) next_hd

from

(

select deptno,ename,hiredate,

count(*) over(partition by hiredate) cnt,

row_number() over(partition by hiredate order by empno) rn

from emp

where deptno=10

)

)