Sql确定当前记录和下一条记录之间相差的天数

最新推荐文章于 2024-10-17 11:28:22 发布

gaotianle

最新推荐文章于 2024-10-17 11:28:22 发布

阅读量4.7k

点赞数 1

文章标签： sql postgresql sql server oracle ant distance

本文链接：https://blog.youkuaiyun.com/gaotianle/article/details/1830314

版权

问题

求两个日期（指存储在两个不同行内的日期）之间相差的天数。例如，对于DEPTNO 10中的每个员工，确定聘用他们的日期及聘用下一个员工（可能是其他部门的员工）的日期之间相差的天数。

解决方案

这个问题的解决方案是找到当前员工聘用的最早HIREDATE（聘用日期）。然后，只需采用8.2节中介绍的技巧，就能够得到相差天数。

DB2

使用标量子查询，可以找到当前HIREDATE的下一个HIREDATE。然后，使用DAYS 函数获得相差天数：

1 select x.*,

2 days(x.next_hd) - days(x.hiredate) diff

3 from (

4 select e.deptno, e.ename, e.hiredate,

5 (select min(d.hiredate) from emp d

6 where d.hiredate > e.hiredate) next_hd

7 from emp e

8 where e.deptno = 10

9 ) x

MySQL和SQL Server

使用标量子查询，可以找到当前HIREDATE的下一个HIREDATE。然后，使用DATEDIFF函数获得相差天数。SQL Server版本的DATEDIFF的用法如下：

1 select x.*,

2 datediff(day,x.hiredate,x.next_hd) diff

3 from (

4 select e.deptno, e.ename, e.hiredate,

5 (select min(d.hiredate) from emp d

6 where d.hiredate > e.hiredate) next_hd

7 from emp e

8 where e.deptno = 10

9 ) x

MySQL用户去掉第一个参数，并交换两个参数的顺序：

2 datediff(x.next_hd, x.hiredate) diff

Oracle

在Oracle8i Database或更高的版本中，需使用窗口函数LEAD OVER访问当前行的下一个HIREDATE，进而进行减法操作：

1 select ename, hiredate, next_hd,

2 next_hd - hiredate diff

3 from (

4 select deptno, ename, hiredate,

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

6 from emp

7 )

8 where deptno=10

在Oracle8 Database或较早版本中，可以采用PostgreSQL解决方案。

PostgreSQL

使用标量子查询，可以找到当前HIREDATE的下一个HIREDATE。然后，只需用减法即可获得相差天数：

1 select x.*,

2 x.next_hd - x.hiredate as diff

3 from (

4 select e.deptno, e.ename, e.hiredate,

5 (select min(d.hiredate) from emp d

6 where d.hiredate > e.hiredate) as next_hd

7 from emp e

8 where e.deptno = 10

9 ) x

讨论

DB2、MySQL、PostgreSQL和SQL Server

对于以上这些解决方案，尽管其语法不同，但步骤是相同的：使用标量子查询，找到当前HIREDATE的下一个HIREDATE，然后采用本章前面8.2节中介绍的技巧，获得两者之间相差的天数。

Oracle

这里提到的窗口函数LEAD OVER非常有用，它能够访问“未来的”行（“未来的”行相对于当前行，由ORDER BY子句决定）。这种无需添加联接就能够访问当前行附近行的功能，提高了代码的可读性和有效性。在采用窗口函数时，一定要记住，它在WHERE子句之后求值，因此在该解决方案中，需要使用内联视图。如果把对DEPTNO的筛选移到内联视图，则结果会发生改变（仅考虑了DEPTNO 10中的HIREDATE）。对于Oracle的LEAD和LAG函数还需要特别注意，它们的结果中可能会有重复。本书前言中曾经提到过，各章节的代码中没有采取“防御性的”措施，这是因为条件太多了，不可能预见到所有可能中断代码执行的情况；即使能够预见所有问题，但其结果可能是SQL语句难以理解。因此，在大多数情况下，解决方案的目标是介绍一种技巧：可以在产品系统中使用它，但一定要依据特定的数据反复进行测试和改进。尽管如此，在这个特定的例子中还是要对此稍加讨论，只因其处理方式不那么显而易见，对非Oracle系统的用户来说尤其如此。在这个例子中，表EMP内不包含重复的HIREDATE，但是，在其他表中，很可能有重复的日期值。下面给出了DEPTNO 10中的员工及他们的HIREDATE：

select ename, hiredate

from emp

where deptno=10

order by 2

ENAME HIREDATE

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

CLARK 09-JUN-1981

KING 17-NOV-1981

MILLER 23-JAN-1982

为了讲清楚这个例子，向表中插入4个重复值，这样在11月17日聘用了5个员工（其中包括KING）：

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

values (1,'ant',10,to_date('17-NOV-1981'))

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

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

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

values (3,'jim',10,to_date('17-NOV-1981'))

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

values (4,'choi',10,to_date('17-NOV-1981'))

select ename, hiredate

from emp

where deptno=10

order by 2

ENAME HIREDATE

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

CLARK 09-JUN-1981

ant 17-NOV-1981

joe 17-NOV-1981

KING 17-NOV-1981

jim 17-NOV-1981

choi 17-NOV-1981

MILLER 23-JAN-1982

目前，DEPTNO 10同一天聘用了多个员工。如果试图对此结果集使用前面提到的解决方案（即把筛选移到内联视图，这样仅考虑DEPTNO 10的员工及他们的HIREDATE），将得到如下输出：

select ename, hiredate, next_hd,

next_hd - hiredate diff

from (

select deptno, ename, hiredate,

lead(hiredate)over(order by hiredate) next_hd

from emp

where deptno=10

)

ENAME HIREDATE NEXT_HD DIFF

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

CLARK 09-JUN-1981 17-NOV-1981 161

ant 17-NOV-1981 17-NOV-1981 0

joe 17-NOV-1981 17-NOV-1981 0

KING 17-NOV-1981 17-NOV-1981 0

jim 17-NOV-1981 17-NOV-1981 0

choi 17-NOV-1981 23-JAN-1982 67

MILLER 23-JAN-1982 (null) (null)

查看一下同一天聘用的5个员工的DIFF值就会发现，其中4个人为0，这是错误的。同一天聘用的所有员工都应该跟下一个聘用其他员工的HIREDATE进行计算，即17日聘用的所有员工，都应该跟MILLER的HIREDATE进行计算。这里的问题是：LEAD 函数按HIREDATE对行排序，而且它不会跳过重复行，所以以ANT为例，若将ANT的HIREDATE跟员工JOE的HIREDATE进行计算，则差为0，因此ANT的DIFF值为0。所幸Oracle针对这类情况提供了一个非常简单的应对措施：当调用LEAD函数时，可以给LEAD传递一个参数，以便准确地指定“未来的”行（是下一行？10行之后？等等）所在。所以，再看看员工ANT的情况，不是要向前查一行，而需要向前查5行（需要跳过其他所有重复行），这里是MILLER。如果查看一下员工JOE，他距离MILLER有4行；JIM距离MILLER3行，KING距离MILLER2行，帅哥 CHOI距离MILLER只有1行。要得到正确答案，只需把每个员工距MILLER的行数作为参数传递给LEAD。其解决方案如下所示：

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

)

ENAME HIREDATE NEXT_HD DIFF

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

CLARK 09-JUN-1981 17-NOV-1981 161

ant 17-NOV-1981 23-JAN-1982 67

joe 17-NOV-1981 23-JAN-1982 67

jim 17-NOV-1981 23-JAN-1982 67

choi 17-NOV-1981 23-JAN-1982 67

KING 17-NOV-1981 23-JAN-1982 67

MILLER 23-JAN-1982 (null) (null)

现在，结果对了。同一天聘用的所有员工都有自己的HIREDATE，它们是相对于下一个HIREDATE（而不是与自己相同的HIREDATE）计算出来的。如果觉得该对应对措施不那么清楚，只需将该查询拆开，先从内联视图开始：

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

DEPTNO ENAME HIREDATE CNT RN

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

10 CLARK 09-JUN-1981 1 1

10 ant 17-NOV-1981 5 1

10 joe 17-NOV-1981 5 2

10 jim 17-NOV-1981 5 3

10 choi 17-NOV-1981 5 4

10 KING 17-NOV-1981 5 5

10 MILLER 23-JAN-1982 1 1

窗口函数COUNT OVER用于计算每个HIREDATE重复的次数，并返回给每一行。对于重复的HIREDATE，该HIREDATE的每一行都返回5；窗口函数 ROW_NUMBER OVER按照EMPNO给每个员工制定序号并且序号依照HIREDATE分区，因此如果没有重复的HIREDATE，则每个员工的序号都为1。至此，所有重复日期都做了计数并制定了序号，可以用该序号来表示距下一个HIREDATE（MILLER的HIREDATE）的行数。当调用LEAD时，将每行的 CNT减RN加1作为参数，就可以看到如下结果：

select deptno, ename, hiredate,

cnt-rn+1 distance_to_miller,

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

)

DEPTNO ENAME HIREDATE DISTANCE_TO_MILLER NEXT_HD

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