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

来源：互联网发布：java ioc 编辑：程序博客网时间：2024/06/01 08:18

例如说吧，对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
)
)