oracle的窗口函数over()详解

本篇为窗口函数姊妹篇2，一篇链接：https://www.cnblogs.com/thomasbc/p/15131611.html

窗口函数的威力是与其他函数联合使用,窗口函数主要和统计函数,排名函数,错行的函数联合使用.

 

★1.和统计函数(max,min,avg,sum等)连用.

需求：查询每个同学单科成绩和班级相应单科成绩的差值.

结果是这样,张三语文+1,张三数学-1.代表了张三语文比班级语文均分高一分,数学成绩比班级均分少一分.

我们可以使用传统的group by 来查出每门功课的平均分,然后查出差值,sql脚本如下

select t1.*, (t1.score - t3.avgs) as gaps
from test_student_score t1,
(select t2.subject_id, avg(t2.score) as avgs
from test_student_score t2
group by t2.subject_id) t3
where t1.subject_id = t3.subject_id;

如果是使用over 窗口函数,这个sql就变成了一行代码,如下

select t.*,
(t.score-avg(t.score) over( partition by t.subject_id)) as gaps 
from test_student_score t

上面两句脚本都得到如下结果

窗口函数与统计函数一块使用详解： https://www.cnblogs.com/thomasbc/p/15136034.html

 

★2.和排名函数(rank,dense_rank)连用.

先说明连个函数的意义:rank是不连续排名函数(1,1,3,3,5),dense_rank 是连续排名函数(1,1,2,2,3).
例:查出科目3 各同学成绩排名情况

 

 

2.1使用不连续排名rank()

select t.*, rank() over(order by t.score desc) as ranks
from test_student_score t
where t.subject_id = 3;
得到的结果

 

 

2.2使用连续排名dense_rank()

select t.*, dense_rank() over(order by t.score desc) as ranks
from test_student_score t
where t.subject_id = 3;
果如下

详细了解排名函数： https://www.cnblogs.com/thomasbc/p/15672366.html

 排名函数实战篇：https://www.cnblogs.com/thomasbc/p/15573536.html

 

★3.和错行函数(lag,lead)联合使用.lag上几行,lead 下几行

/*语法*/
lag(exp_str,offset,defval) over()
Lead(exp_str,offset,defval) over()
--exp_str要取的列
--offset取偏移后的第几行数据
--defval:没有符合条件的默认值
试数据
select * from test_student_score t where t.subject_id = 3;

 

查上一行和下一行数据

select t.subject_id,
t.subject_id,
lag(t.score, 1, -1) over(order by t.score) as lags,
t.score,
lead(t.score, 1, -1) over(order by t.score) as leads
from test_student_score t
where t.subject_id = 3;

  详细了解错行函数：https://www.cnblogs.com/thomasbc/p/15528166.html

 错行函数实战：https://www.cnblogs.com/thomasbc/p/15527356.html

 

★4.和专用函数中的FIRST_VALUE与LAST_VALUE

 

• FIRST_VALUE         返回组中数据窗口的第一个值
• LAST_VALUE          返回组中数据窗口的最后一个值

• SELECT product_id, product_name, product_type, sale_price,
  FIRST_VALUE(sale_price) OVER ( PARTITION BY product_type ORDER BY sale_price  ) AS current_FV,
  LAST_VALUE(sale_price) OVER ( PARTITION BY product_type ORDER BY sale_price  ) AS current_LV
  FROM Product;

 

 

 

★5. 累计分布函数：   CUME_DIST()、NTH_VALUE()、NTILE()  

 

 

 累计分布函数链接：https://www.cnblogs.com/thomasbc/p/15682082.html