ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Новое в оконный функциях (Недокументированное поведение SUM()) ?
12 сообщений из 12, страница 1 из 1
Новое в оконный функциях (Недокументированное поведение SUM()) ?
    #39576781
Cammomile
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cammomile
Участник
Други мои, к своему стыду, узнал, что оказывается SUM() OVER () можно заставить считать набегающий итог без указания ROWS.

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

Например
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
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
НО в то же время
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
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 - уникальное значение для каждой строчки.

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
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() там брутально меняют свое поведение?
...
Рейтинг: 0 / 0
27.12.2017, 13:28
| Ответить | Цитировать | Написать  
Новое в оконный функциях (Недокументированное поведение SUM()) ?
    #39576788
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
Cammomile,

это документированное поведение, связано с умолчательным размером и типом окна в отсутствии его явного указания через ROWS BETWEEN
...
Рейтинг: 0 / 0
27.12.2017, 13:33
| Ответить | Цитировать | Написать  
Новое в оконный функциях (Недокументированное поведение SUM()) ?
    #39576792
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
Cammomile,

если что, по умолчанию окно задается как RANGE, а не ROWS. Поэтому строки с одинаковым значением поля сортировки попадают в одно окно...
...
Рейтинг: 0 / 0
27.12.2017, 13:36
| Ответить | Цитировать | Написать  
Новое в оконный функциях (Недокументированное поведение SUM()) ?
    #39576795
Cammomile
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cammomile
Участник
А можно текста документации с пояснениями. Очевидно, что у меня что-то не складывается в единую картину.

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

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

А у сервера, получается, что если задан порядок, то сумма это не 1+2+3 , а 0 +1 1+2 2+3 , три суммы вместо одной... не очень логично?
...
Рейтинг: 0 / 0
27.12.2017, 13:39
| Ответить | Цитировать | Написать  
Новое в оконный функциях (Недокументированное поведение SUM()) ?
    #39576797
dao
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dao
Участник
CammomileА можно текста документации с пояснениями. Очевидно, что у меня что-то не складывается в единую картину.

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

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

А у сервера, получается, что если задан порядок, то сумма это не 1+2+3 , а 0 +1 1+2 2+3 , три суммы вместо одной... не очень логично?
а если в твою картину добавить интервал? ))
...
Рейтинг: 0 / 0
27.12.2017, 13:40
| Ответить | Цитировать | Написать  
Новое в оконный функциях (Недокументированное поведение SUM()) ?
    #39576799
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
CammomileОбратите в нимание, что в разрезе группы А поле dt имеет два уникальных значения, и соответственно SUM посчитано для "групп внутри групп"
Я вижу комбинацию того, что мы имеем в первом и втором запросах. Нарастающий итог по tag снаружи, и общая сумма по dt внутри. Более того, поскольку dt двух записей, в которые ты показываешь пальцем, полностью идентично, любое иное поведение немедленно привело бы к обвинениям в неоднозначности (в зависимости от того, какая из двух неразличимых записей учтётся раньше, можно получить два различных выходных набора).
...
Рейтинг: 0 / 0
27.12.2017, 13:41
| Ответить | Цитировать | Написать  
Новое в оконный функциях (Недокументированное поведение SUM()) ?
    #39576816
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
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 отсутствует.
...
Рейтинг: 0 / 0
27.12.2017, 13:52
| Ответить | Цитировать | Написать  
Новое в оконный функциях (Недокументированное поведение SUM()) ?
    #39576818
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
Cammomile,

так что, если что - кури в сторону понимания разницы между окном строк (ROWS) и окном диапазонов значений (RANGE)
...
Рейтинг: 0 / 0
27.12.2017, 13:54
| Ответить | Цитировать | Написать  
Новое в оконный функциях (Недокументированное поведение SUM()) ?
    #39576822
Cammomile
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cammomile
Участник
Добрый Э - ЭхCammomile,

так что, если что - кури в сторону понимания разницы между окном строк ( ROWS ) и окном диапазонов значений ( RANGE )
Кажется, это ключевое. Пошел курить, благодарю.
...
Рейтинг: 0 / 0
27.12.2017, 13:58
| Ответить | Цитировать | Написать  
Новое в оконный функциях (Недокументированное поведение SUM()) ?
    #39576918
Cammomile
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cammomile
Участник
А вот еще вопрос.
...
Рейтинг: 0 / 0
27.12.2017, 15:29
| Ответить | Цитировать | Написать  
Новое в оконный функциях (Недокументированное поведение SUM()) ?
    #39576920
Cammomile
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cammomile
Участник
А вот еще вопрос. То, что OVER (ORDER BY) задает порядок не только в работе окна, но и всего внешнего запроса, это тоже описано где-то, или просто совпадение?

Код: sql
1.
2.
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


Планы запросов идентичные.
...
Рейтинг: 0 / 0
27.12.2017, 15:33
| Ответить | Цитировать | Написать  
Новое в оконный функциях (Недокументированное поведение SUM()) ?
    #39576940
Фотография buser
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
buser
Участник
Cammomile, совпедение... серверу один хрен приходится сортировать по num... не станет же он без особой нужды перемешивать данные взад
...
Рейтинг: 0 / 0
27.12.2017, 15:55
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Новое в оконный функциях (Недокументированное поведение SUM()) ?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]