1.over函数的写法:
over(partition by class order by sroce)
按照sroce排序进行累计,order by是个默认的开窗函数,按照class分区。
2、开窗的窗口范围:
over(order by sroce range between 5 preceding and 5 following)
窗口范围为当前行数据幅度减5加5后的范围内的。
over(order by sroce rows between 5 preceding and 5 following)
窗口范围为当前行前后各移动5行。
sum() over(partition by … order by …):求分组后的总和。
first_value() over(partition by … order by …):求分组后的第一个。
last_value() over(partition by … order by …):求分组后的最后一个。
count() over(partition by … order by …):求分组后的总数。
max() over(partition by … order by …):求分组后的最大值。
min() over(partition by … order by …):求分组后的最小值。
avg() over(partition by … order by …):求分组后的平均值。
lag() over(partition by … order by …):取出前n行数据。
lead() over(partition by … order by …):取出后n行数据。
3.over partition by与group by 的区别
原表:
NAME DEPT SALARY
A 10 1000
B 10 2000
C 20 1500
D 20 3000
E 10 1000
用over partition by 我就可以查询到每位员工本来的具体信息和它所在部门的总工资:
select name,dept,salary,sum(salary) over (partition by dept)
total_salary from salary;
name dept salary tatal_salary
A 10 1000 4000
B 10 2000 4000
E 10 1000 4000
C 20 1500 4500
D 20 3000 4500
用goup by 就没办法做到这点,只能查询到每个部门的总工资:
select dept,sum(salary) total_salary from salary group by dept
dept total_salary
10 4000
20 4500
over partition by会把每个数据的明细都显现出来,聚合显示多条
group by 聚合只会显示一条