目录
基本语法
函数名称([参数,...]) over (partition by 子句 字段,...
[order by 子句 字段,... [ASC|DESC] [NULLS FIRST|NULLS LAST]
[WINDOWING 子句]);
- 函数名称:类似于统计函数(COUNT()、RANK()等),但支持更多函数
- OVER子句:为分析函数指明一个查询结果集,在SELECT子句中使用
- PARTITION BY子句:将结果集分为N个分区,而后按不同的组对数据进行统计
- ORDER BY 子句:指明每组的排列顺序,结果与排列顺序有关
- NULLS FIRST|NULLS LAST:决定NULL值在排序中前还是后,默认在前
- WINDOWING子句:给出在定义变化的固定的数据窗口方法,分析函数将对此数据进行操作
Rank () over (partition by deptno order by sal nulls last)
--preceding向上匹配;following向下匹配
Max(sal) over (partition by deptno order by sal range between unbounded preceding and unbounded following)
--设置2行物理偏移
sum(sal) over (partition by deptno order by sal rows 2 preceding)
求部门工资的累加
sum() over (order by...)
--cum
sum (sal) over (order by ename desc)
查询每个部门最高工资和最低工资
max() keep (dense rank first|last order by 表达式 [asc|desc] [nulls first|nulls last],...) [over () 分区查询];
select deptno,
max(sal) keep (dense_rank first order by sal desc) max_salary,
min(sal) keep (dense_rank last order by sal desc) min_salary
from emp
group by deptno;
deptno | max_salary | min_salary |
10 | 5000 | 1300 |
20 | 3000 | 800 |
30 | 2850 | 950 |
按列展示最大最小值
first_value() over (partition by ...order by...)
select deptno, empno, ename, sal,
first_value(sal) over (partititon by deptno order by sal) first_result,
last_value(sal) over (partititon by deptno order by sal) last_result
from emp
where deptno = 10;
deptno | empno | ename | sal | first_result | last_result |
10 | 7934 | MILLER | 1300 | 1300 | 5000 |
10 | 7782 | CLARK | 2450 | 1300 | 5000 |
10 | 7839 | KING | 5000 | 1300 | 5000 |
按工资排序并创建序号
row_number() over ()
select deptno, ename, sal,
row_number() over (partition by deptno order by sal) --按部门分组创建序号
row_number() over (order by sal) --按工资升序创建序号
from emp;
划分数据段区间
WIDTH_BUCKET
语法:WIDTH_BUCKET(expression, minval expression, maxval expression, num buckets)
示例:WIDTH_BUCKET(expression, 0, 2000, 4),会划分4个bucket,其范围为【0,500)【500,100)【1000,1500)【1500,2000)。如果我们指定EXPRESSION 值为300,则width_bucker 返回1,以此类推。如果express的值小于0,则返回0;如果expression大于或者等于2000,则返回5
更多函数
函数名称 | 含义 | 举例 |
等级函数 | ||
RANK() | 根据order by子句的排序字段,从分区查询每一行数据,按照排序生成序号,会出现相同序号 | |
DENSE_RANK | 根据order by子句的排序字段,从分区查询每一行数据,按照排序生成序号,不会出现相同序号 | |
FIRST | 取出DENSE_RANK返回集合中第一行数据 | |
LAST | 取出DENSE_RANK返回集合中最后一行数据 | |
FIRST_VALUE(列) | 返回分区中的第一个值 | |
LAST_VALUE(列) | 返回分区中的最后一个值 | |
LAG(列名称[,行数字][,默认值]) | 访问分区中指定前n行记录,如果没有则返回默认值 | |
LEAD(列名称[,行数字][,默认值]) | 访问分区中指定后n行记录,如果没有则返回默认值 | |
ROW_NUMBER() | 返回每组中的行号 | |
报表函数 | ||
CUME_DIST() | 计算一行在分区中的相对位置 | |
NTILE(数字) | 将一个分区分为“表达式”的散列表示 | |
RATIO_TO_REPORT(表达式) | 该函数计算expression(sum(expression))的值,它给出相对于总数的百分比 | |
辅助函数