Обновить со многими распространенными подвыражениями

Question

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

Если вам не важна производительность, вы можете просто рассчитать каждый столбец с помощью собственного оператора обновления:

update x set b1 = a1 + a2;
update x set b2 = a3 * b1;
update x set b3 = a4 * b2;
...

Или вы можете сделать все в одном обновлении, если вы расширяете формулы вручную:

update x set
  b1 = a1 + a2,
  b2 = a3 * (a1 + a2),
  b3 = a4 * (a3 * (a1 + a2));

Проблема здесь в том, что эти формулы могут стать огромными и их трудно изменить и отладить.

Есть ли какой-нибудь способ в Postgres (или, может быть, вообще в SQL), чтобы вы могли иметь как производительность, так и удобство обслуживания в этих случаях?

Вот что я хотел бы сделать:

update x set
  b1 = _b1,
  b2 = _b2,
  b3 = _b3
with
  _b1 = a1 + a2,
  _b2 = a3 * _b1,
  _b3 = a4 * _b2;

У меня есть одно рабочее решение, где формулы определены в функции с несколькими возвращаемыми значениями, но это довольно негибко в некоторых других отношениях, поэтому я ищу альтернативы.

Answer 1

В последних версиях PostgreSQL вы можете использовать следующее:

UPDATE atable
SET (col1, col2, col3) =
SELECT expr1, expr2, expr3
FROM ...

Если вам нужно повторно использовать вычисленные выражения в запросе, вы можете использовать WITH:

WITH t1(x1) AS (
   SELECT /* complicated */
), t2(x2) AS (
   SELECT /* complicated using t1 */
) ...
SELECT /* final results */

Я не уверен, делает ли это код более читабельным для вас, но он избегает использования функции и соответствует стандарту (думаю, не проверял).

Answer 2

Я придумал что-то близкое к тому, что я искал:

update x set (b1, b2, b3) = ((
  select b1, b2, b3
  from (select b1, b2, a4 * b2 as b3
  from (select b1, a3 * b1 as b2
  from (select a1 + a2 as b1
  ) as _) as _) as _
));

Это все еще более многословно, чем я хотел бы, но в основном достигает цели отсутствия необходимости расширять формулы без использования функции.