SQL 定位连续值区间的开始值和结束值|极客教程

SQL 定位连续值区间的开始值和结束值，本实例是本章第一个实例的引申，它们都用到了视图 V。在之前的实例中，你已经找到了包含一组连续值的区间，你希望知道它们的开始值和结束值。与本章的第一个实例不同，如果有一行并不属于某个连续值区间，你仍然希望返回它。为什么？这是因为，这样的行自成一个区间，它也有自己的开始值和结束值。

SQL 定位连续值区间的开始值和结束值问题描述

本实例是本章第一个实例的引申，它们都用到了视图 V。在之前的实例中，你已经找到了包含一组连续值的区间，你希望知道它们的开始值和结束值。与本章的第一个实例不同，如果有一行并不属于某个连续值区间，你仍然希望返回它。为什么？这是因为，这样的行自成一个区间，它也有自己的开始值和结束值。视图 V 的数据如下所示。

select *
  from V

PROJ_ID PROJ_START  PROJ_END
------- ----------- -----------
      1 01-JAN-2005 02-JAN-2005
      2 02-JAN-2005 03-JAN-2005
      3 03-JAN-2005 04-JAN-2005
      4 04-JAN-2005 05-JAN-2005
      5 06-JAN-2005 07-JAN-2005
      6 16-JAN-2005 17-JAN-2005
      7 17-JAN-2005 18-JAN-2005
      8 18-JAN-2005 19-JAN-2005
      9 19-JAN-2005 20-JAN-2005
     10 21-JAN-2005 22-JAN-2005
     11 26-JAN-2005 27-JAN-2005
     12 27-JAN-2005 28-JAN-2005
     13 28-JAN-2005 29-JAN-2005
     14 29-JAN-2005 30-JAN-2005

你最终希望得到如下所示的结果集。

PROJ_GRP PROJ_START  PROJ_END
-------- ----------- -----------
       1 01-JAN-2005 05-JAN-2005
       2 06-JAN-2005 07-JAN-2005
       3 16-JAN-2005 20-JAN-2005
       4 21-JAN-2005 22-JAN-2005
       5 26-JAN-2005 30-JAN-2005

SQL 定位连续值区间的开始值和结束值解决方案

相较于本章第一个实例，本问题更为复杂。首先，必须明确什么是区间。PROJ_START 和 PROJ_END 的值决定哪些行属于同一个区间。如果某一行的 PROJ_START 值等于上一行的 PROJ_END 值，那么该行就是“连续”的，或者说它属于某个组。如果某一行的 PROJ_START 值不等于上一行的 PROJ_END 值，并且它的 PROJ_END 值也不等于下一行的 PROJ_START 值，那么该行自身就构成了一个独立的组。识别出区间之后，还要对每个区间相关的行进行分组，并找出每一组的开始值和结束值。
我们来看看最终结果集的第一行数据。PROJ_START 值是视图 V 里 PROJ_ID 1 对应的 PROJ_START，PROJ_END 则是视图 V 里 PROJ_ID 4 对应的 PROJ_END。PROJ_ID 4 后面并没有再出现一个连续值，因此它作为这一组连续值的最后一个被纳入了第一组。
DB2、MySQL、PostgreSQL 和 SQL Server
这些数据库对应的解决方案需要用到视图 V2，以增强代码的可读性。视图 V2 的定义如下。

create view v2
as
select a.*,
       case
         when (
            select b.proj_id
              from V b
             where a.proj_start = b.proj_end
              )
              is not null then 0 else 1
       end as flag
  from V a

其结果集如下所示。

select *
  from V2
　
PROJ_ID PROJ_START  PROJ_END          FLAG
------- ----------- ----------- ----------
      1 01-JAN-2005 02-JAN-2005          1
      2 02-JAN-2005 03-JAN-2005          0
      3 03-JAN-2005 04-JAN-2005          0
      4 04-JAN-2005 05-JAN-2005          0
      5 06-JAN-2005 07-JAN-2005          1
      6 16-JAN-2005 17-JAN-2005          1
      7 17-JAN-2005 18-JAN-2005          0
      8 18-JAN-2005 19-JAN-2005          0
      9 19-JAN-2005 20-JAN-2005          0
     10 21-JAN-2005 22-JAN-2005          1
     11 26-JAN-2005 27-JAN-2005          1
     12 27-JAN-2005 28-JAN-2005          0
     13 28-JAN-2005 29-JAN-2005          0
     14 29-JAN-2005 30-JAN-2005          0

在视图 V2 的基础上得到的解决方案如下所示。首先，找出那些属于某个连续值区间的行，并为它们分组。然后，调用 MIN 函数和 MAX 函数找出每一组的开始值和结束值。

 1  select proj_grp,
 2         min(proj_start) as proj_start,
 3         max(proj_end) as proj_end
 4    from (
 5  select a.proj_id,a.proj_start,a.proj_end,
 6         (select sum(b.flag)
 7            from V2 b
 8           where b.proj_id <= a.proj_id) as proj_grp
 9    from V2 a
10         ) x
11   group by proj_grp

Oracle
上述解决方案当然也适用于 Oracle。不过，借助 Oracle 的窗口函数 LAG OVER，无须额外的视图也能解决本问题。我们可以利用 LAG OVER 函数判定前一行的 PROJ_END 是否等于当前行的 PROJ_START，并以此为标准对当前行进行分组。分组完成之后，接着调用聚合函数 MIN 和 MAX 分别找出每组的开始值和结束值。

 1  select proj_grp, min(proj_start), max(proj_end)
 2    from (
 3  select proj_id,proj_start,proj_end,
 4         sum(flag)over(order by proj_id) proj_grp
 5    from (
 6  select proj_id,proj_start,proj_end,
 7         case when
 8              lag(proj_end)over(order by proj_id) = proj_start
 9              then 0 else 1
10         end flag
11    from V
12         )
13         )
14   group by proj_grp

SQL 定位连续值区间的开始值和结束值扩展知识

DB2、MySQL、PostgreSQL 和 SQL Server
有了视图 V2，本问题就相对容易一些了。视图 V2 在 CASE 表达式里用了一个标量子查询来判断当前行是否属于某个连续值区间。如果当前行属于某个连续值区间，那么别名为 FLAG 的 CASE 表达式将返回 0；反之，则返回 1（判定当前行是否属于一组连续值区间的方法是：是否有一条记录的 PROJ_END 值等于当前行的 PROJ_START 值）。下一步是逐一查看内嵌视图 X（第 5 ～ 9 行）的查询结果。内嵌视图 X 返回视图 V2 的全部行以及针对 FLAG 的累计值，该累计值就是我们分组的依据，如下所示。

select a.proj_id,a.proj_start,a.proj_end,
       (select sum(b.flag)
          from v2 b
         where b.proj_id <= a.proj_id) as proj_grp
  from v2 a
　
PROJ_ID PROJ_START  PROJ_END      PROJ_GRP
------- ----------- ----------- ----------
      1 01-JAN-2005 02-JAN-2005          1
      2 02-JAN-2005 03-JAN-2005          1
      3 03-JAN-2005 04-JAN-2005          1
      4 04-JAN-2005 05-JAN-2005          1
      5 06-JAN-2005 07-JAN-2005          2
      6 16-JAN-2005 17-JAN-2005          3
      7 17-JAN-2005 18-JAN-2005          3
      8 18-JAN-2005 19-JAN-2005          3
      9 19-JAN-2005 20-JAN-2005          3
     10 21-JAN-2005 22-JAN-2005          4
     11 26-JAN-2005 27-JAN-2005          5
     12 27-JAN-2005 28-JAN-2005          5
     13 28-JAN-2005 29-JAN-2005          5
     14 29-JAN-2005 30-JAN-2005          5

现在已经确定好各个区间了，接下来要针对 PROJ_START 和 PROJ_END 分别调用聚合函数 MIN 和 MAX 找出每个区间的开始值和结束值，然后根据上述累计值分组。
Oracle
对于本实例而言，窗口函数 LAG OVER 非常有用。我们无须使用自连接、标量子查询或额外的视图就能访问前一行的 PROJ_END 值。去掉了 CASE 表达式的 LAG OVER 函数的执行结果显示如下。

select proj_id,proj_start,proj_end,
       lag(proj_end)over(order by proj_id) prior_proj_end
  from V
　
PROJ_ID PROJ_START  PROJ_END    PRIOR_PROJ_END
------- ----------- ----------- --------------
      1 01-JAN-2005 02-JAN-2005
      2 02-JAN-2005 03-JAN-2005 02-JAN-2005
      3 03-JAN-2005 04-JAN-2005 03-JAN-2005
      4 04-JAN-2005 05-JAN-2005 04-JAN-2005
      5 06-JAN-2005 07-JAN-2005 05-JAN-2005
      6 16-JAN-2005 17-JAN-2005 07-JAN-2005
      7 17-JAN-2005 18-JAN-2005 17-JAN-2005
      8 18-JAN-2005 19-JAN-2005 18-JAN-2005
      9 19-JAN-2005 20-JAN-2005 19-JAN-2005
     10 21-JAN-2005 22-JAN-2005 20-JAN-2005
     11 26-JAN-2005 27-JAN-2005 22-JAN-2005
     12 27-JAN-2005 28-JAN-2005 27-JAN-2005
     13 28-JAN-2005 29-JAN-2005 28-JAN-2005
     14 29-JAN-2005 30-JAN-2005 29-JAN-2005

从完整的代码中可以看到，CASE 表达式只是比较了 LAG OVER 函数返回的结果和当前行的 PROJ_START 值；如果两个值相等，则返回 0，否则返回 1。下一步就是针对 CASE 表达式返回的 0 和 1 产生一个累计值，从而把每一行都编入某个组。上述累计值的计算结果显示如下。

select proj_id,proj_start,proj_end,
       sum(flag)over(order by proj_id) proj_grp
  from (
select proj_id,proj_start,proj_end,
       case when
            lag(proj_end)over(order by proj_id) = proj_start
            then 0 else 1
       end flag
  from V
       )
　
PROJ_ID PROJ_START  PROJ_END      PROJ_GRP
------- ----------- ----------- ----------
      1 01-JAN-2005 02-JAN-2005          1
      2 02-JAN-2005 03-JAN-2005          1
      3 03-JAN-2005 04-JAN-2005          1
      4 04-JAN-2005 05-JAN-2005          1
      5 06-JAN-2005 07-JAN-2005          2
      6 16-JAN-2005 17-JAN-2005          3
      7 17-JAN-2005 18-JAN-2005          3
      8 18-JAN-2005 19-JAN-2005          3
      9 19-JAN-2005 20-JAN-2005          3
     10 21-JAN-2005 22-JAN-2005          4
     11 26-JAN-2005 27-JAN-2005          5
     12 27-JAN-2005 28-JAN-2005          5
     13 28-JAN-2005 29-JAN-2005          5
     14 29-JAN-2005 30-JAN-2005          5