PostgreSQL数学函数

Question

我有一个包含许多整数字段的表aps_sections (如bare_width和worn_width)。我还有多个查找表(如aps_bare_width和aps_worn_width)，它们包含一个ID列和一个加权列。ID记录在aps_sections表的上述列中。我需要对aps_sections表中列的权重进行求和(加权值来自查找表)。我已经使用下面的SELECT语句成功地完成了这一任务。

SELECT aps_sections.ogc_fid,
       ( aps_bare_width.weighting
       + aps_worn_width.weighting
       + aps_gradient.weighting
       + aps_braiding.weighting
       + aps_pigeon.weighting
       + aps_depth.weighting
       + aps_standing_water.weighting
       + aps_running_water.weighting
       + aps_roughness.weighting
       + aps_surface.weighting
       + aps_dynamic.weighting
       + aps_ex_cond.weighting
       + aps_promotion.weighting
       + aps_level_of_use.weighting) AS calc
FROM row_access.aps_sections,
     row_access.aps_bare_width,
     row_access.aps_worn_width,
     row_access.aps_gradient,
     row_access.aps_braiding,
     row_access.aps_pigeon,
     row_access.aps_depth,
     row_access.aps_standing_water,
     row_access.aps_running_water,
     row_access.aps_roughness,
     row_access.aps_surface,
     row_access.aps_dynamic,
     row_access.aps_ex_cond,
     row_access.aps_promotion,
     row_access.aps_level_of_use
WHERE aps_bare_width.fid = aps_sections.bare_width
AND   aps_worn_width.fid = aps_sections.worn_width
AND   aps_gradient.fid = aps_sections.gradient
AND   aps_braiding.fid = aps_sections.braiding
AND   aps_pigeon.fid = aps_sections.pigeon
AND   aps_depth.fid = aps_sections.depth
AND   aps_standing_water.fid = aps_sections.standing_water
AND   aps_running_water.fid = aps_sections.running_water
AND   aps_roughness.fid = aps_sections.roughness
AND   aps_surface.fid = aps_sections.surface
AND   aps_dynamic.fid = aps_sections.dynamic
AND   aps_ex_cond.fid = aps_sections.ex_cond
AND   aps_promotion.fid = aps_sections.promotion
AND   aps_level_of_use.fid = aps_sections.level_of_use

现在我需要做的是创建一个函数，它将计算的结果添加到aps_sections表的physical_sn_priority列中。到目前为止，我的理解是，我的功能应该类似于：

CREATE OR REPLACE FUNCTION row_access.aps_weightings()
  RETURNS trigger AS
$BODY$
    BEGIN
    NEW.physical_sn_priority := ;
       RETURN NEW;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.update_km()
  OWNER TO postgres;

但我不知道在NEW.physical_sn_priority :=之后该放什么。我是一个对SQL和PostgreSQL的初学者，所以我希望得到任何指导！

Answer 1

虽然Erwin (一如既往)是正确的，一个版本将是有帮助的，但我认为您的答案在SELECT ... INTO 施工 for PL/pgSQL中将是最简单的。(不像SELECT INTO那样工作，比如INSERT或CREATE TABLE。)

SELECT ( aps_bare_width.weighting
   + /* obvious deletia */
   + aps_level_of_use.weighting)
INTO NEW.physical_sn_priority
FROM row_access.aps_bare_width,
 /* snip */,
 row_access.aps_level_of_use
WHERE aps_bare_width.fid = NEW.bare_width
    AND /* snip */
    aps_level_of_use.fid = NEW.level_of_use;

RETURN NEW;

根据文档，INTO可以出现在行中的其他几个地方；我觉得这很容易理解。

编辑

虽然这是可行的，但经过反思，我认为应该修改架构。

CREATE TYPE weighted_item_t AS ENUM ('bare_width', /* ... */, 'level_of_use');
CREATE TABLE current_weights(item_type weighted_item_t, fid int, current_weight float);
     /* key and checks omitted */
     /* note, if item_type can be deduced from fid, we don't even need the enum */
CREATE TABLE sections_items(section_id int /* FK into aps_sections */,
     item_type weighted_item_t, fid int);

现在，查询将简化为简单的和。您需要在section_items之前将记录插入到aps_sections中，这可以在有或没有存储过程的事务中使用延迟约束来完成，这取决于您如何获取数据以及对其格式有多大的控制。如果(这还不清楚，因为更新时不会更改)，则可以使用

SELECT SUM(current_weight) INTO NEW.physical_sn_priority
FROM section_items NATURAL JOIN current_weights
WHERE NEW.section_id=section_items.section_id;

如果在将来的某个日期增加额外的加权特性，这将做得更好。

Answer 2

简化测试用例

你应该少噪音地提出你的问题。这个较短的查询可以很好地完成任务：

SELECT aps_sections.ogc_fid,
       ( aps_bare_width.weighting
       + aps_worn_width.weighting
       + aps_gradient.weighting) AS calc
FROM row_access.aps_sections,
     row_access.aps_bare_width,
     row_access.aps_worn_width,
     row_access.aps_gradient,
WHERE aps_bare_width.fid = aps_sections.bare_width
AND   aps_worn_width.fid = aps_sections.worn_width
AND   aps_gradient.fid = aps_sections.gradient;

回答

作为@安德鲁已经建议，最好的方法是简化模式。

如果由于某种原因这是不可能的，这里有一种方法可以简化许多列的添加(这些列可能是NULL，也可能不是)，那么创建这个小而强大的函数：

CREATE OR REPLACE FUNCTION f_sum(ANYARRAY)
  RETURNS numeric LANGUAGE sql AS
'SELECT sum(i)::numeric FROM unnest($1) i';

呼叫：

SELECT f_sum('{2,NULL,7}'::int[])

• 它采用多态数组类型并返回numeric。适用于可由sum()求和的任何数字类型。如有需要，请投出结果。
• 简化了许多列的求和语法。
• NULL值不会破坏计算，因为聚合函数sum()忽略了这些值。

在触发器函数中，可以这样使用：

NEW.physical_sn_priority := f_sum(ARRAY [
    COALESCE(physical_sn_priority, 0)     -- add to original value
   ,(SELECT weighting FROM aps_bare_width x WHERE x.fid = NEW.bare_width)
   ,(SELECT weighting FROM aps_worn_width x WHERE x.fid = NEW.worn_width)
   ,(SELECT weighting FROM aps_gradient   x WHERE x.fid = NEW.gradient)
    ...
   ])

由于所有已连接的表都只适合查找单个字段，并且彼此完全独立，所以您也可以使用单独的子查询。我也是这样做的，因为我们不知道任何子查询是否会返回NULL，在这种情况下，原始查询或Andrew的版本将不会导致任何行/无分配。

分配给NEW实际上只有在表aps_sections上的BEFORE触发器中才有意义。这段代码工作BEFORE INSERT (在表中找不到该行！)以及BEFORE UPDATE。您希望使用NEW中的当前值，而不是表中的旧行版本。