SQL 定位连续的值区间|极客教程

SQL 定位连续的值区间，你想确定哪些行代表了一系列在时间上连续的项目。考虑下述视图 V 的结果集，它包含了项目编号以及各自的起止日期。

SQL 定位连续的值区间问题描述

你想确定哪些行代表了一系列在时间上连续的项目。考虑下述视图 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_START 应该等于前一行的 PROJ_END（“前一行”的定义是其 PROJ_ID 等于当前行的 PROJ_ID 减 1）。仔细查看视图 V 的前 5 行，PROJ_ID 分别等于 1 到 3 的行属于同一“组”，因为每一行的 PROJ_END 都等于后一行的 PROJ_START。我们希望找出一系列连续项目的日期区间，因此希望返回满足“当前行的 PROJ_END 等于下一行的 PROJ_START”这一条件的所有行。如果整个结果集只包含前 5 行，那么我们希望返回的只是最前面的 3 行。（对于视图 V 全部 14 行数据而言）最终的结果集应该如下所示。

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
      6 16-JAN-2005 17-JAN-2005
      7 17-JAN-2005 18-JAN-2005
      8 18-JAN-2005 19-JAN-2005
     11 26-JAN-2005 27-JAN-2005
     12 27-JAN-2005 28-JAN-2005
     13 28-JAN-2005 29-JAN-2005

我们从结果集中剔除掉了 PROJ_ID 为 4、5、9、10 和 14 的行，因为这些行的 PROJ_END 不等于下一行的 PROJ_START。

SQL 定位连续的值区间解决方案

DB2、MySQL、PostgreSQL 和 SQL Server
使用自连接找出包含连续值的行。

1 select v1.proj_id,
2        v1.proj_start,
3        v1.proj_end
4   from V v1, V v2
5  where v1.proj_end = v2.proj_start

Oracle
上述解决方案也适用于 Oracle。除此之外，Oracle 还有另一个解决方案，即利用窗口函数 LEAD OVER 去查看“下一行”的 BEGIN_DATE，这样就不必自连接了。

1 select proj_id,proj_start,proj_end
2   from (
3 select proj_id,proj_start,proj_end,
4        lead(proj_start)over(order by proj_id) next_proj_start
5   from V
6         )
7 where next_proj_start = proj_end

SQL 定位连续的值区间扩展知识

DB2、MySQL、PostgreSQL 和 SQL Server
通过视图 V 的自连接，每一行都可以与其他行进行比较。考虑查询 PROJ_ID 为 1 和 4 时得到的部分结果集。

select v1.proj_id as v1_id,
       v1.proj_end as v1_end,
       v2.proj_start as v2_begin,
       v2.proj_id as v2_id
  from v v1, v v2
 where v1.proj_id in ( 1,4 )
　
V1_ID V1_END      V2_BEGIN         V2_ID
----- ----------- ----------- ----------
    1 02-JAN-2005 01-JAN-2005          1
    1 02-JAN-2005 02-JAN-2005          2
    1 02-JAN-2005 03-JAN-2005          3
    1 02-JAN-2005 04-JAN-2005          4
    1 02-JAN-2005 06-JAN-2005          5
    1 02-JAN-2005 16-JAN-2005          6
    1 02-JAN-2005 17-JAN-2005          7
    1 02-JAN-2005 18-JAN-2005          8
    1 02-JAN-2005 19-JAN-2005          9
    1 02-JAN-2005 21-JAN-2005         10
    1 02-JAN-2005 26-JAN-2005         11
    1 02-JAN-2005 27-JAN-2005         12
    1 02-JAN-2005 28-JAN-2005         13
    1 02-JAN-2005 29-JAN-2005         14
    4 05-JAN-2005 01-JAN-2005          1
    4 05-JAN-2005 02-JAN-2005          2
    4 05-JAN-2005 03-JAN-2005          3
    4 05-JAN-2005 04-JAN-2005          4
    4 05-JAN-2005 06-JAN-2005          5
    4 05-JAN-2005 16-JAN-2005          6
    4 05-JAN-2005 17-JAN-2005          7
    4 05-JAN-2005 18-JAN-2005          8
    4 05-JAN-2005 19-JAN-2005          9
    4 05-JAN-2005 21-JAN-2005         10
    4 05-JAN-2005 26-JAN-2005         11
    4 05-JAN-2005 27-JAN-2005         12
    4 05-JAN-2005 28-JAN-2005         13
    4 05-JAN-2005 29-JAN-2005         14

仔细检查以上结果集，我们就会明白为什么 PROJ_ID 1 被包含在最终结果集中，而 PROJ_ID4 却没有。这是因为对于 V1_ID 4 而言，不存在与 V1_END 值相等的 V2_BEGIN。
如果改变一下筛选条件，PROJ_ID 4 也可以被认为是相邻的项目。考虑如下所示的结果集。

select *
  from V
 where proj_id <= 5
　
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

如果“相邻的项目”是指其开始日期与另一个项目的结束日期相同，那么 PROJ_ID 4 也应该被包含在结果集里。按照最初的筛选条件，由于前向比较（比较 PROJ_END 和下一行的 PROJ_START）的存在，PROJ_ID 4 被排除在外了，但如果做后向比较（比较 PROJ_START 和前一行的 PROJ_END），那么 PROJ_ID 4 就应该被包含在结果集中。
不妨修改一下上述解决方案，把 PROJ_ID 4 也包含进去：只需增加一个筛选条件，确保 PROJ_START 和 PROJ_END 的相邻关系都被纳入检查范围就行了，而不是仅仅检查 PROJ_END。下面的查询展示了上述改动，该查询产生了一个包含 PROJ_ID 4 的结果集（DISTINCT 是必要的，因为一些行同时满足两个条件）。

select distinct
       v1.proj_id,
       v1.proj_start,
       v1.proj_end
  from V v1, V v2
 where v1.proj_end   = v2.proj_start
    or v1.proj_start = v2.proj_end
　
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

Oracle
上述自连接方案当然适用于本问题，不过窗口函数 LEAD OVER 用于解决这一类问题更合适。LEAD OVER 函数可以不执行自连接就能查看其他行的数据（尽管该函数要求必须对结果集进行排序）。对于 PROJ_ID1 和 PROJ_ID4，考虑内嵌视图（第 3 ～ 5 行）的结果集。

select *
  from (
select proj_id,proj_start,proj_end,
       lead(proj_start)over(order by proj_id) next_proj_start
  from v
       )
 where proj_id in ( 1,4 )
　
PROJ_ID PROJ_START  PROJ_END    NEXT_PROJ_START
------- ----------- ----------- ---------------
      1 01-JAN-2005 02-JAN-2005 02-JAN-2005
      4 04-JAN-2005 05-JAN-2005 06-JAN-2005

仔细检查上面的代码片段及其结果集，不难理解为什么 PROJ_ID 4 会被从最终结果集里排除掉。这是因为 PROJ_END 的日期 2005 年 1 月 5 日与下一个项目的开始日期 2005 年 1 月 6 日不同。
LEAD OVER 函数用于解决这一类问题非常方便，尤其在只遍历部分结果的时候。使用窗口函数时，要记得它们在 FROM 和 WHERE 子句之后才会被评估，因此前面的查询里的 LEAD OVER 函数必须被放入一个内嵌视图才行。否则，LEAD OVER 函数的作用对象就变成了只含有 PROJ_ID 1 和 PROJ_ID 4 的结果集，因为执行过 WHERE 子句之后，其他行都会被过滤掉。
现在，如果改变一下筛选条件，我们也能把 PROJ_ID 4 纳入最终的结果集。考虑视图 V 的前 5 行数据。

select *
  from V
 where proj_id <= 5
　
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

如果我们认为 PROJ_ID 4 事实上是相邻的（因为 PROJ_ID 4 的 PROJ_START 等于 PROJ_ID 3 的 PROJ_END），而且只有 PROJ_ID 5 应该被排除在外，那么前述解决方案就不正确了，至少是不全面的。

select proj_id,proj_start,proj_end
  from (
select proj_id,proj_start,proj_end,
       lead(proj_start)over(order by proj_id) next_start
  from V
 where proj_id <= 5
       )
 where proj_end = next_start
　
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

如果我们认为 PROJ_ID 4 应该被包含进来，只需额外加上 LAG OVER 函数，并在 WHERE 子句中也增加一个过滤条件即可。

select proj_id,proj_start,proj_end
  from (
select proj_id,proj_start,proj_end,
       lead(proj_start)over(order by proj_id) next_start,
       lag(proj_end)over(order by proj_id) last_end
  from V
 where proj_id <= 5
       )
 where proj_end   = next_start
    or proj_start = last_end
　
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