sql-為什么where子句中沒有窗口函數?

標題說明了一切,為什么我不能在SQL Server的where子句中使用窗口函數?

此查詢非常合理:

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 /子查詢更好的方法?

編輯

對于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子句中使用窗口函數的原因。

asked 2020-02-22T18:28:05Z
8個解決方案
59 votes

為什么不能在SQL Server的where子句中使用窗口函數?

一個答案,盡管不是特別有用,是因為規范說您不能。

請參閱Itzik Ben Gan的文章-邏輯查詢處理:這是什么以及對您意味著什么,尤其是此處的圖像。 在處理了所有WHERE/JOIN/GROUP BY/HAVING子句之后(步驟5.1),在剩余的結果集上,在col1 > 'B'時評估窗口函數。

真的,我在尋找無法使用的原因   窗口函數在where子句中。

col1 > 'B'子句中不允許使用它們的原因是可以领救济金的游戏,這會造成歧義。 使用窗口函數從高性能T-SQL竊取Itzik Ben Gan的示例(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'謂詞在行編號之前或之后運行?

answered 2020-02-22T18:28:51Z
11 votes

無需CTE,只需在子查詢中使用窗口功能即可:

select id, sales_person_id, product_type, product_id, sale_amount
from
(
  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 sale_amount desc) rn
  from Sales_Log
) sl
where rn = 1

編輯,將我的評論移至答案。

WHERE子句之后實際選擇數據之前,不執行窗口功能。 因此,如果您嘗試在WHERE子句中使用row_number,則該值尚未分配。

answered 2020-02-22T18:29:21Z
7 votes

首先,它叫做QUALIFY

“一次性操作”表示所有表達式都在同一位置   邏輯查詢過程階段在邏輯上同時進行評估。

以及一章對窗口功能的影響:

假設您有:

CREATE TABLE #Test ( Id INT) ;
INSERT  INTO #Test VALUES  ( 1001 ), ( 1002 ) ;
SELECT Id
FROM #Test
WHERE Id = 1002
  AND ROW_NUMBER() OVER(ORDER BY Id) = 1;

一次操作告訴我們在同一時間點對這兩個條件進行邏輯評估。 因此,SQL Server可以   根據以下內容以任意順序評估WHERE子句中的條件   估計執行計劃。 所以這里的主要問題是哪種情況   首先評估。

情況1:

QUALIFY

結果:1002

情況2:

QUALIFY

結果:為空

所以我們有一個悖論。

這個例子說明了為什么我們不能在WHERE子句中使用Window Functions。   您可以對此進行更多思考,并找到為什么使用窗口函數   允許僅在SELECT和ORDER BY子句中使用!


附錄

Terradata支持QUALIFY子句:

根據用戶指定的搜索條件過濾先前計算的有序分析函數的結果。

SELECT Id
FROM #Test
WHERE Id = 1002
QUALIFY ROW_NUMBER() OVER(ORDER BY Id) = 1;
answered 2020-02-22T18:30:47Z
3 votes

您不一定需要使用CTE,可以在使用row_number()之后查詢結果集

select row, id, sales_person_id, product_type, product_id, sale_amount
from (
    select
        row_number() over(partition by sales_person_id, 
            product_type, product_id order by sale_amount desc) AS row,
        id, sales_person_id, product_type, product_id, sale_amount
    from Sales_Log 
    ) a
where row = 1
answered 2020-02-22T18:31:07Z
1 votes

是的,不幸的是,當您執行窗口函數時,即使您的謂詞合法,SQL也會對您發火。 您可以在select語句中進行具有值的cte或嵌套選擇,然后稍后再使用該值引用CTE或嵌套選擇。 簡單的例子應該可以自我解釋。 如果您確實對執行大型數據集時的某些性能問題不滿意,請始終將其放在臨時表或表變量中。

declare @Person table ( PersonID int identity, PersonName varchar(8));
insert into @Person values ('Brett'),('John');
declare @Orders table ( OrderID int identity, PersonID int, OrderName varchar(8));
insert into @Orders values (1, 'Hat'),(1,'Shirt'),(1, 'Shoes'),(2,'Shirt'),(2, 'Shoes');
--Select
--  p.PersonName
--, o.OrderName
--, row_number() over(partition by o.PersonID order by o.OrderID)
--from @Person p 
--  join @Orders o on p.PersonID = o.PersonID
--where row_number() over(partition by o.PersonID order by o.orderID) = 2
-- yields:
--Msg 4108, Level 15, State 1, Line 15
--Windowed functions can only appear in the SELECT or ORDER BY clauses.
;
with a as 
    (
    Select
    p.PersonName
,   o.OrderName
,   row_number() over(partition by o.PersonID order by o.OrderID) as rnk
from @Person p 
    join @Orders o on p.PersonID = o.PersonID
    )
select *
from a 
where rnk >= 2 -- only orders after the first one.
answered 2020-02-22T18:31:28Z
1 votes

最后,還有老式的,SQL Server 2005之前的方法,以及相關的子查詢:

select *
from   Sales_Log sl
where  sl.id = (
    Select Top 1 id
    from   Sales_Log sl2
    where  sales_person_id = sl.sales_person_id
       and product_type = sl.product_type
       and product_id = sl.product_id
    order by sale_amount desc
)

出于完整性考慮,我僅向您提供此信息。

answered 2020-02-22T18:31:53Z
1 votes

這是一個老話題,但是我將嘗試專門回答該主題中表達的問題。

為什么where子句中沒有窗口函數?

WHERE語句具有按鍵入順序指定的以下主要子句:

SELECT DISTINCT TOP list
FROM  JOIN ON / APPLY / PIVOT / UNPIVOT
WHERE
GROUP BY  WITH CUBE / WITH ROLLUP
HAVING
ORDER BY
OFFSET-FETCH

邏輯查詢處理順序或綁定順序是概念上的解釋順序,它定義了查詢的正確性。 此順序確定何時將在一步中定義的對象提供給后續步驟中的子句。

----- Relational result
  1. FROM
    1.1. ON JOIN / APPLY / PIVOT / UNPIVOT
  2. WHERE
  3. GROUP BY
    3.1. WITH CUBE / WITH ROLLUP
  4. HAVING
  ---- After the HAVING step the Underlying Query Result is ready
  5. SELECT
    5.1. SELECT list
    5.2. DISTINCT
----- Relational result
----- Non-relational result (a cursor)
  6. ORDER BY
  7. TOP / OFFSET-FETCH
----- Non-relational result (a cursor)

例如,如果查詢處理器可以綁定(訪問)在WHERE子句中定義的表或視圖,則這些對象及其列可用于所有后續步驟。

相反,WHERE子句之前的所有子句都不能引用SELECT子句中定義的任何列別名或派生列。 但是,這些列可以由諸如T-SQL子句之類的后續子句引用。

WHERE子句確定在應用關聯的窗口函數之前對行集的分區和排序。 也就是說,SELECT子句在基礎查詢結果集中定義了窗口或用戶指定的行集,并且窗口函數針對該窗口計算結果。

Msg 4108, Level 15, State 1, …
Windowed functions can only appear in the SELECT or ORDER BY clauses.

背后的原因是因為邏輯查詢處理的工作方式在WHERE中。由于僅在邏輯查詢處理到達SELECT步驟5.1時才建立基礎查詢結果。 (也就是說,在處理了T-SQLWHEREGROUP BYHAVING步驟之后),僅在查詢的SELECTORDER BY子句中允許使用窗口函數。

需要注意的是可以领救济金的游戏,即使關系模型不處理有序數據,窗口函數仍然是關系層的一部分。 WHERE步驟5.1之后的結果。 與任何窗口功能仍然是關系。

同樣,嚴格來說,在WHERE子句中不允許使用窗口函數的原因不是因為它會產生歧義,而是因為邏輯查詢處理處理SELECT中的SELECT語句的順序。

鏈接:這里,這里和這里

answered 2020-02-22T18:32:59Z
1 votes

基本上,第一個“ WHERE”子句條件是通過sql讀取的,并且向表中查找了相同的列/值id,但在表中row_num = 1仍然不存在。 因此,它將無法正常工作。因此,我們將首先使用括號,然后再編寫WHERE子句。

answered 2020-02-22T18:33:20Z
translate from