如何根据PostgreSQL part2中的枚举列总结所有行？ [英] How to sum up all rows based on a enumeration column in PostgreSQL part2?

问题描述

目前我正在使用这个函数触发...

<$ p $ （timeelapse）FROM（floor（timeindex）:: int）floor（timeindex）:: int timeindex，timeelapse FROM mytable WHERE pcnum = NEW.pcnum AND fnname = NEW.fnname AND timetype ='Lap'ORDER BY 1，2 DESC）别名INTO v_sumtimeelapse_fn;
如果发现那么
NEW.timeelapse：= v_sumtimeelapse_fn;
ELSE
使用MESSAGE的RAISE EXCEPTION ='没有任何前一行...';
END IF;
END IF;

因此，每当'Total'被插入到timetype列时，它总计所有timeelapse FROM mytable WHERE pcnum = NEW.pcnum AND fnname = NEW.fname AND timetype ='Lap'，但只有timeindex具有最高的百分数值（在mytable中，它们将是前三个skyblue高亮区= 00:01:00，00:03:00，00 ：04：00（1.001,2.003,3.003））并将结果放置在NEW.timeelapse中（在mytable中，它将是第一个蓝色突出显示的square = 00:08:00）

这个函数使得SUM正确，但我想添加两条规则......

1. 当'Total'被插入时间类型时，如果函数在'开始'之后没有找到'Lap'（两者都具有相同的pcnum和fnname），那么它应该显示一个错误消息'，没有任何Lap。 ..（在mytable中，它将是红色突出显示的正方形）。请注意，fnname ='bbbb'的'开始'之后没有任何'Lap'的fnname ='bbbb'。我尝试设置我的功能，使用...

    使用MESSAGE提升异常='没有任何前一行...'
     

如果我在这种情况下插入一个'Total'，它简单地留下NEW.timeelapse NULL。

2. 该函数应该使用所有已经设置的规则对timeelapse进行求和，但只能从那些在同一个pcnum和fnname的最后（后代）'Total'之下（在mytable中它将成为第二个蓝色突出显示的正方形）注意它是SUM只有在最后一个（后代）'Total'之后的时间戳（在mytable中，它将是第四个突出显示的蓝色方块）。
   

解决方案

要从计算行中排除早于总计您可以在附件查询中使用主键。
声明新变量 v_fnserial 。
为给定的 pcnum 和<$ c找到最后一次出现'Total'的行的 fnserial $ c> fnname 并将值赋给 v_fnserial 。
在主查询中添加一个条件 fnserial> v_fnserial 。

当主查询返回 null 时，应该引发异常。

  IF NEW.timetype ='Total'THEN 
 SELECT fnserial INTO v_fnserial 
 FROM mytable 
 WHERE timetype ='Total'AND pcnum = NEW.pcnum AND fnname = NEW.fnname 
 ORDER BY fnserial DESC LIMIT 1; 
 
 SELECT SUM（timeelapse）FROM（
 SELECT DISTINCT ON（floor（timeindex）:: int）floor（timeindex）:: int timeindex，timeelapse 
 FROM mytable 
 WHERE fnserial> coalesce（v_fnserial，0）AND pcnum = NEW.pcnum AND fnname = NEW.fnname AND timetype ='Lap'
 ORDER BY 1，2 DESC）别名
 INTO v_sumtimeelapse_fn; 
 IF v_sumtimeelapse_fn NOTNULL THEN 
 NEW.timeelapse：= v_sumtimeelapse_fn; 
 ELSE 
使用MESSAGE的RAISE EXCEPTION ='没有任何前一行...'; 
 END IF; 
 END IF; 
  

))Hi all, this is my table...

Currently Im using this function trigger...

IF NEW.timetype = 'Total' THEN
  SELECT SUM(timeelapse) FROM (SELECT DISTINCT ON (floor(timeindex)::int) floor(timeindex)::int timeindex, timeelapse FROM mytable WHERE pcnum = NEW.pcnum AND fnname = NEW.fnname AND timetype = 'Lap' ORDER BY 1, 2 DESC) alias INTO v_sumtimeelapse_fn;
    IF FOUND THEN
      NEW.timeelapse := v_sumtimeelapse_fn;
    ELSE
      RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...';
    END IF;
END IF;

So whenever 'Total' is INSERTed on the timetype column it SUMs up all timeelapse FROM mytable WHERE pcnum = NEW.pcnum AND fnname = NEW.fname AND timetype = 'Lap' but only where timeindex has the highest centesimal value (in mytable they would be the first three skyblue highighted squares = 00:01:00, 00:03:00, 00:04:00 (1.001, 2.003, 3.003)) and place the result in the NEW.timeelapse (in mytable it would be the first blue highlighted square = 00:08:00)

The function makes the SUM correctly, but I would like to add two more rules to it...

1. when 'Total' is INSERTed in timetype, if the function doesn't find any 'Lap' after a 'Start' (both with the same pcnum and fnname) then it should show an ERROR MESSAGE "'there isn't any Lap..." (in mytable it would be the red highlighted square). Note that there isn't any 'Lap' of fnname = 'bbbb' after 'Start' of fnname = 'bbbb'. I try to set my function to do so, using...

   RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...'
   

but it just doesn't work, if I INSERT a 'Total' in that situation, it simple leaves the NEW.timeelapse NULL.

2. The function should SUM the timeelapse with all its already set rules, but only FROM those which are below the last(descendent) 'Total' of the same pcnum and fnname (in mytable it will be the second blue highlighted square) Note that it SUM only the timeelapses that are after the last(descendet) 'Total' (in mytable it will be the fourth skyblue highlighted square).

Thanks Advanced.

解决方案

To exclude from the calculation rows earlier than last Total you can use the primary key in an accessory query. Declare new variable v_fnserial. Find a fnserial of the row with last occurence of 'Total' for given pcnum and fnname and assign the value to v_fnserial. In the main query add a condition fnserial > v_fnserial.

You should raise an exception when the main query returns null.

IF NEW.timetype = 'Total' THEN
    SELECT fnserial INTO v_fnserial
    FROM mytable 
    WHERE timetype = 'Total' AND pcnum = NEW.pcnum AND fnname = NEW.fnname
    ORDER BY fnserial DESC LIMIT 1;

    SELECT SUM(timeelapse) FROM (
        SELECT DISTINCT ON (floor(timeindex)::int) floor(timeindex)::int timeindex, timeelapse 
        FROM mytable 
        WHERE fnserial > coalesce(v_fnserial, 0) AND pcnum = NEW.pcnum AND fnname = NEW.fnname AND timetype = 'Lap' 
        ORDER BY 1, 2 DESC) alias 
    INTO v_sumtimeelapse_fn;
    IF v_sumtimeelapse_fn NOTNULL THEN
        NEW.timeelapse := v_sumtimeelapse_fn;
    ELSE
        RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...';
    END IF;
END IF;

这篇关于如何根据PostgreSQL part2中的枚举列总结所有行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！