sql - 在 UPDATE 语句的 WHERE 中使用聚合函数的解决方法

Question

我正在向使用 PostgreSQL 数据库的项目添加新功能。

数据库中有一个名为 brew_sessions 的表 - 它有一个名为 condition_date 的列。

brew_sessions 通过 brew_sessions.recipe_id = recipes.id 连接到 recipes 表。 recipes 然后通过 recipes.id = recipe_fermentation_steps.recipe_id 连接到另一个名为 recipe_fermentation_steps 的表。

当 condition_date 小于 或等于从 condition_date 值算起的天数。

此值是 recipe_fermentation_steps.time 的总和。

例如:

SELECT
  brew_sessions.id,
  SUM(recipe_fermentation_steps.time)
FROM brew_sessions
  INNER JOIN recipes ON brew_sessions.recipe_id = recipes.id
  INNER JOIN recipe_fermentation_steps ON recipes.id = recipe_fermentation_steps.recipe_id
GROUP BY brew_sessions.id

这让我想到了以下查询:

UPDATE brew_sessions
SET status = 30 FROM recipes
  INNER JOIN recipe_fermentation_steps ON recipes.id = recipe_fermentation_steps.recipe_id
WHERE brew_sessions.recipe_id = recipes.id
AND brew_sessions.condition_date
    <= CURRENT_TIMESTAMP - INTERVAL '1 day' * SUM(recipe_fermentation_steps.time)

但是，这不会运行，因为您不能在 WHERE 中使用聚合函数。

如何正确书写以上内容？

Answer 1

您可以使用子查询。喜欢:

UPDATE brew_sessions b
SET    status = 30
FROM  (
   SELECT recipe_id, SUM(time) AS sum_time
   FROM   recipe_fermentation_steps
   GROUP  BY 1
   ) f
WHERE  b.recipe_id = f.recipe_id
AND    b.condition_date <= CURRENT_TIMESTAMP - INTERVAL '1 day' * f.sum_time;

如果使用 FK 约束强制执行参照完整性，则根本不需要涉及表 recipes。

不过，这种方法值得商榷。通常，您不会将函数相关值或取决于当前时间的值写入表中。使用 VIEW (或 MATERIALIZED VIEW )或类似的。