Пользовательские агрегаты

Агрегатные функции в QHB определяются в терминах значений состояния и функций перехода состояния. То есть агрегат работает с некоторым значением состояния, которое обновляется при обработке каждой последующей входной строки. Чтобы определить новую агрегатную функцию, надо выбрать тип данных для значения состояния, его начальное значение и функцию перехода состояния. Функция перехода состояния принимает предыдущее значение состояния и входное значение (или значения) агрегата для текущей строки и возвращает новое значение состояния. В случае, если желаемый результат агрегата отличается от данных, которые нужно сохранить в изменяющемся значении состояния, можно также указать функцию завершения. Функция завершения принимает конечное значение состояния и возвращает то, что требуется в качестве результата агрегата. В принципе, функции перехода и завершения — это просто обычные функции, которые можно использовать и вне контекста агрегата. (На практике для повышения производительности часто бывает полезно создать специализированные функции перехода, которые могут работать только при вызове в составе агрегата).

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

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

Приведенное выше определение sum вернет ноль (значение начального состояния), если среди входных данных нет значений, отличных от NULL. Возможно, в таком случае у нас возникнет желание вернуть вместо этого NULL — по стандарту SQL ожидается, что sum будет вести себя именно так. Мы можем добиться этого, просто опустив фразу initcond, так что начальным значением состояния будет NULL. Обычно это будет означать, что функции sfunc понадобится проверить входное значение состояния на NULL. Но для sum и некоторых других простых агрегатов вроде max и min достаточно вставить в переменную состояния первое входное значение не NULL, а затем начать применять функцию перехода со второго значения не NULL. QHB сделает это автоматически, если значение начального состояния равно NULL и функция перехода помечена как «strict» (т. е. не должна вызываться для входных значений NULL).

Еще одна особенность поведения по умолчанию функции перехода «strict» заключается в том, что каждый раз, когда попадается входное значение NULL, предыдущее значение состояние остается неизменным. Таким образом, значения NULL игнорируются. Если вам нужно другое поведение для входных значений NULL, не объявляйте вашу функцию перехода строгой; вместо этого запрограммируйте ее проверять входные значения на NULL и обрабатывать их как требуется.

Функция 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.

Функция прямого перехода для режима движущегося агрегата не разрешено возвращать NULL в качестве нового значения состояния. Если функция обратного перехода возвращает NULL, это воспринимается как признак того, что она не может инвертировать расчет состояния для этого конкретного входного значения, а значит, агрегатное вычисление нужно произвести заново с отметки текущей позиции начала рамки. Это соглашение позволяет использовать режим движущегося агрегата в нечастых ситуациях, когда отматывать обратно текущее значение состояния непрактично. В таких случаях функция обратного перехода может «застопориться», но в большинстве случаев она все равно продолжит пробиваться вперед, насколько это возможно. Например, агрегат, работающий с числами с плавающей запятой, может застопориться, когда ему понадобится убрать из текущего значения состояния значение 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. Обратите внимание, что это принципиальное ограничение всей арифметики чисел с плавающей запятой, а не только QHB.

Агрегаты с полиморфными и переменными аргументами

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

CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    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. Для решения этой проблемы можно объявить функцию завершения принимающей дополнительные «фиктивные» аргументы, которые соответствуют входным аргументам агрегата. В этих фиктивных аргументах всегда передаются значения NULL, так как при вызове функции завершения какое-либо определенное значение недоступно. Единственное их предназначение — позволить типу результата полиморфной функции завершения связаться с типом(ами) входных данных агрегата. Например, определение встроенного агрегата 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 по аналогии с обычными функциями; см. подраздел Функции SQL с переменным числом аргументов. Функции перехода агрегата должны иметь тот же тип массива, что и их последний аргумент. Обычно функции перехода тоже объявляются как VARIADIC, но это не строгое требование.

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

SELECT myaggregate(a ORDER BY a, b, c) FROM ...

анализатор увидит один аргумент агрегатной функции и три ключа сортировки. Однако пользователь мог иметь в виду следующее:

SELECT myaggregate(a, b, c ORDER BY a) FROM ...

Если функция myaggregate является VARIADIC, то оба этих вызова совершенно корректны.

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

Сортирующие агрегаты

Все вышеописанные агрегаты относятся к «нормальным» агрегатам. QHB также поддерживает сортирующие агрегаты, которые имеют два ключевых отличия от нормальных. Во-первых, помимо обычных агрегируемых аргументов, которые вычисляются для каждой входной строки, сортирующий агрегат может принимать «непосредственные» аргументы, которые вычисляются только один раз за всю операцию агрегирования. Во-вторых, синтаксис обычных агрегируемых аргументов задает порядок их сортировки явно. Сортирующий агрегат обычно используется для выполнения вычисления, которое зависит от конкретного порядка строк, например ранг или процентиль, поэтому порядок сортировки является обязательным аспектом каждого вызова. К примеру, встроенное определение функции 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 или другом процедурном языке, сортирующие агрегаты, как правило, должны записываться на нативном языке, так как их значение состояния нельзя выразить каким-либо типом данных SQL. (Обратите внимание, что в приведенном выше примере значение состояния объявлено как имеющее тип internal — это типичный случай.) Кроме того, поскольку сортировку выполняет функция завершения, невозможно продолжать добавление строк, повторно вызывая функцию перехода. Это означает, что функция завершения не может иметь характеристику READ_ONLY; она должна объявляться в CREATE AGGREGATE с характеристикой READ_WRITE или SHAREABLE (если для дополнительных вызовов функции завершения можно использовать уже отсортированное состояние).

Функция перехода состояния для сортирующего агрегата получает текущее значение состояния и агрегируемые входные значения каждой строки и возвращает измененное значение состояния. Это определение аналогично таковому обычных агрегатов, но обратите внимание, что непосредственные аргументы (если таковые имеются) не передаются. Функция завершения получает последнее значение состояния, значения непосредственных аргументов (если таковые имеются) и (если указано finalfunc_extra) значения NULL, соответствующие агрегируемым входным данным. Для нормальных агрегатов указание finalfunc_extra по-настоящему полезно, только если агрегат полиморфный; тогда дополнительные фиктивные аргументы нужны, чтобы связать тип результата функции завершения с типом(ами) входных данных агрегата.

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

Частичная агрегация

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

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

В качестве простых примеров, агрегаты MAX и MIN могут поддерживать частичную агрегацию, если в качестве комбинирующей функции указать функцию сравнения значений (большее-из-двух или меньшее-из-двух соответственно), которую они также используют в качестве функции перехода. Агрегаты SUM просто используют в качестве комбинирующей функцию сложения. (Опять же, эта функция выступает и в качестве их функции перехода, если только значение состояния не выходит за рамки типа входных данных.)

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

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

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

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

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

Вспомогательные функции для агрегатов

Функция, написанная на нативном языке, может определить, была ли она вызвана как вспомогательная функция агрегата, вызвав AggCheckCallContext, например:

if (AggCheckCallContext(fcinfo, NULL))

Единственная причина такой проверки состоит в том, что в случае положительного результата первый входной аргумент должен быть временным значением состояния, которое можно безопасно изменить на месте, вместо того чтобы размещать новую копию. Пример можно увидеть в функции int8inc(). (Хотя агрегатным функциям перехода всегда разрешено изменять значение перехода на месте, агрегатным функциям завершения в целом следует этого избегать; если они это делают, такое поведение должно быть объявлено при создании агрегата. Более подробную информацию см. на справочной странице команды CREATE AGGREGATE.)

Второй аргумент AggCheckCallContext можно использовать для получения контекста памяти, в котором хранятся значения состояния агрегата. Это полезно для функций перехода, которые хотят использовать в качестве значений состояния «развернутые» объекты (см. подраздел Особенности TOAST). При первом вызове эта функция перехода должна вернуть развернутый объект, чей контекст памяти является дочерним по отношению к контексту состояния агрегата, и затем продолжать возвращать тот же развернутый объект при последующих вызовах. Для примера рассмотрим функцию array_append(). (array_append() не является функцией перехода какого-либо встроенного агрегата, но написана так, чтобы эффективно работать в таком качестве в пользовательском агрегате).

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

Copyright © KVANTOM LLC, 2019-2025