sqlserver,OVER（PARTITIONBY）函数⽤法，开窗函数,over
⼦句,o。。。
OVER(PARTITION BY)函数介绍
开窗函数
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）：窗⼝不做限制
3、与over函数结合的⼏个函数介绍
row_number()over()、rank()over()和dense_rank()over()函数的使⽤
下⾯以班级成绩表t2来说明其应⽤
t2表信息如下：
cfe 2 74
dss 1 95
ffd 1 95rank函数的用法
fda 1 80
gds 2 92
gf 3 99
ddd 3 99
adf 3 45
asdf 3 55
3dd 3 78
select * from
(
select name,class,s,rank()over(partition by class order by s desc) mm from t2
)
where mm=1；
得到的结果是:
dss 1 95 1
ffd 1 95 1
gds 2 92 1
gf 3 99 1
ddd 3 99 1
注意：
1.在求第⼀名成绩的时候，不能⽤row_number()，因为如果同班有两个并列第⼀，row_number()只返回⼀个结果; select * from
(
select name,class,s,row_number()over(partition by class order by s desc) mm from t2
)
where mm=1；
1 95 1 --95有两名但是只显⽰⼀个
2 92 1
3 99 1 --99有两名但也只显⽰⼀个
2.rank()和dense_rank()可以将所有的都查出来：
如上可以看到采⽤rank可以将并列第⼀名的都查出来；
rank()和dense_rank()区别：
--rank()是跳跃排序，有两个第⼆名时接下来就是第四名；
select name,class,s,rank()over(partition by class order by s desc) mm from t2
dss 1 95 1
ffd 1 95 1
fda 1 80 3 --直接就跳到了第三
gds 2 92 1
cfe 2 74 2
gf 3 99 1
ddd 3 99 1
3dd 3 78 3
asdf 3 55 4
adf 3 45 5
--dense_rank()l是连续排序，有两个第⼆名时仍然跟着第三名
select name,class,s,dense_rank()over(partition by class order by s desc) mm from t2
select name,class,s,dense_rank()over(partition by class order by s desc) mm from t2
dss 1 95 1
ffd 1 95 1
fda 1 80 2 --连续排序（仍为2）
gds 2 92 1
cfe 2 74 2
gf 3 99 1
ddd 3 99 1
3dd 3 78 2
asdf 3 55 3
adf 3 45 4
--sum()over（）的使⽤
select name,class,s, sum(s)over(partition by class order by s desc) mm from t2 --根据班级进⾏分数求和
dss 1 95 190 --由于两个95都是第⼀名，所以累加时是两个第⼀名的相加
ffd 1 95 190
fda 1 80 270 --第⼀名加上第⼆名的
gds 2 92 92
cfe 2 74 166
gf 3 99 198
ddd 3 99 198
3dd 3 78 276
asdf 3 55 331
adf 3 45 376
first_value() over()和last_value() over()的使⽤
--出这三条电路每条电路的第⼀条记录类型和最后⼀条记录类型
SELECT opr_id,res_type,
first_value(res_type) over(PARTITION BY opr_id ORDER BY res_type) low,
last_value(res_type) over(PARTITION BY opr_id ORDER BY res_type rows BETWEEN unbounded preceding AND unbounded following) high
FROM rm_circuit_route
WHERE opr_id IN ('000100190000000000021311','000100190000000000021355','000100190000000000021339') ORDER BY opr_id;
注：rows BETWEEN unbounded preceding AND unbounded following 的使⽤
--取last_value时不使⽤rows BETWEEN unbounded preceding AND unbounded following的结果
SELECT opr_id,res_type,
first_value(res_type) over(PARTITION BY opr_id ORDER BY res_type) low,
last_value(res_type) over(PARTITION BY opr_id ORDER BY res_type) high
FROM rm_circuit_route
WHERE opr_id IN ('000100190000000000021311','000100190000000000021355','000100190000000000021339') ORDER BY opr_id;
如下图可以看到，如果不使⽤
rows BETWEEN unbounded preceding AND unbounded following，取出的last_value由于与res_type进⾏进⾏排列，因此取出的电路的最后⼀⾏记录的类型就不是按照电路的范围提取了，⽽是以res_type为范围进⾏提取了。
在first_value和last_value中ignore nulls的使⽤
数据如下：
取出该电路的第⼀条记录，加上ignore nulls后，如果第⼀条是判断的那个字段是空的，则默认取下⼀条，结果如下所⽰：
--lag() over()函数⽤法（取出前n⾏数据）
lag(expresstion,<offset>,<default>)
with a as
(select 1 id,'a' name from dual
union
select 2 id,'b' name from dual
union
select 3 id,'c' name from dual
union
select 4 id,'d' name from dual
union
select 5 id,'e' name from dual
)
select id,name,lag(id,1,'')over(order by name) from a;
--lead() over()函数⽤法（取出后N⾏数据）
lead(expresstion,<offset>,<default>)
with a as
(select 1 id,'a' name from dual
union
select 2 id,'b' name from dual
union
select 3 id,'c' name from dual
union
select 4 id,'d' name from dual
union
select 5 id,'e' name from dual
)
select id,name,lead(id,1,'')over(order by name) from a;
--ratio_to_report(a)函数⽤法 Ratio_to_report() 括号中就是分⼦，over() 括号中就是分母 with a as (select 1 a from dual
union all
select 1 a from dual
union all
select 1 a from dual
union all
select 2 a from dual
union all
select 3 a from dual
union all
select 4 a from dual
union all
select 4 a from dual
union all
select 5 a from dual
)
select a, ratio_to_report(a)over(partition by a) b from a
order by a;
with a as (select 1 a from dual
union all
select 1 a from dual
union all
select 1 a from dual
union all
select 2 a from dual
union all
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论