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

Откуда: Зеленоград, Москва, Россия
Сообщений: 21249
Достаточно давно пользуюсь подсчётом нарастающей суммы через оконные функции. Ну типа

create table test (
id integer,
val integer
);
  
insert into test (id, val)
select 7,1 UNION
select 5,0 UNION
select 2,0 UNION
select 3,0 UNION
select 1,1 UNION
select 4,1 UNION
select 6,0 UNION
select 8,1 UNION
select 9,0 UNION
select 10,1;

select id, val, summ = sum(val) over (order by id)
from test;


idvalsumm
111
201
301
412
502
602
713
814
904
1015


Вроде бы результат корректный (и даже в качестве дополнительного бонуса - сортировка выходного набора). НО...

Если сунуть нос в описание OVER Clause применительно к агрегатным функциям, то обнаруживаем, что там не описано использование ORDER BY для них, только PARTITION BY.

Соответственно возникает вопрос: корректная работа ORDER BY в данном случае детерминирована, или это просто элемент везения (например, особенность текущей программной реализации обработки таких выражений)?

Как думаете?
27 авг 15, 12:19    [18075640]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Akina
Достаточно давно пользуюсь подсчётом нарастающей суммы через оконные функции. Ну типа

Если сунуть нос в описание OVER Clause применительно к агрегатным функциям, то обнаруживаем, что там не описано использование ORDER BY для них, только PARTITION BY.

Соответственно возникает вопрос: корректная работа ORDER BY в данном случае детерминирована, или это просто элемент везения (например, особенность текущей программной реализации обработки таких выражений)?



в 2012 сервере оконные функции доработаны, поэтому справку надо смотреть для него, там и про order by есть. а в 2008 нарастающий итог так не получить
27 авг 15, 12:30    [18075713]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21249
2005 сервер и правда ругается на ORDER BY.
2008 у меня сейчас, к сожалению, нет, но sqlfiddle в режиме MS SQL 2008 вроде бы на ошибки не ругается.
27 авг 15, 12:49    [18075881]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
o-o
Guest
Akina,

2005, 2008, 2008R2 не поддерживают order by in aggregates, partition by only.
27 авг 15, 12:53    [18075910]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
o-o
Guest
2008R2:
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near 'order'.


К сообщению приложен файл. Размер - 61Kb
27 авг 15, 12:58    [18075953]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21249
Ну вот, и надейся после этого на фиддлеров...

Ок, вопрос снят.
27 авг 15, 13:00    [18075981]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
o-o
Guest
Elements of Window Functions
Ordering

The ordering element defines the ordering for the calculation, if relevant, within the partition. In
standard SQL, all functions support an ordering element. As for SQL Server, initially it didn’t support
the ordering element with aggregate functions; rather, it only supported partitioning. Support for
ordering for aggregates was added in SQL Server 2012.
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions By Itzik Ben-Gan
27 авг 15, 13:02    [18075995]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21249
o-o, спасибо.

В общем, польза с вопроса получилась неожиданная. Давая совет по версии сервера, нельзя ориентироваться на успешное выполнение кода на sqlfiddle.
27 авг 15, 13:05    [18076015]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
o-o
Guest
буду знать про sqlfiddle, спасибо.
но даже в снятые вопросы предпочитаю впечатать ссылку на приличную литературу/официальную документацию
27 авг 15, 13:09    [18076071]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
Wlr-l
Member

Откуда:
Сообщений: 602
Akina,

Соответственно возникает вопрос: корректная работа ORDER BY в данном случае детерминирована, или это просто элемент везения (например, особенность текущей программной реализации обработки таких выражений)?

Даже, если сервер 2012.
Для обеспечения детерминированности результата нужно, чтобы набор полей для секционирования и сортировки обеспечивал уникальность результирующих строк. Если это не так, то появляются равноправные строки, которые сервер может обрабатывать в произвольном порядке, что приведет к недетерминированности вычисления нарастающего итого (или любых других вычислений).
В данном случае сортировка по id обеспечивает уникальность сортировки. Опущенный раздел секционирования означает, что берутся все строки. Опущенный раздел секционирования означает, строки берутся от первой до текущей, так что результат будет детерминированным.
27 авг 15, 13:50    [18076444]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
Wlr-l
Member

Откуда:
Сообщений: 602
Wlr-l, описка, следует читать так:

В данном случае сортировка по id обеспечивает уникальность сортировки. Опущенный раздел секционирования означает, что берутся все строки. Опущенный раздел кадрирования означает, что строки берутся от первой до текущей, так что результат будет детерминированным.
27 авг 15, 13:54    [18076478]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21249
Wlr-l, это понятно.
27 авг 15, 14:00    [18076524]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
o-o
Guest
Wlr-l
Wlr-l, описка, следует читать так:

В данном случае сортировка по id обеспечивает уникальность сортировки. Опущенный раздел секционирования означает, что берутся все строки. Опущенный раздел кадрирования означает, что строки берутся от первой до текущей, так что результат будет детерминированным.

господи помилуй.
хорошо, кому понятно.
а я вот просто теряюсь в догадках, что у нас сегодня скрывается под словом "кадрирование".
гугл сплошные "кадрирования изображений" выдает, так что извольте объяснить,
кто кого кадрит.
и при чем тут сервер.

хотя спасибо, теперь, возможно, я знаю, почему SQL Server от 18 и старше

К сообщению приложен файл. Размер - 23Kb
27 авг 15, 15:02    [18076868]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
a_voronin
Member

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

Вполне детерминирована и соответствует вот такому синтаксису SQL 2012 +

USE TEST 
GO 

create table test (
id integer,
val integer
);
  
insert into test (id, val)
select 7,1 UNION
select 5,0 UNION
select 2,0 UNION
select 3,0 UNION
select 1,1 UNION
select 4,1 UNION
select 6,0 UNION
select 8,1 UNION
select 9,0 UNION
select 10,1;

select id, val, summ = sum(val) over (order by id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from test;
27 авг 15, 16:37    [18077448]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
o-o
Guest
мне тут прислали выдержку из переведенной книги.
перевод не одобряю, но и свою версию не предложу,
меня вполне устраивает оригинал.
а вот для интересующихся

Framing
Framing is essentially another filter that further restricts the rows in the partition. It is applicable to
aggregate window functions as well as to three of the offset functions: FIRST_VALUE, LAST_VALUE,
and NTH_VALUE. Think of this windowing element as defining two points in the current row’s partition
based on the given ordering, framing the rows that the calculation will apply to.
The framing specification in the standard includes a ROWS or RANGE option that defines the starting
row and ending row of the frame, as well as a window frame-exclusion option. SQL Server 2012
introduced support for framing, with full implementation of the ROWS option, partial implementation
of the RANGE option, and no implementation of the window frame-exclusion option.

К сообщению приложен файл. Размер - 74Kb
27 авг 15, 18:08    [18077846]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2415
o-o,

любят однако переводчики новые слова придумывать (
имхо калька "фрейм" более подходящий термин, чем "кадр".
ну и за фразу "это фильтр, а не ограничение" имхо убивать надо

з.ы. помню полчаса в одной книжке пытался понять "бюджет рабочего стола" (account)
28 авг 15, 12:30    [18080613]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
StarikNavy
o-o,

любят однако переводчики новые слова придумывать (
имхо калька "фрейм" более подходящий термин, чем "кадр".
ну и за фразу "это фильтр, а не ограничение" имхо убивать надо

з.ы. помню полчаса в одной книжке пытался понять "бюджет рабочего стола" (account)


Так я не понял, кадрирование это что ли вот это ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ?
28 авг 15, 12:36    [18080663]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
Wlr-l
Member

Откуда:
Сообщений: 602
o-o

Мы говорим на русском языке, поэтому причем здесь Framing?

StarikNavy,

калька "фрейм" более подходящий термин, чем "кадр".

Термин "фрейм" не хуже, но и не лучше термина "кадр". Оба эти термина перегружены, т.е. имеют несколько значений даже в одной области знаний. Тем более, что слово frame переводится на русский язык как кадр, рамка, пакет, строение, каркас, ...
Назвали кадром, то пусть всегда будет кадр. Не нужно одно и то же называть разными словами.

a_voronin

Так я не понял, кадрирование это что ли вот это

Вам нужно много чего еще понять. Например, не повторять то, что уже было сказано в теме.
28 авг 15, 13:41    [18081192]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
Wlr-l
Вам нужно много чего еще понять. Например, не повторять то, что уже было сказано в теме.


Хватит вохбухать не по делу. Майкрософты в свойм переводе использовали слово кадр.

https://msdn.microsoft.com/ru-ru/library/ms189461(v=sql.120).aspx

ROWS | RANGE
Применимо для следующих объектов: С SQL Server 2012 по SQL Server 2014 включительно.
Еще больше ограничивает строки в пределах секции, указывая начальную и конечную точки. Это достигается путем указания диапазона строк в отношении текущей строки с помощью логических или физических взаимосвязей. Физическая взаимосвязь достигается с помощью предложения ROWS.
Предложение ROWS ограничивает строки внутри секции путем указания фиксированного числа строк, предшествующих или следующих после текущей строки. В качестве альтернативы предложение RANGE логически ограничивает строки внутри секции путем указания диапазона значений в отношении к значению текущей строки. Предшествующие и последующие строки определяются на основании порядка, заданного в предложении ORDER BY. Кадр окна «RANGE … CURRENT ROW …» содержит все строки, которые имеют те же значения в выражении ORDER BY, что и в текущей строке. Например, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW означает, что окно строк, с которым работает функция, содержит всего три строки, при этом текущей строке предшествуют 2 строки (включая текущую).

=============================

Вообще Framing и кадрировавание это термины из фотосъемки и видеомонтажа. И сопутствующих областей. Здесь это иносказательно применено с потоку/последовательности записей как будто это кинопленка с кадрами. Насколько это удобно и понятно решайте сами.

Кадрирование (от фр. cadre — кадр) при фото-, кино- и видеосъёмке — выбор точки съёмки, ракурса и направления съёмки, а также угла зрения и типа объектива для получения необходимого размещения объектов в поле зрения видоискателя аппаратуры и на итоговом изображении.
При кино- и видеосъёмке кадрирование выбирается намного тщательнее, нежели при фотосъёмке на негативную плёнку или цифровую фотокамеру, так как возможности исправления неточностей кадрирования отснятого киноизображения гораздо хуже, нежели у одиночного кадра фотоизображения.
Кадрирование при печати или редактировании изображения — выбор границ и формата фотографического изображения, имеющегося на негативе, слайде или файле, содержащем изображение. Используется, как правило, для получения более гармоничного в визуальном отношении изображения.
У этого термина существуют и другие значения, см. Рамка (значения).
Кадрирующая рамка — устройство, удерживающее фотобумагу на столе фотоувеличителя в выравненном состоянии и правильном положении.
Кадрирование в полиграфии — выделение части кадра, оригинального изображения, используемой для представления его в полосе издания. Как правило, с увеличением[1].

========================================

Это вопрос введения новых терминов переводчиками. Можно перевести киоск, а можно бутик.
28 авг 15, 13:54    [18081291]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2415
Wlr-l
Не нужно одно и то же называть разными словами.

думаю НИКТО против этого не возражает ))
вопрос как этого добиться
28 авг 15, 14:06    [18081375]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
Wlr-l
Member

Откуда:
Сообщений: 602
a_voronin,

Как всегда вы сказали ни о чем. Причем здесь фотосъемки? Да и слово cadre (фр.) это то же одно из значений слова frame (анг.).

Загляните в словарь, например, там есть термины

log frame - лесопильная рама
midship frame - мидель-шпангоут

которые появились задолго до того, как изобрели фото. Как и много других значений слова frame. Почему вы эти случае не приводите. Чем они хуже вашей фотосессии?

Перевели frame, как кадр. Пусть будет так. А операция разделения целого на кадры, пусть будет называться кадрированием. И не нужно вспоминать бурную молодость (это не к вам). Хотя ...
28 авг 15, 14:21    [18081470]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
o-o
Guest
Wlr-l
o-o
Мы говорим на русском языке, поэтому причем здесь Framing?


Туве Янссон
Они говорят по-иностранному! – выпалил Снифф. – Кто их разберет, о чем они болтают!
– А как это звучит? – спросил Муми тролль.
Вместе с Хемулем они толкли кардамон для сладкого пирога.
– Самсла ты крысла! – отвечал Снифф.
– Ну и дела, – вздохнула Муми мама. – Как же я узнаю, что они захотят на третье в свой день рождения и сколько подушек им надо под голову?
– А мы научимся их языку, – сказал Муми тролль. – Нет ничего проще: естьсла, чтосла, дратьсла.
– Мне кажется, я их понял, – задумчиво произнес Хемуль. – Похоже, они сказали Сниффу, что он старая облезлая крыса.

кому надо, тот понял
а теперь предлагаю 2 эксперимента:
1. прикрутите опрос к теме: чью цитату вы поняли с первого раза, от o-o или от Wir-I?
2. забейте в гугл "оконные функции кадрирование", без кавычек, пусть будет свобода творчества.
поглядите, что отловилось
а теперь "оконные функции framing".
из выпавшего по второму запросу вообще все сразу ясно,
не говоря о том, что меня радуют выпавшие сайты и НЕПЕРЕВЕДЕННЫЙ framing в блоге SomewhereSomehow.

вам же советую отныне комп называть исключительно ЭВМ, а сервер "обслуживатель"
или что-то в том же духе, ведь так нехорошо заимствовать слова, когда можно в своем языке нарыть.
пусть из другой оперы и слегка через .опу, но зато никакой иностранщины.

а, ну да, и никаких аккаунтов.
вот же нам только что привели на русском языке: "бюджет рабочего стола".
так и выражайтесь, вам подобные вас непременно поймут :)

К сообщению приложен файл. Размер - 48Kb
28 авг 15, 14:50    [18081660]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
Wlr-l,

Я со словарями очень хорошо в ладах. Только с толковыми. У слова frame ну очень много значений. Даже такие значение как выразить словами и сфальсифицировать уголовное дело.

frame (frām)
Share:
n.
1.
a. A structure that gives shape or support: the frame of a house.
b. The structure or physique of a human or animal body: a worker's sturdy frame.
c. An open structure or rim for encasing, holding, or bordering: a window frame; the frame of a mirror.
2.
a. A closed, often rectangular border of drawn or printed lines.
b. The edge, usually rectangular, delimiting the boundaries of an image.
c. The bounded area of a visual image, as in photography or film: filled the frame with a cast of thousands.
d. One of the set of still images that constitute a film or video.
e. A single image, as in a comic strip or graphic novel, usually bounded by a rectangular line.
f. Computers A rectangular area in which text or graphics can be shown, especially one of several rectangular areas on a web page displaying different documents simultaneously.
3.
a. A general structure or system: the frame of government.
b. A general state or condition: The news put me into a better frame of mind.
c. A frame of reference.
4. The presentation of events in a narrative work, especially a work of literature or film, such that characters in the narrative exist in isolation, uninfluenced by, unaware of, and unable to interact with the narrator or audience.
5. Linguistics
a. The context in which discourse occurs.
b. A pattern for a syntactic construction in which one of a group of words can vary.
6.
a. A round or period of play in some games, such as bowling and billiards.
b. Baseball An inning.
7. often frames A pair of eyeglasses, excluding the lenses: had new lenses fitted into an old pair of frames.
8. See cold frame.
9. Informal A frame-up.
10. Obsolete Shape; form.
v. framed, fram·ing, frames
v.tr.
1.
a. To enclose in a frame: frame a painting.
b. To put together the structural parts of; construct the frame of: frame a house.
2. To conceive or design: framed an alternate proposal.
3. To establish the context for and terminology regarding (a subject of discussion or debate), especially so as to exclude an unwanted point of view: The question was framed to draw only one answer.
4.
a. To put into words; formulate: frame a reply.
b. To form (words) silently with the lips.
5.
a. To make up evidence or contrive events so as to incriminate (a person) falsely.
b. To prearrange (a contest) so as to ensure a desired fraudulent outcome; fix: frame a prizefight.
c. Baseball To catch (a pitch) in such a way as to make it appear to have passed through the strike zone.
v.intr.
Archaic To go; proceed: "Frame upstairs, and make little din" (Emily Brontë).
[Middle English, from framen, to make progress, to frame, from Old English framian, to avail, profit, from fram, forward; see FROM.]
frama·ble, framea·ble adj.
The American Heritage® Dictionary of the English Language, Fifth Edition copyright ©2015 by Houghton Mifflin Harcourt Publishing Company. All rights reserved.
28 авг 15, 15:41    [18082094]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
o-o
Guest
Wlr-l
a_voronin,
Как всегда вы сказали ни о чем. Причем здесь фотосъемки?

+ в поддержку a_voronin-a

я сегодня с a_voronin-ым заодно.
как раз по той самой причине, что вы огласили.
если я забиваю в гугл "кадрирование" вообще без контекста,
то попадаю на те самые фотосъемки.
впору спросить, ну и при чем тут фото?
это слово однозначно указывает на область применения, так сложилось.
и зачем же тогда из кожи вон лезть и переводить "абы как"?
в теории переводов допускается и успешно применяется вот такой вариант:
когда не можешь найти точный аналог чего-то в другом языке, оставляй, как есть.
можно буквами языка-оригинала, можно транскрипцией или транслитерацией,
но обязательно в скобках или сноской поясни, что сей термин означает и предупреди,
что в дальнейшем будешь использовать его.
и это нормально.

в разных странах по-разному подходят к "тотальному переводу".
допустим, у нас никто не делает переводов типа "Жора Куст" (я о Буше, если что),
у нас оставляют "Джельсомино в стране Лжецов", а могли бы из него Жасмина сделать, ага.
но в определенных странах при переводе Шарикова превращают-таки в Pallini[ит.]
(ага, по Москве такой Pallini бегал за кошками),
или вот еще: Петя и Волк -- Pedro y el lobo [исп.]

за что персонажей лишили их имен и почему они должны указывать новыми именами на совсем иные страны?
случай "кадрирования" из этой самой оперы, перевод перевел стрелки с программирования на фото.
IMHO
28 авг 15, 16:00    [18082217]     Ответить | Цитировать Сообщить модератору
 Re: SUM() OVER (ORDER BY)  [new]
o-o
Guest
a_voronin
Я со словарями очень хорошо в ладах.

"прошу считать мое временное присоединение к a_voronin-у расторгнутым, начиная с этой фразы"
28 авг 15, 16:10    [18082287]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить