oracle over（）函数使用

最新推荐文章于 2023-08-30 18:37:14 发布

原创最新推荐文章于 2023-08-30 18:37:14 发布 · 3.1k 阅读

14 ·

CC 4.0 BY-SA版权

文章标签：

#oracle函数 #sql

数据库专栏收录该内容

8 篇文章

订阅专栏

本文深入探讨了SQL中开窗函数的应用，包括over(partition by)、over(order by)、row_number()、rank()和dense_rank()等函数的使用场景与区别。通过具体示例，解析了如何利用这些函数进行数据分区、排序、累计求和及排名计算。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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）：窗口不做限制