какая агрегатная функция используется для расчета суммы

Будем учиться подводить итоги. Нет, это ещё не итоги изучения SQL, а итоги значений столбцов таблиц базы данных. Агрегатные функции SQL действуют в отношении значений столбца с целью получения единого результирующего значения. Наиболее часто применяются агрегатные функции SQL SUM, MIN, MAX, AVG и COUNT. Следует различать два случая применения агрегатных функций. Первый: агрегатные функции используются сами по себе и возвращают одно результирующее значение. Второй: агрегатные функции используются с оператором SQL GROUP BY, то есть с группировкой по полям (столбцам) для получения результирующих значений в каждой группе. Рассмотрим сначала случаи использования агрегатных функций без группировки.

Функция SQL SUM

Функция SQL SUM возвращает сумму значений столбца таблицы базы данных. Она может применяться только к столбцам, значениями которых являются числа. Запросы SQL для получения результирующей суммы начинаются так:

После этого выражения следует FROM (ИМЯ_ТАБЛИЦЫ), а далее с помощью конструкции WHERE может быть задано условие. Кроме того, перед именем столбца может быть указано DISTINCT, и это означает, что учитываться будут только уникальные значения. По умолчанию же учитываются все значения (для этого можно особо указать не DISTINCT, а ALL, но слово ALL не является обязательным).

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

Пример 1. Есть база данных фирмы с данными о её подразделениях и сотрудниках. Таблица Staff помимо всего имеет столбец с данными о заработной плате сотрудников. Выборка из таблицы имеет следующий вид (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):

Этот запрос вернёт значение 287664,63.

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

Пример 2. Вывести сумму комиссионных, получаемых всеми сотрудниками с должностью Clerk.

Функция SQL MIN

Функция SQL MIN также действует в отношении столбцов, значениями которых являются числа и возвращает минимальное среди всех значений столбца. Эта функция имеет синтаксис аналогичный синтаксису функции SUM.

Запрос вернёт значение 10505,90.

И вновь упражнение для самостоятельного решения. В этом и некоторых других упражнениях потребуется уже не только таблица Staff, но и таблица Org, содержащая данные о подразделениях фирмы:

Пример 4. К таблице Staff добавляется таблица Org, содержащая данные о подразделениях фирмы. Вывести минимальное количество лет, проработанных одним сотрудником в отделе, расположенном в Бостоне.

Функция SQL MAX

Аналогично работает и имеет аналогичный синтаксис функция SQL MAX, которая применяется, когда требуется определить максимальное значение среди всех значений столбца.

Запрос вернёт значение 18352,80

Пришло время упражнения для самостоятельного решения.

Функция SQL AVG

Указанное в отношении синтаксиса для предыдущих описанных функций верно и в отношении функции SQL AVG. Эта функция возвращает среднее значение среди всех значений столбца.

Результатом будет значение 6,33

В следующем упражнении для самостоятельного решения помимо агрегатной функции требуется использовать также предикат BETWEEN.

Функция SQL COUNT

Результатом будет значение 11.

Результатом будет значение 17.

В следующем упражнении для самостоятельного решения потребуется использовать подзапрос.

Агрегатные функции вместе с SQL GROUP BY (группировкой)

Теперь рассмотрим применение агрегатных функций вместе с оператором SQL GROUP BY. Оператор SQL GROUP BY служит для группировки результирующих значений по столбцам таблицы базы данных. На сайте есть урок, посвящённый отдельно этому оператору.

Результатом будет следующая таблица:

Результатом будет следующая таблица:

Итоговые и индивидуальные значения в одной таблице можно получить объединением результатов запросов с помощью оператора UNION.

Источник

Агрегатные функции SQL: полное руководство

Агрегатные функции SQL извлекают информацию о данных в базе данных. Например, вы можете использовать COUNT, чтобы узнать, сколько записей находится в базе данных. Допустимые агрегатные функции: COUNT, MIN, MAX, AVG и SUM.

Бывают случаи, когда вы можете захотеть получить информацию о данных, но не о самих данных. Возможно, вы хотите узнать, сколько сотрудников работает в каждом офисе или кто проработал в вашем бизнесе дольше всех. Здесь на помощь приходят агрегатные функции SQL.

В этом руководстве мы обсудим основы агрегатных функций SQL, как они работают и как вы можете использовать их в своих запросах.

Примечание. Для целей этой статьи мы используем PostgreSQL.

Агрегатные функции SQL

Агрегатные функции SQL собирают информацию о том, что находится в базе данных. Например, вы можете использовать СУММ, чтобы найти сумму всех значений в столбце. Функции агрегирования экономят ваше время, когда вам нужно найти информацию, которая включает агрегирование записей.,

Вот список агрегатных функций в SQL, которые вы можете использовать:

Давайте рассмотрим каждый из них по отдельности. В этом руководстве мы будем обращаться к базе данных сотрудников.

Агрегатные функции используются в начале оператора SQL SELECT.

Если вы запрашиваете и столбец, и агрегатную функцию в одном операторе SELECT, вы должны использовать предложение SQL GROUP BY. Это предложение сообщает SQL, как представлять данные в вашем запросе.

SQL COUNT

Функция SQL COUNT возвращает общее количество строк, возвращённых запросом. Используя оператор WHERE, функция COUNT возвращает количество строк, соответствующих вашему условию.

Например, вы хотите узнать, сколько сотрудников работает в Стамфордском филиале вашего бизнеса. Вы можете узнать эту информацию, используя следующий запрос:

SELECT COUNT(name) FROM employees WHERE branch = «Stamford»;

Наш запрос возвращает количество сотрудников, работающих в филиале Стэмфорд:

SQL MIN и MAX

Функция SQL MIN возвращает наименьшее значение в столбце. Оператор SQL MAX возвращает наибольшее значение в столбце. Оба этих оператора являются агрегатными функциями SQL.

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

SELECT MIN(employee_month_awards) FROM employees;

Наш запрос возвращает:

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

SELECT MAX(employee_month_awards) FROM employees;

Результат нашего запроса выглядит следующим образом:

Следует отметить, что вы можете использовать функции MIN и MAX как для числовой, так и для буквенной информации в вашей базе данных.

Если вы хотите получить имя, которое появилось первым в алфавите из вашей базы данных, вы можете использовать MIN (имя). Точно так же MAX (имя) можно использовать для получения имени, которое идёт последним в алфавите.

SQL AVG

Функция SQL AVG возвращает среднее значение определённого столбца.

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

SELECT AVG(employee_month_awards) FROM employees;

Наш запрос вычисляет среднее значение сотрудника за месяц и возвращает следующий набор результатов:

SQL SUM

Функция SQL SUM находит общую сумму определённого столбца.

Предположим, вы хотите узнать, сколько наград работника месяца когда-либо было выдано. Эта информация легко доступна, если вы используете SQL-оператор SUM:

SELECT SUM(employee_month_awards) FROM employees;

Наш запрос возвращает следующее:

Вывод

Агрегатные функции SQL возвращают информацию о данных в базе данных. AVG, например, возвращает среднее значение в столбце базы данных. Всего существует пять агрегатных функций: MIN, MAX, COUNT, SUM и AVG.

Источник

Агрегатные функции SQL

Здравствуйте! Сегодня мы познакомимся с агрегатными функциями в SQL, подробно разберем их работу с данными из таблиц, которые создавали в прошлых уроках.

Общее понятие

В прошлом уроке по оператору SELECT мы познакомились с тем, как строятся запросы к данным. Агрегатные функции же существуют для того, чтобы была возможность каким либо образом обобщить полученные данные, то есть манипулировать ими так, как нам это захочется.

Эти функции выполняются с помощью ключевых слов, которые включаются в запрос SELECT, и о том, как они прописываются будет рассказано далее. Чтобы было понятно, вот некоторые возможности агрегатных функций в SQL:

Примеры агрегатных функций SQL

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

Функция SUM

Эта функция позволяет просуммировать значения какого либо поля при запросе SELECT. Достаточно полезная функция, синтаксис которой довольно прост, как и всех других агрегатных функций в SQL. Для понимания сразу начнем с примера:

Получить сумму всех заказов из таблицы Orders, которые были совершены в 2016 году.

Можно было бы просто вывести сумму заказов, но мне кажется, что это совсем просто. Напомним структуру нашей таблицы:

Следующий код осуществит нужную выборку:

В результате получим:

В данном запросе мы использовали функцию SUM, после которой в скобках нужно указать поле для суммирования. Затем мы указали условие в WHERE, которое отобрало строчки только с 2016 годом. На самом деле это условие можно записать по другому, но сейчас важнее агрегатная функция суммирования в SQL.

Функция AVG

Следующая функция осуществляет подсчет среднего арифметического поля данных, которое мы укажем в качестве параметра. Синтаксис такой функции идентичен функции суммирования. Поэтому сразу перейдем к простейшей задаче:

Вывести среднюю стоимость заказа из таблицы Orders.

В результате получим:

В целом, все похоже на предыдущую функцию. И синтаксис достаточно прост. В этом и состоит особенность языка SQL — быть понятным для человека.

Функции MIN и MAX

Еще 2 функции, которые близки по своему действию. Они находят минимальное или максимальное значение соответственно того параметра, который будет передан в скобках. Синтаксис повторяется и поэтому следующий пример:

Вывести максимальное и минимальное значения цены заказа, для тех заказов в которых цена менее 1000.

Получается такой запрос,

Также стоит сказать, что в отличие от предыдущих функций, эти 2 могут работать с символьными параметрами, то есть можно написать запрос типа MIN(odate) (в данном случае дата у нас символьная), и тогда нам вернется 2016-01-01.

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

Еще одним важным моментом является то, что мы можем производить некоторые простые математические операции в запросе SELECT, например, такой запрос:

Вернет такой ответ:

Функция COUNT

Эта функция необходима для того, чтобы подсчитать количество выбранных значений или строк. Существует два основных варианта ее использования:

Теперь разберем пример использования COUNT в SQL:

Подсчитать количество сделанных заказов и количество продавцов в таблице Orders.

Очевидно, что количество заказов — 10, но если вдруг у вас имеется большая таблица, то такая функция будет очень удобной. Что касается уникальных продавцов, то здесь необходимо использовать DISTINCT, потому что один продавец может обслужить несколько заказов.

Оператор GROUP BY

Теперь рассмотрим 2 важных оператора, которые помогают расширить функционал наших запросов в SQL. Первым из них является оператор GROUP BY, который осуществляет группировку по какому либо полю, что иногда является необходимым. И уже для этой группы производит заданное действие. Например:

Вывести сумму всех заказов для каждого продавца по отдельности.

То есть теперь нам нужно для каждого продавца в таблице Orders выделить поля с ценой заказа и просуммировать. Все это сделает оператор GROUP BY в SQL достаточно легко:

Как видно, SQL выделил группу для каждого продавца и посчитал сумму всех их заказов.

Оператор HAVING

Этот оператор используется как дополнение к предыдущему. Он необходим для того, чтобы ставить условия для выборки данных при группировке. Если условие выполняется то выделяется группа, если нет — то ничего не произойдет. Рассмотрим следующий код:

Который создаст группу для продавца и посчитает сумму заказов этой группы, только в том случае, если максимальная сумма заказа больше 1000. Очевидно, что такой продавец только один, для него выделится группа и посчитается сумма всех заказов:

Казалось бы, почему тут не использовать условие WHERE, но SQL так построен, что в таком случае выдаст ошибку, и именно поэтому в SQL есть оператор HAVING.

Примеры на агрегатные функции в SQL

1. Напишите запрос, который сосчитал бы все суммы заказов, выполненных 1 января 2016 года.

2. Напишите запрос, который сосчитал бы число различных, отличных от NULL значений поля city в таблице заказчиков.

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

4. Напишите запрос, который бы выбирал заказчиков чьи имена начинаются с буквы Г.

5. Напишите запрос, который выбрал бы высший рейтинг в каждом городе.

Заключение

На этом мы будем заканчивать. В этой статье мы познакомились с агрегатными функциями в SQL. Разобрали основные понятия и базовые примеры, которые могут пригодиться далее.

Если у вас остались вопросы, то задавайте их в комментариях.

Источник

Агрегатные функции в SQL: объяснение с примерами запросов

Перевод статьи «SQL Aggregate Functions – With Example Data Queries for Beginners».

Если вы когда-либо работали с таблицами Excel или Google Sheets, агрегатные функции SQL покажутся вам знакомыми. Например, вам случалось использовать SUM при работе с таблицами? Функция SUM есть и в SQL. Она как раз относится к агрегатным функциям.

Агрегатные функции выполняют конкретные действия со строками таблиц.

Допустим, вы каждый год запускаете сбор денег на какие-то нужды. У вас есть база жертвователей, где хранятся их имена, адреса электронной почты и перечисленные суммы (по годам).

При помощи функции COUNT вы можете определить, сколько всего пожертвований было сделано. А при помощи SUM можно вычислить общую сумму денег, которую удалось собрать в этом году.

В этой статье мы рассмотрим следующие агрегатные функции: COUNT, SUM, MIN/MAX и AVG. А для иллюстрации будем использовать небольшой набор данных:

Таблица donors

Функция COUNT

Функция COUNT возвращает количество строк. В самой простой форме COUNT подсчитывает общее количество строк в вашей таблице.

Вернется общее число жертвователей, в нашем случае это 6. Мы, конечно, и так видим, что их 6, но представьте, что таблица у нас очень большая.

Возможно, вам нужно сосчитать только какие-то определенные строки. Например, вывести число жертвователей, у которых указан адрес электронной почты.

Функция SUM

SUM — очень полезная агрегатная функция, с помощью которой можно складывать числовые значения в различных строках.

Поэтому в нашем примере при помощи функции SUM можно сложить все донаты всех жертвователей за определенный год, но не суммы, перечисленные каждым отдельным человеком за два года.

Функции MIN и MAX

Как вы наверняка догадываетесь, MIN и MAX используются для поиска минимального и максимального значений в определенном столбце базы данных.

Вернемся к нашему примеру. Допустим, нам нужно найти, каким было самое маленькое и самое большое пожертвование в 2021 году. Для этого мы можем запустить следующий запрос:

Обратите внимание, что здесь для возвращаемых столбцов мы добавили псевдонимы, при этом взяли их в кавычки. Если в вашем псевдониме нет пробелов, кавычки не обязательны, но у нас пробелы есть.

Любопытный факт: функции MIN и MAX можно использовать и для нечисловых значений.

MIN ищет самое маленькое число, букву, которая в алфавите стоит ближе всего к A, или самую раннюю дату. Функция MAX ищет самое большое число, букву, стоящую ближе всего к Z, или самую последнюю дату. Это очень полезная особенность!

Функция AVG

Чтобы получить средний размер пожертвований в 2020 году, можно запустить следующий запрос:

Итоги

Источник

Пользовательские агрегатные и оконные функции в PostgreSQL и Oracle

В этой статье мы посмотрим, как в двух системах создавать пользовательские агрегатные и оконные (в терминологии Oracle — аналитические) функции. Несмотря на различия в синтаксисе и в целом в подходе к расширяемости, механизм этих функций очень похож. Но и различия тоже имеются.

Надо признать, что собственные агрегатные и оконные функции встречается довольно редко. Оконные функции вообще по каким-то причинам традиционно относят к разряду «продвинутого» SQL и считают сложными для понимания и освоения. Тут бы разобраться с теми функциями, которые уже имеются в СУБД!

Зачем тогда вообще вникать в этот вопрос? Могу назвать несколько причин:

Агрегатные функции

Будем двигаться от простого к сложному, переключаясь между PostgreSQL и Oracle.

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

Итак, нам потребуется четыре составляющие:

PostgreSQL

Для хранения состояния нужно выбрать подходящий тип данных. Можно взять стандартный, а можно определить свой. Для функции, вычисляющей среднее, нужно отдельно суммировать значения и отдельно подсчитывать их количество. Поэтому создадим свой составной тип с двумя полями:

CREATE TYPE average_state AS (
accum numeric,
qty numeric
);

Теперь определим функцию для обработки очередного значения. В PostgreSQL она называется функцией перехода:

Кроме этого, мы выводим (RAISE NOTICE) параметры функции — это позволит нам увидеть, как выполняется работа. Старый добрый отладочный PRINT, нет ничего тебя лучше.

Следующая функция — возвращение финального значения:

«Финт ушами» с функцией trim нужен исключительно для аккуратности вывода: таким образом мы избавляемся от незначащих нулей, которые иначе будут загромождать экран и мешать восприятию. Примерно вот так:

SELECT 1::numeric / 2::numeric;
?column?
————————
0.50000000000000000000
(1 row)

В реальной жизни эти фокусы, конечно, не нужны.

И, наконец, определяем собственно агрегатную функцию. Для этого используется специальная команда CREATE AGGREGATE:

Можно пробовать. Почти все примеры в этой статье будут использовать простую таблицу с пятью строками: раз, два, три, четыре, пять. Таблицу создаем на лету функцией generate_series, незаменимым помощником генерации тестовых данных:

SELECT average(g.x) FROM generate_series(1,5) AS g(x);
NOTICE: 0(0) + 1
NOTICE: 1(1) + 2
NOTICE: 3(2) + 3
NOTICE: 6(3) + 4
NOTICE: 10(4) + 5
NOTICE: = 15(5)
average
———
3
(1 row)

Результат верный, а вывод функций позволяет проследить ход выполнения:

SELECT average(g.x) FROM generate_series(1,0) AS g(x);
NOTICE: = 0(0)
average
———

Oracle

В Oracle вся расширяемость обеспечивается механизмом Data Cartridge. Говоря по-простому, нам потребуется создать объектный тип, реализующий необходимый для агрегации интерфейс. Контекст естественным образом представляется атрибутами этого объекта.

CREATE OR REPLACE TYPE AverageImpl AS OBJECT(
accum number,
qty number,
STATIC FUNCTION ODCIAggregateInitialize (actx IN OUT AverageImpl)
RETURN number,
MEMBER FUNCTION ODCIAggregateIterate (self IN OUT AverageImpl, val IN number
RETURN number,
MEMBER FUNCTION ODCIAggregateMerge (self IN OUT AverageImpl, ctx2 IN AverageImpl)
RETURN number,
MEMBER FUNCTION ODCIAggregateTerminate (self IN OUT AverageImpl, returnValue OUT number, flags IN number)
RETURN number
);
/

Начальное значение контекста определяется здесь не константой, а отдельной (статической, то есть не привязанной к конкретному экземпляру объекта) функцией ODCIAggregateInitialize.

Функция, вызываемая для каждой строки — это ODCIAggregateIterate.

Результат возвращает функция ODCIAggregateTerminate, и ей, заметьте, передаются некие флаги, с которыми мы разберемся чуть позже.

Интерфейс включает еще одну обязательную функцию: ODCIAggregateMerge. Мы ее определим — куда ж деваться, — но разговор о ней пока отложим.

Теперь создадим тело объекта с реализацией перечисленных методов.

CREATE OR REPLACE TYPE BODY AverageImpl IS
STATIC FUNCTION ODCIAggregateInitialize (actx IN OUT AverageImpl)
RETURN number IS
BEGIN
actx := AverageImpl(0,0);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate (self IN OUT AverageImpl, val IN number)
RETURN number IS
BEGIN
dbms_output.put_line(self.accum||'(‘||self.qty||’) + ‘||val);
self.accum := self.accum + val;
self.qty := self.qty + 1;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge (self IN OUT AverageImpl, ctx2 IN AverageImpl)
RETURN number IS
BEGIN
dbms_output.put_line(self.accum||'(‘||self.qty||’) & ‘||ctx2.accum||'(‘||ctx2.qty||’)’);
self.accum := self.accum + ctx2.accum;
self.qty := self.qty + ctx2.qty;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate (self IN OUT AverageImpl, returnValue OUT number, flags IN number)
RETURN number IS
BEGIN
dbms_output.put_line(‘= ‘||self.accum||'(‘||self.qty||’) flags:’||flags);
returnValue := CASE WHEN self.qty > 0 THEN self.accum / self.qty END;
RETURN ODCIConst.Success;
END;
END;
/

Реализация, по большей части, повторяет все то, что мы делали для PostgreSQL, но в немного другом синтаксисе.

Trim-пляски вокруг возвращаемого значения не нужны: Oracle самостоятельно отрезает незначащие нули при выводе значения.

Обратите внимание, что все функции возвращают признак успешности выполнения (значение ODCIConst.Success), а смысловые значения передаются через параметры OUT и IN OUT (которые в PL/SQL никак не связаны с собственно возвращаемым значением, как в PL/pgSQL). В частности, любая функция, в том числе и ODCIAggregateTerminate, может изменять атрибуты своего объекта, ссылка на который передается ей в первом параметре (self).

Определение агрегатной функции выглядит следующим образом:

CREATE OR REPLACE FUNCTION average(val number) RETURN number
AGGREGATE USING AverageImpl;
/

Проверяем. Для генерации значений используем идиоматическую конструкцию с рекурсивным запросом CONNECT BY level:

SELECT average(level) FROM dual CONNECT BY level
Можно обратить внимание на то, что вывод сообщений в PostgreSQL появляется до результата, а в Oracle — после. Это из-за того, что RAISE NOTICE работает асинхронно, а пакет dbms_output буферизует вывод.

Как мы видим, в функцию ODCIAggregateTerminate был передан нулевой флаг. Это означает, что контекст больше не требуется и его — при желании — можно забыть.

И проверка на пустом множестве:

SELECT average(rownum) FROM dual WHERE 1 = 0;
AVERAGE(ROWNUM)
—————

Оконные функции: OVER()

Хорошая новость: написанная нами агрегатная функция может без всяких изменений работать и как оконная (аналитическая).

Оконная функция отличается от агрегатной тем, что не сворачивает выборку в одну (агрегированную) строку, а вычисляется как бы отдельно для каждой строки. Синтаксически вызов оконной функции отличается наличием конструкции OVER с указанием рамки, которая определяет множество строк для обработки. В простейшем случае она так и записывается: OVER(), и это означает, что функция должна обработать все строки. Результат получается такой, как будто мы посчитали обычную агрегатную функцию и записали результат (один и тот же) напротив каждой строки выборки.

Иными словами, рамка статична и охватывает все строки:

PostgreSQL

SELECT g.x, average(g.x) OVER ()
FROM generate_series(1,5) as g(x);
NOTICE: 0(0) + 1
NOTICE: 1(1) + 2
NOTICE: 3(2) + 3
NOTICE: 6(3) + 4
NOTICE: 10(4) + 5
NOTICE: = 15(5)
x | average
—+———
1 | 3
2 | 3
3 | 3
4 | 3
5 | 3
(5 rows)

По выводу NOTICE видно, что все происходит точно так же, как и ранее при вычислении обычной агрегатной функции. Получив результат от функции average_final, PostgreSQL проставляет его в каждой строке.

Oracle

SELECT average(level) OVER() average
FROM dual CONNECT BY level
Неожиданно. Вместо того, чтобы вычислить результат один раз, Oracle вызывает функцию ODCIAggregateTerminate N+1 раз: сначала для каждой строки с флагом 1 (что означает, что контекст еще пригодится) и затем еще один раз в конце. Значение, полученное при последнем вызове, просто игнорируется.

Вывод такой: если в функции ODCIAggregateTerminate используется вычислительно сложная логика, надо подумать о том, чтобы не делать одну и ту же работу несколько раз.

Оконные функции: OVER(PARTITION BY)

Предложение PARTITION BY в определении рамки похоже на обычную агрегатную конструкцию GROUP BY. Оконная функция с указанием PARTITION BY вычисляется отдельно для каждой группы строк, и результат приписывается к каждой строке выборки.

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

PostgreSQL

SELECT g.x/3 part,
g.x,
average(g.x) OVER (PARTITION BY g.x/3)
FROM generate_series(1,5) as g(x);
NOTICE: 0(0) + 1
NOTICE: 1(1) + 2
NOTICE: = 3(2)
NOTICE: 0(0) + 3
NOTICE: 3(1) + 4
NOTICE: 7(2) + 5
NOTICE: = 12(3)
part | x | average
——+—+———
0 | 1 | 1.5
0 | 2 | 1.5
1 | 3 | 4
1 | 4 | 4
1 | 5 | 4
(5 rows)

Вычисление снова происходит последовательно, но теперь при переходе к другой группе строк состояние сбрасывается в начальное значение (initcond).

Oracle

SELECT trunc(level/3) part,
level,
average(level) OVER(PARTITION BY trunc(level/3)) average
FROM dual CONNECT BY level 0(0) + 2
2(1) + 1
= 3(2) flags:1
= 3(2) flags:1
0(0) + 4
4(1) + 5
9(2) + 3
= 12(3) flags:1
= 12(3) flags:1
= 12(3) flags:1
= 12(3) flags:0

Занятно, что Oracle решил переставить строки местами. Это может что-то сказать о деталях реализации, но в любом случае — имеет право.

Оконные функции: OVER(ORDER BY)

Если в определение рамки добавить предложение ORDER BY, указывающее порядок сортировки, функция начнет работать в режиме нарастания (для функции sum мы бы так и сказали — нарастающим итогом).

Для первой строки рамка будет состоять из одной этой строки; для второй — из первой и второй; для третьей — из первой, второй и третьей и так далее. Иными словами, в рамку будут входить строки с первой до текущей.

На самом деле, это можно ровно так и записать: OVER(ORDER BY… ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), но, поскольку это многословие подразумеваются по умолчанию, его обычно опускают.

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

PostgreSQL

SELECT g.x, average(g.x) OVER (ORDER BY g.x)
FROM generate_series(1,5) as g(x);
NOTICE: 0(0) + 1
NOTICE: = 1(1)
NOTICE: 1(1) + 2
NOTICE: = 3(2)
NOTICE: 3(2) + 3
NOTICE: = 6(3)
NOTICE: 6(3) + 4
NOTICE: = 10(4)
NOTICE: 10(4) + 5
NOTICE: = 15(5)
x | average
—+———
1 | 1
2 | 1.5
3 | 2
4 | 2.5
5 | 3
(5 rows)

Как видим, строки все так же добавляются к контексту по одной, но теперь функция average_final вызывается после каждого добавления, выдавая промежуточный итог.

Oracle

SELECT level, average(level) OVER(ORDER BY level) average
FROM dual CONNECT BY level
На этот раз обе системы работают одинаково.

Оконные функции: OVER(PARTITION BY ORDER BY)

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

PostgreSQL

Oracle

Оконные функции со скользящей рамкой

Во всех примерах, которые мы посмотрели, рамка либо была статической, либо двигалась только ее голова (при использовании предложения ORDER BY). Это давало нам возможность вычислять состояние последовательно, добавляя к контексту строку за строкой.

Но рамку оконной функции можно задать и таким образом, что ее хвост тоже будет смещаться. В нашем примере это будет соответствовать понятию скользящего среднего. Например, указание OVER(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) говорит о том, что для каждой строки результата будут усредняться текущее и два предыдущих значений.

Сможет ли вычисляться оконная функция в таком случае? Оказывается, сможет, правда неэффективно. Но, написав еще немного кода, можно улучшить ситуацию.

PostgreSQL

SELECT g.x,
average(g.x) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM generate_series(1,5) as g(x);
NOTICE: 0(0) + 1
NOTICE: = 1(1)
NOTICE: 1(1) + 2
NOTICE: = 3(2)
NOTICE: 3(2) + 3
NOTICE: = 6(3)
NOTICE: 0(0) + 2
NOTICE: 2(1) + 3
NOTICE: 5(2) + 4
NOTICE: = 9(3)
NOTICE: 0(0) + 3
NOTICE: 3(1) + 4
NOTICE: 7(2) + 5
NOTICE: = 12(3)
x | average
—+———
1 | 1
2 | 1.5
3 | 2
4 | 3
5 | 4
(5 rows)

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

Итак, было бы здорово иметь не только функцию добавления очередного значения, но и функцию удаления значения из состояния. И действительно, такую функцию можно создать:

Чтобы оконная функция смогла ей воспользоваться, нужно пересоздать агрегат следующим образом:

Oracle

Тут ситуация аналогична. Созданный вариант аналитической функции работает, но неэффективно:

SELECT level,
average(level) OVER(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) average
FROM dual CONNECT BY level 0(0) + 2
2(1) + 3
5(2) + 4
= 9(3) flags:1
0(0) + 3
3(1) + 4
7(2) + 5
= 12(3) flags:1
= 12(3) flags:0

Функция удаления значения из контекста определяется следующим образом:

Пересоздавать саму функцию не нужно. Проверим:

Параллельность

И PostgreSQL, и Oracle (Enterprise Edition) умеют вычислять агрегатные функции в параллельном режиме. При этом каждый из параллельных процессов выполняет свою часть работы, формируя промежуточное состояние. Затем основной процесс-координатор получает эти несколько состояний и должен объединить их в одно итоговое.

Для этого нужна еще одна функция объединения, которую мы сейчас и напишем. В нашем случае она просто складывает и суммы, и количество значений.

PostgreSQL

Функция выглядит следующим образом:

Поскольку мы убираем вывод, то отпадает и необходимость использовать процедурный язык — напишем функцию на чистом SQL:

CREATE TABLE t(n) AS SELECT generate_series(1,1000)::numeric;

С настройками по умолчанию PostgreSQL не построит параллельный план для такой таблицы — слишком она мала, — но его несложно уговорить:

SET parallel_setup_cost=0;
SET min_parallel_table_scan_size=0;

EXPLAIN(costs off) SELECT average(n) FROM t;
QUERY PLAN
——————————————
Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Parallel Seq Scan on t

В плане запроса видим:

SELECT average(n) FROM t;
NOTICE: 0(0) & 281257(678)
NOTICE: 281257(678) & 127803(226)
NOTICE: 409060(904) & 91440(96)
NOTICE: = 500500(1000)
average
———
500.5
(1 row)

Почему функция average_combine вызывается три раза, а не два? Дело в том, что в PostgreSQL координирующий процесс тоже выполняет часть работы. Поэтому, хотя было запущено два рабочих процесса, реально работа выполнялась в трех. Один из них успел обработать 678 строк, другой 226 и третий — 96 (хотя эти цифры ничего не значат и при другом запуске могут отличаться).

Oracle

Если помните, функцию ODCIAggregateMerge мы уже написали в самом начале, поскольку в Oracle она является обязательной. Документация настаивает, что эта функция необходима не только для параллельной работы, но и для последовательной — хотя мне трудно понять, зачем (и на практике не приходилось сталкиваться с ее выполнением при последовательной обработке).

Все, что остается сделать — объявить функцию безопасной для параллельной работы:

CREATE OR REPLACE FUNCTION average(val number) RETURN number
PARALLEL_ENABLE
AGGREGATE USING AverageImpl;
/

Создаем таблицу:

CREATE TABLE t(n) AS SELECT to_number(level) FROM dual CONNECT BY level
Уговорить Oracle еще проще, чем PostgreSQL — достаточно написать хинт. Вот какой получается план (вывод сильно урезан для простоты):

EXPLAIN PLAN FOR SELECT /*+ PARALLEL(2) */ average(n) FROM t;
SELECT * FROM TABLE(dbms_xplan.display);
———————————
| Id | Operation |
———————————
| 0 | SELECT STATEMENT |
| 1 | SORT AGGREGATE |
| 2 | PX COORDINATOR |
| 3 | PX SEND QC (RANDOM) |
| 4 | SORT AGGREGATE |
| 5 | PX BLOCK ITERATOR |
| 6 | TABLE ACCESS FULL |
———————————

План также содержит:

Документация

Самое время привести ссылки на документацию, в том числе и на агрегатные и оконные функции, уже включенным в СУБД. Там можно найти много интересного.

Пример про округление копеек

И обещанный пример из жизни. Эту функцию я придумал, когда приходилось писать отчеты для бухгалтерии, работающей по РСБУ (правилам российского бухучета).

Самая простая задача, в которой возникает необходимость округления — распределение общих расходов (скажем, 100 рублей) на отделы (скажем, 3 штуки) по какому-то принципу (скажем, поровну):

WITH depts(name) AS (
VALUES (‘A’), (‘B’), (‘C’)
), report(dept,amount) AS (
SELECT name, 100.00 / count(*) OVER() FROM depts
)
SELECT dept, round(amount,2) FROM report;
dept | round
——+——-
A | 33.33
B | 33.33
C | 33.33
(3 rows)

Этот запрос показывает проблему: суммы надо округлять, но при этом теряется копейка. А РСБУ этого не прощает.

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

WITH depts(name) AS (
VALUES (‘A’), (‘B’), (‘C’)
), report(dept,amount) AS (
SELECT name, 100.00 / count(*) OVER() FROM depts
)
SELECT dept, round2(amount) OVER (ORDER BY dept) FROM report;
dept | round2
——+———
A | 33.33
B | 33.34
C | 33.33
(3 rows)

Состояние такой функции включает ошибку округления (r_error) и текущее округленное значение (amount). Функция обработки очередного значения увеличивает ошибку округления, и, если она уже превышает полкопейки, добавляет к округленной сумме копеечку:

Полный код функции приводить не буду: используя уже приведенные примеры написать ее не представляет сложности.

Если вам встречались интересные примеры использования собственных агрегатных или оконных функций — поделитесь ими в комментариях.

Источник