Столбец, сгенерированный Postgresql, требует суммы нескольких столбцов целочисленного массива

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

{
physical_ed: 40,
music: 90,
first_term: {10,23,43},
second_term: {1,5,5,7}
}

Поля массива объявляются как целое число[].

Мне нужно создать столбец оценок, который суммирует все эти поля. До сих пор я пробовал:

ALTER TABLE scores DROP IF EXISTS score;
ALTER TABLE scores add COLUMN total_score integer GENERATED ALWAYS AS (physical_ed::integer + 
first_term[3]::integer + second_term[1]::integer + second_term[2]::integer + second_term[3]::integer) 
STORED;

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

Я попытался запустить оператор выбора, и я могу вычислить сумму каждого массива в операторе выбора, как правило, так:

SELECT *, (SELECT SUM(s) FROM UNNEST(first_term) s) as total_first_term from scores;

К сожалению, это не работает внутри сгенерированного запроса столбца, и нам нужно, чтобы он был частью нашей сгенерированной суммы total_score.


django postgresql django-forms postgresql-12
person JESlabbert    schedule 04.06.2020    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Сначала создайте функцию, которая суммирует элементы массива.

create or replace 
function array_sum (the_array integer[])
  returns integer
  language sql 
  immutable 
as $$
    select sum(a_element)::integer from (select unnest(the_array) a_element) u;
$$;

Затем вызовите эту функцию, чтобы помочь вычислить столбец total_score. (при условии, что ваша таблица уже существует), затем:

alter table scores 
      add column total_score integer 
      generated always as 
      (physical_ed + array_sum(first_term) + array_sum(second_term)) 
      stored;

В качестве альтернативы вы можете объединить массивы перед вызовом функции:

alter table scores 
      add column total_score integer 
      generated always as 
      (physical_ed + array_sum(first_term || second_term)) 
      stored;

Я бы предпочел первое, так как я думаю, что оно более четко указывало на мои намерения. Но это не сильное предпочтение. Оба генерируют один и тот же результат. См. скрипку:

person Belayer    schedule 04.06.2020