1、基础用法，sql server over()可以是为空，那样就是把整个表当成一个分区
over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
over（partition by deptno）按照部门分区
over（partition by deptno order by salary）按照部门分区，在一个分区内按照salary排序
假设有表格
| NUM | ODD |
|---|---|
| 0 | 0 |
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
SELECT NUM,SUM(NUM) OVER(partition by ODD) s from odd_nums
输出结果：ODD为0代表偶数，1为奇数，根据ODD分组后对NUM求和，输出每个NUM对应组的和，比如0是偶数，偶数组的和为2
| NUM | s |
|---|---|
| 0 | 2 |
| 2 | 2 |
| 1 | 4 |
| 3 | 4 |
2、前后移动
over（order by salary range between 2 preceding and 2 following）
窗口范围为当前行数据幅度减2加2后的范围内的
sum(s)over(order by s range between 2 preceding and 2 following) 表示加2或2的范围内的求和
sum(s)over(order by s rows between 2 preceding and 2 following) 表示在上下两行之间的范围内
3、分区后排序
cume_dist() over()
percent_rank() over()
#仅oracle支持，返回相对排名
比如3行里排第1，2前一个为0.3333，0.666后一个为0，0.5
rank() over(partition by class order by score desc
按照 class 分区，在一个分区内按照score排序
rank()和dense_rank()区别：
--rank()是跳跃排序，有两个第二名时接下来就是第四名
--dense_rank()l是连续排序，有两个第二名时仍然跟着第三名
4、编号：row_number(),为分区中的各数据行指定唯一数字
5、找出每个分区的第一条记录类型和最后一条记录类型以及忽略空值
first_value()over()和last_value()over() 、ignore nulls
first_value(route_name ignore nulls) over(ORDER BY opr_id)
--取出该电路的第一条记录，加上ignore nulls后，如果第一条是判断的那个字段是空的，则默认取下一条，这里好像最好加
last_value(res_type) over(PARTITION BY opr_id ORDER BY res_type rows
BETWEEN unbounded preceding AND unbounded following)
6、取前n/后n行数据，lag() over()函数和lead() over()函数用法
lag(age,1) over (order by id desc) --lag,lag函数是按id倒序排序把上一行的age记录显示在当前行的列lag,为null显示null
lead(age,1) over (order by id desc) --lag, lead表示按id倒序排序把下一行的age记录显示在当前行的列lag
7、求数据在分组类的百分数排名
--ratio_to_report(a)函数用法 Ratio_to_report() 括号中就是分子，over() 括号中就是分母
select a, ratio_to_report(a)over(partition by a) b from a
select a, ratio_to_report(a)over() b from a --分母缺省就是整个占比
网友评论