ORACLE分析函数结合开窗函数

ORACLE分析函数结合开窗函数

1. 分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。
2. 在分析函数出现以前，我们必须使用自联查询，子查询或者内联视图，甚至复杂的存储过程实现的语句，现在只要一条简单的SQL语句就可以实现了，而且在执行效率方面也有相当大的提高。
3. 分析函数需要结合开窗函数over()使用，所以亦有人称呼分析函数为窗口函数。

分析函数结合开窗函数使用示例

准备测试数据

-- 创建测试表
create table t_test (v_name varchar2(30),v_subject varchar2(30),v_score number);

-- 插入测试数据
-- 插入测试数据
insert all 
into t_test(v_name,v_subject,v_score)
values ('张三','数学',80)
into t_test(v_name,v_subject,v_score)
values ('张三','语文',80)
into t_test(v_name,v_subject,v_score)
values ('张三','英语',69)
into t_test(v_name,v_subject,v_score)
values ('张三','物理',69)

into t_test(v_name,v_subject,v_score)
values ('李四','数学',80)
into t_test(v_name,v_subject,v_score)
values ('李四','语文',85)
into t_test(v_name,v_subject,v_score)
values ('李四','英语',78)
into t_test(v_name,v_subject,v_score)
values ('李四','物理',69)

into t_test(v_name,v_subject,v_score)
values ('王五','数学',69)
into t_test(v_name,v_subject,v_score)
values ('王五','语文',95)
into t_test(v_name,v_subject,v_score)
values ('王五','英语',88)
into t_test(v_name,v_subject,v_score)
values ('王五','物理',69)

into t_test(v_name,v_subject,v_score)
values ('赵柳','数学',69)
into t_test(v_name,v_subject,v_score)
values ('赵柳','语文',95)
into t_test(v_name,v_subject,v_score)
values ('赵柳','英语',88)
into t_test(v_name,v_subject,v_score)
values ('赵柳','物理',69)
select 1 from dual;

分析函数的朋友，开窗函数over()

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：

-- 按照v_score排序进行累计
over(order by v_score)

-- 按照姓名分区
over(partition by v_name)

-- 定义前行幅度和后行幅度：每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过4
over(order by v_score range range between 5 preceding and 4 following)

分析函数之评级函数

用于等级、百分点、n分片等

函数	说明
RANK()	返回数据项在分组中的排名，排名相等会在名次中留下空位，排名不连续
DENSE_RANK()	返回数据项在分组中的排名，排名相等会在名次中不会留下空位，排名连续
CUME_DIST()	返回当前名次在总名次中占的%分比,最后一行的值一定为1;
PERCENT_RANK()	返回当前名次在总结果集中占的%分比,但该值从0开始计数，因此一个结果集为10的计算方法为:第一行为0/10,第二行为1/(10-1),第三行为2/(10-1),最后一行值亦为1
NTILE()	返回n分片后的值
ROW_NUMBER()	为每条记录返回一个数字

示例：

select a.*,
  -- 返回数据项在分组中的排名，排名相等会在名次中留下空位，排名不连续
  rank() over(partition by a.v_subject order by a.v_score desc) as v1,
  -- 返回数据项在分组中的排名，排名相等会在名次中不会留下空位，排名连续
  dense_rank() over(partition by a.v_subject order by a.v_score desc) as v2,
  -- 返回特定值对于一组值的位置“cumulative distribution”(累积分布))，即大于等于该分数的人所占总人数的百分比
  cume_dist() over(partition by a.v_subject order by a.v_score desc) as v3,
  -- 返回某个值相对于一组值的百分比排名,【（RANK名次-1）/（总人数-1）】
  percent_rank() over(partition by a.v_subject order by a.v_score desc) as v4,
  /*
    Ntile(n)即将结果集划分n片,计算当前值在n片中的那一个范围片中,
    如果n等于4则每一片的值为0.25,根据percent_rank()计算出当前行的值,
    如果值<=0.25则返回值为1,如果>0.25 and <=0.25*2,则返回值为2,
    如果>0.25*2 and <=0.75则值为3,其它值均为4;
  */
  ntile(4) over(partition by a.v_subject order by a.v_score desc) as v5,
  ntile(3) over(partition by a.v_subject order by a.v_score desc) as v5,
  ntile(2) over(partition by a.v_subject order by a.v_score desc) as v5,
  ntile(1) over(partition by a.v_subject order by a.v_score desc) as v5,
  -- 为每条记录返回一个数字
  row_number() over(partition by a.v_subject order by a.v_score desc) as v6
from t_test a

分析函数之报表函数

select a.*,
  sum(a.v_score) over(partition by a.v_name) as v_1,-- 求总分
  min(a.v_score) over(partition by a.v_name) as v_2,-- 求最低分 
  max(a.v_score) over(partition by a.v_name) as v_3,-- 求最高分
  avg(a.v_score) over(partition by a.v_name) as v_4 -- 求平均分

from t_test a

LAG()、LEAD()

获得相对于当前记录指定距离的那条记录的数据
LAG()为向前、LEAD()为向后

select a.*,
  row_number() over(partition by a.v_subject order by a.v_score desc) as v1,
  
  -- 往前取1位
  lag(a.v_name,1) over(partition by a.v_subject order by a.v_score desc) as v_2_1,
  lag(a.v_subject,1) over(partition by a.v_subject order by a.v_score desc) as v_2_2,
  
  -- 往后取两位  
  lead(a.v_name,2) over(partition by a.v_subject order by a.v_score desc) as v_3_1,
  lead(a.v_subject,2) over(partition by a.v_subject order by a.v_score desc) as v_3_2

from t_test a