Oracle 技能强化 Part 9 范围查询

本部分主要涉及到 定位连续值的范围 、查找同一组或分区中行之间的差、定位连续值范围的开始点和结束点、合并时间段。

第一点：定位连续值的范围。

工程明细中项目的结束时间是下一个项目的开始时间，表明整个项目全生命周期是连续的。现在要求返回这些连续工程的信息。

CREATE OR REPLACE VIEW v AS
SELECT 1 proj_id, to_date('01-01-2005', 'dd-mm-yyyy') AS proj_start, to_date('02-01-2005', 'dd-mm-yyyy') AS
proj_end FROM dual UNION ALL
SELECT 2 proj_id, to_date('02-01-2005', 'dd-mm-yyyy') AS proj_start, to_date('03-01-2005', 'dd-mm-yyyy') AS
proj_end FROM dual UNION ALL
SELECT 3 proj_id, to_date('03-01-2005', 'dd-mm-yyyy') AS proj_start, to_date('04-01-2005', 'dd-mm-yyyy') AS
proj_end FROM dual UNION ALL
SELECT 4 proj_id, to_date('04-01-2005', 'dd-mm-yyyy') AS proj_start, to_date('05-01-2005', 'dd-mm-yyyy') AS
proj_end FROM dual UNION ALL
SELECT 5 proj_id, to_date('06-01-2005', 'dd-mm-yyyy') AS proj_start, to_date('07-01-2005', 'dd-mm-yyyy') AS
proj_end FROM dual UNION ALL
SELECT 6 proj_id, to_date('16-01-2005', 'dd-mm-yyyy') AS proj_start, to_date('17-01-2005', 'dd-mm-yyyy') AS
proj_end FROM dual UNION ALL
SELECT 7 proj_id, to_date('17-01-2005', 'dd-mm-yyyy') AS proj_start, to_date('18-01-2005', 'dd-mm-yyyy') AS
proj_end FROM dual UNION ALL
SELECT 8 proj_id, to_date('18-01-2005', 'dd-mm-yyyy') AS proj_start, to_date('19-01-2005', 'dd-mm-yyyy') AS
proj_end FROM dual UNION ALL
SELECT 9 proj_id, to_date('19-01-2005', 'dd-mm-yyyy') AS proj_start, to_date('20-01-2005', 'dd-mm-yyyy') AS
proj_end FROM dual UNION ALL
SELECT 10 proj_id, to_date('21-01-2005', 'dd-mm-yyyy') AS proj_start, to_date('22-01-2005', 'dd-mm-yyyy') AS
proj_end FROM dual UNION ALL
SELECT 11 proj_id, to_date('26-01-2005', 'dd-mm-yyyy') AS proj_start, to_date('27-01-2005', 'dd-mm-yyyy') AS
proj_end FROM dual UNION ALL
SELECT 12 proj_id, to_date('27-01-2005', 'dd-mm-yyyy') AS proj_start, to_date('28-01-2005', 'dd-mm-yyyy') AS
proj_end FROM dual UNION ALL
SELECT 13 proj_id, to_date('28-01-2005', 'dd-mm-yyyy') AS proj_start, to_date('29-01-2005', 'dd-mm-yyyy') AS
proj_end FROM dual UNION ALL
SELECT 14 proj_id, to_date('29-01-2005', 'dd-mm-yyyy') AS proj_start, to_date('30-01-2005', 'dd-mm-yyyy') AS
proj_end FROM dual;

SELECT 工程号, 开始日期, 结束日期
FROM (SELECT proj_id AS 工程号,
proj_start 开始日期,
proj_end 结束日期,
lead(proj_start) over(ORDER BY proj_id) 下一个工程开始日期
FROM v)
WHERE 下一个工程开始日期 = 结束日期;

2.查找同一组或分区中之间的差

SELECT 部门编号, 姓名, 工资, 聘用日期, 工资 - 部门内下一个人的工资 AS 差额
  FROM (SELECT deptno AS 部门编号,
               ename AS 姓名,
               sal AS 工资,
               hiredate AS 聘用日期,
               lead(sal) over(PARTITION BY deptno ORDER BY hiredate) 部门内下一个人的工资
          FROM emp);
 

 这里还介绍一个知识点：关于轮换行值

在电脑或手表上，在向前后调时，上面的数字会循环现实，这里可以用分析函数LAG与LEAD模拟这样的功能。

select ename as names,
       sal as salarys,
       MIN(sal) over() as min_salary,
       MAX(sal) over() as max_salary,
       nvl(lead(sal)over(order by sal),min(sal) over()) front_rotation,
       nvl(lead(sal)over(order by sal),max(sal) over()) end_rotation
   from emp;

3. 定位连续值范围的开始点和结束点

我们接着看前面第一节的数据，现在要求把连续项目合并，返回合并后的起止时间，如前四个项目合并后起止时间就是 1 到 5 号。我们如果计算 各部门工资的最小最大值，按部门汇总就是了。而返回最小开始时间与最大结束时间分组依据呢？下面我们用分步查询的方法来找出问题的答案。

第一步，提取上一工程结束日期。

第二步，标定工程的连续状态。

第三步，我们对这个位置状态进行累加，得到了分组依据。

 4.合并时间段

CREATE OR REPLACE VIEW Timesheets(task_id, start_date , end_date) AS
SELECT 1, DATE '1997-01-01', DATE '1997-01-03' FROM dual UNION ALL
SELECT 2, DATE '1997-01-02', DATE '1997-01-04' FROM dual UNION ALL
SELECT 3, DATE '1997-01-04', DATE '1997-01-05' FROM dual UNION ALL
SELECT 4, DATE '1997-01-06', DATE '1997-01-09' FROM dual UNION ALL
SELECT 5, DATE '1997-01-09', DATE '1997-01-09' FROM dual UNION ALL
SELECT 6, DATE '1997-01-09', DATE '1997-01-09' FROM dual UNION ALL
SELECT 7, DATE '1997-01-12', DATE '1997-01-15' FROM dual UNION ALL
SELECT 8, DATE '1997-01-13', DATE '1997-01-13' FROM dual UNION ALL
SELECT 9, DATE '1997-01-15', DATE '1997-01-15' FROM dual UNION ALL
SELECT 10, DATE '1997-01-17', DATE '1997-01-17' FROM dual;

SELECT 分组依据, MIN(开始时间) AS 开始时间, MAX(结束时间) AS 结束时间
/*生成分组依据*/
  FROM (SELECT 开始时间,
               结束时间,
               SUM(连续状态) over(ORDER BY 开始时间) AS 分组依据
          FROM (SELECT start_date AS 开始时间,
                       end_date   AS 结束时间,
                       /*生成连续标识*/
                       CASE
                       /*取第一行至上一行的最大值来对比*/
                         WHEN MAX(end_date)
                          over(ORDER BY start_date rows BETWEEN UNBOUNDED
                                   preceding AND 1 preceding) >= start_date THEN
                          0
                         ELSE
                          1
                       END AS 连续状态
                  FROM timesheets b) c) d
 GROUP BY 分组依据
 ORDER BY 分组依据;