Столбец ОБНОВЛЕНИЕ с накопительной суммой
У меня есть столбец a_increment, и я хочу использовать его для вычисления столбца a_cumulative, который представляет собой сумму всех предыдущих значений a_increment, включая текущую строку. «Раньше» согласно определению в столбце timestamp"DateTime".
Я пробовал несколько подходов, например итеративный подход, при котором я вычисляю новый a_cumulative как предыдущий a_cumulative + текущий a_increment. Однако это было сделано в обратном направлении во времени, что не дает правильного результата.
Я обнаружил, что оконная функция может дать правильный результат, однако оконные функции не разрешены при обновлении таблицы.
Моя интуиция подсказывает, что использование итеративного подхода будет самым быстрым, но я не знаю, как заставить итерацию продолжаться.
Вот чего я ожидаю:
ID "DateTime" a_increment a_cumulative
1 "2024-05-17 09:00:04+02" 1 1
2 "2024-05-17 09:00:14+02" 2 3
3 "2024-05-17 09:00:24+02" 0 3
4 "2024-05-17 09:00:34+02" 5 8
Это код для брутфорса (работает, но очень неэффективен, на запуск ушло более 5 дней):
UPDATE data_cumulative_sums t
SET a_cumulative = COALESCE((SELECT SUM(a_increment) FROM "Data" WHERE "DateTime" <= t."DateTime"), 0);
Вот где я попытался сделать это итеративно:
UPDATE data_cumulative_sums t
SET a_cumulative = COALESCE((SELECT COALESCE(t2.a_cumulative, 0) FROM data_cumulative_sums t2 WHERE t2.id = t.id - 1), 0) + t.a_increment
Это возвращает:
ID "DateTime" a_increment a_cumulative
1 "2024-05-17 09:00:04+02" 1 1
2 "2024-05-17 09:00:14+02" 2 2
3 "2024-05-17 09:00:24+02" 0 0
4 "2024-05-17 09:00:34+02" 5 5
Если я запущу его снова, он вернет:
ID "DateTime" a_increment a_cumulative
1 "2024-05-17 09:00:04+02" 1 1
2 "2024-05-17 09:00:14+02" 2 3
3 "2024-05-17 09:00:24+02" 0 2
4 "2024-05-17 09:00:34+02" 5 5
Если я запущу его столько раз, сколько у меня строк, ожидаемый результат будет достигнут (но, конечно, это будет слишком медленно)
Я нашел оконную функцию:
Select sum(a_increment) over (order by "DateTime")
from data_cumulative_sums
который возвращает ожидаемый результат. Однако, когда я пытаюсь:
UPDATE data_cumulative_sums t
SET a_cumulative =sum(a_increment) OVER (ORDER BY "DateTime");
Я получаю ошибку:
оконные функции не разрешены в UPDATE
Помимо запрошенных @ValNik элементов также включите ожидаемые результаты этих данных. Добавьте все как обновление к вашему вопросу, но не как дополнительный комментарий.
Спасибо за ответ! Я внес некоторые изменения в свой пост, надеюсь, это поможет!
Ответы 1
... однако оконные функции не допускаются при обновлении таблицы.
Это почти, но не совсем так. (Это сообщение об ошибке может быть более конкретным.) Вы по-прежнему можете использовать оконные функции в подзапросе или CTE, а затем самостоятельно присоединиться. Нравиться:
UPDATE "Data" t
SET a_cumulative = COALESCE(u.a_cumu, 0)
FROM (
SELECT id, SUM(a_increment) OVER (ORDER BY "DateTime") AS a_cumu
FROM "Data"
) u
WHERE u.id = t.id;
id является первичным ключом таблицы. Вы можете использовать любые уникальные, ненулевые (комбинации) столбцы.
Если в "DateTime" могут быть повторяющиеся значения, вам необходимо точно определить, как с ними обращаться. Этот запрос включает все пиры в a_cumulative.
Отлично, спасибо большое! Есть даже бонус: ID не всегда указывается в хронологическом порядке, однако накопительная сумма рассчитывается в хронологическом порядке. Это идеально =)
Пожалуйста, покажите пример данных и вашу текущую попытку. См. sum(a_increment)over(partition by... order by Datetime) как a_cumulative.