分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是对于每个组返回多行,而聚合函数对于每个组只返回一行。over函数 over函数指定了分析函数工作的数据窗口的大小,这个数据窗口大小可能会随着行的变化而变化 (开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化,举例如下:) 常见的搭配 与over 开窗函数row_number() over(partition by ... order by ...) rank() over(partition by ... order by ...) dense_rank() over(partition by ... order by ...) count() over(partition by ... order by ...) max() over(partition by ... order by ...) min() over(partition by ... order by ...) sum() over(partition by ... order by ...) avg() over(partition by ... order by ...) first_value(参数) over(partition by ... order by ...) last_value() over(partition by ... order by ...) lag() over(partition by ... order by ...) lead() over(partition by ... order by ... ---------------------------------------------------------------基表 emp表partition by 分区
select * from emp;
-------------------------------
select empno,ename,sal,comm,deptno,sum(sal) over(order by deptno desc)
from emp
按照deptno排序进行累计, order by 是个开窗函数
------------------------
select empno,ename,sal,comm,deptno,first_value(sal)over(partition by deptno ) "部门最大值"
from emp;
select empno,ename,sal,comm,deptno,LAST_value(sal)over(partition by deptno ) "部门最小值"
from emp;
select empno,ename,sal,comm,deptno,row_number() over(partition by deptno order by sal)
from emp 可以看到每个人的工资在各自组中的排名
over函数
最新推荐文章于 2024-03-22 14:42:57 发布