数据库语法总结（10）——区间查询

这一章内容怎么说呢，都是在前面几章介绍的内容的基础上操作，主打的就是一个思路的宽泛性

1、查找对应的连续值

我们现在有这样一个视图

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

然后我们想要找出连续的项目（连续是指同一天衔接起始） 这里我们就用到了之前讲的两个开窗函数，lead()、lag()偏差函数

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

上述我们找到了连续的区间，现在我们想要查询所有连续项目区间的起始时间和结束时间，改如何操作呢，聚合函数min()和max()结合group by 使用查找汇总项目sum()over()

select proj_grp, min(proj_start), max(proj_end)
  from (
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
       )
       )
 group by proj_grp

2、查找同一个分组中相邻行的差

我们现在有一个员工表emp，里面字段如下deptno(部门序号)、ename(员工名称)、sal(工资)、hiredate(入职时间)

然后我们想要根据deptno、hiredate排序后计算前一个员工和下一个员工的工资差，最后一个员工工资差记为N/A，此时还要用到lead()函数

with next_sal_tab(deptno,ename,sal,hiredate,next_sal)
as
(select deptno,ename,sal,hiredate,
    lead(sal) over (partition by deptno order by hiredate) as next_sal
 from emp)

select deptno,ename,sal,hiredate,
    coalesce(cast(sal-next_sal as varchar2(100),'N/A') as diff)
from next_sal_tab

上述解决方案对于不重复数据而言毫无问题，但如果考虑重复行，就不对了。考虑如下所示的例子，有3人同一天入职。那我们在分组的时候就会遇到问题，这时我们可以考虑进行排序号的形式rownum()

select deptno,ename,sal,hiredate,
       lpad(nvl(to_char(sal-next_sal),'N/A'),10) diff
  from (
select deptno,ename,sal,hiredate,
       lead(sal,cnt-rn+1)over(partition by deptno
                         order by hiredate) next_sal
  from (
select deptno,ename,sal,hiredate,
       count(*)over(partition by deptno,hiredate) cnt,
       row_number()over(partition by deptno,hiredate order by sal) rn
  from emp
       )
       )

这样就可以避免重复造成的错误了

3、为值区间填补空隙

你想列出整个2013~2023年代里每年新入职的员工人数，但有一些年份并没有新增员工，记录为0.这时我们要先生成10行数据，然后关联人员表emp计算人数，以Oracle为例，但是其它数据库用法是相同的，只是语法不同。

select x.yr, coalesce(cnt,0) cnt
  from (
select extract(year from min(hiredate)over()) -
       mod(extract(year from min(hiredate)over()),10) +
       rownum-1 yr
 from emp
where rownum <= 10
      ) x
left join 
      (
select to_number(to_char(hiredate,'YYYY')) yr, count(*) cnt
 from emp
 group by to_number(to_char(hiredate,'YYYY'))
       ) y
 on x.yr = y.yr

5、生成连续的数值

假如你想拥有一个行数据生成其，实现给出范围值自动生成数值行数，要如何实现呢

DB2 和 SQL Server
使用with递归查询生成一系列含有递增值的行。先借助一个像t1这样的只有 1 行数据的表来启动行数据生成操作，其余的交给with子句即可。

 with x (id)
 as (
 select 1
  from t1
  union all
select id+1
  from x
 where id+1 <= 10
)
select * from x

Oracle
使用connect by递归查询（适用于 Oracle 9i 及后续版本）。

with x
as (
select level id
  from dual
  connect by level <= 10
)
select * from x

PostgreSQL
使用gengrate_series函数，该函数就是为快速生成行数据而设计的。

select id
 from generate_series(1,10) x(id)

扩展：gengrate_series()该函数有 3 个参数，它们都是数值类型。第一个参数是初始值，第二个参数是结束值，第三个参数是可选项，代表“步长”（每次增加的值）。如果没有指定第 3 个参数，则默认每次增加 1。

好了，本章节到这里就结束了，由于工作原因目前比较忙一些，目前就先记录到这里啦，后面的章节可能会延长更新，喜欢的小伙伴可以关注。