CREATE AGGREGATE#

CREATE AGGREGATE
CREATE AGGREGATE
Назад НаверхSQL КомандыНачало Далее

CREATE AGGREGATE

CREATE AGGREGATE — определить новую агрегатную функцию

Синтаксис

CREATE [ OR REPLACE ] AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , SSPACE = state_data_size ]
    [ , FINALFUNC = ffunc ]
    [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , COMBINEFUNC = combinefunc ]
    [ , SERIALFUNC = serialfunc ]
    [ , DESERIALFUNC = deserialfunc ]
    [ , INITCOND = initial_condition ]
    [ , MSFUNC = msfunc ]
    [ , MINVFUNC = minvfunc ]
    [ , MSTYPE = mstate_data_type ]
    [ , MSSPACE = mstate_data_size ]
    [ , MFINALFUNC = mffunc ]
    [ , MFINALFUNC_EXTRA ]
    [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , MINITCOND = minitial_condition ]
    [ , SORTOP = sort_operator ]
    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
)

CREATE [ OR REPLACE ] AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ]
                        ORDER BY [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , SSPACE = state_data_size ]
    [ , FINALFUNC = ffunc ]
    [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , INITCOND = initial_condition ]
    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
    [ , HYPOTHETICAL ]
)

or the old syntax

CREATE [ OR REPLACE ] AGGREGATE name (
    BASETYPE = base_type,
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , SSPACE = state_data_size ]
    [ , FINALFUNC = ffunc ]
    [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , COMBINEFUNC = combinefunc ]
    [ , SERIALFUNC = serialfunc ]
    [ , DESERIALFUNC = deserialfunc ]
    [ , INITCOND = initial_condition ]
    [ , MSFUNC = msfunc ]
    [ , MINVFUNC = minvfunc ]
    [ , MSTYPE = mstate_data_type ]
    [ , MSSPACE = mstate_data_size ]
    [ , MFINALFUNC = mffunc ]
    [ , MFINALFUNC_EXTRA ]
    [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , MINITCOND = minitial_condition ]
    [ , SORTOP = sort_operator ]
)

Описание

CREATE AGGREGATE определяет новую агрегатную функцию. CREATE OR REPLACE AGGREGATE либо определяет новую агрегатную функцию, либо заменяет существующее определение. Некоторые базовые и часто используемые агрегатные функции включены в дистрибутив; они документированы в Раздел 9.21. Если вы определяете новые типы или вам нужна агрегатная функция, которая еще не предоставляется, то CREATE AGGREGATE может быть использована для предоставления необходимых возможностей.

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

Если указано имя схемы (например, CREATE AGGREGATE myschema.myagg ...), то агрегатная функция создается в указанной схеме. В противном случае она создается в текущей схеме.

Все агрегатные функции идентифицируются своим именем и типом(ами) входных данных. Две агрегатные функции в одной схеме могут иметь одно и то же имя, если они работают с разными типами входных данных. Имя и тип(ы) входных данных агрегатной функции также должны отличаться от имени и типа(-ов) входных данных каждой обычной функции в той же схеме. Это поведение идентично перегрузке имен обычных функций (см. CREATE FUNCTION).

Простая агрегатная функция состоит из одной или двух обычных функций: функции перехода состояния sfunc, и необязательной функции окончательного вычисления ffunc. Их использование следующее: 

sfunc( internal-state, next-data-values ) ---> next-internal-state
ffunc( internal-state ) ---> aggregate-value

Tantor BE создает временную переменную типа данных stype для хранения текущего внутреннего состояния агрегата. При каждой входной строке вычисляются значения аргументов агрегата и вызывается функция перехода состояния с текущим значением состояния и новыми значениями аргументов для вычисления нового значения внутреннего состояния. После обработки всех строк вызывается конечная функция один раз для вычисления возвращаемого значения агрегата. Если нет конечной функции, то возвращается конечное значение состояния без изменений.

Агрегатная функция может предоставить начальное условие, то есть начальное значение для внутреннего состояния. Это указывается и хранится в базе данных как значение типа text, но оно должно быть допустимым внешним представлением константы типа данных значения состояния. Если оно не предоставляется, то значение состояния начинается с null.

Если функция перехода состояния объявлена как “strict”, то ее нельзя вызывать с пустыми входными данными. В случае использования такой функции перехода, выполнение агрегата происходит следующим образом. Строки с пустыми значениями входных данных игнорируются (функция не вызывается и предыдущее значение состояния сохраняется). Если начальное значение состояния равно null, то при первой строке со всеми непустыми значениями входных данных, первое значение аргумента заменяет значение состояния, и функция перехода вызывается для каждой последующей строки со всеми непустыми значениями входных данных. Это удобно для реализации агрегатных функций, таких как max. Обратите внимание, что такое поведение доступно только тогда, когда state_data_type совпадает с первым arg_data_type. Если эти типы различаются, необходимо указать начальное состояние not-null или использовать нестрогую функцию перехода.

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

Если конечная функция объявлена как “strict”, то она не будет вызываться, когда конечное значение состояния равно null; вместо этого будет автоматически возвращен null результат. (Конечно, это просто нормальное поведение строгих функций). В любом случае, конечная функция имеет возможность возвращать null значение. Например, конечная функция для avg возвращает null, когда она обнаруживает, что количество входных строк равно нулю.

Иногда полезно объявить конечную функцию таким образом, чтобы она принимала не только значение состояния, но и дополнительные параметры, соответствующие входным значениям агрегата. Основная причина для этого заключается в том, что конечная функция может быть полиморфной, и тип данных значения состояния может быть недостаточным для определения типа результата. Эти дополнительные параметры всегда передаются как NULL (поэтому конечная функция не должна быть строгой, когда используется опция FINALFUNC_EXTRA), но тем не менее они являются допустимыми параметрами. Например, конечная функция может использовать функцию get_fn_expr_argtype для определения фактического типа аргумента в текущем вызове.

Агрегат может дополнительно поддерживать режим агрегации с перемещением, как описано в Раздел 35.11.1. Для этого требуется указать параметры MSFUNC, MINVFUNC, и MSTYPE, а также, по желанию, параметры MSSPACE, MFINALFUNC, MFINALFUNC_EXTRA, MFINALFUNC_MODIFY, и MINITCOND. За исключением MINVFUNC, эти параметры работают так же, как соответствующие параметры простого агрегата без M; они определяют отдельную реализацию агрегата, которая включает обратную функцию перехода.

Синтаксис с ORDER BY в списке параметров создает специальный тип агрегата, называемый упорядоченным наборным агрегатом; или если указано HYPOTHETICAL, то создается гипотетизирующая агрегатная функция. Эти агрегаты работают с группами отсортированных значений в зависимости от порядка, поэтому указание порядка сортировки входных данных является неотъемлемой частью вызова. Кроме того, они могут иметь прямые аргументы, которые вычисляются только один раз для каждой агрегации, а не для каждой входной строки. Гипотетизирующие агрегатные функции являются подклассом функций с отсортированным набором, в которых некоторые из прямых аргументов должны соответствовать, по количеству и типу данных, столбцам агрегированных аргументов. Это позволяет добавлять значения этих прямых аргументов в коллекцию входных строк агрегата в качестве дополнительной “гипотетизирующой” строки.

Агрегат может дополнительно поддерживать частичную агрегацию, как описано в Раздел 35.11.4. Для этого требуется указать параметр COMBINEFUNC. Если state_data_type имеет тип internal, обычно также целесообразно предоставить параметры SERIALFUNC и DESERIALFUNC, чтобы была возможна параллельная агрегация. Обратите внимание, что агрегат также должен быть помечен как PARALLEL SAFE, чтобы включить параллельную агрегацию.

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

SELECT agg(col) FROM tab;

должно быть равнозначно: 

SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;

Дополнительные предположения состоят в том, что агрегат игнорирует пустые входные данные и возвращает пустой результат только в том случае, если нет непустых входных данных. Обычно оператор < типа данных является правильным оператором сортировки для функции MIN, а оператор > - правильным оператором сортировки для функции MAX. Обратите внимание, что оптимизация никогда не будет применяться, если указанный оператор не является стратегическим членом класса операторов индекса B-дерева “меньше” или “больше”.

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

Параметры

name

Имя (опционально с указанием схемы) агрегатной функции для создания.

argmode

Режим аргумента: IN или VARIADIC. (Агрегатные функции не поддерживают аргументы OUT). Если не указано, используется значение по умолчанию - IN. Только последний аргумент может быть помечен как VARIADIC.

argname

Имя аргумента. В настоящее время это полезно только для целей документирования. Если не указано, аргумент не имеет имени.

arg_data_type

Тип входных данных, на которых работает эта агрегатная функция. Чтобы создать агрегатную функцию без аргументов, вместо списка спецификаций аргументов напишите *. (Пример такой агрегатной функции - count(*)).

base_type

В старом синтаксисе для CREATE AGGREGATE тип входных данных указывается параметром basetype, а не записывается рядом с именем агрегата. Обратите внимание, что этот синтаксис позволяет указывать только один входной параметр. Чтобы определить агрегатную функцию без аргументов с использованием этого синтаксиса, укажите basetype как "ANY" (а не *). Агрегаты с отсортированным набором не могут быть определены с использованием старого синтаксиса.

sfunc

Имя функции перехода состояния, которая будет вызываться для каждой входной строки. Для обычной агрегатной функции с N аргументами, sfunc должна принимать N+1 аргументов, первый из которых имеет тип state_data_type, а остальные соответствуют объявленным типам входных данных агрегата. Функция должна возвращать значение типа state_data_type. Эта функция принимает текущее значение состояния и текущее значение(я) входных данных и возвращает следующее значение состояния.

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

state_data_type

Тип данных для значения состояния агрегата.

state_data_size

Размер (в байтах) приблизительного среднего значения состояния агрегата. Если этот параметр не указан или равен нулю, используется значение по умолчанию, основанное на state_data_type. Планировщик использует это значение для оценки требуемой памяти для запроса с группировкой агрегатов.

ffunc

Имя конечной функции, вызываемой для вычисления результата агрегата после обхода всех входных строк. Для обычного агрегата эта функция должна принимать единственный аргумент типа state_data_type. Тип данных возвращаемого значения агрегата определяется типом возвращаемого значения этой функции. Если ffunc не указан, то в качестве результата агрегата используется конечное значение состояния, а тип возвращаемого значения - state_data_type.

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

Если указано FINALFUNC_EXTRA, то в дополнение к конечному значению состояния и любым прямым аргументам, конечная функция получает дополнительные NULL-значения, соответствующие обычным (агрегированным) аргументам агрегата. Это особенно полезно для правильного определения типа результата агрегата при определении полиморфного агрегата.

FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }

Этот параметр указывает, является ли конечная функция чистой функцией, которая не изменяет свои аргументы. READ_ONLY указывает, что функция не изменяет аргументы; другие два значения указывают, что она может изменять состояние перехода. См. Notes ниже для получения более подробной информации. По умолчанию используется значение READ_ONLY, за исключением агрегатов с отсортированным набором, для которых значение по умолчанию - READ_WRITE.

combinefunc

Функция combinefunc может быть указана для поддержки частичной агрегации в агрегатной функции. Если она предоставлена, combinefunc должна объединять два значения state_data_type, каждое из которых содержит результат агрегации над некоторым подмножеством входных значений, чтобы получить новое значение state_data_type, которое представляет результат агрегации над обоими наборами входных значений. Эту функцию можно рассматривать как sfunc, где вместо того, чтобы действовать на отдельную входную строку и добавлять ее к текущему состоянию агрегата, она добавляет другое состояние агрегата к текущему состоянию.

Вам нужно объявить функцию combinefunc, которая принимает два аргумента типа state_data_type и возвращает значение типа state_data_type. По желанию эта функция может быть “strict”. В этом случае функция не будет вызываться, если одно из входных состояний равно null; другое состояние будет принято как правильный результат.

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

serialfunc

Агрегатная функция, чей state_data_type является internal, может участвовать в параллельной агрегации только если у нее есть функция serialfunc, которая должна сериализовать состояние агрегата в значение bytea для передачи другому процессу. Эта функция должна принимать единственный аргумент типа internal и возвращать тип bytea. Также требуется соответствующая функция deserialfunc.

deserialfunc

Десериализует ранее сериализованное состояние агрегата обратно в state_data_type. Эта функция должна принимать два аргумента типов bytea и internal и возвращать результат типа internal. (Примечание: второй аргумент internal не используется, но требуется из соображений безопасности типов).

initial_condition

Начальное значение для значения состояния. Это должна быть строковая константа в форме, принятой для типа данных state_data_type. Если не указано, значение состояния начинается с null.

msfunc

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

minvfunc

Имя функции обратного перехода состояния, которая будет использоваться в режиме перемещения-агрегации. Эта функция имеет те же аргументы и типы результата, что и msfunc, но она используется для удаления значения из текущего состояния агрегата, а не для добавления значения к нему. Функция обратного перехода должна иметь тот же атрибут строгости, что и функция прямого перехода состояния.

mstate_data_type

Тип данных для значения состояния агрегата при использовании режима подвижного агрегата.

mstate_data_size

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

mffunc

Имя конечной функции, вызываемой для вычисления результата агрегата после обхода всех входных строк при использовании режима подвижного агрегата. Это работает так же, как ffunc, за исключением того, что тип первого аргумента равен mstate_data_type, а дополнительные фиктивные аргументы указываются путем записи MFINALFUNC_EXTRA. Тип результата агрегата, определенный mffunc или mstate_data_type, должен соответствовать типу, определенному обычной реализацией агрегата.

MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }

Этот параметр аналогичен FINALFUNC_MODIFY, но описывает поведение оконечной функции подвижной агрегации.

minitial_condition

Начальное значение для значения состояния при использовании режима подвижной агрегации. Это работает так же, как initial_condition.

sort_operator

Оператор сортировки, связанный с агрегатной функцией 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. Однако, не указывайте там типы аргументов — типы аргументов функций поддержки определяются из других параметров.

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

Точно так же, хотя обычно ожидается, что окончательная функция агрегата не изменяет свои входные значения, иногда невозможно избежать изменения аргумента состояния перехода. Такое поведение должно быть объявлено с использованием параметра FINALFUNC_MODIFY. Значение READ_WRITE указывает, что окончательная функция изменяет состояние перехода неопределенным образом. Это значение предотвращает использование агрегата в качестве оконной функции, а также предотвращает слияние состояний перехода для агрегатных вызовов, которые используют одни и те же входные значения и функции перехода. Значение SHAREABLE указывает, что функцию перехода нельзя применять после окончательной функции, но на конечное значение состояния перехода можно выполнить несколько вызовов окончательной функции. Это значение предотвращает использование агрегата в качестве оконной функции, но позволяет объединение состояний перехода. (То есть, интересующая нас оптимизация заключается не в повторном применении одной и той же окончательной функции, а в применении разных окончательных функций к одному и тому же конечному значению состояния перехода. Это разрешено, пока ни одна из окончательных функций не помечена как READ_WRITE).

Если агрегат поддерживает режим перемещения агрегата, это повысит эффективность вычислений, когда агрегат используется в качестве оконной функции для окна с перемещающимся началом (то есть режимом начала рамки, отличным от UNBOUNDED PRECEDING). Концептуально, прямая функция перехода добавляет входные значения в состояние агрегата, когда они входят в рамку окна снизу, а обратная функция перехода снова удаляет их, когда они покидают рамку сверху. Таким образом, при удалении значений они всегда удаляются в том же порядке, в котором были добавлены. Каждый раз, когда вызывается обратная функция перехода, она получает самые ранние добавленные, но еще не удаленные аргументы. Обратная функция перехода может предполагать, что после удаления самой старой строки в текущем состоянии останется хотя бы одна строка. (Когда это не так, механизм оконных функций просто начинает новую агрегацию, а не использует обратную функцию перехода).

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

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

Синтаксис для агрегатов с отсортированными наборами позволяет указывать VARIADIC как для последнего прямого параметра, так и для последнего агрегированного (WITHIN GROUP) параметра. Однако текущая реализация ограничивает использование VARIADIC двумя способами. Во-первых, агрегаты с отсортированными наборами могут использовать только VARIADIC "any", а не другие вариативные типы массивов. Во-вторых, если последний прямой параметр - это VARIADIC "any", то может быть только один агрегированный параметр, и он также должен быть VARIADIC "any". (В представлении, используемом в системных каталогах, эти два параметра объединяются в один элемент VARIADIC "any", поскольку pg_proc не может представлять функции с более чем одним параметром VARIADIC). Если агрегат является гипотетизирующей агрегатной функцией, прямые аргументы, которые соответствуют параметру VARIADIC "any", являются гипотетизирующими; любые предшествующие параметры представляют дополнительные прямые аргументы, которые не ограничены совпадением с агрегированными аргументами.

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

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

Примеры

См. Раздел 35.11.

Совместимость

CREATE AGGREGATE - это расширение языка Tantor BE. Стандарт SQL не предусматривает возможность определения пользовательских агрегатных функций.

Назад Наверх Далее
CREATE ACCESS METHOD Начало CREATE CAST