开窗函数 over ( partition by)以及和group by 的区别 |
您所在的位置:网站首页 › 1050gaming和1058有区别吗 › 开窗函数 over ( partition by)以及和group by 的区别 |
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 4500over partition by会把每个数据的明细都显现出来,聚合显示多条 group by 聚合只会显示一条 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |