SQL 计算当前记录和下一条记录之间的日期差|极客教程

SQL 计算当前记录和下一条记录之间的日期差，计算两个日期之间相差多少天（特别是当两者分别存储于不同的行的时候）。例如，对于 DEPTNO 等于 10 的部门的每一个员工，你希望计算出他们的入职日期之间相差多少天。

SQL 计算当前记录和下一条记录之间的日期差问题描述

计算两个日期之间相差多少天（特别是当两者分别存储于不同的行的时候）。例如，对于 DEPTNO 等于 10 的部门的每一个员工，你希望计算出他们的入职日期之间相差多少天。

SQL 计算当前记录和下一条记录之间的日期差解决方案

要解决本问题有一个诀窍是，在早于当前入职时间的所有记录里找出 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 版本的 DATEDIFF 函数，我们需要省略第一个参数 day，并把剩下的两个参数的顺序颠倒过来。

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

Oracle
对于 Oracle 8i 及后续版本，使用窗口函数 LEAD OVER 访问相对于当前 HIREDATE 的下一个 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

对于 Oracle 8i 及更早的版本，则需要采用下面的 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

SQL 计算当前记录和下一条记录之间的日期差扩展知识

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 条重复数据，这样就有（包括 KING 在内的）5 个员工的 HIREDATE 都是 11 月 17 日了。

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 的部门里就有不止一个人的 HIREDATE 是同一天了。如果仍然使用上述解决方案（但是要把 DEPTNO 过滤动作移到内嵌视图里面去，我们只关注 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)

看一下 HIREDATE 相同的 5 个员工，他们中有 4 个人的 DIFF 值是 0。这显然不正确。对于 HIREDATE 相同的员工而言，应该与下一个不同的 HIREDATE 相比较，例如 HIREDATE 是 11 月 17 日的员工要与 MILLER 的 HIREDATE 相比较。问题的根源在于 LEAD 函数仅仅按照 HIREDATE 排序，却不会自动去重。因此，如果把 ANT 的 HIREDATE 与 JOE 的相比较，相差的天数就是 0，如此一来 ANT 的 DIFF 值就变成 0 了。所幸 Oracle 已经为这种情况提供了一个简单的变通方案。当调用 LEAD 函数的时候，我们可以传递一个参数用于指定“未来行”的位置（例如，它位于下一行，或者隔了 10 行之后，等等）。因此，对于员工 ANT，需要跨过 5 行，而不是一行（我们希望跳过所有重复的 HIREDATE），去看一下 MILLER 的 HIREDATE。员工 JOE 距离 MILLER 有 4 行的距离，JIM 则是 3 行，KING 是 2 行，CHOI 幸运地只相隔一行。为了获取正确答案，只要把上述每一个员工所在行到 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，CNT 的值都是 5。窗口函数 ROW_NUMBER OVER 按照 EMPNO 为每一个员工排名。排名按照 HIREDATE 分区，除非有重复的 HIREDATE 出现，否则每个员工对应的 RN 值都是 1。现在，所有重复数据都被分组计数并算出其在分组里的排名，而该排名值可以用于度量当前 HIREDATE 到下一个 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)