oracle over（） 函数使用

1.   over(partition by t.class) 这里是通过class分区，分别统计每个班的总分，并且每条数据都会呈现

SELECT t.*, sum(t.SCORE) over(partition by t.class)  cnt   from T_SCORE t ORDER BY t.ID

2.   over(partition by t.class order by  id) 这里是通过班级分区，按id的顺序分别统计每个班的总分（累计加和），并且每条数据都会呈现

SELECT t.*, sum(t.SCORE) over(partition by t.class ORDER BY t.id)  cnt from T_SCORE t ORDER BY t.ID

3.   row_number()over()、rank()over()和dense_rank()over()函数的使用

1）rank（）over（）是跳跃性排名

SELECT * from (select t.name,t.class,t.course ,t.score, rank() over(partition by t.course,t.class order by t.score desc) mm from t_score t) where mm=1 order by class,course

这段sql是取每个班级每门学科的第一名

 

SELECT * from (select t.name,t.class,t.course ,t.score, rank() over(partition by t.course order by t.score desc) mm from t_score t)  order by course

这段sql是取每门学科的排名，可以看到有数学有两个第一名

2）dense_rank()over() 是连续排名

SELECT * from (select t.name,t.class,t.course ,t.score, DENSE_RANK() over(partition by t.course order by t.score desc) mm from t_score t)  order by course

3）row_number()over()   在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果;

SELECT * from (select t.name,t.class,t.course ,t.score, ROW_NUMBER() over(partition by t.course order by t.score desc) mm from t_score t)  order by course

4. 开窗函数的范围

开窗函数          
     Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

      开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
1：over后的写法：    
   over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
   over（partition by deptno）按照部门分区

 

   over（partition by deptno order by salary）

 

2：开窗的窗口范围：
over（order by salary range between 5 preceding and 5 following）：窗口范围为当前行数据幅度减5加5后的范围内的。

举例：

 

--sum(s)over(order by s range between 2 preceding and 2 following) 表示加2或2的范围内的求和

 select name,class,s, sum(s)over(order by s range between 2 preceding and 2 following) mm from t2
adf        3        45        45  --45加2减2即43到47，但是s在这个范围内只有45
asdf       3        55        55
cfe        2        74        74
3dd        3        78        158 --78在76到80范围内有78，80，求和得158
fda        1        80        158
gds        2        92        92
ffd        1        95        190
dss        1        95        190
ddd        3        99        198

gf         3        99        198

 

 

 

over（order by salary rows between 5 preceding and 5 following）：窗口范围为当前行前后各移动5行。

举例：

 

--sum(s)over(order by s rows between 2 preceding and 2 following)表示在上下两行之间的范围内
select name,class,s, sum(s)over(order by s rows between 2 preceding and 2 following) mm from t2
adf        3        45        174  （45+55+74=174）
asdf       3        55        252   （45+55+74+78=252）
cfe        2        74        332    （74+55+45+78+80=332）
3dd        3        78        379    （78+74+55+80+92=379）
fda        1        80        419
gds        2        92        440
ffd        1        95        461
dss        1        95        480
ddd        3        99        388
gf         3        99        293

 

over（order by salary range between unbounded preceding and unbounded following）或者

over（order by salary rows between unbounded preceding and unbounded following）：窗口不做限制