统计数据与分组函数

综合数据和分组函数
分组函数是对一批（一组）数据进行操作（综合）之后返回一个值

oracle常用的分组语句有以下5个：COUNT、AVE、SUM、MAX、MIN

COUNT({*[DISTINCT|ALL]表达式})：该函数返回查询的行数
SELECT COUNT(*) FROM USER_BASE_INFO; --包含空行和重复的行
SELECT COUNT(MOB_BELONG_TO) AS "城市总数" FROM USER_BASE_INFO; --查询城市总数
SELECT COUNT(DISTINCT MOB_BELONG_TO) AS "城市总数" FROM USER_BASE_INFO; --不重复的城市总数
如何达到去重去空，可以利用表达式达到效果。我的理解：表达式不仅仅局限与表达式也可以是查询语句等

 

AVG([DISTINCT|ALL]表达式)：返回表达式的平均值
SUM([DISTINCT|ALL]表达式)：返回表达式的总合
SELECT AVG(sal) "Average Salary", SUM(sal) "Summary", COUNT(sal) "Records"
FROM emp;
为每一列取有意义的别名很重要
MAX([DISTINCT|ALL]表达式)：该函数返回表达式的最大值
MIN([DISTINCT|ALL]表达式)：该函数返回表达式的最小值
SELECT MIN(sal) "Lower Salary", MAX(sal) "Highest Salary"
FROM emp;
AVG和SUM只能操作数字型数据，MIN和MAX函数不但可用于数字型数据，而且还可以用于字符型数据和日期型数据。
SELECT MIN(hiredate) "First Day", MAX(hiredate) "Last Day"
FROM emp;

GROUP BY子句的应用
简单使用：
SELECT job,AVG(sal) "Average Salary"
FROM emp
GROUP BY job;

改变GROUP BY子句中的排序次序
可以通过ORDER BY子句改变排序次序
SELECT job，AVG(sal) "Average Salary"
FROM emp
GROUP BY job
ORDER BY "average Salary" DESC;
改变group by的排序的，并且group by子句可以使用别名

GROUP BY子句的特殊用法
GROUP BY子句中查询的列可以不在SELECT列表中。
SELECT AVG(sal) "Average Salary"
FROM emp
GROUP BY job
为了提高结果的可读性，应该尽量不使用这种方式，难以看出是依据什么进行排序的

 

分组函数与GROUP BY子句的非法操作
SELECT job,AVG(sal)
FROM emp;
注意：如果在一个查询中使用了分组函数，任何不在分组函数中的列或表达式必须在GROUP BY子句中。
分析：仔细研究例子的查询语句就不难理解Oracle为什么做出这样的规定。在SELECT子句中第一项job高数Oracle系统显示每行数据的职位，在emp表中一共有14行的数据。而在SELECT子句中的第二项AVG(sal)高数Oracle系统显示emp表中所有数据行的平均工资，在这个查询语句中只能产生一个平均工资。查询语句中的两个要求是矛盾的，所以报错了。
改正错误的做法是：
在后面增加GROUP by语句将列job放入该子句中。

HAVING子句的使用。
例子：
SELECT mob_belong_to,user_id,MAX(BIRTH_DATE)
FROM user_base_info
WHERE sign_in_date IS NOT NULL
-- AND MAX(BIRTH_DATE) IS NOT NULL
GROUP BY user_id, mob_belong_to
ORDER BY mob_belong_to DESC,USER_ID DESC;
执行例子，我们可以发现当可以成功执行，但是当我们将where条件使用聚合函数的注释去掉的时候，我们可以发现报错。这个是因为WHERE语句的执行顺序在聚合函数之前，所以报错。遇到这种情况我们应该使用的是HAVING：
SELECT mob_belong_to,user_id,MAX(BIRTH_DATE)
FROM user_base_info
HAVING -- sign_in_date IS NOT NULL -- 报错 AND
MAX(BIRTH_DATE) IS NOT NULL
GROUP BY user_id, mob_belong_to
ORDER BY mob_belong_to DESC,USER_ID DESC;
我们发现注释行会报错，它发现了前面隐藏的问题，sign_in_date不是分组的group by选项。然后将该行注释再使用HAVING子句就没有问题了。
所以我们得出结论：WHERE是在分组前面进行的执行，HAVING是在分组后进行的执行。
一般地：我们习惯将HAVING子句放在GROUP BY子句之后，应为HAVING是在分组之后执行的。
SELECT MOB_BELONG_TO
FROM user_base_info
GROUP BY MOB_BELONG_TO
HAVING MAX(BIRTH_DATE) IS NOT NULL

分组函数的嵌套：
SELECT MIN(AVG(sal)),MAX(AVG(sal))
FROM emp;
WHERE job NOT LIKE 'PRESI%'
GROUP BY job;
oracle分组函数只能嵌套两层。

注意：oracle虽然为我们带来便捷，但是使用起来会明显使系统效率降低，特别是在对容量大的表格进行的操作时，因为分组函数通常要扫描整个表。

分组函数的空值问题：
除了COUNT(*)以外，其他的分组函数都不处理空值（NULL）。
SELECT AVG(comm) "Average Commission"
FROM emp;
发现查询出来的结果似乎有问题，偏高。那是因为AVG(comm)不包括comm为NULL的记录行。修改SQL:
SELECT AVG(comm) "Average Commission",SUM(comm) "Summary Commission",job,COUNT(comm) "Records"
FROM emp;
GROUP BY job;

NVL函数在分组函数中的使用
我们知道COUNT(*)之外的分组函数是不处理NULL的，那么要处理NULL，就可以使用NVL函数。
SELECT AVG(NVL(comm，0)) ”Average Commission"
FROM emp;