oracle中group学习个人笔记

4 group
group操作相关：聚合函数，group by子句，having子句，group扩展-rollup,cube,group sets
4.1聚合函数
>>>null与聚合函数
count(*)不受null影响，统计的是行数
count(col),col是列名，如果col有null，则不参与统计
例外grouping，不会忽略null,其他聚合函数均不统计null

AVG(sale_price) <> SUM(sale_price) / COUNT(*),
应该是
AVG(sale_price) = SUM(sale_price) / COUNT(sale_price)
可能的情况:COUNT(*)>COUNT(sale_price)

如果对所有行求平均，如下方法
SELECT AVG(NVL(sale_price,0)) FROM cust_order;
nvl函数会把所有null的行转为0，再参与平均。

>>>使用distinct与all
默认:count(all col_name)
count(distinct col_name)

某列有null，即使用all关键字，null的记录仍然不会参与统计
SELECT COUNT(ALL sale_price) FROM cust_order;--仍然返回14，事实表中记录是20

4.2group by子句
>>>select list与group by对应
select中非聚合的列均需在group by子句后出现
group by子句后不能有聚合函数
select list中的常量可不出现在group by子句中

group by 中可出现标量函数scalar函数
SELECT SUBSTR(lname,1,1), COUNT(*)
FROM employee
GROUP BY SUBSTR(lname,1,1);
group by lname 也可，但会有重复记录

以下两种写法均可
SELECT manager_emp_id || job_id, COUNT(*)
FROM employee
GROUP BY manager_emp_id || job_id;
与
SELECT manager_emp_id || job_id, COUNT(*)
FROM employee
GROUP BY manager_emp_id, job_id;

group by子句中的表达式可不在select list中出现
SELECT cust_nbr, COUNT(order_nbr)
FROM cust_order
GROUP BY cust_nbr, order_dt;
但可读性差，建议保持select list与group by子句中非聚合函数表达式的一致性

>>>group by子句与null值
分组的列如果有null，null集中统计，就出现一次
SELECT sale_price, COUNT(order_nbr)
FROM cust_order
GROUP BY sale_price;
查询结果如下：
SALE_PRICE COUNT(ORDER_NBR)
---------- ----------------
        25                4
        34                2
        56                4
        99                4
                          6
可在order by后用 NULLS FIRST 或者 NULLS LAST，调整null那组的显示顺序
ORDER BY sale_price NULLS FIRST / ORDER BY sale_price NULLS LAST

4.3Having子句
比较性能，建议第二种写法，第一种先分组再过滤，第二种先筛选再分组
SELECT cust_nbr, COUNT(order_nbr)
FROM cust_order
GROUP BY cust_nbr
HAVING cust_nbr < 6;
与
SELECT cust_nbr, COUNT(order_nbr)
FROM cust_order
WHERE cust_nbr < 6
GROUP BY cust_nbr;
                
group by子句与having子句的顺序后互换，没有区别
SELECT cust_nbr, COUNT(order_nbr)
FROM cust_order
GROUP BY cust_nbr
HAVING COUNT(order_nbr) > 2;
与
SELECT cust_nbr, COUNT(order_nbr)
FROM cust_order
HAVING COUNT(order_nbr) > 2
GROUP BY cust_nbr;
等价

以下语句不正确：
SELECT cust_nbr, COUNT(order_nbr)
FROM cust_order
GROUP BY cust_nbr
HAVING order_dt < SYSDATE;
原因是having子句中的order_dt不在select list中

4.4嵌套的group操作
SELECT MAX(sal) FROM
(SELECT dept_id, SUM(salary) sal
FROM employee
GROUP BY dept_id);
与以下等价
SELECT MAX(SUM(salary))
FROM employee
GROUP BY dept_id;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/777981/viewspace-674977/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/777981/viewspace-674977/