¿Por qué no hay funciones de ventana en las cláusulas where?

No hay necesidad de CTE, solo use la función de ventana en una subconsulta:

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

Editar, moviendo mi comentario a la respuesta.

Las funciones de ventana no se realizan hasta que los datos se seleccionan realmente, que es después de la cláusula WHERE. Así que si intentas usar una row_number en una cláusula WHERE el valor aún no está asignado.

En primer lugar, algo llamado all-at-once operation

"Operaciones de una sola vez" significa que todas las expresiones en el mismo la fase del proceso de consulta lógica se evalúa lógicamente al mismo tiempo.

Y gran capítulo Impacto en las Funciones de Ventana :

Supongamos que usted tiene:

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;

Las operaciones All-at-Once nos dicen estas dos condiciones evaluadas lógicamente en el mismo punto del tiempo. Por lo tanto, SQL Server puede evaluar condiciones en DONDE cláusula en orden arbitrario, basado en plan de ejecución estimado. Así que la pregunta principal aquí es qué condición evalúa primero.

Caso 1:

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

Resultado: 1002

Caso 2:

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

Resultado: vacío

Así que tenemos una paradoja.

Este ejemplo muestra por qué no podemos usar Funciones de Ventana en la cláusula WHERE. Puede pensar más sobre esto y encontrar por qué las funciones de Ventana ser se permite su uso solo en SELECCIONE y ORDEN POR cláusulas!

No necesariamente necesita usar un CTE, puede consultar el conjunto de resultados después de usar 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í desafortunadamente cuando haces una función de ventana SQL se enoja contigo incluso si tu predicado where es legítimo. Cree un cte o una selección anidada que tenga el valor en su instrucción select y, a continuación, haga referencia a su CTE o selección anidada con ese valor más adelante. Ejemplo simple que debe explicarse por sí mismo. Si realmente ODIA los cte por algún problema de rendimiento al hacer un conjunto de datos grande, siempre puede caer a la tabla temporal o a la variable de la tabla.

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.

Finalmente, está la antigua forma pre-SQL Server 2005, con una subconsulta correlacionada:

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
)

Les doy esto por completo, simplemente.

Es un hilo viejo, pero voy a tratar de responder específicamente a la pregunta expresada en el tema.

¿Por qué no hay funciones de ventana en las cláusulas where?

SELECT la declaración tiene las siguientes cláusulas principales especificadas en keyed-in order :

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

El Orden Lógico de Procesamiento de Consultas , u Orden Vinculante, es el orden conceptual de interpretación, define la corrección de la consulta. Este orden determina cuándo los objetos definidos en uno paso se ponen a disposición de las cláusulas en pasos posteriores.

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

Por ejemplo, si el procesador de consultas puede enlazar (acceder) a las tablas o vistas definidas en la cláusula FROM, estos objetos y sus columnas están disponibles para todos los pasos posteriores.

Por el contrario, todas las cláusulas que preceden a la cláusula SELECT no pueden hacer referencia a ningún alias de columna o columnas derivadas definidas en la cláusula SELECT. Sin embargo, esas columnas pueden ser referenciadas por cláusulas posteriores como la ORDER BY clausula.

OVER la cláusula determina la partición y el orden de un conjunto de filas antes de aplicar la función ventana asociada. Es decir, la cláusula OVER define una ventana o conjunto de filas especificado por el usuario dentro de un Resultado de la Consulta Subyacente la función set y window calcula el resultado contra esa ventana.

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

La razón detrás es porque la forma en que Procesamiento Lógico de Consultas funciona en T-SQL. Dado que el resultado de la consulta subyacente es se establece solo cuando el procesamiento lógico de consultas alcanza el paso SELECT 5.1. (es decir, después de procesar el FROM, WHERE, GROUP BY y HAVING pasos), las funciones de ventana solo se permiten en las cláusulas SELECT y ORDER BY de la consulta.

Tenga en cuenta que las funciones de ventana siguen siendo parte de la capa relacional, incluso el Modelo Relacional no se ocupa de los datos ordenados. El resultado después del paso 5.1 SELECT. con cualquier función de ventana sigue siendo relacional.

También, hablando estrictamente, la razón por la que las funciones de ventana no están permitidas en la cláusula WHERE no es porque crearía ambigüedad, sino porque el orden en el que Procesamiento Lógico de Consultas procesa la instrucción SELECT en T-SQL.

Enlaces: aquí, aquí y aquí