分析函数 / 聚合函数的区别
聚合函数 & GROUP BY 的语法规范:
1 聚合函数是能够直接使用的。 max() ,min(),avg(),count()…
2 聚合函数的计算结果返回一个值。
3 GROUP BY 分组之后,聚合函数会根据分组的字段,每一个组返回一个计算结果。
4 聚合函数若是要和其余的字段一块儿查询,那么其余的字段必须是分组的字段。
5 GROUP BY 之后,在 SELECT 后面 只能查询 被分组的字段,和聚合函数,其余的未分组的字段不容许直接查询。
6 若是表中不存在的数据,不受以上条件的约束。web
--(SELECT AVG(SAL) FROM EMP ) AVGSAL 直接用子查询做为输出结果
SELECT E.* ,(SELECT AVG(SAL) FROM EMP ) AVGSAL
FROM EMP E;
ABC不是表中的数据,不受group by 语法约束sql
--聚合函数若是要和其余的字段一块儿查询,那么其余的字段必须是分组的字段
SELECT DEPTNO ,AVG(SAL) ,'ABC'
FROM EMP
GROUP BY DEPTNO ;
分析函数
分析函数和聚合函数的区别?
一、 分析函数几乎没有任何限制,而聚合函数使用限制比较多的。
2 、 分组的方式不一样,分析函数 使用 PARTITION BY 进行分组,聚合函数使用 GROUP BY 分组。
3 、 返回值数量不一样:
分析函数会根据表中的行数,每行返回一个计算结果,而聚合函数只返回一个结果或者每一个组返回一个结果。
4 、ORDER BY 在聚合函数中,只是单纯的排序,
在分析函数中除了能够排序,还能够累计求值。svg
分析函数的最初形态
函数名 over()
进化体
聚合函数 over()
如: avg()over()
--直接求平均值
SELECT e.* ,AVG(e.sal)OVER(), (SELECT AVG(SAL) FROM EMP )
FROM emp e
高级形态
函数名 OVER(PARTITION BY 分组字段1,分组字段2)
如:avg()OVER(PARTITION BY )
SELECT e.* ,AVG(e.sal)OVER(PARTITION BY e.deptno) 各部门平均工资
FROM emp e
终极形态
函数名 OVER(PARTITION BY 分组字段1,分组字段2 ORDER BY 排序字段1,排序字段2..)
-- 分析函数中 order by 既能够排序,另外还能够累计求值
– 查询每一年各月,各部门,各类岗位的人数函数
SELECT DEPTNO 部门,
AVG(DEPTNO)OVER() 部门平均值,
MAX(DEPTNO)OVER(ORDER BY DEPTNO ASC),
MIN(DEPTNO)OVER(ORDER BY DEPTNO ASC)
FROM DEPT
– 分析函数中 order by 既能够排序,另外还能够累计求值
– 查询每一年每一个月,累计入职的员工人数, 平均工资spa
SELECT E.ENAME ,
E.HIREDATE,
E.SAL,
count(1)OVER( ORDER BY E.HIREDATE ASC ) 累计人数,
AVG(E.SAL)OVER( ORDER BY E.HIREDATE ASC ) 平均工资累计
FROM EMP E
– 分析函数中 order by 既能够排序,另外还能够累计求值
code
– 查询每一年,每个月,各部门,各岗位的员工人数xml
SELECT TO_CHAR(E.hiredate,'YYYY-MM') 年月,
E.DEPTNO ,
E.JOB ,
COUNT(1)OVER(PARTITION BY TO_CHAR(E.hiredate,'YYYY-MM'), DEPTNO ,JOB) CT
FROM EMP E;
函数名 OVER(PARTITION BY 分组字段1,分组字段2 ,分组字段3)
blog
-- 函数名OVER(PARTITION BY 分组字段1,分组字段2 ORDER BY 排序字段1,排序字段2..)
-- 分析函数中 order by 既能够排序,另外还能够累计求值
SELECT E.DEPTNO,
E.HIREDATE,
E.SAL,
AVG(E.SAL) OVER(PARTITION BY E.DEPTNO) 各部门平均工资,
AVG(E.SAL) OVER(PARTITION BY E.DEPTNO
ORDER BY E.DEPTNO ASC,
E.HIREDATE DESC) 各部门累计平均工资
FROM EMP E