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 аргумент, первый должен иметь тип тип_данных_состояния, а остальные — соответствовать заявленному типу входных данных агрегата. Функция должна возвращать значение типа тип_данных_состояния. Эта функция принимает текущее значение состояния и текущее(ие) значение(я) входных данных, а возвращает следующее значение состояния.
В сортирующих (в том числе гипотезирующих) агрегатах функция перехода состояния получает только текущее значение состояния и агрегируемые аргументы, а не непосредственные аргументы. В остальном это одно и то же.
тип_данных_состояния
Тип данных значения состояния агрегатной функции.
размер_данных_состояния
Приблизительный средний размер (в байтах) значения состояния агрегата. Если этот параметр опущен или равен нулю, используется оценка по умолчанию, определяемая по типу тип_данных_состояния. Планировщик использует это значение для оценки памяти, необходимой для агрегатного запроса с группировкой. Планировщик может применить агрегирование по хэшу для такого запроса, только если хэш-таблица, судя по оценке, поместится в work_mem; таким образом, при больших значениях этого параметра агрегирование по хэшу будет менее желательным.
функция_завершения
Имя функции завершения, вызываемой для вычисления результата агрегатной функции после обработки всех входных строк. Для нормального агрегата эта функция должна принимать один аргумент типа тип_данных_состояния. Возвращаемый тип данных агрегата определяется как возвращаемый тип этой функции. Если функция_завершения не указана, то значение конечного состояния используется в качестве результата агрегирования, а типом возвращаемого значения является тип_данных_состояния.
В сортирующих (в том числе гипотезирующих) агрегатах функция завершения получает не только значение конечного состояния, но и значения всех непосредственных аргументов.
Если команда содержит указание 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 не
предусматривает создание пользовательских агрегатных функций.