Oracle——分析函数

目录

 

基本语法

求部门工资的累加

查询每个部门最高工资和最低工资

按工资排序并创建序号

---

 

基本语法

函数名称([参数,...]) 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))的值，它给出相对于总数的百分比

辅助函数