Чем отличается ORDER BY от WITHIN GROUP (ORDER BY) простым языком?

Question

[user_of_toster]

Читаю документацию про aggregation expression.

aggregation_function(expression, ORDER BY a) - в начале сортирует таблицу по а, затем применяет aggregation_function к получившемуся списку.

А что делает aggregation_function(expression), WITHIN GROUP (ORDER BY a) ? Прочел эту часть документации много раз, объяснения не понятны для новичка.

Comments

[di]

WITHIN GROUP - это вообще не про order by. это синтаксический сахар. https://www.2ndquadrant.com/en/blog/the-within-gro... . Но так действительно сложно вкурить зачем он может пригодиться. сам не до конца понимаю

Answer 1

[galaxy]

aggregation_function(expression, ORDER BY a) - в начале сортирует таблицу по а, затем применяет aggregation_function к получившемуся списку.

Да, только запятая в скобках лишняя. Такой синтаксис позволяет иметь агрегатам в списке SELECT сортировку входных значение (напомню, что ORDER BY в запросе вычисляется после GROUP BY и агрегации, т.е. чтобы раньше так сделать, был нужен подзапрос, который отсортирует массив данных), даже каждому агрегату свою (раньше вообще нельзя было сделать нормально). Агрегатным функциям часто не важен порядок входных значений (COUNT, SUM, MAX - не зависят от порядка), но есть функции, для которых он значение имеет (string_agg, etc, можно свои также написать).

А что делает aggregation_function(expression), WITHIN GROUP (ORDER BY a)

Это синтаксис для специального вида агрегатов: Ordered-Set Aggregate Functions и Hypothetical-Set Aggregate Functions (запятая тут у вас тоже лишняя). Обычные с ним не заработают.
Они принимают на вход отсортированный массив данных, указанный в ORDER BY (т.е. a, а не expression!), плюс, возможно, значение expression (вычисляется один раз за запрос).
Область применения пока достаточно специфична: Ordered-Set Aggregate Functions по сути применяется только для вычисления перцентилей (зато их стало легко и приятно считать:

SELECT percentile_disc (0.2) WITHIN GROUP (ORDER BY ...)

).
Hypothetical-Set Aggregate Functions - это такие аналоги некоторых WINDOW агрегатов (типа rank):

SELECT rank() OVER (ORDER BY value) ...; -- вернет ранк данной записи в отсортированном по value списке
SELECT rank(55) WITHIN GROUP (ORDER BY value) ...; -- вернет ранк числа 55, как если бы оно было в  отсортированном по value списке

В общем, штука своеобразная. Читайте документацию, сходу может быть трудно понять.

Comments

[user_of_toster]

Вопрос возник при чтении документации)) Судя по ответу, у вас знания продвинутые. Можете подсказать, как получали знания?

В лоб читали документацию, книги? Или как-то сочетали практику? Или документацию читали только по потребности? Или может быть только практика? В каком соотношении чтение:практика?

[galaxy]

Я еще с ораклом работал, там вообще model мозговыносящий есть (но штука крутая) :)

За постгресом я просто инкрементально слежу версии с 8, по принципу: ух ты, какую штуку добавили, надо почитать, как работает.

Учите по нарастающей, начиная просто с хорошей книги по SQL (постгрес следует стандартам, в отличие от некоторых, и общие знание будут непосредственно применимы).
Потом добавляйте уже более новые или специфичные вещи (разобрались, например, с классическими агрегатами SQL-92, переходите к их расширениям, типа FILTER, GROUPING SETS. Потом к WINDOW FUNCTIONS). Сразу взять и по документации заботать весь синтексис SELECT - так себе идея, конечно.