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

问题

求两个日期(指存储在两个不同行内的日期)之间相差的天数。例如,对于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

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

    10 CLARK  09-JUN-1981                  1 17-NOV-1981

    10 ant    17-NOV-1981                  5 23-JAN-1982

    10 joe    17-NOV-1981                  4 23-JAN-1982

    10 jim    17-NOV-1981                  3 23-JAN-1982

    10 choi   17-NOV-1981                  2 23-JAN-1982

    10 KING   17-NOV-1981                  1 23-JAN-1982

    10 MILLER 23-JAN-1982                  1 (null)

可以看到,传递给它向前跳越的距离值后,LEAD函数就可以相对于正确的日期进行减法操作了。

 
发布了85 篇原创文章 · 获赞 1 · 访问量 18万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览