【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加

系列文章目录

【SQL开发实战技巧】系列（一）:关于SQL不得不说的那些事
【SQL开发实战技巧】系列（二）：简单单表查询
【SQL开发实战技巧】系列（三）：SQL排序的那些事
【SQL开发实战技巧】系列（四）：从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项
【SQL开发实战技巧】系列（五）：从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论
【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放
【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
【SQL开发实战技巧】系列（八）：聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！
【SQL开发实战技巧】系列（十）：从拆分字符串、替换字符串以及统计字符串出现次数说起
【SQL开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数
【SQL开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值IN列表?)
【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工
【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算
【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算
【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？
【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据
【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据
【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式
【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的
【SQL开发实战技巧】系列（二十五）：数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组
【SQL开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行

文章目录

前言

本篇文章讲解的主要内容是：常用聚集函数及group by与空值的影响、详解通过执行计划看sum()over()分析函数。
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

一、常用聚集函数

SQL>SELECT deptno,2AVG(sal)AS平均值,MIN(sal)AS最小值,MAX(sal)AS最大值,SUM(sal)工资合计,COUNT(*)总行数,3COUNT(comm)获得提成的人数,4AVG(comm)错误的人均提成算法,5AVG(coalesce(comm,0))正确的人均提成 FROM emp
  6GROUPBY deptno;

DEPTNO      AS平均值      AS最小值      AS最大值       工资合计        总行数    获得提成的人数  错误的人均提成算法    正确的人均提成
------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------102916.66666130050008750300202175800300010875500301566.666669502850940064550366.666666

聚集函数需要注意的一点就是：聚集函数会忽略空值,这对sum等来说没什么影响,但对avg、count来说就可能会出现预料之外的结果。所以要根据需求决定是否把空值转为零。
注意,当表中没有数据时,不加group by会返回一行数据,但加了group by会没有数据返回。
建立空表：

SQL>createtable emp22 asselect*from emp where1=2;Table created

SQL>selectcount(*)as cnt,sum(sal)as ssal from emp22 where deptno=10;

       CNT       SSAL
---------- ----------0SQL>

有group by

SQL>selectcount(*)as cnt,sum(sal)as ssal from emp22 where deptno=10groupby deptno;

       CNT       SSAL
---------- ----------SQL>

因此,当你在错误的地点错误地增加了group by,Oracle就会报错。没有group by时,输出正常：

SQL>declare2  v_sal emp22.sal%type;3begin4selectsum(sal)into v_sal from emp22 where deptno=10;5    dbms_output.put_line('v_sal='||v_sal);6end;7/

v_sal=

PL/SQLprocedure successfully completed

SQL>

有GROUP BY时,执行报错：

SQL>declare2  v_sal emp22.sal%type;3begin4selectsum(sal)into v_sal from emp22 where deptno=10groupby deptno;5    dbms_output.put_line('v_sal='||v_sal);6end;7/declare
v_sal emp22.sal%type;beginselectsum(sal)into v_sal from emp22 where deptno=10groupby deptno;
  dbms_output.put_line('v_sal='||v_sal);end;

ORA-01403: 未找到任何数据
ORA-06512: 在 line 4SQL>

二、生成累计和

公司为了查看用人成本,需要对员工的工资进行累加,以便查看员工人数与工资支出之间的对应关系。
首先,按进入公司的先后顺序（人员编码：empno)来累加查看。

SQL>SELECT empno AS 编号,2         ename AS 姓名,3         sal AS 人工成本,4SUM(sal)over(ORDERBY empno)AS 成本累计
  5FROM emp
  6WHERE deptno =307ORDERBY empno;

   编号 姓名            人工成本       成本累计
----- ---------- --------- ----------7499 ALLEN        1600.0016007521 WARD         1250.0028507654 MARTIN       1250.0041007698 BLAKE        2850.0069507844 TURNER       1500.0084507900 JAMES         950.0094006rows selected

通过上面SQL可以看到,分析函数SUM(sal) over(ORDER BY empno)的结果是排序over(ORDER BY empno)后第一行到当前行的所有工资之和。
我们先看一下该语句的PLAN:

Planhashvalue: 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 BYINDEX ROWID| EMP     |6|102|2(0)|00:00:01||3|INDEXFULL SCAN         | IDX_EMPNO |15||1(0)|00:00:01|------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------1- SEL$12- SEL$1/ EMP@SEL$13- 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(ORDERBY"EMPNO" RANGE
       BETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)[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]76rows selected.

大家请看上面ld=1的语句：

SUM(sal)over(ORDERBYempno)

转换成了如下语句：

SUM("SAL")OVER(ORDERBY"EMPNO" RANGE BETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)

这个语句前面的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 人工成本,4SUM(sal)over(ORDERBY empno)AS 成本累计,5(select listagg(sal,'+')withingroup(orderby empno)from emp e where deptno =30and  e.empno<=emp.empno)as 计算公式
  6FROM emp
  7WHERE deptno =308ORDERBY empno;

   编号 姓名            人工成本       成本累计 计算公式
----- ---------- --------- ---------- --------------------------------------------------------------------------------7499 ALLEN        1600.00160016007521 WARD         1250.0028501600+12507654 MARTIN       1250.0041001600+1250+12507698 BLAKE        2850.0069501600+1250+1250+28507844 TURNER       1500.0084501600+1250+1250+2850+15007900 JAMES         950.0094001600+1250+1250+2850+1500+9506rows selected

下面是分析函数简写、rows开窗、range开窗、标量方式的累加方法对比,及标量方式的解释。

SELECT empno,
       sal,SUM(sal)over(ORDERBY empno)AS 简写,SUM(sal)over(ORDERBY empno rowsBETWEENunboundedprecedingANDCURRENTROW)ASrow开窗,SUM(sal)over(ORDERBY empno RANGE BETWEENunboundedprecedingANDCURRENTROW)AS range开窗,(SELECTSUM(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 =30ORDERBY1;

在这个案例中,简写、ROW开窗、RANGE开窗、标量几列写法等价。
在没有分析函数的时候,计算累加经常要用这个示例中标量的方式,因为使用标量需要两次访问emp表,会比较慢,是做优化时被改写的目标。
最后一列"标量解释"是每行的计算方式说明,取出来单独执行就是每行的值。
需要注意,本章中各示例语句最后的排序子句只是为了方便大家观察,与分析函数的结果无关.

总结

本章主要是介绍一下常用分析函数在有无空值的情况下，group by写法的差异，以及通过一个简单的累加需求的执行计划，看分析函数到底是怎么改写的！！！