Oracle SQL 查询优化.Part6

一、聚集函数

1. null 对聚集函数的影响：

聚集函数会忽略 null，不做统计。这个对于 sum 来说没什么影响，但是对于 avg、count 来说，就会出现意料之外的结果。所以，通常来说，按照需求来决定是否要把空值转为 0。

-- select
select * from emp;

-- 没用 nvl
select count(emp.empsalary) emp_cou,
       avg(emp.empsalary) emp_avg,
       sum(emp.empsalary) emp_sum
  from emp;

-- 用 nvl
select count(nvl(emp.empsalary, 0)) emp_cou,
       avg(nvl(emp.empsalary, 0)) emp_avg,
       sum(nvl(emp.empsalary, 0)) emp_sum
 from emp;

从上边对比可以看出，聚集函数会忽略掉对 null 的统计。

二、分析函数

1. 分析函数之开窗 —— over && range、rows

range、rows 是两个不同的窗口：

• range 是逻辑窗口，是指定当前行对应值的取值范围，列数不固定，对应列都包含在内。

• rows 是物理窗口，是根据 order by 排序后，取前 N 行，后 M 行的数据计算（与当前值无关，只与排序后的行号相关）。

select emp.empno,
       emp.empname,
       emp.empsalary,
       sum(emp.empsalary) over(order by emp.empsalary) normal_sum,
       sum(emp.empsalary) over(order by emp.empsalary range between unbounded preceding and current row) range_unbou_sum,
       sum(emp.empsalary) over(order by emp.empsalary rows between unbounded preceding and current row) rows_unbou_sum,
       sum(emp.empsalary) over(order by emp.empsalary range between 1 preceding and 1 following) range_sum,
       sum(emp.empsalary) over(order by emp.empsalary rows between 1 preceding and 1 following) rows_sum
  from emp;

上边的例子中，range 是取所有对应值的列，而 rows 是取排序后对应物理行号的列：range_unbou_sum 和 rows_unbou_sum 对比，range 是取从第一行到当前行对应值对应的所有行；而 rows 是从第一行到当前行。range_sum 和 rows_sum 对比，range_sum 中 ‘1’ preceding 的 ‘1’ 其实已经没有了意义，就是找当前行的这列值对应的所有行；而 rows_sum 是找物理当前行的前一行和当前行的下一行。（ps：这里讲起来逻辑好复杂。总而一句话，range 是和当前行的这列值相等的所有行；rows 是排序后的物理行，和值没关系。）

这里还需要说下 unbounded、current、preceding、following...

unbounded preceding        第一行

current row                当前行

N preceding                向前 N 行

M following                向后 M 行

2. 分析函数之序列号 —— partition by && row_number、rank、dense_rank

三个分析函数 row_number()、rank()、dense_rank() 都是生成序号的分析函数，通过 partition by 分组之后，按照 order by 对应的列对每组的每条记录生成一个排序后的序号。至于三个分析函数之间的区别，看一个例子就一目了然啦：

-- partition by
select deptno,
       empno,
       empsalary,
       row_number() over(partition by deptno order by empsalary) rm_sal,
       dense_rank() over(partition by deptno order by empsalary) dense_sal,
       rank() over(partition by deptno order by empsalary) rank_sal
  from emp;

row_number() 不管 order by 的列值是多少，按照排序后的结果依次递增产生序列号。dense_rank() 和 rank() 函数对于 order by 相同列值的列，都会产生相同的序号，比如 emp01 和 emp03 两列的序号都是 2。但是它们的区别是：dense_rank() 产生的序列号是连续的，rank() 产生的序列号是不连续的。举一个 rank() 函数的应用场景：前两个学生都考100，并列第一名，第三个学生考 99，那么他只能是第三名。

3. 分析函数之最值所在行 —— keep && first、last

-- first、last 返回最小值所在行和最大值所在行
  select emp.empno,
         emp.empname,
         emp.empsalary,
         to_char(wmsys.wm_concat(empname) keep(dense_rank first order by emp.empsalary) over(partition by emp.deptno)) min_sal_emp,
         to_char(wmsys.wm_concat(empname) keep(dense_rank last order by emp.empsalary) over(partition by emp.deptno)) max_sal_emp
    from emp
   where emp.deptno = 'dept02'
order by emp.empsalary;

上边 SQL 执行结果显示，当最值有重复的时候（最小值 2000 对应的“Clare 和 Adela”、最大值 9000 对应的“Mark.李 和 大Boss”），keep 返回的是一个数据集。

4. 分析函数之比例的计算 —— over && ratio_to_report

• 利用 sum()、over() 实现
  

   -- 利用 sum()、over() 实现
   select emp.empno,
          emp.empname,
          emp.empsalary,
          sum(emp.empsalary) over() sal_total,
          round(emp.empsalary / (sum(emp.empsalary) over()), 4) * 100 || '%' sal_percent
     from emp

• 利用 ratio_to_report 实现
  

   -- 利用 ratio_to_report 实现
   select emp.empno,
          emp.empname,
          emp.empsalary,
          sum(emp.empsalary) over() sal_total,
          round(ratio_to_report(emp.empsalary) over(), 4) * 100 || '%' sal_percent
     from emp;

查询的结果集与上边是一样的。