Oracle中窗口函数的使用

1.概述

    语法：

Function (arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>][<window_expression>])

    Function (arg1,..., argn) 可以是下面的函数：

Aggregate Functions: 常用聚合函数,比如：count(*)、sum(...)、min(...)、avg(...) 等

Sort Functions: 数据排序函数, 比如 ：dense_rank()、rank(...)、row_number(...)等

Analytics Functions: 统计和比较函数, 比如：lead(...)、lag(...)、 first_value(...)等

     具体还要结合实际情况使用。

2.窗口函数应用(举栗子)

    今天接触到一个sql题目，也是对ROW_NUMBER() OVER PARTITION BY 的使用，下面实战一下吧！

 题目：统计各个班级中学生各个科目总成绩排名第一的情况

  建表

CREATE TABLE tmp_20191101( --创建一个学生表 CLASS INTEGER, --学生id SNAME VARCHAR2(20), --学生姓名 SSEX VARCHAR2(10), --学生性别 SUBJECT VARCHAR2(20), --科目名称 SCORE INTEGER --对应科目成绩 );

 

  插入数据

INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(10,'小朱','男','英语',97); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(11,'小欣','女','英语',91); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(12,'小王','男','英语',80); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(12,'小朱','男','语文',90); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(12,'小王','男','数学',76); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(10,'小陶','男','数学',89); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(10,'小薛','男','语文',94); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(11,'小王','女','语文',93); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(11,'小朱','女','数学',61); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(13,'小苏','女','语文',99); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(13,'小龚','女','数学',92); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(13,'小刘','女','英语',81); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(10,'小李','男','英语',90); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(11,'小欣','女','英语',96); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(12,'小张','男','英语',88); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(12,'小张','男','语文',99); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(12,'小张','男','数学',73); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(10,'小李','男','数学',85); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(10,'小李','男','语文',93); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(11,'小欣','女','语文',95); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(11,'小欣','女','数学',63); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(13,'小萌','女','语文',90); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(13,'小萌','女','数学',92); INSERT INTO tmp_20191101(CLASS,SNAME,SSEX,SUBJECT,SCORE)VALUES(13,'小萌','女','英语',86);

 

tmp_20191101 全部数据截图：

3. 执行sql 

select * from tmp_20191101 select * from( select t.sname, t.class, t.SumScore, ROW_NUMBER() OVER (PARTITION BY t.class order by t.SumScore desc) rn --根据class分区，sumscore排序 from ( select sname,class,sum(score) as SumScore --每班同学的各自总分 from tmp_20191101 group by class,sname) t) RN where RN.rn <= 1

执行sql 结果截图：

４.思考

 如果有并列的情况，怎么看处理呢？  ROW_NUMBER()是不考虑并列的，你看

相同总分，排名没有并列，那该怎么办呢？Oracle中提供了Rank()可以用 ，如图

如果  想要名次之间没有“间隔”， 可以使用 dense_rank()函数，例如: