История версий
Требования

Введение

Быстрый старт

Развертывание

Моделирование данных

Подключение

Запись данных

Миграция

Запросы

Операции и обслуживание

Настройка производительности

Устранение неполадок

Справочник

Справочник по SQL

Часто задаваемые вопросы

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 } ]
    [ , REPSAFE = <boolean> ]
  )

  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 } ]
    [ , COMBINEFUNC = <combinefunc> ]
    [ , INITCOND = <initial_condition> ]
    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
    [ , REPSAFE = <boolean> ]
    [ , 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> ]
    [ , REPSAFE = <boolean> ]
  )

Описание

CREATE AGGREGATE определяет новую агрегатную функцию. CREATE OR REPLACE AGGREGATE либо определяет новую агрегатную функцию, либо заменяет существующее определение. Некоторые базовые и часто используемые агрегатные функции, такие как count(), min(), max(), sum(), avg() и другие, уже предоставлены в Database. Если вы определяете новые типы или вам нужна агрегатная функция, которой ещё нет, вы можете использовать команду CREATE AGGREGATE для предоставления требуемых возможностей.

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

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

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

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

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

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

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

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

Если функция перехода состояния объявлена как STRICT, её нельзя вызывать с NULL-аргументами. При такой функции выполнение агрегации происходит следующим образом: строки с любыми NULL-значениями игнорируются (функция не вызывается, предыдущее значение состояния сохраняется). Если начальное значение состояния — NULL, то при первой строке со всеми ненулевыми значениями первый аргумент заменяет значение состояния, а затем функция перехода вызывается на последующих строках с ненулевыми значениями. Это полезно для реализации агрегатов вроде max(). Обратите внимание, что такое поведение доступно только тогда, когда state_data_type совпадает с первым arg_data_type. Когда эти типы различаются, необходимо указать ненулевое начальное условие или использовать нестрогую (nonstrict) функцию перехода.

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

Если финальная функция объявлена как STRICT, она не будет вызываться, когда конечное состояние — NULL; вместо этого автоматически возвращается NULL-результат. (Таково нормальное поведение STRICT-функций.) В любом случае финальная функция может вернуть NULL-значение. Например, финальная функция для avg() возвращает NULL, если было обработано ноль строк.

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

Агрегат может дополнительно поддерживать режим скользящего агрегирования (moving-aggregate mode), как описано в разделе Moving-Aggregate Mode документации PostgreSQL. Это требует указания функций MSFUNC, MINVFUNC и MSTYPE, а также, опционально, MSSPACE, MFINALFUNC, MFINALFUNC_EXTRA, MFINALFUNC_MODIFY и MINITCOND. За исключением MINVFUNC, эти функции работают аналогично соответствующим простым агрегатным функциям без префикса M; они определяют отдельную реализацию агрегата, включающую обратную функцию перехода.

Синтаксис с ORDER BY в списке параметров создаёт особый тип агрегата — агрегат с упорядоченным набором (ordered-set aggregate); если указано HYPOTHETICAL, создаётся агрегат с гипотетическим набором (hypothetical-set aggregate). Такие агрегаты работают с группами отсортированных значений способом, зависящим от порядка, поэтому указание порядка сортировки входных данных является обязательной частью вызова. Кроме того, они могут иметь прямые аргументы — аргументы, которые вычисляются один раз за всё агрегирование, а не один раз на каждую строку. Агрегаты с гипотетическим набором — это подкласс агрегатов с упорядоченным набором, в которых некоторые прямые аргументы должны совпадать по количеству и типам данных со столбцами агрегированных аргументов. Это позволяет добавлять значения этих прямых аргументов к коллекции строк входных данных как дополнительную «гипотетическую» строку.

Агрегат может дополнительно поддерживать частичное агрегирование. Для этого требуется указать параметр 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;

Дополнительные предположения: агрегатная функция игнорирует NULL-входы и возвращает NULL тогда и только тогда, когда не было ни одного непустого входа. Обычно < оператор типа данных является правильным оператором сортировки для min(), а > — для max(). Обратите внимание, что оптимизация будет применяться только в том случае, если указанный оператор является стратегией «меньше чем» или «больше чем» в классе операторов B-дерева.

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

Вы можете указать combinefunc как метод оптимизации выполнения агрегата. Указав combinefunc, агрегат может выполняться параллельно сначала на сегментах, а затем на координаторе. При двухуровневом выполнении sfunc запускается на сегментах для формирования частичных результатов агрегирования, а combinefunc — на координаторе для объединения частичных результатов с сегментов. Если выполняется одноуровневое агрегирование, все строки отправляются на координатор, и к ним применяется sfunc.

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

Параметры

  • 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. Планировщик использует это значение для оценки объема памяти, необходимого для группового агрегированного запроса. Планировщик рассмотрит возможность использования хеш-агрегирования для такого запроса только в том случае, если оценочный размер хеш-таблицы помещается в work_mem; следовательно, большие значения этого параметра снижают вероятность применения хеш-агрегирования.

  • ffunc

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

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

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

  • FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }

    • Эта опция указывает, является ли финальная функция чистой функцией, не изменяющей свои аргументы. READ_ONLY означает, что не изменяет; другие два значения означают, что она может изменять значение состояния перехода. Подробности см. в примечаниях ниже. По умолчанию установлено 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 не должна быть strict. В этом случае combinefunc должна гарантировать корректную обработку NULL-состояний и правильное размещение возвращаемого состояния в контексте памяти агрегата.
    • В Database, если результат агрегатной функции вычисляется сегментированным способом, комбинирующая функция вызывается для отдельных внутренних состояний, чтобы объединить их в конечное внутреннее состояние.
    • Обратите внимание, что эта функция также вызывается в режиме хеш-агрегирования внутри сегмента. Поэтому, если вы вызываете эту агрегатную функцию без функции combine, хеш-агрегирование никогда не выбирается. Поскольку хеш-агрегирование эффективно, по возможности старайтесь определять функцию combine.

  • 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, но используется для удаления значения из текущего состояния агрегата, а не для его добавления. Обратная функция перехода должна иметь ту же строгость (strictness), что и прямая функция перехода.

  • 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. Обратите внимание, что планировщик не проверяет метки параллельной безопасности вспомогательных функций агрегата, только метку самого агрегата.

  • REPSAFE = boolean

    • Указывает, можно ли безопасно выполнять агрегат на реплицированных срезах. Агрегат, не зависящий от порядка, считается безопасным в данном контексте. Значение по умолчанию — false.
    • Установка REPSAFE = true указывает оптимизатору выполнить дополнительные оптимизации, специально подавляющие определённые движения широковещательной рассылки.

Внимание!
Неправильная установка REPSAFE = true для агрегата, зависящего от порядка, может привести к неверным результатам.

  • HYPOTHETICAL
    • Только для агрегатов с упорядоченным набором. Этот флаг указывает, что аргументы агрегата должны обрабатываться в соответствии с требованиями к агрегатам с гипотетическим набором: последние несколько прямых аргументов должны совпадать по типам данных с агрегированными (WITHIN GROUP) аргументами. Флаг HYPOTHETICAL не влияет на поведение во время выполнения, только на разбор типов данных и правила сортировки аргументов агрегата на этапе анализа.

Параметры команды CREATE AGGREGATE могут быть указаны в любом порядке, а не только в порядке, показанном выше.

Примечания

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

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

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

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

name ( arg [ , ... ] [ORDER BY sortspec [ , ...]] )

Ключевое слово ORDERED поддерживается для обратной совместимости, но игнорируется.

В предыдущих версиях Database опция COMBINEFUNC называлась PREFUNC. Она по-прежнему поддерживается для обратной совместимости как синоним COMBINEFUNC.

Пример

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

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

Эта функция указывается как sfunc в агрегатной функции.

CREATE FUNCTION mysfunc_accum(numeric, numeric, numeric) 
  RETURNS numeric
   AS 'select $1 + $2 + $3'
   LANGUAGE SQL
   RETURNS NULL ON NULL INPUT;

Эта функция указывается как combinefunc в агрегатной функции.

CREATE FUNCTION mycombine_accum(numeric, numeric )
  RETURNS numeric
   AS 'select $1 + $2'
   LANGUAGE SQL
   RETURNS NULL ON NULL INPUT;

Следующая команда CREATE AGGREGATE создает агрегатную функцию, суммирующую два столбца.

CREATE AGGREGATE agg_twocols(numeric, numeric) (
   SFUNC = mysfunc_accum,
   STYPE = numeric,
   COMBINEFUNC = mycombine_accum,
   INITCOND = 0 );

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

CREATE TABLE t1 (a int, b int) DISTRIBUTED BY (a);
INSERT INTO t1 VALUES
   (10, 1),
   (20, 2),
   (30, 3);
SELECT agg_twocols(a, b) FROM t1;

Дополнительные примеры создания агрегатных функций см. в документации PostgreSQL по теме «Пользовательские агрегаты».

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

CREATE AGGREGATE является расширением языка Database. Стандарт SQL не предусматривает пользовательские агрегатные функции.

См. также

ALTER AGGREGATE, DROP AGGREGATE, CREATE FUNCTION

← Предыдущая
CREATE_ACCESS_METHOD
Следующая →
CREATE_CAST