高级函数_分析函数与窗口函数
高级函数_分析函数与窗口函数
分析函数往往与窗口函数一起使用，over()为窗口函数
一、分析函数
1.01、排名
分析函数中的排名函数可以针对窗口中的记录生成排序序号，常用的排名函数有：rank()、dense_rank()、row_number()
语句：select * from employee;
EMP_ID EMP_NAME EMP_SALARY
------------------------ ------------------------------ ----------------------
0001 张三 5000
0002 李四 4500
0003 赵飞 6000
0004 韩明 4500
0005 王兰 4500
0006 李丽 6000
语句：select emp_name,rank()over(order by emp_salary) position from employee;
EMP_NAME POSITION
--------------------- ----------------------
王兰 1
李四 1
韩明 1
张三 4
李丽 5赵飞 5
rank()函数对于相同排名的记录，会返回相同的排序序号，当出现多个排名 相同的记录时，下一排名序号，将根据前一排名个数进行跳跃，如里面没有出现2和3，直接跳到4
语句：select emp_name,dense_rank()over(order by emp_salary) position from employee;
EMP_NAME POSITION
------------------------ ----------------------
王兰 1
李四 1
韩明 1
张三 2
李丽 3
赵飞 3
dense_rank()函数对于相同排名的记录，会返回相同的排序序号，当出现多个排名 相同的记录时，不会进行跳跃
语句：select emp_name,row_number() over(order by emp_salary) position from employee;
EMP_NAME POSITION
------------------------ ----------------------
王兰 1
李四 2
韩明 3
张三 4
李丽 5
赵飞 6row_number()函数每条记录会存在唯一的排序序号
二、分区窗口
利用partition by 可以指定 分区窗口
语句： select * from employee;
EMP_ID EMP_NAME EMP_SALARY EMP_DEPARTMENT
-------------------- ----------------- ---------------------- --------------------
0001 张三 5000 设计部
0002 李四 4500 工程部
0003 赵飞 6000 项目部
0004 韩明 4500 项目部
0005 王兰 4500 工程部
0006 李丽 6000 项目部
语句：select t.*,dense_rank() over(partition byemp_department order by emp_salary) position from employee t order by t.emp_id;
EMP_ID EMP_NAME EMP_SALARY EMP_DEPARTMENT POSITION
-------------------- -------------------- ---------------------- -------------------- - ---------------------
0001 张三 5000 设计部 1
0002 李四 4500 工程部10003 赵飞 6000项目部20004 韩明 4500项目部10005 王兰 4500工程部10006 李丽 6000项目部2
分区内可以求和
语句：select t.*,sum(emp_salary)over(partition byemp_department ) position from employee t order by t.emp_id;
EMP_ID EMP_NAME EMP_SALARY EMP_DEPARTMENT TOTAL_SALARY
-------------------- -------------------- ---------------------- -------------------- ---------------------
0001 张三 5000 设计部 5000
0002李四4500工程部9000
0003赵飞6000项目部16500
0004韩明4500项目部16500
0005王兰4500工程部9000
0006李丽6000项目部16500
三、窗口子句
可以使用窗口子句来进一步控制窗口的范围，包含两类：rows和range
3.01、rows子句
rows子句以当前记录为参照，可以向前向后推移，形成新的结果集
语句：select emp_id,emp_name,emp_salary,sum(emp_salary) over(order by emp_id rows between 1 preceding and 1 following) total_salary from employee;
EMP_ID EMP_NAME EMP_SALARY TOTAL_SALARY
-------------------- -------------------- ---------------------- ----------------------
0001 张三 5000 9500
0002李四450015500
0003赵飞600015000
0004韩明450015000
0005王兰450015000
0006李丽600010500
rows子句因为和位置有关,必须使用order by排序，否则报错
3.02、range子句range子句按照列值进行窗口的进一步限制
语句：select emp_id,emp_name,emp_salary,count(1) over(order by emp_salary range between 300 preceding and 400 following) total_count from employee;
EMP_ID EMP_NAME EMP_SALARY TOTAL_COUNT
-------------------- -------------------- ---------------------- ----------------------
0005 王兰 4500 3
0002 李四450030004 韩明450030001 张三500010006 李丽600020003 赵飞60002
3.03、unbouned和 current row
unbouned表示没有任何限制，current row代表当前行
四、主要的分析函数
分析函数作用对象为窗口函数所捕获的记录集合
4.01 、first_value()函数的使用first_value()函数用于获取窗口函数所捕获的记录集中的第一条记录，并根据第一条记录返回返回表达式参数的值
语句： select * from employee;
EMP_ID EMP_NAME EMP_SALARY EMP_DEPARTMENT
-------------------- ----------------- ---------------------- --------------------
0001 张三 5000 设计部
0002 李四 4500 工程部
0003 赵飞 6000 项目部
0004 韩明 4500 项目部
0005 王兰 4500 工程部
0006 李丽 6000 项目部
语句：select distinctemp_department,first_value(emp_name) over(partition by emp_department order by emp_salary)minname,
first_value(emp_salary) over(partition by emp_department order by emp_salary)minsalary from employee;
EMP_DEPARTMENT MINNAME MINSALARY
-------------------- -------------------- ----------------------
设计部 张三 5000
项目部韩明4500
工程部李四4500
备注：distinct关键字是必要的，查询每部门工资最少的人 ，人名和工资都要用first_value()函数，每一列都是一个结果集，互不影响，若有一个不加，不加的会显示所有的值，结果如下：
语句：select distinct emp_department, first_value(emp_name) over(partition by emp_department order by emp_salary) minname,emp_salary from employee;
EMP_DEPARTMENT MINNAME EMP_SALARY
-------------------- -------------------- ----------------------
工程部 李四 4500
设计部张三5000项目部韩明4500
项目部韩明6000
4.02 、last_value()函数的使用
last_value()函数返回窗口中的最后一条记录的相关信息
语句：select distinct last_value(emp_name) over(partition by emp_department order by emp_salary rows between unbounded preceding and unbounded following) maxname,emp_department,
last_value(emp_salary) over(partition by emp_department order by emp_salaryrows between unbounded preceding and unbounded following) maxsalary from employee;
MAXNAME EMP_DEPARTMENT MAXSALARY
-------------------- -------------------- ----------------------
张三 设计部 5000
赵飞项目部6000李四工程部4500
备注：窗口函数中必须使用rows between unbounded preceding and unbounded following，因为默认情况下，order by 窗口为第一条记录至当前记录，对于order by 子句，如果没有显示指定rows和range子句，则相当于使用了rows between unbounded preceding and current row following或者range between unbounded preceding andcurrent rowfollowing，当排序后存在相同的排名，则所有相同的记录放到被刷选的窗口中，值不同会放到不同的窗口，未使用的结果如下：
语句：select distinct last_value(emp_name) over(partition by emp_department order by emp_salary) maxname,emp_department,
last_value(emp_salary) over(partition by emp_department order by emp_salary) maxsalary from employee;
MAXNAME EMP_DEPARTMENT MAXSALARY
-------------------- -------------------- ----------------------
张三 设计部 5000
韩明项目部4500赵飞项目部6000王兰工程部4500
4.03 、lead()函数的使用
lead()函数对于通过排序之后的窗口集合，lead()函数自当前记录向下推移，获得新的记录
语法：lead(表达式，偏移量，替换值) 其中替换值是当无法获取新纪录时，用该值替换
语句：select emp_id,emp_name,emp_salary,emp_department from employee order by emp_id;
EMP_ID EMP_NAME EMP_SALARY EMP_DEPARTMENT
-------------------- -------------------- ---------------------- --------------------
0001 张三 5000 设计部
0002 李四 4500 工程部
0003 赵飞 6000 项目部
0004 韩明 4500 项目部
0005 王兰 4600 工程部
0006 李丽 6000 项目部
语句：select emp_id,emp_name,emp_salary,emp_department,lead(emp_name,1,'无') over(partition by emp_department order by emp_salary) pre_name from employee order by emp_id;
EMP_ID EMP_NAME EMP_SALARY EMP_DEPARTMENT PRE_NAME
-------------------- -------------------- ---------------------- -------------------- --------------------
0001 张三 5000 设计部 无
0002 李四 4500 工程部 王兰
0003 赵飞 6000 项目部 无
0004 韩明 4500 项目部 李丽
0005 王兰 4600 工程部 无0006 李丽 6000 项目部 赵飞
4.04 、lag()函数的使用
lag()函数与lead()函数具有相同的语法规则，在排序之后，lag()函数自当前记录向上搜索
语法：select emp_id,emp_name,emp_salary,emp_department,lag(emp_name,1,'无') over(partition by emp_department order by emp_salary) pre_name from employee order by emp_id;
EMP_ID EMP_NAME EMP_SALARY EMP_DEPARTMENT PRE_NAME
-------------------- -------------------- ---------------------- -------------------- --------------------
0001 张三 5000 设计部 无
0002 李四 4500 工程部 无
0003 赵飞 6000 项目部 李丽
0004 韩明 4500 项目部 无
0005 王兰 4600 工程部 李四
0006 李丽 6000 项目部 韩明
