前言
本篇文章讲解的主要内容是：常用聚集函数及group by与空值的影响、详解通过执行计划看sum()over()分析函数。
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
一、常用聚集函数
SQL> SELECT deptno,
2 AVG(sal)AS平均值,MIN(sal)AS最小值,MAX(sal)AS最大值,SUM(sal)工资合计,COUNT(*)总行数,
3 COUNT(comm)获得提成的人数,
4 AVG(comm)错误的人均提成算法,
5 AVG(coalesce(comm,0))正确的人均提成 FROM emp
6 GROUP BY deptno;
DEPTNO AS平均值 AS最小值 AS最大值 工资合计 总行数 获得提成的人数 错误的人均提成算法 正确的人均提成
------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
10 2916.66666 1300 5000 8750 3 0 0
20 2175 800 3000 10875 5 0 0
30 1566.66666 950 2850 9400 6 4 550 366.666666聚集函数需要注意的一点就是：聚集函数会忽略空值,这对sum等来说没什么影响,但对avg、count来说就可能会出现预料之外的结果。所以要根据需求决定是否把空值转为零。
注意,当表中没有数据时,不加group by会返回一行数据,但加了group by会没有数据返回。
建立空表：
SQL> create table emp22 as select * from emp where 1=2;
Table created
SQL> select count(*) as cnt,sum(sal) as ssal from emp22 where deptno=10;
CNT SSAL
---------- ----------
0
SQL> 有group by
SQL> select count(*) as cnt,sum(sal) as ssal from emp22 where deptno=10 group by deptno;
CNT SSAL
---------- ----------
SQL> 因此,当你在错误的地点错误地增加了group by,Oracle就会报错。没有group by时,输出正常：
SQL> declare
2 v_sal emp22.sal%type;
3 begin
4 select sum(sal) into v_sal from emp22 where deptno=10;
5 dbms_output.put_line('v_sal='||v_sal);
6 end;
7 /
v_sal=
PL/SQL procedure successfully completed
SQL> 有GROUP BY时,执行报错：
SQL> declare
2 v_sal emp22.sal%type;
3 begin
4 select sum(sal) into v_sal from emp22 where deptno=10 group by deptno;
5 dbms_output.put_line('v_sal='||v_sal);
6 end;
7 /
declare
v_sal emp22.sal%type;
begin
select sum(sal) into v_sal from emp22 where deptno=10 group by deptno;
dbms_output.put_line('v_sal='||v_sal);
end;
ORA-01403: 未找到任何数据
ORA-06512: 在 line 4
SQL> 二、生成累计和
公司为了查看用人成本,需要对员工的工资进行累加,以便查看员工人数与工资支出之间的对应关系。
首先,按进入公司的先后顺序（人员编码：empno)来累加查看。
SQL> SELECT empno AS 编号,
2 ename AS 姓名,
3 sal AS 人工成本,
4 SUM(sal) over(ORDER BY empno) AS 成本累计
5 FROM emp
6 WHERE deptno = 30
7 ORDER BY empno;
编号 姓名 人工成本 成本累计
----- ---------- --------- ----------
7499 ALLEN 1600.00 1600
7521 WARD 1250.00 2850
7654 MARTIN 1250.00 4100
7698 BLAKE 2850.00 6950
7844 TURNER 1500.00 8450
7900 JAMES 950.00 9400
6 rows selected通过上面SQL可以看到,分析函数SUM(sal) over(ORDER BY empno)的结果是排序over(ORDER BY empno)后第一行到当前行的所有工资之和。
我们先看一下该语句的PLAN:
Plan hash value: 155210085
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | WINDOW BUFFER | | 6 | 102 | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 102 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IDX_EMPNO | 15 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP@SEL$1
3 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPT_PARAM('_bloom_filter_enabled' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_optimizer_unnest_scalar_sq' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_dsdir_usage_control' 0)
OPT_PARAM('_optimizer_adaptive_plans' 'false')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_null_accepting_semijoin' 'false')
OPT_PARAM('_optimizer_gather_feedback' 'false')
OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')
OPT_PARAM('_optimizer_reduce_groupby_key' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('_fix_control' '8611462:0 14826303:0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :SYS_B_0 (NUMBER): 30
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=:SYS_B_0)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "EMPNO"[NUMBER,22], "EMP".ROWID[ROWID,10], "DEPTNO"[NUMBER,22],
"ENAME"[VARCHAR2,10], "SAL"[NUMBER,22], SUM("SAL") OVER ( ORDER BY "EMPNO" RANGE
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )[22]
2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10],
"SAL"[NUMBER,22], "DEPTNO"[NUMBER,22]
3 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
76 rows selected.大家请看上面ld=1的语句：
SUM(sal)over(ORDERBYempno)转换成了如下语句：
SUM("SAL") OVER ( ORDER BY "EMPNO" RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )这个语句前面的SUM("SAL")容易理解,就是对sal求和。后面分为以下三部分：
ORDER BY "EMPNO":按EMPNO排序。
RANGE:表示这是一个范围开窗。
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示区间从UNBOUNDED PRECEDING(第一行）到CURRENT ROW(当前行）。
为了形象地说明这一点,我们用listagg模拟出每一行是哪些值相加。
SQL>
SQL> SELECT empno AS 编号,
2 ename AS 姓名,
3 sal AS 人工成本,
4 SUM(sal) over(ORDER BY empno) AS 成本累计,
5 (select listagg(sal,'+') within group(order by empno) from emp e where deptno = 30and e.empno<=emp.empno) as 计算公式
6 FROM emp
7 WHERE deptno = 30
8 ORDER BY empno;
编号 姓名 人工成本 成本累计 计算公式
----- ---------- --------- ---------- --------------------------------------------------------------------------------
7499 ALLEN 1600.00 1600 1600
7521 WARD 1250.00 2850 1600+1250
7654 MARTIN 1250.00 4100 1600+1250+1250
7698 BLAKE 2850.00 6950 1600+1250+1250+2850
7844 TURNER 1500.00 8450 1600+1250+1250+2850+1500
7900 JAMES 950.00 9400 1600+1250+1250+2850+1500+950
6 rows selected下面是分析函数简写、rows开窗、range开窗、标量方式的累加方法对比,及标量方式的解释。
SELECT empno,
sal,
SUM(sal) over(ORDER BY empno) AS 简写,
SUM(sal) over(ORDER BY empno rows BETWEEN unbounded preceding AND CURRENT ROW) AS row开窗,
SUM(sal) over(ORDER BY empno RANGE BETWEEN unbounded preceding AND CURRENT ROW) AS range开窗,
(SELECT SUM(sal) FROM emp b WHERE b.empno <= a.empno) AS 标量,
'(SELECT SUM(sal)FROM emp b WHERE b.empno<=' || a.empno || ')' AS 标量解释
FROM emp a
WHERE deptno = 30
ORDER BY 1;在这个案例中,简写、ROW开窗、RANGE开窗、标量几列写法等价。
在没有分析函数的时候,计算累加经常要用这个示例中标量的方式,因为使用标量需要两次访问emp表,会比较慢,是做优化时被改写的目标。
最后一列"标量解释"是每行的计算方式说明,取出来单独执行就是每行的值。
需要注意,本章中各示例语句最后的排序子句只是为了方便大家观察,与分析函数的结果无关.
总结
本章主要是介绍一下常用分析函数在有无空值的情况下，group by写法的差异，以及通过一个简单的累加需求的执行计划，看分析函数到底是怎么改写的！！！
