Скидка накопленной отрицательной суммы из столбца

У меня есть следующий набор данных в 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);
идентификатор ценить ожидаемый_выход комментарий 1 0,89 0,89 2 0,17 0,17 3 -0,30 0 накопление = -0,30 4 -0,36 0 накопление = -0,30 - 0,36 = -0,66 5 1.41 0,75 0,75 = 1,41 – 0,66; накопление = 0 6 -0,49 0 накопление = -0,49 7 0,24 0 0, поскольку 0,24–0,49 = -0,25 по-прежнему отрицательно, поэтому накопление = -0,25. 8 6.15 5,9 5,9 = 6,15 – 0,25; накопление = 0 9 6.05 6.05

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

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

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, но не смог добиться ожидаемого результата. Есть ли способ напрямую создать агрегат, который делает то, что я хочу?

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
2
0
68
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

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

-- 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;

рабочий пример

Другие вопросы по теме