CREATE AGGREGATE
CREATE AGGREGATE — определить новую агрегатную функцию
Синтаксис
CREATE [ OR REPLACE ] AGGREGATE имя ( [ режим_аргумента ] [ имя_аргумента ] тип_данных_аргумента [ , ... ] ) (
SFUNC = функция_состояния,
STYPE = тип_данных_состояния
[ , SSPACE = размер_данных_состояния ]
[ , FINALFUNC = функция_завершения ]
[ , FINALFUNC_EXTRA ]
[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
[ , COMBINEFUNC = комбинирующая_функция ]
[ , SERIALFUNC = функция_сериализации ]
[ , DESERIALFUNC = функция_десериализации ]
[ , INITCOND = начальное_условие ]
[ , MSFUNC = функция_состояния_движения ]
[ , MINVFUNC = обратная_функция_движения ]
[ , MSTYPE = тип_данных_состояния_движения ]
[ , MSSPACE = размер_данных_состояния_движения ]
[ , MFINALFUNC = функция_завершения_движения ]
[ , MFINALFUNC_EXTRA ]
[ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
[ , MINITCOND = начальное_условие_движения ]
[ , SORTOP = оператор_сортировки ]
[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
)
CREATE [ OR REPLACE ] AGGREGATE имя ( [ [ режим_аргумента ] [ имя_аргумента ] тип_данных_аргумента [ , ... ] ]
ORDER BY [ режим_аргумента ] [ имя_аргумента ] тип_данных_аргумента [ , ... ] ) (
SFUNC = функция_состояния,
STYPE = тип_данных_состояния
[ , SSPACE = размер_данных_состояния ]
[ , FINALFUNC = функция_завершения ]
[ , FINALFUNC_EXTRA ]
[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
[ , INITCOND = начальное_условие ]
[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
[ , HYPOTHETICAL ]
)
или старый синтаксис:
CREATE [ OR REPLACE ] AGGREGATE имя (
BASETYPE = базовый_тип,
SFUNC = функция_состояния,
STYPE = тип_данных_состояния
[ , SSPACE = размер_данных_состояния ]
[ , FINALFUNC = функция_завершения ]
[ , FINALFUNC_EXTRA ]
[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
[ , COMBINEFUNC = комбинирующая_функция ]
[ , SERIALFUNC = функция_сериализации ]
[ , DESERIALFUNC = функция_десериализации ]
[ , INITCOND = начальное_условие ]
[ , MSFUNC = функция_состояния_движения ]
[ , MINVFUNC = обратная_функция_движения ]
[ , MSTYPE = тип_данных_состояния_движения ]
[ , MSSPACE = размер_данных_состояния_движения ]
[ , MFINALFUNC = функция_завершения_движения ]
[ , MFINALFUNC_EXTRA ]
[ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
[ , MINITCOND = начальное_условие_движения ]
[ , SORTOP = оператор_сортировки ]
)
Описание
Команда CREATE AGGREGATE определяет новую агрегатную функцию. CREATE OR REPLACE AGGREGATE будет либо определять новую агрегатную функцию, либо заменять определение
уже существующей. Некоторые основные и часто используемые агрегатные функции
включены в дистрибутив; они описаны в разделе Агрегатные функции. При определении
новых типов или возникновении потребности в агрегатной функции, которая еще не
предоставлена, CREATE AGGREGATE можно использовать для получения требуемых объектов.
При замене существующего определения типы аргументов, тип результата и количество непосредственных аргументов изменить нельзя. Кроме того, новое определение должно быть того же вида (обычный, сортирующий или гипотезирующий агрегат), что и старое.
Если задано имя схемы (например CREATE AGGREGATE myschema.myagg ...), то
агрегатная функция создается в указанной схеме. В противном случае — в текущей.
Агрегатная функция идентифицируется по имени, а также по типу (или типам) входных
данных. Два агрегата в одной схеме могут иметь одно и то же имя, если они
работают с разными типами входных данных. Имя и тип (или типы) входных данных
агрегата также должны отличаться от имени и типа (или типов) входных данных
простых функций в той же схеме. Это поведение идентично перегрузке обычных имен
функций (см. CREATE FUNCTION).
Простая агрегатная функция состоит из одной или двух обычных функций: функции перехода состояния функция_состояния и необязательной функции окончательного вычисления функция_завершения. Они используются следующим образом:
функция_состояния( внутреннее-состояние, следующие-значения-данных ) ---> следующее-внутреннее-состояние
функция_завершения( внутреннее-состояние ) ---> агрегатное-значение
QHB создает временную переменную типа данных тип_данных_состояния, чтобы сохранить текущее внутреннее состояние агрегата. В каждой входной строке вычисляются значения агрегированных аргументов и вызывается функция перехода состояния с текущим значением состояния и значениями новых аргументов для вычисления нового значения внутреннего состояния. После обработки всех строк однократно вызывается завершающая функция для вычисления возвращаемого агрегатом значения. Если нет завершающей функции, то значение конечного состояния возвращается как есть.
Агрегатная функция может предоставить начальное условие, то есть начальное значение для значения внутреннего состояния. Это значение задается и сохраняется в базе данных в виде строки типа text, но оно должно быть допустимым внешним представлением константы типа данных значения состояния. Если оно не предоставляется, то значение состояния начинается с NULL.
Если функция перехода состояния объявлена как «strict» (строгая), то ее нельзя вызвать с аргументами NULL. С такой функцией перехода выполнение агрегата происходит следующим образом. Строки с входными значениями NULL игнорируются (функция не вызывается, и сохраняется предыдущее значение состояния). Если начальное значение состояния равно NULL, то в первой строке со всеми входными значениями, отличными от NULL, значение первого аргумента заменяется значением состояния, а функция перехода вызывается в каждой последующей строке со всеми входными значениями, отличными от NULL. Это удобно для реализации агрегатов вроде max. Обратите внимание, что это поведение возможно только тогда, когда тип_данных_состояния совпадает с первым типом_данных_аргумента. Если эти типы отличаются, необходимо указать начальное условие, отличное от NULL, или использовать нестрогую функцию перехода.
Если функция перехода состояния не является строгой, то она будет вызываться безусловно в каждой входной строке и должна сама обрабатывать вводимые значения и значения состояния, равные NULL. Это позволяет разработчику агрегата полностью управлять тем, как она воспринимает значения NULL.
Если функция завершения объявлена как «strict» (строгая), то она не будет вызываться, если значение конечного состояния равно NULL; вместо этого будет автоматически возвращаться результат NULL. (Разумеется, это всего лишь обычное поведение строгих функций.) В любом случае функция завершения имеет возможность возвращать результат NULL. Например, функция завершения для avg возвращает NULL, если определяет, что было обработано ноль входных строк
Иногда бывает полезно объявить функцию завершения как принимающую не только значение состояния, но и дополнительные параметры, соответствующие входным значениям агрегата. В основном это имеет смысл, если функция завершения является полиморфной и типа данных значения состояния было бы недостаточно, чтобы вывести тип результата. Эти дополнительные параметры всегда передаются как NULL (поэтому функция завершения не должна быть строгой, когда применяется параметр FINALFUNC_EXTRA), но в остальном это допустимые параметры. Функция завершения может, например, использовать get_fn_expr_argtype, чтобы определить фактический тип аргумента в текущем вызове.
Агрегатная функция может дополнительно поддерживать режим движущегося агрегата, как описано в подразделе Режим движущегося агрегата. Для этого необходимо указать параметры MSFUNC, MINVFUNC и MSTYPE, а также можно указать необязательные параметры MSSPACE, MFINALFUNC, MFINALFUNC_EXTRA, MFINALFUNC_MODIFY и MINITCOND. За исключением MINVFUNC, эти параметры работают как соответствующие параметры простого агрегата без начальной буквы M; они определяют отдельную реализацию агрегата, включающую функцию обратного перехода.
Если в список параметров добавлено указание ORDER BY, создается особый типа агрегата, называемый сортирующим агрегатом; с указанием HYPOTHETICAL создается гипотезирующий агрегат. Эти агрегаты работают над группами отсортированных значений в зависимости от порядка сортировки, так что указание порядка сортировки входных данных является неотъемлемой частью вызова. Кроме того, они могут иметь непосредственные аргументы, которые вычисляются единожды для всей процедуры агрегирования, а не для каждой входной строки. Гипотезирующие агрегаты — это подкласс сортирующих агрегатов, в котором часть непосредственных аргументов должна соответствовать столбцам агрегированных аргументов по количеству и типам данных. Это позволяет добавить значения этих непосредственных аргументов в набор агрегированных строк в качестве дополнительной «гипотетической» строки.
Агрегатная функция может дополнительно поддерживать частичное агрегирование, как описано в подразделе Частичная агрегация. Для этого необходимо задать параметр COMBINEFUNC. Если в качестве типа_данных_состояния выбран internal, обычно также указывают параметры SERIALFUNC и DESERIALFUN, чтобы было возможно параллельное агрегирование. Обратите внимание, что для возможности параллельного агрегирования агрегат также должен быть помечен как PARALLEL SAFE.
Агрегаты, ведущие себя как MIN и MAX, иногда можно оптимизировать, заменив сканирование каждой входной строки обращением по индексу. Если агрегат можно оптимизировать таким образом, обозначьте это, указав оператор сортировки. Основное требование состоит в том, что агрегат должен выдавать первый элемент по порядку сортировки, задаваемому этим оператором, другими словами:
SELECT agg(col) FROM tab;
должно быть равнозначно:
SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
Дополнительно предполагается, что агрегат игнорирует аргументы NULL и возвращает
результат NULL тогда и только тогда, когда строки со значениями не NULL отсутствуют.
Обычно для MIN подходящим оператором сортировки является оператор <, а для
MAX — >. Обратите внимание, что оптимизация ни на что не повлияет, если заданный
оператор не является членом стратегии «меньше, чем» или «больше, чем» в классе
операторов индекса B-дерева.
Чтобы иметь возможность создать агрегатную функцию, необходимо иметь право USAGE для типов аргументов, типа (или типов) состояния и типа результата, а также право EXECUTE для вспомогательных функций.
Параметры
имя
Имя создаваемой агрегатной функции (может быть дополнено схемой).
режим_аргумента
Режим аргумента: IN (входной) или VARIADIC (переменный). (Агрегатные функции не поддерживают аргументы OUT (выходные).) Если этот параметр опущен, значение по умолчанию равно IN. Только последний аргумент может быть помечен как VARIADIC.
имя_аргумента
Имя аргумента. В настоящее время используется только для целей документирования. Если опущено, соответствующий аргумент будет безымянным.
тип_данных_аргумента
Тип входных данных, с которым работает эта агрегатная функция. Чтобы создать агрегатную функцию без аргументов, вместо списка определений аргументов напишите *. (Примером такого агрегата является count(*).)
базовый_тип
В старом синтаксисе для CREATE AGGREGATE тип входных данных задается параметром
базовый_тип, а не записывается после имени агрегата. Обратите внимание, что
этот синтаксис допускает только один входной параметр. Чтобы с помощью этого
синтаксиса определить агрегатную функцию без аргументов, в качестве значения
базового_типа нужно указать ANY (не *). Создать сортирующий агрегат
старый синтаксис не позволяет.
функция_состояния
Имя функции перехода состояния, вызываемой для каждой входной строки. Для обычных агрегатных функций с N аргументами функция_состояния должна принимать N+1 аргументов, первый должен иметь тип тип_данных_состояния, а остальные — соответствовать заявленным типам входных данных агрегата. Функция должна возвращать значение типа тип_данных_состояния. Эта функция принимает текущее значение состояния и текущее значение (или значения) входных данных, а возвращает следующее значение состояния.
В сортирующих (в том числе гипотезирующих) агрегатах функция перехода состояния получает только текущее значение состояния и агрегированные аргументы, а не непосредственные аргументы. В остальном это одно и то же.
тип_данных_состояния
Тип данных значения состояния агрегатной функции.
размер_данных_состояния
Приблизительный средний размер (в байтах) значения состояния агрегата. Если этот параметр опущен или равен нулю, используется оценка по умолчанию, определяемая на основе типа_данных_состояния. Планировщик использует это значение для оценки памяти, необходимой для агрегатного запроса с группировкой.
функция_завершения
Имя функции завершения, вызываемой для вычисления результата агрегатной функции после обработки всех входных строк. Для обычного агрегата эта функция должна принимать один аргумент типа тип_данных_состояния. Возвращаемый тип данных агрегата определяется как возвращаемый тип этой функции. Если функция_завершения не указана, то в качестве результата агрегирования используется значение конечного состояния, а типом возвращаемого значения является тип_данных_состояния.
В сортирующих (в том числе гипотезирующих) агрегатах функция завершения получает не только значение конечного состояния, но и значения всех непосредственных аргументов.
Если указано FINALFUNC_EXTRA, то в дополнение к значению конечного состояния и всем непосредственным аргументам конечная функция получает дополнительные значения NULL, соответствующие обычным (агрегированным) аргументам агрегата. Это полезно в основном для обеспечения правильного определения типа результата агрегата при определении полиморфной агрегатной функции.
FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }
Этот параметр указывает, является ли функция завершения чистой функцией, которая не изменяет свои аргументы. Это свойство функции передает значение READ_ONLY; другие два значения указывают, что она может изменить значение состояния перехода. Подробнее это рассматривается в Примечаниях ниже. По умолчанию значение равно READ_ONLY, за исключением сортирующих агрегатов (для них значение по умолчанию — READ_WRITE).
комбинирующая_функция
Можно дополнительно задать комбинирующую_функцию, чтобы разрешить агрегатной функции поддерживать частичное агрегирование. Если комбинирующая_функция предоставляется, она должна комбинировать два значения типа_данных_состояния, каждое из которых содержит результат агрегирования по некоторому подмножеству входных значений, чтобы получить новое значение типа_данных_состояния, представляющее результат агрегирования по обоим наборам данных. Эту функцию можно рассматривать как функцию_состояния, которая вместо обработки отдельной входной строки и добавления ее данных в текущее агрегатное состояние включает еще одно агрегатное состояние в текущее.
комбинирующая_функция должна быть объявлена как принимающая два аргумента с типом_данных_состояния и возвращающая значение с типом_данных_состояния. Эту функцию дополнительно можно объявить как «строгую». В этом случае функция не будет вызываться, когда одно из входных состояний равно NULL; в качестве корректного результата будет выдано другое состояние.
Для агрегатных функций, у которых тип_данных_состояния — internal, комбинирующая_функция не должна быть «строгой». В этом случае комбинирующая_функция должна позаботиться о том, чтобы состояния NULL обрабатывались правильно и чтобы возвращаемое состояние правильно хранилось в контексте агрегатной памяти.
функция_сериализации
Агрегатная функция, у которой тип_данных_состояния — internal, может участвовать в параллельном агрегировании только в том случае, если для нее задана функция_сериализации, которая должна сериализовать агрегатное состояние в значение bytea для передачи другому процессу. Эта функция должна принимать один аргумент типа internal и возвращать тип bytea. Также при этом нужно задать соответствующую функцию_десериализации.
функция_десериализации
Десериализует ранее сериализованное агрегатное состояние обратно в тип_данных_состояния. Эта функция должна принимать два аргумента типа bytea и internal и возвращать результат типа internal. (Примечание: второй аргумент internal не используется, но требуется из соображений безопасности для типа.)
начальное_условие
Начальное значение переменной состояния. Это должна быть строковая константа в форме, принятой для типа данных тип_данных_состояния. Если не указано, то значение состояния начинается с NULL.
функция_состояния_движения
Имя функции прямого перехода состояния, которая будет вызываться для каждой входной строки в режиме движущегося агрегата. Это такая же функция, как и обычная функция перехода, за исключением того, что ее первый аргумент и результат имеют тип тип_данных_состояния_движения, который может отличаться от типа_данных_состояния.
обратная_функция_движения
Имя функции обратного перехода состояния, которая будет использоваться в режиме движущегося агрегата. Эта функция имеет те же типы аргументов и результатов, что и функция_состояния_движения, но используется для удаления значения из текущего агрегатного состояния, а не для добавления к нему значения. Функция обратного перехода должна иметь ту же характеристику строгости, что и функция прямого перехода состояния.
тип_данных_состояния_движения
Тип данных для значения агрегатного состояния при использовании режима движущегося агрегата.
размер_данных_состояния_движения
Приблизительный средний размер (в байтах) значения состояния агрегата при использовании режима движущегося агрегата. Он работает так же, как размер_данных_состояния.
функция_завершения_движения
Имя функции завершения, вызываемой для вычисления результата агрегата после обработки всех входных строк при использовании режима движущегося агрегата. Она работает так же, как функция_завершения, за исключением того, что тип ее первого аргумента равен типу_данных_состояния_движения, а дополнительными пустыми аргументами управляет параметр MFINALFUNC_EXTRA. Тип результата, который определяет функция_завершения_движения или тип_данных_состояния_движения, должен совпадать с типом результата обычной реализации агрегата.
MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }
Этот параметр подобен FINALFUNC_MODIFY, но описывает поведение функции завершения для движущегося агрегата.
начальное_условие_движения
Начальное значение переменной состояния в режиме движущегося агрегата. Оно работает так же, как начальное_условие.
оператор_сортировки
Связанный оператор сортировки для реализации агрегатов подобных MIN или MAX. Это просто имя оператора (может быть дополнено схемой). Предполагается, что этот оператор имеет те же типы входных данных, что и агрегат (который должен быть обычным и иметь один аргумент).
PARALLEL = { SAFE | RESTRICTED | UNSAFE }
Указания PARALLEL SAFE, PARALLEL RESTRICTED и PARALLEL UNSAFE имеют
тот же смысл, что и в команде CREATE FUNCTION. Агрегатная функция не будет
считаться распараллеливаемой, если она помечена как PARALLEL UNSAFE (что
является значением по умолчанию!) или PARALLEL RESTRICTED. Обратите внимание,
что планировщик не обращает внимание на допустимость распараллеливания вспомогательных
функций агрегата, а учитывает только характеристику самого агрегата.
HYPOTHETICAL
Только для сортирующих агрегатов. Этот флаг указывает, что агрегатные аргументы должны обрабатываться в соответствии с требованиями для гипотезирующих агрегатов: то есть последние несколько непосредственных аргументов должны соответствовать типам данных агрегированных (WITHIN GROUP) аргументов. Флаг HYPOTHETICAL не влияет на поведение во время выполнения, а учитывается только при разрешении типов данных и правил сортировки для аргументов агрегата во время синтаксического анализа.
Параметры CREATE AGGREGATE можно записывать в любом порядке, а не только в
показанном выше.
Примечания
В параметрах, определяющих имена вспомогательных функций, при необходимости
можно записать имя схемы, например SFUNC = public.sum. Однако не записывайте
там типы аргументов — типы аргументов вспомогательных функций выводятся из
других параметров.
Обычно предполагается, что функции QHB являются настоящими функциями, которые не изменяют свои входные значения. Тем не менее функциям агрегатного перехода, используемым в контексте агрегата, разрешено действовать хитрее и изменять аргумент переходного состояния на ходу. Это может обеспечить существенные преимущества в производительности по сравнению с созданием каждый раз новой копии переходного состояния.
Подобным образом, хотя функция завершения агрегата обычно не должна изменять свои входные значения, иногда может быть полезно допустить изменение аргумента со значением переходного состояния. Такое поведение должно быть объявлено с помощью параметра FINALFUNC_MODIFY. Его значение READ_WRITE указывает, что функция завершения модифицирует переходное состояние неопределенным образом. Это значение предотвращает использование агрегата в качестве оконной функции, а также объединение переходных состояний при вызове агрегатов с одинаковыми входными данными и функциями перехода. Значение SHAREABLE указывает, что функцию перехода нельзя применять после функции завершения, но со значением конечного переходного состояния можно выполнить несколько вызовов функции завершения. Это значение предотвращает использование агрегата в качестве оконной функции, но позволяет объединять переходные состояния. (То есть оптимизация в данном случае заключается не в многократном применении одной и той же функции завершения, а в применении различных функции завершения к одному и тому же конечному переходному состоянию. Это разрешено, только если ни одна из функций завершения не помечена как READ_WRITE.)
Если агрегатная функция поддерживает режим движущегося агрегата, это повысит эффективность вычислений, когда агрегат будет использоваться в качестве оконной функции для окна с движущимся началом рамки (то есть при режиме начала рамки отличном от UNBOUNDED PRECEDING). По существу, функция прямого перехода добавляет входные значения к состоянию агрегата, когда они появляются в оконной рамке, а функция обратного перехода снова вычитает их, когда они покидают рамку. Поэтому вычитаются значения в том же порядке, в каком добавлялись. Всякий раз, когда вызывается обратная функция перехода, она, таким образом, получит самое раннее добавленное, но еще не удаленное значение (или значения) аргумента. Функция обратного перехода может предполагать, что по крайней мере одна строка останется в текущем состоянии после удаления самой старой строки. (В противном случае механизм функции окна просто запускает новую агрегацию, а не использует обратную функцию перехода.)
Функции прямого перехода движущегося агрегата нельзя возвращать NULL в качестве нового значения состояния. Если функция обратного перехода возвращает значение NULL, это показывает, что обратная функция не может произвести обратное вычисление состояния для этих конкретных входных данных, и поэтому вычисление агрегата следует выполнить заново от начальной позиции текущей рамки. Это соглашение позволяет использовать режим движущегося агрегата в ситуациях, в которых обратное вычисление состояния производить непрактично.
Даже без реализации движущегося агрегата агрегатную функцию можно использовать с движущимися рамками, но QHB будет заново агрегировать все данные при каждом перемещении начала рамки. Обратите внимание, что независимо от того, поддерживает ли агрегатная функция режим движущегося агрегата, QHB может обойтись без повторных вычислений при сдвиге конца рамки; это делается путем продолжения добавления новых значений в состояние агрегата. Именно поэтому использование агрегата в качестве оконной функции требует, чтобы функция завершения была только читающей: она не должна повредить значение состояния агрегата, чтобы агрегация могла продолжаться даже после получения значения результата агрегирования для одного набора границ рамки.
Синтаксис сортирующих агрегатных функций позволяет указать VARIADIC и в последнем непосредственном, и в последнем агрегатном (WITHIN GROUP) параметрах. Тем не менее текущая реализация ограничивает двоякое использование VARIADIC. Во-первых, в сортирующих агрегатных функциях можно использовать только VARIADIC "any", но не другие типы переменных массивов. Во-вторых, если последним непосредственным аргументом является VARIADIC "any", тогда может быть только один агрегированный параметр, и он также должен быть VARIADIC "any". (В представлении, используемом в системных каталогах, эти два параметра объединяются в один элемент VARIADIC "any", так как в pg_proc нельзя представить функцию с несколькими параметрами VARIADIC.) Если агрегат является гипотезирующим, то непосредственные аргументы, соответствующие параметру VARIADIC "any", будут гипотетическими; любые предыдущие параметры представляют дополнительные непосредственные аргументы, которые могут не соответствовать агрегированным.
В настоящее время сортирующие агрегатные функции не поддерживают режим движущегося агрегата, поскольку не могут использоваться в качестве оконных функций.
Частичное (в том числе параллельное) агрегирование в настоящее время не поддерживается для сортирующих агрегатных функций. Также оно никогда не будет применяться для агрегатных вызовов с предложениями DISTINCT или ORDER BY, так как они по природе своей не могут быть реализованы с частичным агрегированием.
Примеры
См. раздел Пользовательские агрегаты.
Совместимость
Команда CREATE AGGREGATE является расширением QHB. Стандарт
SQL не предусматривает создание пользовательских агрегатных функций.