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

Откуда:
Сообщений: 1214
Други мои, к своему стыду, узнал, что оказывается SUM() OVER () можно заставить считать набегающий итог без указания ROWS.

Совершенно случайно столкнулся, что если в SUM() задать ORDER BY так, что ЗНАЧЕНИЯ поля по которому идет сортировка РАЗЛИЧАЮТСЯ, то SUM() будет выводиться отдельно на каждую "смену" значения. Этакое квантование для меня оказалось полностью неожиданным.

Например
DECLARE @T TABLE ( id int, tag varchar, dt date , cnt int) 
INSERT INTO @T SELECT 1, 'A', GETDATE(), 10
INSERT INTO @T SELECT 2, 'A', GETDATE(), 20
INSERT INTO @T SELECT 3, 'A', GETDATE()-1, 30
INSERT INTO @T SELECT 4, 'B', GETDATE()-3, 100
INSERT INTO @T SELECT 5, 'B', GETDATE()-4, 200
 
select SUM(cnt) OVER (partition by tag order by tag ) , * FROM @t  

Совершенно ожидаемое поведение. SUM() одна для всей группы, указанной в PARTITION BY
НО в то же время
DECLARE @T TABLE ( id int, tag varchar, dt date , cnt int) 
INSERT INTO @T SELECT 1, 'A', GETDATE(), 10
INSERT INTO @T SELECT 2, 'A', GETDATE(), 20
INSERT INTO @T SELECT 3, 'A', GETDATE()-1, 30
INSERT INTO @T SELECT 4, 'B', GETDATE()-3, 100
INSERT INTO @T SELECT 5, 'B', GETDATE()-4, 200
 
select SUM(cnt) OVER (partition by tag order by id ) , * FROM @t 

Превращается в классический набегающий итог. ID - уникальное значение для каждой строчки.

А вот более заковыристо

DECLARE @T TABLE ( id int, tag varchar, dt date , cnt int) 
INSERT INTO @T SELECT 1, 'A', GETDATE(), 10
INSERT INTO @T SELECT 2, 'A', GETDATE(), 20
INSERT INTO @T SELECT 3, 'A', GETDATE()-1, 30
INSERT INTO @T SELECT 4, 'B', GETDATE()-3, 100
INSERT INTO @T SELECT 5, 'B', GETDATE()-4, 200
 

select SUM(cnt) OVER (partition by tag order by dt ) , * FROM @t 

Обратите в нимание, что в разрезе группы А поле dt имеет два уникальных значения, и соответственно SUM посчитано для "групп внутри групп"

Получается, что ORDER BY задает не только порядок вычисления внутри окна, что отражено в документации, но и разбивает группу, указанную в PARTITION BY на подгруппы!

Скажите, это вообще штатное поведение? Может оно прямо описано в доках, а я просто не туда глядел?

Ну и, наконец, какие ф-и кроме SUM() там брутально меняют свое поведение?
27 дек 17, 13:28    [21066381]     Ответить | Цитировать Сообщить модератору
 Re: Новое в оконный функциях (Недокументированное поведение SUM()) ?  [new]
Добрый Э - Эх
Guest
Cammomile,

это документированное поведение, связано с умолчательным размером и типом окна в отсутствии его явного указания через ROWS BETWEEN
27 дек 17, 13:33    [21066395]     Ответить | Цитировать Сообщить модератору
 Re: Новое в оконный функциях (Недокументированное поведение SUM()) ?  [new]
Добрый Э - Эх
Guest
Cammomile,

если что, по умолчанию окно задается как RANGE, а не ROWS. Поэтому строки с одинаковым значением поля сортировки попадают в одно окно...
27 дек 17, 13:36    [21066403]     Ответить | Цитировать Сообщить модератору
 Re: Новое в оконный функциях (Недокументированное поведение SUM()) ?  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
А можно текста документации с пояснениями. Очевидно, что у меня что-то не складывается в единую картину.

Там же написано, что ORDER BY задает "логический порядок вычисления"

В моей картине мира, когда мы говорим про СУММУ, это вообще ничего не должно менять.
Ведь 1 + 2 + 3 это то же самое, что 2 + 1 + 3, ЛОГИЧЕСКИ.

А у сервера, получается, что если задан порядок, то сумма это не 1+2+3 , а 0+1 1+2 2+3 , три суммы вместо одной... не очень логично?
27 дек 17, 13:39    [21066409]     Ответить | Цитировать Сообщить модератору
 Re: Новое в оконный функциях (Недокументированное поведение SUM()) ?  [new]
dao
Member

Откуда: Москва
Сообщений: 771
Cammomile
А можно текста документации с пояснениями. Очевидно, что у меня что-то не складывается в единую картину.

Там же написано, что ORDER BY задает "логический порядок вычисления"

В моей картине мира, когда мы говорим про СУММУ, это вообще ничего не должно менять.
Ведь 1 + 2 + 3 это то же самое, что 2 + 1 + 3, ЛОГИЧЕСКИ.

А у сервера, получается, что если задан порядок, то сумма это не 1+2+3 , а 0+1 1+2 2+3 , три суммы вместо одной... не очень логично?

а если в твою картину добавить интервал? ))
27 дек 17, 13:40    [21066415]     Ответить | Цитировать Сообщить модератору
 Re: Новое в оконный функциях (Недокументированное поведение SUM()) ?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20484
Cammomile
Обратите в нимание, что в разрезе группы А поле dt имеет два уникальных значения, и соответственно SUM посчитано для "групп внутри групп"

Я вижу комбинацию того, что мы имеем в первом и втором запросах. Нарастающий итог по tag снаружи, и общая сумма по dt внутри. Более того, поскольку dt двух записей, в которые ты показываешь пальцем, полностью идентично, любое иное поведение немедленно привело бы к обвинениям в неоднозначности (в зависимости от того, какая из двух неразличимых записей учтётся раньше, можно получить два различных выходных набора).
27 дек 17, 13:41    [21066420]     Ответить | Цитировать Сообщить модератору
 Re: Новое в оконный функциях (Недокументированное поведение SUM()) ?  [new]
Добрый Э - Эх
Guest
Cammomile,

у сервера получается "логическое" окно шириной от всех значений, предшествующих, текущему, включая все значения, равные текущему. rabge between unbounded preceding amd current row. что не понятно-то?

BOL
Читать нотацию:
https://docs.microsoft.com/ru-ru/sql/t-sql/queries/select-over-clause-transact-sql
Примечание

If ORDER BY is not specified entire partition is used for a window frame.Если предложение ORDER BY не указано, то для рамки окна используется весь раздел. Это относится только к тем функциям, которым не требуется предложение ORDER BY. Если предложение ROWS или RANGE не указаны, а указано предложение ORDER BY, то в качестве значения по умолчанию для рамки окна используется RANGE UNBOUNDED PRECEDING AND CURRENT ROW. Это относится только к тем функциям, которые могут принимать дополнительную спецификацию ROWS или RANGE. Например, ранжирующая функция не может принимать предложение ROWS или RANGE, поэтому данная рамка окна не может использоваться, даже несмотря на наличие предложения ORDER BY, а предложение ROWS или RANGE отсутствует.
27 дек 17, 13:52    [21066468]     Ответить | Цитировать Сообщить модератору
 Re: Новое в оконный функциях (Недокументированное поведение SUM()) ?  [new]
Добрый Э - Эх
Guest
Cammomile,

так что, если что - кури в сторону понимания разницы между окном строк (ROWS) и окном диапазонов значений (RANGE)
27 дек 17, 13:54    [21066473]     Ответить | Цитировать Сообщить модератору
 Re: Новое в оконный функциях (Недокументированное поведение SUM()) ?  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Добрый Э - Эх
Cammomile,

так что, если что - кури в сторону понимания разницы между окном строк (ROWS) и окном диапазонов значений (RANGE)

Кажется, это ключевое. Пошел курить, благодарю.
27 дек 17, 13:58    [21066486]     Ответить | Цитировать Сообщить модератору
 Re: Новое в оконный функциях (Недокументированное поведение SUM()) ?  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
А вот еще вопрос.
27 дек 17, 15:29    [21067003]     Ответить | Цитировать Сообщить модератору
 Re: Новое в оконный функциях (Недокументированное поведение SUM()) ?  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
А вот еще вопрос. То, что OVER (ORDER BY) задает порядок не только в работе окна, но и всего внешнего запроса, это тоже описано где-то, или просто совпадение?

SELECT SUM( CAST(num as bigint))  OVER (order by num desc), Num from   dbo.Numbers  WHERE NUM <= 100000
SELECT SUM( CAST(num as bigint))  OVER (order by num desc), Num from   dbo.Numbers  WHERE NUM <= 100000 ORDER BY NUM DESC

Планы запросов идентичные.
27 дек 17, 15:33    [21067025]     Ответить | Цитировать Сообщить модератору
 Re: Новое в оконный функциях (Недокументированное поведение SUM()) ?  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
Cammomile, совпедение... серверу один хрен приходится сортировать по num... не станет же он без особой нужды перемешивать данные взад
27 дек 17, 15:55    [21067173]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить