sql - 窗口函数只能出现在 SELECT 或 ORDER BY 子句中

Question

谁能解释为什么我们不能在 group by 中使用窗口函数条款以及为什么它只允许在 SELECT 中使用和 ORDER BY
我试图根据 row_number() 对记录进行分组和 SQL Server 中的一列，如下所示:

SELECT Invoice
from table1
group by row_number() over(order by Invoice),Invoice

我收到一个错误

Windowed functions can only appear in the SELECT or ORDER BY

我可以选择这个 row_number()在 SELECT 子句中，但我想知道为什么我们不能使用它 group by？

Answer 1

ANSI 规范中定义的窗口函数在处理 GROUP BY 后逻辑执行, HAVING , WHERE .

更具体地说，它们在 Logical Query Processing flow chart here 中的步骤 5.1 和 6 中是允许的。 .

我想他们可以用另一种方式定义它并允许 GROUP BY , WHERE , HAVING使用窗口函数，窗口是该阶段开始时的逻辑结果集，但假设它们具有并且我们被允许构造查询，例如

SELECT a, 
       b, 
       NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForSelect
  FROM YourTable
  WHERE NTILE(2) OVER (PARTITION BY a ORDER BY b) > 1
  GROUP BY a, 
           b, 
           NTILE(2) OVER (PARTITION BY a ORDER BY b)
  HAVING NTILE(2) OVER (PARTITION BY a ORDER BY b) = 1

有四个不同的逻辑窗口，祝你好运，计算出这会是什么结果!如果在 HAVING 中又会怎样您实际上想通过 GROUP BY 中的表达式进行过滤高于水平而不是行窗口是 GROUP BY 之后的结果?

CTE 版本更冗长，但也更明确且更易于遵循。

WITH T1 AS
(
SELECT a, 
       b, 
       NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForWhere
  FROM YourTable
), T2 AS
(
SELECT a,
       b,
       NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForGroupBy
FROM T1
WHERE NtileForWhere > 1
), T3 AS
(
SELECT a,
       b,
       NtileForGroupBy,
       NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForHaving
FROM T2
GROUP BY a,b, NtileForGroupBy
)
SELECT a,
       b,
       NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForSelect
FROM T3
WHERE NtileForHaving = 1

因为这些都在 SELECT 中定义语句和别名很容易消除不同级别的结果歧义，例如只需切换 WHERE NtileForHaving = 1至 NtileForGroupBy = 1