Пользовательские агрегатные функции
Эта страница переведена при помощи нейросети GigaChat.
Агрегатные функции в PostgreSQL определяются с помощью значений состояния и функций перехода состояния. То есть агрегат работает со значением состояния, которое обновляется при обработке каждой последующей строки ввода. Чтобы определить новую агрегатную функцию, необходимо выбрать тип данных для значения состояния, начальное значение для состояния и функцию перехода состояния. Функция перехода состояния принимает предыдущее значение состояния и входное значение агрегата для текущей строки и возвращает новое значение состояния. Также можно указать конечную функцию, если желаемый результат агрегирования отличается от данных, которые должны храниться в текущем значении состояния. Конечная функция принимает конечное значение состояния и возвращает то, что требуется в качестве результата агрегирования. В принципе, функции перехода и завершения являются просто обычными функциями, которые также могут использоваться вне контекста агрегата. (На практике часто бывает полезно для повышения производительности создавать специализированные функции перехода, которые могут работать только тогда, когда они вызываются в контексте агрегата.)
Таким образом, помимо типов аргументов и результатов данных, которые видит пользователь агрегата, существует внутренний тип данных состояния значения, который может отличаться от обоих типов аргумента и результата.
Если определяется агрегат, который не использует конечную функцию, то это агрегат, который вычисляет текущую функцию значений столбцов из каждой строки. sum является примером такого типа агрегирования. sum начинается с нуля и всегда добавляет текущее значение строки к своей текущей сумме. Например, если хотим создать агрегат sum для работы с типом данных комплексных чисел, нужна только функция сложения для этого типа данных. Определение агрегата будет следующим:
CREATE AGGREGATE sum (complex)
(
sfunc = complex_add,
stype = complex,
initcond = '(0,0)'
);
Этот агрегат можно использовать так:
SELECT sum(a) FROM test_complex;
sum
-----------
(34,53.9)
(Обратите внимание, что полагаемся на перегрузку функций: существует более одного агрегата под названием sum, но PostgreSQL может определить, какой вид суммы применяется к столбцу типа complex).
Вышеуказанное определение sum вернет ноль (начальное значение состояния), если нет непустых входных значений. Возможно, требуется вернуть нулевое значение, в этом случае вместо этого – стандарт SQL ожидает, что sum будет вести себя таким образом. Можно сделать это просто, опустив фразу initcond, так что начальное значение состояния равно нулю. Обычно это означало бы, что sfunc должен проверять наличие нулевого значения переменной состояния. Но для sum и некоторых других простых агрегатов, таких как max и min, достаточно вставить первое ненулевое входное значение в переменную состояния, а затем начать применять функцию перехода ко второму ненулевому входному значению. PostgreSQL сделает это автоматически, если начальное состояние равно нулю, а функция перехода помечена как «strict» (то есть не вызывается для нулевых входов).
Еще одна часть поведения по умолчанию для «strict» функции перехода заключается в том, что предыдущее значение состояния сохраняется неизменным всякий раз, когда встречается нулевое входное значение. Таким образом, нулевые значения игнорируются. Если нужно другое поведение для нулевых входов, не объявляйте свою функцию перехода строгой; вместо этого запрограммируйте его на проверку наличия нулевых входов и выполнение необходимых действий.
avg (среднее значение) является более сложным примером агрегата. Для него требуется два фрагмента текущего состояния: сумма входных данных и количество входных данных. Конечный результат получается путем деления этих величин. Среднее значение обычно реализуется с использованием массива в качестве значения состояния. Например, встроенная реализация avg(float8) выглядит следующим образом:
CREATE AGGREGATE avg (float8)
(
sfunc = float8_accum,
stype = float8[],
finalfunc = float8_avg,
initcond = '{0,0,0}'
);
float8_accum требует массив из трех элементов, а не просто два элемента, потому что он накапливает сумму квадратов, а также сумму и количество входных данных. Это делается для того, чтобы его можно было использовать для некоторых других агрегатных функций, а также для avg.
Вызовы агрегатных функций в SQL допускают параметры DISTINCT и ORDER BY, которые управляют тем, какие строки подаются функции перехода агрегата и в каком порядке. Это реализовано на заднем плане и непосредственно не затрагивает функции, поддерживающие работу агрегата.
Для получения дополнительной информации см. команду CREATE AGGREGATE.
Режим движущегося агрегата
Агрегатные функции могут дополнительно поддерживать режим перемещения агрегатов, который позволяет значительно быстрее выполнять агрегатные функции внутри окон с движущимися начальными точками кадра. Основная идея заключается в том, что помимо нормальной функции перехода вперед, агрегат предоставляет обратную функцию перехода, которая позволяет удалять строки из текущего состояния агрегата при выходе за пределы рамки окна. Например, агрегат sum, который использует сложение в качестве прямой переходной функции, будет использовать вычитание в качестве обратной переходной функции. Без обратной функции перехода механизм функции окна должен пересчитывать агрегат заново каждый раз, когда начальная точка кадра перемещается, что приводит к времени выполнения, пропорциональному количеству входных строк, умноженному на среднюю длину кадра. С помощью обратной функции перехода время выполнения составляет всего лишь произведение количества входных строк.
Обратная функция перехода передается текущему значению состояния и агрегированному входному значению(ям) для самой ранней строки, включенной в текущее состояние. Она должна восстановить то значение состояния, которое было бы, если бы данная строка ввода никогда не была агрегирована, но только следующие за ней строки. Для этого иногда требуется, чтобы прямая переходная функция сохраняла больше состояний, чем необходимо для обычного режима агрегации. Поэтому режим движущегося агрегата использует совершенно отдельную реализацию от простого режима: у него есть свой собственный тип данных состояния, своя прямая переходная функция и своя конечная функция, если это необходимо. Они могут быть такими же, как тип данных и функции простого режима, если нет необходимости в дополнительном состоянии.
В качестве примера можно расширить агрегат sum приведенный выше, чтобы поддерживать режим движущегося агрегата следующим образом:
CREATE AGGREGATE sum (complex)
(
sfunc = complex_add,
stype = complex,
initcond = '(0,0)',
msfunc = complex_add,
minvfunc = complex_sub,
mstype = complex,
minitcond = '(0,0)'
);
Параметры, названия которых начинаются с m, определяют реализацию движущегося агрегата. За исключением обратной переходной функции minvfunc, они соответствуют параметрам простого агрегата без m.
Прямая переходная функция для режима движущегося агрегата не может возвращать нулевое значение в качестве нового значения состояния. Если обратная переходная функция возвращает ноль, это воспринимается как указание на то, что обратная функция не может обратить вычисление состояния для данного конкретного входа, и поэтому расчет агрегата будет выполнен заново для текущей позиции кадра запуска. Эта конвенция позволяет использовать режим движущегося агрегата в ситуациях, когда существуют некоторые редкие случаи, которые трудно исключить из текущего значения состояния. Обратная переходная функция может «передавать» эти случаи, и все равно выйти вперед до тех пор, пока она сможет работать в большинстве случаев. В качестве примера, агрегат, работающий с числами с плавающей запятой, может выбрать передачу управления, когда ввод NaN (не число) должен быть удален из текущего значения состояния.
При написании функций поддержки движущихся агрегатов важно убедиться, что обратная функция перехода может точно восстановить правильное значение состояния. В противном случае могут быть заметные пользователю различия в результатах в зависимости от того, используется ли режим движущегося агрегата. Примером агрегатной функции, для которой добавление обратной переходной функции кажется простым на первый взгляд, но где это требование не может быть выполнено, является sum над float4 или float8 входами. Наивное объявление sum(``float8``) может быть таким:
CREATE AGGREGATE unsafe_sum (float8)
(
stype = float8,
sfunc = float8pl,
mstype = float8,
msfunc = float8pl,
minvfunc = float8mi
);
Однако этот агрегат может дать совершенно разные результаты, чем если бы он был без обратной переходной функции. Например:
SELECT
unsafe_sum(x) OVER (ORDER BY n ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
FROM (VALUES (1, 1.0e20::float8),
(2, 1.0::float8)) AS v (n,x);
Этот запрос возвращает 0 в качестве второго результата, а не ожидаемый ответ 1. Причина заключается в ограниченной точности значений с плавающей запятой: сложение 1 и 1e20 дает 1e20 снова, и так вычитание 1e20 из этого дает 0, а не 1. Обратите внимание, что это ограничение арифметики с плавающей точкой в целом, а не ограничение PostgreSQL.
Агрегатные функции с полиморфными и переменными аргументами
Агрегатные функции могут использовать полиморфные функции перехода состояния или конечные функции, чтобы одни и те же функции можно было использовать для реализации нескольких агрегатов. См. раздел «Полиморфные типы» для объяснения полиморфных функций. Идя дальше, агрегатная функция сама может быть указана с полиморфным типом ввода(ами) и типом состояния, позволяя одному определению агрегата служить для различных типов входных данных. Вот пример полиморфного агрегата:
CREATE AGGREGATE array_accum (anycompatible)
(
sfunc = array_append,
stype = anycompatiblearray,
initcond = '{}'
);
Здесь фактический тип состояния для любого данного вызова агрегата – это массив типа, элементы которого имеют фактический тип ввода. Поведение агрегата заключается в том, что все входы объединяются в массив этого типа (встроенный агрегат array_agg предоставляет аналогичную функциональность с лучшей производительностью, чем эта версия).
Вывод при использовании двух разных фактических типов данных в качестве аргументов:
SELECT attrelid::regclass, array_accum(attname)
FROM pg_attribute
WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass
GROUP BY attrelid;
attrelid | array_accum
---------------+---------------------------------------
pg_tablespace | {spcname,spcowner,spcacl,spcoptions}
(1 row)
SELECT attrelid::regclass, array_accum(atttypid::regtype)
FROM pg_attribute
WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass
GROUP BY attrelid;
attrelid | array_accum
---------------+---------------------------
pg_tablespace | {name,oid,aclitem[],text[]}
(1 row)
Обычно агрегатная функция с полиморфным типом результата имеет полиморфный тип состояния, как показано выше. Это необходимо потому что иначе конечная функция не может быть объявлена разумно: ей нужно было бы иметь полиморфный тип результата без полиморфного типа аргумента, который CREATE FUNCTION отвергнет на основании того, что тип результата не может быть выведен из вызова. Но иногда неудобно использовать полиморфный тип состояния. Наиболее распространенный случай – когда функции поддержки агрегирования должны быть написаны на языке С и тип состояния должен быть объявлен как internal поскольку нет эквивалентного уровня SQL для него. Чтобы решить эту проблему, можно объявить конечную функцию так, чтобы она принимала дополнительные «фиктивные» аргументы, которые соответствуют входным аргументам агрегата. Такие фиктивные аргументы всегда передаются как нулевые значения, поскольку конкретное значение недоступно при вызове конечной функции. Их единственное назначение – позволить результату полиморфной конечной функции быть связанным с типом (типами) ввода агрегата. Например, определение встроенного агрегата array_agg эквивалентно следующему:
CREATE FUNCTION array_agg_transfn(internal, anynonarray)
RETURNS internal ...;
CREATE FUNCTION array_agg_finalfn(internal, anynonarray)
RETURNS anyarray ...;
CREATE AGGREGATE array_agg (anynonarray)
(
sfunc = array_agg_transfn,
stype = internal,
finalfunc = array_agg_finalfn,
finalfunc_extra
);
Здесь опция finalfunc_extra указывает, что конечная функция получает, помимо значения состояния, дополнительные фиктивные аргументы, соответствующие входному(ым) аргументу(ам) агрегата. Дополнительный anynonarray аргумент позволяет объявлению array_agg_finalfn быть допустимым.
Агрегатную функцию можно заставить принимать различное количество аргументов, объявив ее последний аргумент как VARIADIC массив, примерно таким же образом, как и обычные функции. Функция перехода агрегата должна иметь тот же тип массива в качестве последнего аргумента. Функцию перехода обычно также помечают как VARIADIC, но строго это не требуется.
Вариативные агрегаты легко неправильно использовать в сочетании с опцией ORDER BY, поскольку синтаксический анализатор не может определить, было ли предоставлено неправильное количество фактических аргументов в такой комбинации. Помните, что все справа от ORDER BY является ключом сортировки, а не аргументом агрегата. Например:
SELECT myaggregate(a ORDER BY a, b, c) FROM ...
Синтаксический анализатор увидит это как один аргумент функции агрегации и три ключа сортировки. Однако пользователь мог иметь в виду:
SELECT myaggregate(a, b, c ORDER BY a) FROM ...
Если myaggregate является вариативным, оба этих вызова могут быть абсолютно допустимыми.
По той же причине, прежде чем создавать агрегатные функции с одинаковыми именами и разным количеством обычных аргументов, стоит подумать дважды.
Агрегаты упорядоченного набора
Агрегаты, которые описывались до сих пор, являются «нормальными». PostgreSQL также поддерживает агрегаты упорядоченных наборов, которые отличаются от нормальных агрегатов двумя ключевыми способами. Во-первых, помимо обычных агрегированных аргументов, которые оцениваются один раз для каждой входной строки, агрегат упорядоченного набора может иметь «прямые» аргументы, которые оцениваются только один раз за операцию агрегации. Во-вторых, синтаксис для обычных агрегированных аргументов явно указывает порядок сортировки для них. Агрегат упорядоченного набора обычно используется для реализации вычисления, зависящего от определенного порядка строк, например ранга или процентиля, так что порядок сортировки является обязательным аспектом любого вызова. Например, встроенное определение percentile_disc эквивалентно:
CREATE FUNCTION ordered_set_transition(internal, anyelement)
RETURNS internal ...;
CREATE FUNCTION percentile_disc_final(internal, float8, anyelement)
RETURNS anyelement ...;
CREATE AGGREGATE percentile_disc (float8 ORDER BY anyelement)
(
sfunc = ordered_set_transition,
stype = internal,
finalfunc = percentile_disc_final,
finalfunc_extra
);
Эта агрегатная функция принимает float8 прямой аргумент (долю процентиля) и агрегированный ввод, который может быть любого сортированного типа данных. Она может использоваться для получения медианы дохода домохозяйства следующим образом:
SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY income) FROM households;
percentile_disc
-----------------
50489
Здесь 0.5 является прямым аргументом; было бы бессмысленно, чтобы доля процентиля была значением, изменяющимся от строки к строке.
В отличие от случая с нормальными агрегатами, сортировка входных строк для упорядоченного набора агрегатов выполняется не за кулисами, а является ответственностью функций поддержки агрегата. Типичный подход к реализации заключается в сохранении ссылки на объект «tuplesort» в значение состояния агрегата, подаче входящих строк в этот объект и затем завершении сортировки и считывании данных в конечной функции. Этот дизайн позволяет конечной функции выполнять специальные операции, такие как инъекция дополнительных «гипотетических» строк в данные, которые должны быть отсортированы. В то время как обычные агрегаты часто могут быть реализованы с помощью функций поддержки, написанных на PL/pgSQL или другом языке PL, агрегаты упорядоченных наборов обычно должны быть написаны на C, поскольку их значения состояния не могут быть определены ни как какой-либо тип данных SQL. (В приведенном выше примере обратите внимание, что значение состояния объявлено типом internal – это типично.) Кроме того, из-за того, что окончательная функция выполняет сортировку, невозможно продолжать добавлять входящие строки, повторно выполняя функцию перехода позже. Это означает, что конечная функция не является READ_ONLY; она должна быть объявлена в CREATE AGGREGATE как READ_WRITE, или как SHAREABLE, если возможно, что дополнительные вызовы конечных функций смогут использовать уже отсортированное состояние.
Функция перехода состояния для агрегатной функции с упорядоченным набором получает текущее значение состояния плюс агрегированные входные значения для каждой строки и возвращает обновленное значение состояния. Это то же определение, что и для обычных агрегатов, но обратите внимание, что прямые аргументы (если таковые имеются) не предоставляются. Финальная функция принимает последнее значение состояния, значения прямых аргументов, если они есть, а также (если указано finalfunc_extra) пустые значения, соответствующие агрегированным входным данным. Как и обычные агрегаты, finalfunc_extra действительно полезен только в том случае, если агрегат является полиморфным; тогда дополнительные фиктивные аргументы необходимы для подключения типа результата финальной функции к типу(ам) ввода агрегата.
В настоящее время агрегаты с упорядоченными наборами не могут использоваться в качестве оконных функций, поэтому нет необходимости поддерживать режим движущегося агрегирования.
Частичная агрегация
Дополнительно агрегатная функция может поддерживать частичную агрегацию. Идея частичной агрегации заключается в том, чтобы запустить функцию перехода состояния агрегата по разным подмножествам входных данных независимо друг от друга, а затем объединить полученные из этих подмножеств значения состояний для получения того же значения состояния, которое было бы получено при сканировании всех входных данных за одну операцию. Этот режим можно использовать для параллельной агрегации путем сканирования различных частей таблицы разными рабочими процессами. Каждый рабочий процесс создает частичное состояние, а в конце эти состояния объединяются для создания конечного состояния.
Для поддержки частичной агрегации определение агрегата должно предоставлять функцию объединения, которая принимает два значения типа состояния агрегата (представляющие результаты агрегирования двух подмножеств входных строк) и создает новое значение типа состояния, представляющее то, каким было бы состояние после агрегирования над комбинацией этих наборов строк. При этом относительный порядок входных строк в этих двух множествах не оговаривается. Это означает, что обычно невозможно определить полезную функцию объединения для агрегатов, чувствительных к порядку ввода строк.
В качестве простых примеров, агрегаты MAX и MIN могут быть сделаны для поддержки частичной агрегации путем указания функции объединения как той же функции сравнения большего или меньшего из двух, которая используется в их переходной функции. Агрегатам SUM просто нужна функция сложения в качестве объединяющей функции. (Опять же, это та же самая функция, что и их переходная функция, если значение состояния шире, чем тип входных данных.)
Функция объединения обрабатывается почти так же, как переходная функция, которая случайно принимает значение типа состояния, а не базового входного типа, в качестве своего второго аргумента. В частности, правила обработки значений NULL и строгих функций аналогичны. Кроме того, если определение агрегата указывает ненулевое значение initcond, имейте в виду, что оно будет использоваться не только в качестве начального состояния для каждого прогона частичного агрегирования, но также и в качестве начального состояния для функции объединения, которая будет вызвана для объединения каждого частичного результата в это состояние.
Если тип состояния агрегата объявлен как internal, ответственность за результат функции объединения заключается в том, чтобы он был выделен в правильном контексте памяти для значений состояния агрегата. Это означает, в частности, что когда первый ввод является NULL, недопустимо просто возвращать второй ввод, поскольку это значение будет находиться в неправильном контексте и не будет иметь достаточного срока службы.
Когда тип состояния агрегата объявлен как internal, обычно также целесообразно для определения агрегата предоставить функцию сериализации и функцию десериализации, которые позволяют копировать такое значение состояния из одного процесса в другой. Без этих функций параллельная агрегация не может быть выполнена, а будущие приложения, такие как локальная/дистанционная агрегация, вероятно, тоже не будут работать.
Функция сериализации должна принимать единственный аргумент типа internal и возвращать результат типа bytea, который представляет состояние значения, упакованное в плоский блок байтов. В противоположность этому функция десериализации обращает это преобразование вспять. Она должна принимать два аргумента типов bytea и internal, и возвращать результат типа internal. (Второй аргумент не используется и всегда равен нулю, но он требуется по причинам безопасности типов.) Результат функции десериализации должен просто выделяться в текущем контексте памяти, поскольку, в отличие от результата функции объединения, он недолговечен.
Также стоит отметить, что для выполнения агрегата параллельно сам агрегат должен быть помечен как PARALLEL SAFE. Метки параллельности на его вспомогательных функциях не рассматриваются.
Вспомогательные функции для агрегатов
Функция, написанная на языке C, может определить, что она вызывается в качестве функции поддержки агрегирования, вызывая AggCheckCallContext, например:
if (AggCheckCallContext(fcinfo, NULL))
Одна из причин для проверки этого заключается в том, что когда это правда, первый ввод должен быть временным значением состояния и поэтому его можно безопасно изменить на месте вместо выделения новой копии. См. int8inc() для примера. (Хотя переходные функции агрегации всегда разрешают изменять значение перехода на месте, конечные функции агрегации обычно не рекомендуются делать это; если они делают это, поведение должно быть объявлено при создании агрегата. См. CREATE AGGREGATE для получения более подробной информации.)
Второй аргумент функции AggCheckCallContext может использоваться для получения контекста памяти, в котором хранятся значения агрегатного состояния. Это полезно для функций перехода, которые хотят использовать объекты «расширенные» (см. раздел «Особенности TOAST») в качестве своих значений состояния. При первом вызове функция перехода должна вернуть расширенный объект, контекст памяти которого является дочерним по отношению к контексту агрегатного состояния, а затем продолжать возвращать один и тот же расширенный объект при последующих вызовах. См. пример в array_append(). (array_append() не является функцией перехода какой-либо встроенной агрегации, но она написана так, чтобы работать эффективно при использовании в качестве функции перехода пользовательской агрегации.)
Еще одна вспомогательная процедура, доступная для агрегатных функций, написанных на языке C, это AggGetAggref, которая возвращает узел разбора Aggref, который определяет вызов агрегата. Это главным образом полезно для агрегированных наборов с упорядочением, которые могут проверять подструктуру узла Aggref, чтобы определить, какую сортировку они должны реализовать. Примеры можно найти в файле orderedsetaggs.c исходного кода PostgreSQL.