总结一下,OVER子句可以与排名函数(Rank、Row_Number、Dense_Rank等)、聚合函数(AVG、Max、Min、SUM等)和分析函数(First_Value、Last_Value等)一起使用。
让我们看一下OVER子句的基本语法。
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
PARTITION BY:
这用于将数据进行分区,并对具有相同数据的组执行操作。
ORDER BY:
它用于定义分区中数据的逻辑顺序。当我们没有指定分区时,整个结果集被视为单个分区。
:
在执行操作时,可以使用此选项指定应考虑哪些行作为分区。
让我们举个例子:
这是我的数据集:
Id Name Gender Salary
----------- -------------------------------------------------- ---------- -----------
1 Mark Male 5000
2 John Male 4500
3 Pavan Male 5000
4 Pam Female 5500
5 Sara Female 4000
6 Aradhya Female 3500
7 Tom Male 5500
8 Mary Female 5000
9 Ben Male 6500
10 Jodi Female 7000
11 Tom Male 5500
12 Ron Male 5000
所以让我执行不同的场景,看看数据受到了什么影响,我会从复杂的语法转向简单的语法。
Select *,SUM(salary) Over(order by salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sum_sal from employees
Id Name Gender Salary sum_sal
----------- -------------------------------------------------- ---------- ----------- -----------
6 Aradhya Female 3500 3500
5 Sara Female 4000 7500
2 John Male 4500 12000
3 Pavan Male 5000 32000
1 Mark Male 5000 32000
8 Mary Female 5000 32000
12 Ron Male 5000 32000
11 Tom Male 5500 48500
7 Tom Male 5500 48500
4 Pam Female 5500 48500
9 Ben Male 6500 55000
10 Jodi Female 7000 62000
只观察sum_sal部分。我在使用order by Salary,并使用"RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"。
在这种情况下,我们不使用分区,因此整个数据将被视为一个分区,我们按薪水排序。
而且这里重要的是UNBOUNDED PRECEDING AND CURRENT ROW。这意味着当我们计算总和时,对于每一行,从起始行到当前行。
但是,如果我们看到工资为5000且名称为“Pavan”的行,则应该是17000,如果工资为5000且姓名为Mark,则应该是22000。但是,由于我们使用了RANGE,在这种情况下,如果它找到任何相似的元素,则将它们视为同一逻辑组并对它们执行操作,并为该组中的每个项分配值。这就是为什么我们在salary=5000的地方有相同的值。引擎达到薪水= 5000和Name = Ron,并计算总和,然后将其分配给所有salary = 5000的人的原因。
Select *,SUM(salary) Over(order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sum_sal from employees
Id Name Gender Salary sum_sal
----------- -------------------------------------------------- ---------- ----------- -----------
6 Aradhya Female 3500 3500
5 Sara Female 4000 7500
2 John Male 4500 12000
3 Pavan Male 5000 17000
1 Mark Male 5000 22000
8 Mary Female 5000 27000
12 Ron Male 5000 32000
11 Tom Male 5500 37500
7 Tom Male 5500 43000
4 Pam Female 5500 48500
9 Ben Male 6500 55000
10 Jodi Female 7000 62000
因此,使用ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW的区别在于,对于相同值的项,它不会将它们分组在一起,而是从起始行到当前行计算总和,并且不像RANGE一样特别处理具有相同值的项。
Select *,SUM(salary) Over(order by salary) as sum_sal from employees
Id Name Gender Salary sum_sal
----------- -------------------------------------------------- ---------- ----------- -----------
6 Aradhya Female 3500 3500
5 Sara Female 4000 7500
2 John Male 4500 12000
3 Pavan Male 5000 32000
1 Mark Male 5000 32000
8 Mary Female 5000 32000
12 Ron Male 5000 32000
11 Tom Male 5500 48500
7 Tom Male 5500 48500
4 Pam Female 5500 48500
9 Ben Male 6500 55000
10 Jodi Female 7000 62000
这些结果与之前相同
Select *, SUM(salary) Over(order by salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sum_sal from employees
这是因为 Over(order by salary) 只是 Over(order by salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 的一个简化版本。
因此,无论我们在哪里仅指定 Order by 而不带 ROWS or RANGE,它都会以 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 作为默认值。
注意:这只适用于实际接受 RANGE/ROW 函数。例如,ROW_NUMBER 和其他一些函数不接受 RANGE/ROW,在这种情况下,这个不会影响。
迄今为止,我们看到带有 order by 的 Over 子句正在使用 Range/ROWS,并且语法类似于 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
实际上,它从第一行开始计算到当前行。但如果它想要计算整个数据分区的值,并对每个列进行计算(即从第一行到最后一行),则可以使用以下查询:
Select *,sum(salary) Over(order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as sum_sal from employees
Id Name Gender Salary sum_sal
----------- -------------------------------------------------- ---------- ----------- -----------
1 Mark Male 5000 62000
2 John Male 4500 62000
3 Pavan Male 5000 62000
4 Pam Female 5500 62000
5 Sara Female 4000 62000
6 Aradhya Female 3500 62000
7 Tom Male 5500 62000
8 Mary Female 5000 62000
9 Ben Male 6500 62000
10 Jodi Female 7000 62000
11 Tom Male 5500 62000
12 Ron Male 5000 62000
我不是使用CURRENT ROW,而是指定UNBOUNDED FOLLOWING,这会指示引擎计算每行分区的最后一条记录。
现在来谈谈你提到的带空括号的OVER()是什么意思?
它只是一个快捷方式,等同于Over(order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
在这里,我们间接地指定将所有结果集视为单个分区,然后从每个分区的第一条记录到最后一条记录执行计算。
Select *,Sum(salary) Over() as sum_sal from employees
Id Name Gender Salary sum_sal
----------- -------------------------------------------------- ---------- ----------- -----------
1 Mark Male 5000 62000
2 John Male 4500 62000
3 Pavan Male 5000 62000
4 Pam Female 5500 62000
5 Sara Female 4000 62000
6 Aradhya Female 3500 62000
7 Tom Male 5500 62000
8 Mary Female 5000 62000
9 Ben Male 6500 62000
10 Jodi Female 7000 62000
11 Tom Male 5500 62000
12 Ron Male 5000 62000
我已经制作了一个视频,如果您感兴趣,可以访问它。
https://www.youtube.com/watch?v=CvVenuVUqto&t=1177s
谢谢,
Pavan Kumar Aryasomayajulu
HTTP://xyzcoder.github.io