Oracle---分组函数（四）

一、学习目标:

1）使用Group By子句对数据分组（）

2）使用having子句进行过滤分组结果集[where 语句不能对分组函数进行过滤]

二、分组函数类型：

AVG ,COUNT,MAX,MIN,STDDEV(方差)，SUM

--分组函数作用于一组数据，并返回一个值

-- 例如求 最大（小） 平均值等

--查询的列，只要不是组函数都要出现在 GROUP BY 中，少一个一会出错

--样本的标准差，求标准差（方差） 
--运动员 
--张三，李四 跳远成绩 都是 2.1M 2.1M
--(所有成绩减2.1 然后差平方)【值越小成绩发挥稳定】

--求工资平均值
-- SELECT avg(SALARY),max(SALARY),min(SALARY),sum(SALARY)
-- FROM EMPLOYEES

-- SELECT max(last_name),min(LAST_NAME),max(HIRE_DATE),min(HIRE_DATE)
-- FROM EMPLOYEES
-- SELECT count(EMPLOYEE_id),count(LAST_NAME)
-- FROM EMPLOYEES

-- 显示的数据为1 ，进入一次记录一次为1，
-- SELECT count(1),count(2),count(*)
-- FROM EMPLOYEES
-- avg(salary) = sum(salary) /count(salary)

-- SELECT avg(COMMISSION_PCT),sum(COMMISSION_PCT)/count(COMMISSION_PCT),sum(COMMISSION_PCT)/107
-- FROM EMPLOYEES

-- 单行 nvl ()不忽略
-- SELECT avg(COMMISSION_PCT),sum(COMMISSION_PCT)/count (nvl(COMMISSION_PCT,0)),sum(COMMISSION_PCT)/107
-- FROM EMPLOYEES

-- SELECT count(DISTINCT(DEPARTMENT_ID))
-- FROM EMPLOYEES

--分组数据（求出各个部门的平均工资）
--SELECT DEPARTMENT_id ,job_id, round(avg(SALARY),2)
--FROM EMPLOYEES 
--WHERE DEPARTMENT_ID in(10,40,60,80) --过滤条件

--GROUP BY DEPARTMENT_ID ,job_id  --按照部门分组

--查询的列只要不是组函数都要出现在 GROUP BY 中
--少一个一会出错

-- 过滤 HAVING
--求出各个部门平均工资大于6000的，以及平均工资
-- SELECT DEPARTMENT_ID , avg(SALARY)
-- FROM EMPLOYEES
-- WHERE avg(SALARY)> 6000  不能使用组函数
-- WHERE DEPARTMENT_ID in (40,60,80) 不是组函数
-- HAVING avg(SALARY) >6000
-- GROUP BY DEPARTMENT_ID
-- ORDER BY DEPARTMENT_ID ASC  --从小到大 升序

-- 组函数是可以相互嵌套的
SELECT  max(avg(salary))
FROM EMPLOYEES
GROUP BY DEPARTMENT_id,JOB_ID

--1)查询 Toronto这个城市的员工的平均工资
-- SELECT avg(SALARY)
-- FROM EMPLOYEES e, DEPARTMENTS d,LOCATIONS l
-- WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID AND d.LOCATION_ID = l.LOCATION_ID 
-- AND lower(l.CITY) = 'toronto'


--2)（有员工的城市）各个城市的平均工资
-- SELECT l.CITY ,avg(SALARY)
-- FROM EMPLOYEES e, DEPARTMENTS d, LOCATIONS l
-- WHERE e.DEPARTMENT_ID =d.DEPARTMENT_ID AND d.LOCATION_ID = l.LOCATION_ID
-- GROUP BY CITY


--3)求平均/最高工资高于8000的部门id和它的平均工资
-- SELECT DEPARTMENT_ID ,avg(SALARY)
-- FROM EMPLOYEES 
-- HAVING avg(SALARY)>8000
-- GROUP BY DEPARTMENT_ID 


--4）查询平均工资高于6000的Job_title有哪些
-- SELECT JOB_TITLE ,avg(SALARY)
-- FROM EMPLOYEES e join JOBS j 
-- ON e.JOB_ID = j.JOB_ID
-- GROUP BY JOB_TITLE
-- HAVING avg(SALARY)>6000


--5） 组函数处理多行返回一行吗？
是

--6）组函数不计算空值？
是

--7）where子句可否使用组函数进行过滤

不可
--8)查询员工工资的最大，最小，平均值，总和
-- SELECT max(SALARY),min(SALARY),avg(SALARY),sum(SALARY)
-- FROM EMPLOYEES

--9）查询job_id的员工工资的最大，最小，平均值，总和
-- SELECT JOB_ID,max(SALARY),min(SALARY),avg(SALARY),sum(SALARY)
-- FROM EMPLOYEES
-- GROUP BY JOB_ID

--10)选择具有各个job_id的员工人数
-- SELECT  JOB_ID,count(EMPLOYEE_ID)
-- FROM EMPLOYEES
-- GROUP BY JOB_ID

--11）查询员工最高工资和最低工资的差距（Difference）
-- SELECT max(SALARY) ,min(SALARY),max(SALARY) - min(SALARY) "Difference"
-- FROM EMPLOYEES

--12) 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者不计算在内
-- SELECT MANAGER_ID, min(SALARY)
-- FROM EMPLOYEES
-- WHERE MANAGER_ID IS NOT NULL 
-- GROUP BY MANAGER_ID 
-- HAVING min(SALARY)>=6000

--13)查询所有部门的名字，location_id,员工数量和工资平均值
-- SELECT d.DEPARTMENT_NAME ,LOCATION_ID,count(EMPLOYEE_ID),avg(SALARY)
-- FROM EMPLOYEES e right join DEPARTMENTS d ON
-- e.DEPARTMENT_ID = d.DEPARTMENT_ID
-- GROUP BY DEPARTMENT_NAME ,LOCATION_ID


--14)查询公司在1995-1998年之间，每年雇佣的人数
SELECT count(*) "total" , 
count(decode(to_char(HIRE_DATE,'yyyy'),'1995',1,null)) "1995",
count(decode(to_char(HIRE_DATE,'YYYY'),'1996',1,null)) "1996",
count(decode(to_char(HIRE_DATE,'YYYY'),'1997',1,null)) "1997",
count(decode(to_char(HIRE_DATE,'YYYY'),'1998',1,null)) "1998"
FROM EMPLOYEES 
WHERE to_char(HIRE_DATE,'yyyy') in ('1995','1996','1997','1998')


-- 通用函数回顾【复习】
-- SELECT EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID,SALARY,  
--  decode(DEPARTMENT_ID,10,SALARY*1.1,

20,SALARY*1.2,
SALARY*1.3) NewSalary
--  FROM EMPLOYEES
--  WHERE DEPARTMENT_ID in (10,20,30)


-- SELECT  EMPLOYEE_ID,LAST_NAME,SALARY,
-- decode(DEPARTMENT_ID,10,SALARY*1.1,
--                      20,SALARY*1.2,
--                      SALARY*1.3) 新工资
-- FROM EMPLOYEES
-- WHERE DEPARTMENT_ID in (10,20,30)