logo标签列表

SQL - 何时使用空的OVER子句？

sqlsql-server-2005sql-server-2008
16

我正在分析一些代码，该代码在使用 Count() 函数时在 OVER 子句中使用了空值。

例如：

SELECT 
        ROW_NUMBER() OVER (ORDER BY Priority DESC) AS RowID,
        CAST((COUNT(*) OVER() / @pagesize) AS Int) AS TotalPages,

我试图理解为什么在这里使用了空的 OVER 子句。

上面列出的两行之下还有其他标准的 select 元素，当我从第二个 TotalPages 行中删除空的 OVER 子句时，就会出现如下错误：

Column 'TableA.Priority' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

一旦我加回了OVER()，错误就没了。

我对OVER子句的理解非常有限...我感觉我理解了RowID行中正在发生的事情...但TotalPages行让我感到困惑。

3个回答
13

OVER() 是分析函数的一部分，用于定义记录集中的分区。 OVER() 只是一个分区，并应用于整个数据集

例如，COUNT(*) OVER() 将返回每行数据集中有多少条记录。

请参阅此链接

3
这个方法可以在select语句中获取总记录数吗？ - Brian Webster
5
说我们的表是employees:
+-----------+-------+---------+
| badge_num | name  | surname |
+-----------+-------+---------+
|         1 | John  | Smith   |
|         2 | Mark  | Pence   |
|         3 | Steve | Smith   |
|         4 | Bob   | Smith   |
+-----------+-------+---------+

运行中

SELECT surname, COUNT(*)
FROM employees
GROUP BY surname;

我们将得到：

+---------+----------+
| surname | COUNT(*) |
+---------+----------+
| Smith   |        3 |
| Pence   |        1 |
+---------+----------+

在运行时

SELECT surname, COUNT(*) OVER()
FROM employees
GROUP BY surname;

我们将得到：

+---------+-----------------+
| surname | COUNT(*) OVER() |
+---------+-----------------+
| Smith   |               2 |
| Pence   |               2 |
+---------+-----------------+

在第二种情况下，每一行中我们只是在计算整个选取的行数（而不是单个分区）。
1
总结一下，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

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接