Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите разобраться с Over в статистических функциях  [new]
Satans Claws
Member

Откуда:
Сообщений: 64
1) я правильно понимаю, что конструкция

SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Average'
FROM Sales.SalesOrderDetail


равносильна следующей:


select
	s.SalesOrderID, 
	s.ProductID, 
	s.OrderQty,
	gs.Total,
	gs.Average
from
	Sales.SalesOrderDetail s
	Left join 
		(
		select 
			SalesOrderID,
			SUM(OrderQty) Total,
			AVG(OrderQty) Average
		from
			Sales.SalesOrderDetail
		group by
			SalesOrderID
		) gs on gs.SalesOrderID = s.SalesOrderID


2) BOL нифига толком ничего не пишет про order by в over (отсылает на общее описание order by, а там вообще нет ни слова про over), но если я правильно понял, вот такой запрос посчитает сумму нарастающим итогом
SELECT 
    SalesOrderID, 
    ProductID, 
    SUM(OrderQty) OVER(ORDER BY ProductID)
FROM Sales.SalesOrderDetail

т.е. для каждого SalesOrderID набор строк будет отранжирован по ProductID, и для каждого ProductID будет посчитан нарастающий итог по OrderQty

что примерно соответствует такому запросу
SELECT 
    s1.SalesOrderID, 
    s1.ProductID, 
    SUM(s2.OrderQty)
FROM 
	Sales.SalesOrderDetail s
	left join 
		(
			select
				SalesOrderID, 
				ProductID,
				OrderQty
			from
				Sales.SalesOrderDetail
		) s2 on s2.SalesOrderID = s1.SalesOrderID
			and s2.ProductID <= s1.ProductID
Group by
    s1.SalesOrderID, 
    s1.ProductID, 

(точнее, поскольку sum...over строки не сворачивает, к исходному набору строк надо приджойнить посчитанную сумму по SalesOrderID = SalesOrderID and ProductID = ProductID)

если же мы в селект листе напишем
SUM(OrderQty) OVER(ORDER BY SalesOrderID, ProductID)
,
то условие джойна s2 и s1 будет, соответственно
on (s2.SalesOrderID < s1.SalesOrderID) 
	or (s2.SalesOrderID = s1.SalesOrderID
	and s2.ProductID <= s1.ProductID)


Я прав или же серьезно заблуждаюсь?
31 окт 19, 07:26    [22006697]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с Over в статистических функциях  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20530
Satans Claws
я правильно понимаю, что конструкция ... равносильна следующей

Ну если не буквоедствовать по поводу разной структуры выходного набора, и при условии, что SalesOrderID не может быть NULL - да.
Satans Claws
BOL нифига толком ничего не пишет про order by в over (отсылает на общее описание order by, а там вообще нет ни слова про over)

https://docs.microsoft.com/ru-ru/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15
Если предложение ORDER BY не указано, то для рамки окна используется весь раздел. Это относится только к тем функциям, которым не требуется предложение ORDER BY. Если предложение ROWS или RANGE не указаны, а указано предложение ORDER BY, то в качестве значения по умолчанию для рамки окна используется RANGE UNBOUNDED PRECEDING AND CURRENT ROW. Это относится только к тем функциям, которые могут принимать дополнительную спецификацию ROWS или RANGE. Например, ранжирующая функция не может принимать предложение ROWS или RANGE, поэтому данная рамка окна не может использоваться, даже несмотря на наличие предложения ORDER BY, а предложение ROWS или RANGE отсутствует.
31 окт 19, 07:52    [22006706]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с Over в статистических функциях  [new]
PsyMisha
Member

Откуда: другая столица
Сообщений: 769
Satans Claws
я правильно понимаю, что конструкция
равносильна следующей:


Поверхностно, как результат вывода - да. Но все же оконные функции производительней, - так как имеет место быть порядок выполнения конструкций и операторов под капотом - оконные функции прозрачно применяются к уже полученному результирующему набору в памяти, в следствии чего и появляется возможность, применять в блоке SELECT разные агрегации секций построчно, независимо друг от друга.
Таким образом - отпадают лишние чтения и операции обращения к таблицам, как те, что во втором случае через LEFT JOIN.
31 окт 19, 09:26    [22006755]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с Over в статистических функциях  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4727
Satans Claws,

1)да
2)
(OrderQty) OVER(ORDER BY ProductID)

Это краткая запись более полной конструкции
(OrderQty) OVER(ORDER BY ProductID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)


посмотрите как работает ROWS BETWEEN
31 окт 19, 10:04    [22006806]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с Over в статистических функциях  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4727
А ещё для некоторых сюрпризом является вот такая конструкция

SUM(OrderQty) OVER() AS 'Total'
31 окт 19, 10:05    [22006808]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с Over в статистических функциях  [new]
iap
Member

Откуда: Москва
Сообщений: 46977
a_voronin
Satans Claws,

1)да
2)
(OrderQty) OVER(ORDER BY ProductID)


Это краткая запись более полной конструкции
(OrderQty) OVER(ORDER BY ProductID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)



посмотрите как работает ROWS BETWEEN
Это неверно, поскольку по умолчанию - RANGE, а не ROWS. Как это ни странно и очень печально.
31 окт 19, 10:29    [22006847]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с Over в статистических функциях  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4727
iap,

Да действительно RANGE, но в данном примере это роли не играет
31 окт 19, 10:40    [22006859]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить