时间戳

首页 > 解决方案 > 尝试更新表时，postgresql 中列的最小-最大规范化不起作用

sql - 尝试更新表时，postgresql 中列的最小-最大规范化不起作用

问题描述

我有一个列的最小，最大规范化查询，它工作正常：

SELECT
    1.00*(number_of_pedestrians_killed-Min_number_of_pedestrians_killed)/number_of_pedestrians_killed_Range
FROM
    (
    SELECT
       number_of_pedestrians_killed,
       MIN(number_of_pedestrians_killed) OVER () AS Min_number_of_pedestrians_killed,
       MAX(number_of_pedestrians_killed) OVER () - MIN(number_of_pedestrians_killed) OVER () AS number_of_pedestrians_killed_Range

    FROM
       ways
    ) x ;

但是当我尝试将这些值添加到表列中时它不起作用

UPDATE ways
SET ways.Number_of_pedestrians_killed_normalized = 1.00 * (ways.Number_of_pedestrians_killed - Min_number_of_pedestrians_killed) / number_of_pedestrians_killed_Range
FROM
  (
      SELECT number_of_pedestrians_killed,
        min(number_of_pedestrians_killed) OVER () AS Min_number_of_pedestrians_killed,
        max(number_of_pedestrians_killed) OVER () - min(number_of_pedestrians_killed) OVER () AS number_of_pedestrians_killed_Range

    FROM ways
  ) x ;

错误是：关系“方式”的“方式”列不存在。我尝试过无路可走。但我再次收到一条错误消息，指出列引用“number_of_pedestrians_killed”不明确。任何帮助，将不胜感激！

标签： sqlpostgresql

解决方案

要修复语法，您需要执行以下操作：

UPDATE ways w
    SET Number_of_pedestrians_killed_normalized = 1.00 * (w.Number_of_pedestrians_killed - x.Min_number_of_pedestrians_killed) / x.number_of_pedestrians_killed_Range
FROM
    (
        SELECT number_of_pedestrians_killed,
               min(number_of_pedestrians_killed) OVER () AS Min_number_of_pedestrians_killed,
               max(number_of_pedestrians_killed) OVER () - min(number_of_pedestrians_killed) OVER () AS number_of_pedestrians_killed_Range

        FROM ways
    ) x ;

但请注意，没有where条款！这意味着您将多次更新表的每一行（选择结果的数量）。

你最好做这样的事情：

update ways w
    set ...
    from ( select id, ... from ways group by id) x
    where w.id=x.id;

推荐阅读