36.11. Пользовательские агрегаты#
36.11. Пользовательские агрегаты
Агрегатные функции в Tantor SE определяются в терминах значений состояния и функций перехода состояния. То есть, агрегат работает с использованием значения состояния, которое обновляется при обработке каждой последующей входной строки. Для определения новой агрегатной функции необходимо выбрать тип данных для значения состояния, начальное значение состояния и функцию перехода состояния. Функция перехода состояния принимает предыдущее значение состояния и значения входных данных агрегата для текущей строки и возвращает новое значение состояния. Также можно указать функцию окончательной обработки, если желаемый результат агрегата отличается от данных, которые необходимо сохранить в текущем значении состояния. Функция окончательной обработки принимает конечное значение состояния и возвращает желаемый результат агрегата. В принципе, функции перехода и окончательной обработки являются обычными функциями, которые также могут использоваться вне контекста агрегата. (На практике часто полезно по причинам производительности создавать специализированные функции перехода, которые могут работать только при вызове в составе агрегата).
Таким образом, помимо типов данных аргумента и результата, видимых пользователем агрегата, существует внутренний тип данных значения состояния, который может отличаться как от типа аргумента, так и от типа результата.
Если мы определим агрегат, который не использует финальную функцию,
у нас будет агрегат, который вычисляет накопительную функцию
значений столбца из каждой строки. 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, но Tantor SE может определить, какой вид суммы применяется к столбцу типа complex).
Вышеопределение функции sum вернет ноль (начальное значение состояния), если нет ненулевых входных значений. Возможно, вместо этого мы хотим вернуть null в этом случае - стандарт SQL ожидает, что функция sum будет вести себя таким образом. Мы можем сделать это просто, опустив фразу initcond, чтобы начальное значение состояния было null. Обычно это означало бы, что sfunc должна проверять наличие null входного значения состояния. Но для функций sum и некоторых других простых агрегатов, таких как max и min, достаточно вставить первое ненулевое входное значение в переменную состояния, а затем начать применять функцию перехода к второму ненулевому входному значению. Tantor SE будет делать это автоматически, если начальное значение состояния равно null, а функция перехода помечена как “strict” (то есть не вызывается для null-входов).
Второй аспект стандартного поведения для строгой функции перехода “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.
36.11.1. Режим перемещения агрегата
Агрегатные функции могут дополнительно поддерживать режим подвижной агрегации, что позволяет значительно ускорить выполнение агрегатных функций в окнах с подвижными точками начала рамки.
(См. Раздел 3.5 и Раздел 4.2.8 для получения информации о использовании агрегатных функций в качестве оконных функций).
Основная идея заключается в том, что, помимо обычной “прямой” функции перехода, агрегат предоставляет обратную функцию перехода, которая позволяет удалять строки из текущего значения состояния агрегата при выходе из оконной рамки.
Например, агрегат 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. Обратите внимание, что это ограничение связано с арифметикой с плавающей запятой в целом, а не с ограничениями Tantor SE.
36.11.2. Полиморфные и вариативные агрегаты
Агрегатные функции могут использовать полиморфные функции перехода состояния или конечные функции, так что одни и те же функции могут использоваться для реализации нескольких агрегатов. См. Раздел 36.2.5 для объяснения полиморфных функций. Еще дальше, сама агрегатная функция может быть указана с полиморфным типом(ами) входных данных и типом состояния, позволяя одному определению агрегата служить для нескольких типов входных данных. Вот пример полиморфного агрегата:
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 отклонит на том основании, что тип результата нельзя вывести из вызова. Но иногда неудобно использовать полиморфный тип состояния. Самый распространенный случай - это когда функции поддержки агрегата должны быть написаны на C, и тип состояния должен быть объявлен как 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 массив, так же, как и для обычных функций; см. Раздел 36.5.6. Функции перехода агрегата должны иметь тот же тип массива, что и их последний аргумент. Функции перехода обычно также помечаются как VARIADIC, но это не является обязательным.
Примечание
Вариативные агрегаты легко использовать неправильно в связи с опцией ORDER BY (см. Раздел 4.2.7), так как парсер не может определить, было ли указано неправильное количество фактических аргументов в таком сочетании. Имейте в виду, что все, что находится справа от ORDER BY, является ключом сортировки, а не аргументом агрегата. Например, в
SELECT myaggregate(a ORDER BY a, b, c) FROM ...
парсер увидит это как один аргумент агрегатной функции и три ключа сортировки. Однако, пользователь мог иметь в виду
SELECT myaggregate(a, b, c ORDER BY a) FROM ...
Если myaggregate является вариативным, оба этих вызова могут быть
абсолютно допустимыми.
Для той же причины, стоит дважды подумать, прежде чем создавать агрегатные функции с одинаковыми именами и разным количеством обычных аргументов.
36.11.3. Агрегатные функции с отсортированным набором
Агрегаты, которые мы описывали до сих пор, являются “обычными”
агрегатами. Tantor SE также
поддерживает агрегаты с отсортированным набором, которые отличаются от
обычных агрегатов двумя ключевыми способами. Во-первых, в дополнение к обычным
агрегированным аргументам, которые вычисляются один раз для каждой входной строки,
упорядоченный агрегат может иметь “прямые” аргументы, которые
вычисляются только один раз для каждой агрегатной операции. Во-вторых, синтаксис
для обычных агрегированных аргументов явно указывает порядок сортировки для них.
Упорядоченный агрегат обычно используется для реализации вычисления, которое зависит
от конкретного порядка строк, например, ранга или процентиля, так что порядок сортировки
является необходимым аспектом любого вызова. Например, встроенное
определение 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) значения null, соответствующие агрегированным входным данным. Как и в случае с обычными агрегатами, finalfunc_extra действительно полезна только в том случае, если агрегат является полиморфным; тогда дополнительные фиктивные аргументы необходимы для связи типа результата финальной функции с типом ввода агрегата.
В настоящее время упорядоченные наборы агрегатов не могут использоваться в качестве оконных функций, и, следовательно, нет необходимости в их поддержке режима подвижных агрегатов.
36.11.4. Частичная агрегация
По желанию, агрегатная функция может поддерживать частичную агрегацию. Идея частичной агрегации заключается в том, чтобы запустить функцию перехода состояния агрегата над различными подмножествами входных данных независимо и затем объединить значения состояния, полученные из этих подмножеств, чтобы получить то же значение состояния, которое было бы получено при сканировании всех входных данных в одной операции. Этот режим может быть использован для параллельной агрегации, позволяя различным рабочим процессам сканировать различные части таблицы. Каждый рабочий процесс производит частичное значение состояния, и в конце эти значения состояния объединяются для получения окончательного значения состояния. (В будущем этот режим также может использоваться для целей, таких как объединение агрегаций над локальными и удаленными таблицами; но это пока не реализовано).
Для поддержки частичной агрегации определение агрегата должно предоставить функцию combine, которая принимает два значения типа состояния агрегата (представляющих результаты агрегации двух подмножеств входных строк) и производит новое значение типа состояния, представляющее то, каким было бы состояние после агрегации комбинации этих наборов строк. Неопределено, каким был бы относительный порядок входных строк из двух наборов. Это означает, что обычно невозможно определить полезную функцию combine для агрегатов, чувствительных к порядку входных строк.
В качестве простых примеров агрегаты MAX и MIN могут поддерживать частичную агрегацию, указав функцию объединения в качестве функции сравнения "больше-из-двух" или "меньше-из-двух", которая используется в качестве их переходной функции. Для агрегатов SUM достаточно функции сложения в качестве функции объединения. (Опять же, это то же самое, что и их переходная функция, если значение состояния шире, чем тип входных данных).
Функция combine рассматривается как функция перехода, которая случайно принимает значение типа состояния, а не типа входных данных. В частности, правила обработки значений null и строгих функций аналогичны. Кроме того, если определение агрегата указывает ненулевое значение initcond, имейте в виду, что оно будет использоваться не только в качестве начального состояния для каждого частичного выполнения агрегации, но и в качестве начального состояния для функции combine, которая будет вызываться для объединения каждого частичного результата в это состояние.
Если тип состояния агрегата объявлен как internal, то ответственность за выделение памяти для значения состояния агрегата в правильном контексте памяти лежит на функции комбинирования. Это означает, что в частности, когда первый входной параметр равен NULL, недопустимо просто возвращать второй входной параметр, так как это значение будет находиться в неправильном контексте памяти и не будет иметь достаточного срока службы.
Когда тип состояния агрегата объявлен как internal, обычно также целесообразно, чтобы определение агрегата предоставляло функцию сериализации и функцию десериализации, которые позволяют копировать такое состояние из одного процесса в другой. Без этих функций невозможно выполнять параллельную агрегацию, и, вероятно, не будут работать будущие приложения, такие как локальная/удаленная агрегация.
Сериализационная функция должна принимать единственный аргумент типа internal и возвращать результат типа bytea, который представляет собой состояние значения, упакованное в плоский блоб байтов.
Обратно, функция десериализации обратно преобразует эту конверсию. Она должна принимать два аргумента типов bytea и internal и возвращать результат типа internal. (Второй аргумент не используется и всегда равен нулю, но он требуется по соображениям безопасности типов). Результат функции десериализации должен просто выделяться в текущем контексте памяти, так как, в отличие от результата функции объединения, он не является долговечным.
Важно отметить, что для выполнения агрегата параллельно сам агрегат должен быть помечен как PARALLEL SAFE. Маркировка параллельной безопасности на его вспомогательные функции не учитывается.
36.11.5. Функции поддержки для агрегатов
Функция, написанная на языке C, может определить, что она вызывается в качестве функции поддержки агрегатов, вызвав функцию AggCheckCallContext, например:
if (AggCheckCallContext(fcinfo, NULL))
Одна из причин проверки этого состоит в том, что когда это истинно, первый входной
должен быть временным состоянием и, следовательно, может быть безопасно изменен
на месте, а не выделять новую копию.
См. int8inc() для примера.
(Хотя агрегатные функции перехода всегда могут изменять
значение перехода на месте, агрегатные конечные функции обычно
не рекомендуется делать это; если они это делают, поведение должно быть объявлено
при создании агрегата. См. CREATE AGGREGATE
для получения более подробной информации).
Второй аргумент функции AggCheckCallContext может быть использован для получения контекста памяти, в котором хранятся значения состояния агрегата. Это полезно для переходных функций, которые хотят использовать “расширенные” объекты (см. Раздел 36.12.1) в качестве значений состояния. При первом вызове переходная функция должна вернуть расширенный объект, контекст памяти которого является дочерним для контекста состояния агрегата, а затем сохранять возвращаемый объект при последующих вызовах. См. функцию array_append() для примера. (Функция array_append() не является переходной функцией встроенного агрегата, но она написана так, чтобы работать эффективно при использовании в качестве переходной функции пользовательского агрегата).
Другая вспомогательная функция, доступная для агрегатных функций, написанных на C, - это AggGetAggref, которая возвращает узел разбора Aggref, определяющий вызов агрегата. Это особенно полезно для агрегатов с отсортированным набором, которые могут изучать подструктуру узла Aggref, чтобы узнать, какой порядок сортировки они должны реализовывать. Примеры можно найти в файле orderedsetaggs.c в исходном коде Tantor SE.