Oracle分組查詢

分組查詢

分組函數 ：group by 要分組的列名 對數據集合處理的函數，可以處理多行數據
--5個分組函數
--count --計數函數，用於數量的統計
--sum 求和函數，對數據進行求和
--avg 求評平均，對一組數據求平均值
--min ,求最小值
--max ,求最大值
--分組函數，也叫統計函數，一般也做數據統計使用

例子：
--查詢出10部門的最高工資和最低工資，平均工資；
select max(sal) ,min(sal) ,avg(sal) from emp where deptno=10;
--查詢出入職日期在1981年6月後的員工數量，及平均工資
select count(*) ,avg(sal) from emp where to_char(hiredate,'yyyy-mm') >= '1981-06';
select count(*),avg(sal) from emp where hiredate>=to_date('1981-06','yyyy-mm');
--查詢出公司每月的工資支出是多少
select sum(sal) from emp;
--公司員工普提工資上調50%，查詢公司上調工資前後，每月工資支出
select sum(sal),sum(sal+(sal*0.5)) from emp;

1. select 語句的執行順序：
from -> where -> group by -> having -> select - order by
– 1.通過FROM子句中找到需要查詢的表；
– 2.通過WHERE子句進行非分組函數篩選判斷；
– 3.通過GROUP BY子句完成分組操作；
– 4.通過HAVING子句完成組函數篩選判斷；
– 5.通過SELECT子句選擇顯示的列或表達式及組函數；
– 6.通過ORDER BY子句進行排序操作。
例子：查詢出入職日期在1981年2月後的員工信息，並按照部門進行分組，查詢出每個部門的平均工資,並且平均工資在2600以上 ,按照平均工資進行倒序排序
select deptno, avg(sal) avgsal
from emp
where hiredate >= to_date('1981-02', 'yyyy-mm')
group by deptno
having avg(sal) > 2500
order by avg(sal) desc;

2. 聯合主鍵
例子：按入職年份和部門進行統計，查詢出各部門各入職年份的最高工資和最低工資，多列統計
理解：就是按照入職年份，部門進行分組
select to_char(hiredate,'yyyy'),deptno,max(sal),min(sal) from emp group by to_char(hiredate,'yyyy'),deptno;

3. 主函數中的distinct函數
1）DISTINCT會消除重覆記錄後再使用組函數
--理解distinct 關鍵與組函數的組合使用，用於何種場合
例如：統計員工所在的部門的數量
select count(deptno),count(distinct deptno) from emp;
2）遇到有null空值數據時，可以組合單行函數進行使用
例如：統計員工的平均獎金，沒有獎金看為0
select count(*),count(comm),avg(comm),avg(nvl(comm,0)) from emp;

分組查詢時註意：
1.帶group by的分組查詢，執行順序是先分組group by 後查詢 select，即，寫在select 後面的列，必須是分組列，或 組函數
2.where條件中不能直接使用組函數
3.分組數據可以使用having字句進行過濾
4.分組列可以不在select子句中顯示，select 列中要顯示的列，必須出現在group by 分組中（在select 子句中非組函數列，都必須添加到group by 中）
5.having 是對group by分組後的數據進行過濾
6.組查詢語句可以使用在：select ，having ,order by 。不能直接使用在

4. count統計函數
count(*) 和count(列名)
在count 統計中，不統計值為null的行
阿裡巴巴的編碼規範：在做行數統計時，要求使用count(*),以獲得更高的效率或準確率

5.分組函數中空值處理
1）.除了COUNT（*）之外，其它所有分組函數都會忽略列中的空值，然後再進行計算。
2 ). 在分組函數中使用NVL函數
– NVL 函數可以使分組函數強制包含含有空值的記錄
3）.遇到有null空值數據時，可以組合單行函數進行使用
例如：統計員工的平均獎金，沒有獎金看為0
select count(*),count(comm),avg(comm),avg(nvl(comm,0)) from emp;

--補充說明
1）. min 和 max 統計字元類型時，會根據字元的先後順序進行統計
min和max統計時，如果數值為null，則不統計
MIN和MAX可以用於任何數據類型
例如：查詢入職日期最早和最晚的日期
MIN(hiredate) 最早日期 ， MAX(hiredate) 最晚日期

2). SUM和AVG函數都是只能夠對數值類型的列或表達式操作。
例如：查詢工資總和，平均工資
SUM(sal)，AVG(sal) ，

3).COUNT函數的主要功能是返回滿足條件的每組記錄條數
COUNT(*)：返回表中滿足條件的行記錄數

4). 組函數中DISTINCT：DISTINCT會消除重覆記錄後再使用組函數

其他三個函數是具有相同特性
select * from emp;
select min(job),max(job) from emp;
select min(ename),max(ename) from emp;
select min(comm),max(comm) = emp;