У меня есть следующий набор данных в PostgreSQL 15:
Генерация данных
SELECT id, value, expected_output, comment
FROM (VALUES
(1, 0.89, 0.89, ''),
(2, 0.17, 0.17, ''),
(3, -0.30, 0, 'accum = -0.30'),
(4, -0.36, 0, 'accum = -0.30 - 0.36 = -0.66'),
(5, 1.41, 0.75, '0.75 = 1.41 - 0.66; accum = 0'),
(6, -0.49, 0, 'accum = -0.49'),
(7, 0.24, 0, '0, because 0.24 - 0.49 = -0.25 is still negative, so accum = -0.25'),
(8, 6.15, 5.9, '5.9 = 6.15 - 0.25; accum = 0'),
(9, 6.05, 6.05, '')
)
AS t(id, value, expected_output, comment);
И я хочу сгенерировать ожидаемый результат с помощью функции. Идея состоит в том, что отрицательные значения следует накапливать, а затем дисконтировать всякий раз, когда возникает положительное значение.
Я попытался создать агрегат, который условно суммирует только отрицательные значения, а затем использовал его как оконную функцию. Функция состояния агрегата, которую я придумал, такова:
CREATE OR REPLACE FUNCTION public.sum_if_less_accum(
_accumulated anyelement,
_current anyelement)
RETURNS anyelement
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
IF _current < 0 OR _accumulated < 0THEN
RETURN _accumulated + _current;
ELSIF _current > _accumulated THEN
return 0;
END IF;
RETURN _accumulated;
END
$BODY$;
Я пробовал использовать это с некоторыми выражениями CASE
, но не смог добиться ожидаемого результата. Есть ли способ напрямую создать агрегат, который делает то, что я хочу?
Да, вы можете создать пользовательскую агрегатную функцию:
-- state transition function
CREATE OR REPLACE FUNCTION f_sum_dept(numeric, numeric)
RETURNS numeric
LANGUAGE sql IMMUTABLE PARALLEL SAFE
RETURN LEAST($1, 0) + $2;
-- final function
CREATE OR REPLACE FUNCTION f_sum_dept_final(numeric)
RETURNS numeric
LANGUAGE sql IMMUTABLE PARALLEL SAFE
RETURN GREATEST($1, 0);
-- aggregate
CREATE OR REPLACE AGGREGATE sum_dept(numeric) (
SFUNC = f_sum_dept
, STYPE = numeric
, INITCOND = 0
, FINALFUNC = f_sum_dept_final
);
Это требует ввода numeric
. (Возможна также полиморфная версия.)
По сути, это создает текущую сумму, но исключает положительные промежуточные итоги предыдущей итерации (как будто они были выплачены).
Наконец, вместо отрицательных промежуточных итогов возвращается 0
.
Стандартные функции SQL, которые я использую здесь, требуют Postgres 14 или более поздней версии. Вам подойдет (стр. 15). В более старых версиях используйте тела функций в кавычках. Простота настройки. Видеть:
Ваш запрос теперь очень прост. Используйте агрегат при вызове оконной функции:
SELECT id, value
, sum_dept(value) OVER (ORDER BY id) AS expected_output
FROM tbl;
Последняя функция в агрегате является необязательной и делает его более специализированным. Вы можете оставить это, чтобы сделать агрегат более общим, и вместо этого реализовать последний шаг запроса:
CREATE OR REPLACE AGGREGATE sum_dept(numeric) (
SFUNC = f_sum_dept -- same as above
, STYPE = numeric
, INITCOND = 0
);
SELECT id, value
, GREATEST(sum_dept(value) OVER (ORDER BY id), 0) AS expected_output
FROM tbl;