Perché nessuna funzione con finestra in dove clausole?

Il titolo dice tutto, perché non posso usare una funzione con finestra in una clausola where in SQL Server?

Questa query ha perfettamente senso:

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) 

Ma non funziona. C’è un modo migliore di un CTE / sottoquery?

MODIFICARE

Per quello che vale è la query con un 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 

MODIFICARE

+1 per le risposte che mostrano con una sottoquery, ma in realtà sto cercando il ragionamento che sta dietro il non poter usare le funzioni di windowing in dove clausole.

    perché non posso usare una funzione con finestra in una clausola where in SQL Server?

    Una risposta, sebbene non particolarmente informativa, è perché le specifiche dicono che non è ansible.

    Vedi l’articolo di Itzik Ben Gan – Elaborazione di query logiche: cosa è e cosa significa per te e in particolare l’immagine qui . Le funzioni della finestra vengono valutate al momento della SELECT sul set di risultati rimanente dopo che tutte le clausole WHERE / JOIN / GROUP BY / HAVING sono state trattate (passo 5.1).

    davvero sto cercando il ragionamento che sta dietro il non poter usare le funzioni di windowing in dove clausole.

    Il motivo per cui non sono ammessi nella clausola WHERE è che creerebbe ambiguità. Rubare l’esempio di Itzik Ben Gan da T-SQL ad alte prestazioni utilizzando le funzioni di finestra (p.25)

    Supponiamo che il tuo tavolo sia stato

     CREATE TABLE T1 ( col1 CHAR(1) PRIMARY KEY ) INSERT INTO T1 VALUES('A'),('B'),('C'),('D'),('E'),('F') 

    E la tua domanda

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

    Quale sarebbe il risultato giusto? Ti aspetti che il col1 > 'B' eseguito prima o dopo la numerazione delle righe?

    Non c’è bisogno di CTE, basta usare la funzione di windowing in una sottoquery:

     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 

    Modifica, spostando il mio commento alla risposta.

    Le funzioni di generazione non vengono eseguite fino a quando i dati non vengono effettivamente selezionati, ovvero dopo la clausola WHERE . Quindi, se si tenta di utilizzare un row_number in una clausola WHERE , il valore non è ancora stato assegnato.

    Prima di tutto si tratta di all-at-once operation chiamata all-at-once operation

    “Operazioni tutte insieme” significa che tutte le espressioni nella stessa fase del processo di query logica vengono valutate logicamente allo stesso tempo.

    E grande capitolo Impatto sulle funzioni della finestra :

    Supponiamo di avere:

     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; 

    Le operazioni “tutto in una volta” ci dicono che queste due condizioni sono valutate logicamente nello stesso momento. Pertanto, SQL Server può valutare le condizioni nella clausola WHERE in ordine arbitrario, in base al piano di esecuzione stimato. Quindi la domanda principale qui è quale condizione valuta per prima.

    Caso 1:

    If ( Id = 1002 ) is first, then if ( ROW_NUMBER() OVER(ORDER BY Id) = 1 )

    Risultato: 1002

    Caso 2:

    If ( ROW_NUMBER() OVER(ORDER BY Id) = 1 ), then check if ( Id = 1002 )

    Risultato: vuoto

    Quindi abbiamo un paradosso.

    Questo esempio mostra perché non possiamo usare le funzioni di finestra nella clausola WHERE. Puoi pensare di più a questo e scoprire perché le funzioni della finestra possono essere utilizzate solo nelle clausole SELECT e ORDER BY !

    Non è necessario utilizzare un CTE, è ansible eseguire una query sul set di risultati dopo aver utilizzato 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 

    Sì, sfortunatamente quando si esegue una funzione a windows SQL si arrabbia con te anche se il tuo predicato è legittimo. Fai un cte o nested seleziona il valore nella tua select statement, quindi fai riferimento al tuo CTE o seleziona nested con quel valore in seguito. Semplice esempio che dovrebbe essere auto esplicativo. Se davvero si odiano cte per alcuni problemi di prestazioni durante l’esecuzione di un set di dati di grandi dimensioni, è sempre ansible passare alla tabella temporanea o alla variabile di tabella.

     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. 

    Infine, c’è il vecchio modo pre-SQL Server 2005, con una sottoquery correlata:

     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 ) 

    Ti do questo per completezza, semplicemente.

    È un thread vecchio, ma proverò a rispondere in modo specifico alla domanda espressa nell’argomento.

    Perché nessuna funzione con finestra in dove clausole?

    SELECT istruzione SELECT ha le seguenti clausole principali specificate nell’ordine digitato :

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

    Ordine di elaborazione della query logica o Ordine vincolante è l’ordine di interpretazione concettuale , definisce la correttezza della query. Questo ordine determina quando gli oggetti definiti in una fase vengono resi disponibili alle clausole nei passaggi successivi.

     ----- 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) 

    Ad esempio, se il processore di query può associare (accedere) alle tabelle o alle viste definite nella clausola FROM , questi oggetti e le relative colonne vengono resi disponibili per tutti i passaggi successivi.

    Viceversa, tutte le clausole che precedono la clausola SELECT non possono fare riferimento a alias di colonne o colonne derivate definite nella clausola SELECT . Tuttavia, tali colonne possono essere referenziate da clausole successive come la clausola ORDER BY .

    OVER clausola OVER determina il partizionamento e l’ordinamento di una serie di righe prima che venga applicata la funzione della finestra associata. Cioè, la clausola OVER definisce una serie o una serie di righe specificate dall’utente all’interno di un set di risultati della query sottostante e il risultato della funzione della finestra risulta rispetto a tale finestra.

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

    La ragione di ciò è dovuta al modo in cui il Logical Query Processing funziona in T-SQL . Poiché il risultato della query sottostante viene stabilito solo quando l’elaborazione della query logica raggiunge il passo SELECT 5.1. (ovvero, dopo aver elaborato i passaggi FROM , WHERE , GROUP BY e HAVING ), le funzioni della finestra sono consentite solo nelle clausole SELECT e ORDER BY della query.

    Nota per citare, le funzioni delle windows fanno ancora parte del livello relazionale, anche il modello relazionale non tratta i dati ordinati. Il risultato dopo il passo SELECT 5.1. con qualsiasi funzione finestra è ancora relazionale.

    Inoltre, parlando strettamente, il motivo per cui la funzione window non è consentita nella clausola WHERE non è perché creerebbe ambiguità, ma perché l’ordine su come Logical Query Processing elabora l’istruzione SELECT in T-SQL .

    Collegamenti: qui , qui e qui