更新时间:2023-09-03 15:23:10
UPDATE
使用以下更短,更高效的单个UPDATE
命令替换第一个查询:
UPDATE
Replace the first query with this much shorter and more efficient single UPDATE
command:
UPDATE features
SET (x1,x2,x3,x4,x5,x6, y)
= ((x1 - g.avg1) / g.range1
, (x2 - g.avg2) / g.range2
-- , (x3 - ...
, (y - g.avgy) / g.rangey)
FROM (
SELECT avg(x1) AS avg1, max(x1) - min(x1) AS range1
, avg(x2) AS avg2, max(x2) - min(x2) AS range2
-- , avg(x3) ...
, avg(y) AS avgy, max(y) - min(y) AS rangey
FROM features
) g;
关于简短的UPDATE
语法:
在更简单的查询的基础上,以下是任意数量列的动态函数:
Building on the simpler query, here is a dynamic function for any number of columns:
CREATE OR REPLACE FUNCTION scale_function_dyn()
RETURNS void AS
$func$
DECLARE
cols text; -- list of target columns
vals text; -- list of values to insert
aggs text; -- column list for aggregate query
BEGIN
SELECT INTO cols, vals, aggs
string_agg(quote_ident(attname), ', ')
, string_agg(format('(%I - g.%I) / g.%I'
, attname, 'avg_' || attname, 'range_' || attname), ', ')
, string_agg(format('avg(%1$I) AS %2$I, max(%1$I) - min(%1$I) AS %3$I'
, attname, 'avg_' || attname, 'range_' || attname), ', ')
FROM pg_attribute
WHERE attrelid = 'features'::regclass
AND attname NOT IN ('n', 'x0') -- exclude columns from update
AND NOT attisdropped -- no dropped (dead) columns
AND attnum > 0; -- no system columns
EXECUTE format('UPDATE features
SET (%s) = (%s)
FROM (SELECT %s FROM features) g'
, cols, vals, aggs);
END
$func$ LANGUAGE plpgsql;
相关答案以及更多说明:
Related answer with more explanation: