为什么 where 子句中没有窗口函数? [英] Why no windowed functions in where clauses?

问题描述

标题说明了一切，为什么我不能在 SQL Server 的 where 子句中使用窗口函数?

Title says it all, why can't I use a windowed function in a where clause in SQL Server?

这个查询很有意义:

select id, sales_person_id, product_type, product_id, sale_amount
from Sales_Log
where 1 = row_number() over(partition by sales_person_id, product_type, product_id order by sale_amount desc)

但它不起作用.有没有比 CTE/子查询更好的方法?

But it doesn't work. Is there a better way than a CTE/Subquery?

编辑

就其价值而言，这是带有 CTE 的查询:

For what its worth this is the query with a CTE:

with Best_Sales as (
    select id, sales_person_id, product_type, product_id, sale_amount, row_number() over (partition by sales_person_id, product_type, product_id order by sales_amount desc) rank
    from Sales_log
)
select id, sales_person_id, product_type, product_id, sale_amount
from Best_Sales
where rank = 1

编辑

+1 用于显示子查询的答案，但实际上我正在寻找无法在 where 子句中使用窗口函数的原因.

+1 for the answers showing with a subquery, but really I'm looking for the reasoning behind not being able to use windowing functions in where clauses.

推荐答案

为什么我不能在 SQL Server 的 where 子句中使用窗口函数?

why can't I use a windowed function in a where clause in SQL Server?

一个答案，虽然不是特别有用，是因为规范说你不能.

One answer, though not particularly informative, is because the spec says that you can't.

请参阅 Itzik Ben Gan 的文章 - 逻辑查询处理:它是什么以及它对你意味着什么，特别是 这里的图片.窗口函数在 SELECT 时对所有 WHERE/JOIN/GROUP BY/HAVING 子句已处理(步骤 5.1).

See the article by Itzik Ben Gan - Logical Query Processing: What It Is And What It Means to You and in particular the image here. Window functions are evaluated at the time of the SELECT on the result set remaining after all the WHERE/JOIN/GROUP BY/HAVING clauses have been dealt with (step 5.1).

我真的在寻找无法使用的原因where 子句中的窗口函数.

really I'm looking for the reasoning behind not being able to use windowing functions in where clauses.

在 WHERE 子句中不允许使用它们的原因是它会造成歧义.从 使用窗口函数的高性能 T-SQL 中窃取 Itzik Ben Gan 的示例(第 25 页)

The reason that they are not allowed in the WHERE clause is that it would create ambiguity. Stealing Itzik Ben Gan's example from High-Performance T-SQL Using Window Functions (p.25)

假设你的桌子是

CREATE TABLE T1
(
col1 CHAR(1) PRIMARY KEY
)

INSERT INTO T1 VALUES('A'),('B'),('C'),('D'),('E'),('F')

以及您的查询

SELECT col1
FROM T1
WHERE ROW_NUMBER() OVER (ORDER BY col1) <= 3
AND col1 > 'B'

正确的结果是什么?您是否期望 col1 >'B' 谓词在行编号之前还是之后运行?

What would be the right result? Would you expect that the col1 > 'B' predicate ran before or after the row numbering?

这篇关于为什么 where 子句中没有窗口函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！