Почему нет оконных функций в предложениях?

Заголовок говорит все, почему я не могу использовать оконную функцию в предложении where в 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/Subquery?

ИЗМЕНИТЬ

Для чего стоит запрос с помощью 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 для ответов, отображаемых с помощью подзапроса, но на самом деле я ищу аргументы в пользу того, что вы не можете использовать функции окон в тех случаях, когда клаузулы.

Ответ 1

На самом деле, я ищу рассуждения о том, что не могу использовать функции windowing в разделах where.

Причина, по которой они не разрешены в предложении WHERE, заключается в том, что это создаст неоднозначность. Кража примера Итзика Бен Гана из Высокопроизводительный T-SQL с использованием функций окна (стр .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' запущен до или после нумерации строк?

ROW_NUMBER оценивается во время SELECT в результирующем наборе, оставшемся после того, как были рассмотрены все предложения WHERE/HAVING.

Ответ 2

Нет необходимости в CTE, просто используйте функцию windowing в подзапросе:

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. Поэтому, если вы попытаетесь использовать row_number в предложении WHERE, значение еще не назначено.

Ответ 3

Вам необязательно использовать 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

Ответ 4

Да, к сожалению, когда вы выполняете оконную функцию, SQL сердится на вас, даже если ваш предикат является законным. Вы делаете cte или вложенный выбор, имеющий значение в своем предложении select, а затем ссылайтесь на свой CTE или вложенный выбор с этим значением позже. Простой пример, который должен быть понятным. Если вы действительно HATE cte для некоторой проблемы с производительностью при выполнении большого набора данных, вы всегда можете отказаться от переменной temp или таблицы.

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.

Ответ 5

Наконец, существует старомодный путь до 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
)

Я даю вам это для полноты, просто.

Ответ 6

Прежде всего, это называется all-at-once operation

"Операции All-at-Once" означает, что все выражения в одном и том же логическая логическая последовательность запросов логически оценивается одновременно.

И отличная глава Влияние на функции окна:

Предположим, что у вас есть:

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;

Операции All-at-Once говорят нам, что эти два условия оцениваются логически в один и тот же момент времени. Поэтому SQL Server может оценивать условия в предложении WHERE в произвольном порядке, исходя из расчетный план выполнения. Итак, главный вопрос заключается в том, какое условие сначала оценивает.

Случай 1:

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

Результат: 1002

Случай 2:

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

Результат: пустой

Итак, у нас есть парадокс.

В этом примере показано, почему мы не можем использовать функции Window в предложении WHERE. Вы можете больше подумать об этом и узнать, почему функции окна разрешено использовать только в SELECT и предложения ORDER BY!