sql - registros - ¿Por qué no hay funciones de ventana en cláusulas where?

sql contar registros agrupados

Question

El título lo dice todo, ¿por qué no puedo usar una función de ventana en una cláusula where en SQL Server?

Esta consulta tiene perfecto sentido:

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)

Pero no funciona. ¿Hay una manera mejor que una CTE / subconsulta?

EDITAR

Por lo que vale la pena es la consulta 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

EDITAR

+1 para las respuestas que se muestran con una subconsulta, pero realmente estoy buscando el razonamiento detrás de no poder usar funciones de ventanas en cláusulas where.

Answer 1

¿Por qué no puedo usar una función de ventana en una cláusula WHERE en SQL Server?

Una respuesta, aunque no particularmente informativa, es porque la especificación dice que no se puede.

Vea el artículo de Itzik Ben Gan - Procesamiento de consultas lógicas: qué es y qué significa para usted y, en particular, la imagen aquí . Las funciones de ventana se evalúan en el momento del SELECT en el conjunto de resultados restante después de que se hayan tratado todas las cláusulas WHERE / JOIN / GROUP BY / HAVING (paso 5.1).

Realmente estoy buscando el razonamiento detrás de no poder usar funciones de ventanas en cláusulas where.

La razón por la que no están permitidos en la cláusula WHERE es que crearía ambigüedad. Robo del ejemplo de Itzik Ben Gan del T-SQL de alto rendimiento usando las funciones de la ventana (p.25)

Supongamos que tu mesa era

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

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

Y tu consulta

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

¿Cuál sería el resultado correcto? ¿Esperaría que el predicado col1 > ''B'' ejecutara antes o después de la numeración de las filas?

Answer 2

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

"Operaciones todo en uno" significa que todas las expresiones en la misma fase de proceso de consulta lógica se evalúan lógicamente al mismo tiempo.

Y gran capítulo impacto en las funciones de la ventana :

Supongamos que 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 que estas dos condiciones se evalúan lógicamente en el mismo momento. Por lo tanto, SQL Server puede evaluar las condiciones en la cláusula WHERE en orden arbitrario, según el plan de ejecución estimado. Entonces 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

Entonces tenemos una paradoja.

Este ejemplo muestra por qué no podemos usar las funciones de la ventana en la cláusula WHERE. ¡Puede pensar más sobre esto y descubrir por qué las Funciones de Ventana pueden usarse solo en las cláusulas SELECT y ORDER BY !

Answer 3

Es un hilo viejo, pero intentaré responder específicamente a la pregunta expresada en el tema.

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

SELECT instrucción SELECT tiene las siguientes cláusulas principales especificadas en el orden tecleado :

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

El orden de procesamiento de consultas lógicas , u orden de enlace, es un orden de interpretación conceptual , define la corrección de la consulta. Este orden determina cuándo los objetos definidos en un paso están disponibles para las cláusulas en los 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 estarán disponibles para todos los pasos subsiguientes.

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 referenciarse mediante cláusulas posteriores, como la cláusula ORDER BY .

OVER cláusula OVER determina la partición y el orden de un conjunto de filas antes de aplicar la función de ventana asociada. Es decir, la cláusula OVER define una ventana o un conjunto de filas especificado por el usuario dentro de un conjunto de resultados subyacentes de consulta y la función de ventana 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 funciona el Procesamiento de consultas lógicas en T-SQL . Dado que el resultado de la consulta subyacente se establece solo cuando el procesamiento de consulta lógica alcanza el paso SELECT 5.1. (es decir, después de procesar los pasos FROM , WHERE , GROUP BY y HAVING ), las funciones de ventana solo se permiten en las cláusulas SELECT y ORDER BY de la consulta.

Nota para mencionar, las funciones de ventana todavía son parte de la capa relacional, incluso el Modelo Relacional no se ocupa de los datos ordenados. El resultado después del paso SELECT 5.1. con cualquier función de ventana sigue siendo relacional.

Además, hablando estrictamente, la razón por la cual la función de ventana no está permitida en la cláusula WHERE no es porque crearía ambigüedad, sino porque ordena cómo procesa la consulta lógica procesa la instrucción SELECT en T-SQL .

Enlaces: here , here y here

Answer 4

No es necesario que utilice 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

Answer 5

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 realmente se seleccionan los datos que están después de la cláusula WHERE . Por lo tanto, si intenta utilizar un row_number en una cláusula WHERE el valor aún no está asignado.

Answer 6

Por último, está el método anticuado, anterior al 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
)

Te doy esto por completo, simplemente.

Answer 7

Sí, desafortunadamente cuando haces una función con ventana SQL se enoja contigo incluso si tu predicado where es legítimo. Usted realiza una selección cte o anidada que tiene el valor en su declaración de selección, luego referencia su CTE o selección anidada con ese valor más adelante. Ejemplo simple que debería ser auto explicativo. Si realmente ODIO cte es un problema de rendimiento al hacer un gran conjunto de datos, siempre puede caer a la tabla temporal o 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.