SQL根据平均值等比例修改原始值

最新推荐文章于 2024-01-03 14:09:08 发布

justin_lin_0

最新推荐文章于 2024-01-03 14:09:08 发布

阅读量1.2k

点赞数

分类专栏：数据库学习心得文章标签： sql

本文链接：https://blog.csdn.net/pang_da_xing/article/details/78424405

版权

数据库学习心得专栏收录该内容

6 篇文章 3 订阅

订阅专栏

由于业务需求数据显示的年度报表数值时每日采集的整点数据的平均值，然而现场人员给我们反馈回来直接就是平均值，但是数据库中原始存储的数据是每日每个时间段的整点数值。
SWS_ST_SPB_P 原始数据每天都有数个整点数据：

SWS_ST_SPB_P_copy 这是我们修改后每日的平均值每天只有一条数据：
这里写图片描述

那么我所要解决的问题是将SWS_ST_SPB_P 原始数据中需要修改的数据根据修改过后的SWS_ST_SPB_P_copy 中的数据等比例的缩放。

这里写图片描述

-- 删除所要修改的原始数据
DELETE sc FROM SWS_ST_SPB_P sc
inner join SWS_ST_SPB_P_copy s
on s.stcd = sc.stcd 
and convert(varchar(10), s.tm, 126)  = convert(varchar(10), sc.tm, 126)
and s.mpcd = sc.mpcd  
where s.tm >='2017-09-01 00:00:00' and s.tm < '2017-10-01 00:00:00'  
and s.mpcd = sc.mpcd

-- 增加的字段
 ALTER TABLE SWS_ST_SPB_P_copy add r1sum decimal,r2sum decimal,r3sum decimal,rcount decimal ;
 SELECT * INTO SWS_ST_SPB_P_copy1 FROM SWS_ST_SPB_P;

-- 创建临时表 aaa
select t.mpcd, sum(t.r1) as r1sum,sum(t.r2) as r2sum,sum(t.r3) as r3sum,count(*) rcount, convert(varchar(10), t.tm, 126) as ttime  
into aaa from (
select s.mpcd,s.r1,s.r2,s.r3,sc.r1 as c1,sc.r2 AS c2,sc.r3 as c3, s.tm AS TM from SWS_ST_SPB_P_copy1 s 
LEFT JOIN SWS_ST_SPB_P_copy sc on s.stcd = sc.stcd 
and convert(varchar(10), s.tm, 126)  = convert(varchar(10), sc.tm, 126)
and s.mpcd = sc.mpcd  
where s.tm >='2017-09-01 00:00:00' and s.tm < '2017-10-01 00:00:00'  
and s.mpcd = sc.mpcd
) t  
 GROUP BY t.mpcd ,convert(varchar(10), t.tm, 126)

select a.*,s.r1,s.r2 into result from aaa  a
LEFT JOIN SWS_ST_SPB_P_copy s
on a.mpcd = s.mpcd and a.ttime = s.tm
where s.tm >='2017-09-01 00:00:00' and s.tm < '2017-10-01 00:00:00' 
and s.mpcd = a.mpcd

select t.stcd, t.mpcd,t.r1*(t.c1*rcount)/t.r1sum as r1,t.r2*(t.c2*rcount)/t.r2sum as r2,t.tm
into resultSPB from (
select s.stcd, s.mpcd,s.r1,s.r2,s.r3, s.tm AS TM,sc.r1 as c1,sc.r2 as c2,sc.r1sum as r1sum,sc.r2sum as r2sum,sc.r3sum as r3sum,sc.rcount as rcount from SWS_ST_SPB_P_copy1 s 
LEFT JOIN result sc on  
  convert(varchar(10), s.tm, 126)  = convert(varchar(10), sc.ttime, 126)
and s.mpcd = sc.mpcd  
where s.tm >='2017-09-01 00:00:00' and s.tm <='2017-09-30 23:59:00'
and s.mpcd = sc.mpcd
)t 
;