6.1常用聚集函数

聚集函数需要注意的一点就是：聚集函数会忽略空值，当数据不全为空时对sum等来说没什么影响，但对avg、count来说可能会出现预料之外的结果。所以要根据需求决定是否把空值转为零。

注意，当表中没有数据时，不加group by会返回一行数据，但加了group by 会没有数据返回。

建立空表：

没有group by:

有group by:

因此，当你在错误的地点错误地增加了group by,Oracle就会报错：

这个地方并不需要有group by:

6.2列转行

UNION ALL可以把同表或不同表的数据通过两个或两个以上的语句上下拼在一起：

而当我们把同一个表中的不同列使用UNION ALL拼在一起时，就是我们常说的列转行：

6.3行转列

行转列是一个常用的功能，在处理数据或配置报表时会经常用到，如下图所示。

行转列是指把不同行的数据按给定的规则转为不同的列。

我们通过分拆的方式来学习行转列，看下原数据：

首先需要使用条件表达式增加需要的数据列：

然后把数据汇总：

特别注意上面语句SUM的位置，我们应对条件表达式的结果求和，所以应把条件表达式放在SUM里面，而不仅仅是SUM(SAL):

这种简单的条件表达式也可以直接使用DECODE函数：

6.4生成累计和

假设公司就是按EMP表中的信息来发工资的，那么HIREDATE对应的时点应该发多少工资？

第二行应该是1600+1250，第三行是1600+1250+2850，依次类推。我们可以这样写语句：

或者：

两种方式都有点麻烦，而且很多人也会认为第二种方式易读性不好。在Oracle及很多数据库中都支持分析函数，可以在对EMP只读取一遍的情况下通过分析计算得到需要的数据。

里面的分析函数是指“SUM(sal) over(ORDER BY hirEDAte)”这个组合。让我们通过PLAN来看下它干了什么：

我们的原始语句：

转换成了：

这个语句前面的SUM("SAL")容易理解，就是对sal求和。后面分为以下三部分。

１.ORDER BY "EMPNO" : 按EMPNO排序。

2.RANGE:指按数值范围分析。

3.BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : BETWEEN...AND...子句，表示区间从 UNBOUNDED PRECEDING（第一行）到 CURRENT ROW（当前行）。

以分析到第三行时为例，上面三处的意思加在一起就是：

有时我们需要对数据分组处理，比如EMP的三个部门分别累计。可以加入参数PARTITION BY：

6.5累计与重复值

当分析函数的ORDER BY后面的列有重复数据时，可能得到的结果与期望的不一致，比如我们想用5000来雇佣员工，看最多能雇佣几个人。为了把问题一起显示出来，我们直接用条件<=6000:

可以看到，如果我们按5000算，就会少一名工资1250的员工。原因是分析函数用<=1250计算时会查询到两条数据。这时我们可以增加排序列使排序数据不重复的方式解决：

也可以更改默认的关键字"RANGE"为"ROWS"：

结果会同上面一样。

6.6生成排名

配制报表时可能会遇到生成排名的需求，比如查看员工的工资排名。排名时需要注意的是重复数据，对于重复数据有三种排名方式：

因为工资为3000的员工有两个，所以排序有三种方式：顺序、同名跳号、同名不跳号。

查询中分别用了分析函数ROW_NUMBER、RANK、DENSE_RANK来分组 (PARTITION BY deptno)生成排名。

ROW_NUMBER会生成序号1、2、3。

RANK相同的工资会生成同样的序号，而且其后的序号与ROW_NUMBER相同（empno=7566,生成的序号是3）。

DENSE_RANK相同的工资会生成同样的序号，而且其后的序号递增（empno=7566,生成的序号是２）。

实际查询时使用哪一个函数，要确认好需求再定。

6.7返回最值对应信息

有时我们需要的不是最大最小值，而是要求返回其对应的信息，如返回各部门工资最高的员工姓名。

我们可以按上节的方法取排名第一的数据：

部门20最高工资对应的有两个人，我们这里通过函数RANK返回了两条。

如果我们的需求是只显示一条，可以使用另一个函数：

这个函数可以分两部分来理解，KEEP()返回对应DENSE_RANK函数取值为1的数据，而前面的聚合函数在KEEP()的基础上进一步处理。

这个函数可以把得到的数据与明细一起显示：

6.8求总和的百分比

如下面的表格所示，要求计算各部门的工资合计，以及该合计工资占总工资的比例。

其中的工资合计很简单，直接用group by语句就可以得到。要点在于总工资合计，需要用到分析函数:sum()和over()。

当over()后不加任何内容时，就是对所有的数据进行汇总，步骤如下。

1.分组汇总

2.通过分析函数获取总合计

3.得到总合计后就可以计算比例

另外，我们也可以用专用的比例函数"ratio_to_report"来直接计算。

同其他分析函数一样，可以使用PARTITION BY分组计算，如查询各员工占本部门的工资比例：