统计数据与分组函数
综合数据和分组函数
分组函数是对一批(一组)数据进行操作(综合)之后返回一个值
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;