求助，使用SQL汇总某一列的数据

php_

刚接触sql，现在需要使用存储过程完成如下任务：

有一个表如下：

ID   ADDData      DELData  TIMESTAMP



其中以时间作为索引，时间字段是递增排列的，需要找出整张表中某一段时间内同一个ID的ADDData列和DELData列累加的结果。并写入一张新表中。

下一次累加的时候将新表中的结果作为基值。

renxiao2003

不太明白要干什么。

zhanghb77

with sum_tmp(ID,ADD_SUM,DEL_SUM) as (
  select ID,sum(ADDData),sum(DELData) from table_name
  where t>='YYYY-MM-DD HH:MM:SS' and t<='YYYY-MM-DD HH:MM:SS'
  group by ID
)
update sum_table
set ADDData=ADDData+sum_tmp.ADD_SUM,DELData=DELData+sum_tmp.DEL_SUM
from sum_tmp where sum_table.ID=sum_tmp.ID

renxiao2003

回复 3# zhanghb77


    这个是干什么用的？

php_

非常感谢两位。


我照着zhanghb77提示的方法写了一个：

两张表，table_1和table_2，都有同样的4列，f1,f2,f3,f4
需要将table_1的数据累加到table_2上面。


select f1, f2, sum(f3), sum(f4)
        from table_1
        group by f1, f2
        order by f1
as tbltmp(a1, a2, a3, a4)

update table2
set f3 = f3 + tbltmp.a3, f4 = f4 + tbltmp.a4
where f1 = tbltmp.a1 and f2 = tbltmp.a2


但是这样的过程似乎没有办法运行。


请版主和大家再帮忙看一下，这样的思路是否能用存储过程实现，多谢。

zhanghb77

用CTE不就是一条SQL语句就能搞定，没必要用存储过程。
你那个update也写错了。

php_

非常感谢你的回复，我才学这个没几天，但是现在必须搞定这个问题，急啊，再帮忙看看，多谢！

表结构：
CREATE TABLE stub
(
  uid integer,
  bid integer,
  sz integer,
  ct integer
)
WITH (
  OIDS=FALSE
);
ALTER TABLE stub OWNER TO postgres;

stub2和stub完全相同。

用你说的CTE执行不下去啊，只能执行下面这样的语句：

with tbltmp(a1, a2, a3, a4) as
(        

                select uid, bid, sum(sz), sum(ct)
                from stub2
                group by uid, bid
                order by uid
)
select * from tbltmp


如果是：

with tbltmp(a1, a2, a3, a4) as
(        

                select uid, bid, sum(sz), sum(ct)
                from stub2
                group by uid, bid
                order by uid
)
update stub
set uid = 0
from tbltmp
where tbltmp.a1 = uid;
就会报错，提示update处语法错误。


我也尝试写了下面的方方法，创建函数没有问题，但是执行的时候就报错。

CREATE OR REPLACE FUNCTION getcursor()
        RETURNS SETOF refcursor AS
$BODY$
declare

BEGIN

        select * from (
                select uid, bid, sum(sz), sum(ct)
                from stub2
                group by uid, bid
                order by uid)
        as tbltmp(a1, a2, a3, a4);
        

        update stub
        set sz = sz + tbltmp.a3, ct = ct + tbltmp.a4
        from tbltmp
        where uid = tbltmp.a1 and bid = tbltmp.a2;
        
    RETURN;
END;
$BODY$ LANGUAGE plpgsql;

zhanghb77

你的pgsql什么版本，不支持update......from....，不支持的话就用子查询。

with sum_tmp as (
    select uid, bid, sum(sz) as sz, sum(ct) as ct
    from stub2 group by uid,bid order by uid,bid
)
update stub set
sz=sz+(select sz from sum_tmp where sum_tmp.uid=stub.uid and sum_tmp.bid=stub.bid),
ct=ct+(select ct from sum_tmp where sum_tmp.uid=stub.uid and sum_tmp.bid=stub.bid)
where exists (select 1 from sum_tmp where sum_tmp.uid=stub.uid and sum_tmp.bid=stub.bid);

zhanghb77

知道原因了，9.1才支持UPDATE和CTE合用，那就用临时表。

begin;

create temp table sum_tmp (like stub2) on commit drop;

insert into sum_tmp
select uid, bid, sum(sz) as sz, sum(ct) as ct
from stub2 group by uid,bid order by uid,bid;

update stub
set sz=sz+sum_tmp.sz,ct=ct+sum_tmp.ct
from sum_tmp
where stub.uid=sum_tmp.uid and stub.bid=sum_tmp.bid;

commit;

php_

太感谢了！的确是用的8.1的版本。


也考虑到了临时表，担心临时表会不会有性能问题。我先尝试一下吧。

还有一个问题，如果需要累加的数据在底表中找不到，则需要插入一行新的数据，将这个逻辑结合进来有办法做么？

底表在一开始的时候是空的，是数据不断插入才建立起来的底表。


是不是必须用游标来逐行操作了？