指尖世界

分享的乐趣

Oracle内根据上一行总数和本行新增数计算本行总数

类别: 观点 更新时间: 2016-02-21

[摘要:需供以下: 数据库内的一张表,天天都市稀有据新促进去,有day字段做日期操纵,天天有新删数战停止当日的总数两个字段值,目前依据前一天的总数战本日的新删数目盘算停止本日的]

需求如下:
数据库内的一张表,每天都会有数据新增进来,有day字段做日期控制,每天有新增数和截止当日的总数两个字段值,现在根据前一天的总数和今天的新增数量计算截止今天的总数,SQL:

--1. 初始化每个区县的 第一条退休人口数据
update T_FACT_SOCAIL_RETIRED t set t.totalcount = t.newcount where t.day = (select min(t2.day) from T_FACT_SOCAIL_RETIRED t2 where t2.areacode = t.areacode);
--2.循序渐进,计算后面每天 的退休人口总数,思路是,第二天的 退休人口总数 = 第一天的退休人口总数 + 第二天新增的退休人口总数,以此类推
begin -- for t in (select * from T_FACT_SOCAIL_RETIRED t3 where t3.areacode = '340222' and t3.totalcount = 0 order by t3.day asc) loop update T_FACT_SOCAIL_RETIRED r set r.totalcount = r.newcount + (select totalcount from ((select r2.totalcount from T_FACT_SOCAIL_RETIRED r2 where r2.areacode = t.areacode and r2.day < t.day order by r2.day desc)) where rownum = 1) where r.day = t.day and r.areacode = t.areacode;
  end loop;
end;


begin for tt in (select distinct areacode from T_FACT_SOCAIL_RETIRED) loop for t in (select * from T_FACT_SOCAIL_RETIRED t3 where t3.areacode = tt.areacode and t3.totalcount = 0 order by t3.day asc) loop update T_FACT_SOCAIL_RETIRED r set r.totalcount = r.newcount + (select totalcount from ((select r2.totalcount from T_FACT_SOCAIL_RETIRED r2 where r2.areacode = t.areacode and r2.day < t.day order by r2.day desc)) where rownum = 1) where r.day = t.day and r.areacode = t.areacode;
  end loop;
    end loop;
end;
--插入每天不存在的区县
begin for tt2 in (select distinct day from T_Fact_Gas_Account) loop for tt in (select t1.areacode from t_dim_area t1 where t1.areatype in ('3','4') and t1.areacode not in (select t2.areacode from T_Fact_Gas_Account t2 where t2.day=tt2.day)) loop insert into T_Fact_Gas_Account values (substr(tt2.day,1,4),substr(tt2.day,1,6),tt2.day,tt.areacode,0,0);
end loop;
end loop;
end;
--更新当前总数
begin for tt in (select distinct areacode from T_Fact_Gas_Account) loop for t in (select * from T_Fact_Gas_Account t3 where t3.areacode = tt.areacode and t3.totalcount = 0 order by t3.day asc) loop update T_Fact_Gas_Account r set r.totalcount = r.newcount + nvl((select totalcount from ((select r2.totalcount from T_Fact_Gas_Account r2 where r2.areacode = t.areacode and r2.day < t.day order by r2.day desc)) where rownum = 1),0) where r.day = t.day and r.areacode = t.areacode;
  end loop;
  end loop;
end;


感谢关注 V8指尖世界Oracle频道,v8en.com是专门为互联网人打造的学习交流平台,全面满足互联网人工作与学习需求,更多互联网资讯尽在 V8指尖世界!