现场DCS仪表清零后报表每小时用量的SQL处理方法（列转行）

    最近我在做项目的时候，在现场碰到客户煤气仪表每天累积量达到几百万，一个月累计下来表的度数就要超出仪表的上限，所以客户每个月25号必须进行一次清零。这样仪表的度数就重新从零开始计算。在统计每小时煤气产量的时候，现场如果是手工抄数就不要紧，只需要做一下简单的公式计算即可 【产量】=【清零时表的读数】-【上个整点读数】+【当前点清零后的读数】即可。但是对于一个软件系统来说，没有人的大脑进行干预可就没有那么简单，他需要知道仪表是否清零，得知道什么时候清的零，还需要知道清零时候的读数。知道这三点才能提供软件的方法进行自动处理清零情况。

    我在现场给客户生产部门开发了一个24小时，每小时整点的煤气产量报表，每天打开，自动计算出整点的煤气使用量，如下图

当时通过实时库软件按照整点请采集到全部是整点数据，这个在仪表不清零持续累积是没有问题的，但是一旦清零，就会出现下面情况，见红框框

因为这数据是每个小时的整点采集，不管是否在两个整点之间清零，系统根据读数向减都会算错一个整点的数据，当前这种情况的数据采集表是每小时一次，结构如下：

按照上面提到的算法公式：【产量】=【清零时表的读数】-【上个整点读数】+【当前点清零后的读数】，需要知道仪表是否清零，还得知道什么时候清的零，还需要知道清零时候的读数。那么系统要及时反应出清零时刻，如果不及时的话会造成比较大的误差。我用了一个比较简单的方法，重新去实时库系统采集每分钟的煤气读数，一旦清零，1分钟就可以反映过了，不会造成很大误差，用户可以接受，同时比按照秒来采集，会大大减少表的数据，对报表性能影响不大。

    提供这个表，提过后前相邻的分钟数据相减，一旦出现负值，我就可以知道这个仪表在这个时间（小时+分钟）出现清零，同时找到上一分钟的读数，和第一分钟的度数M0就可以算出清零之前的使用量，同时再加上M59读数，就得到了清零的这个时间段整点的使用量。提过上图 我们就可以知道在10点8分出现过清零，那么10点的使用量就是M8-M0+M59.

    通过上面的分析，我们的思路就出来了，

1、找出 清零的数据时间点 ,后前分钟数相减

2、为了便于计算，通过简单的sql 运算就可以定位到在什么时间出现清零，我们需要将列进行反转变成行，同时得到分指数。通过 unpivot  方法来进行

3、通过 后减前为赋值定位到清零的时间点和上一个时间点，发生在哪个的小时

SELECT top 1  SUBSTRING(MNAME,2,LEN(mname)-2) mins1 ,datepart(hh,[DATE]),
SUBSTRING(MNAME,2,LEN(mname)-2)-1 mins0 ，datepart(hh,[DATE]) OCCH
from  unpvt WHERE  mvalues<0;

4、定位到时间点以后，通过时间点请找到分钟表里面的读数，进行计算即可，因为字段是计算得到，这里用动态sql

declare @m1value decimal(22,6) ,@m0value decimal(22,6) ,@m59value decimal(22,6) ;
 set @qzerosql=
 ‘  select top 1 @m1value=M‘+@mins1 +‘ , @m0value=M0, @m59value=m59 ‘
 + ‘   from tblxxxx  ‘
 + ‘   where CONVERT(varchar(10),[date],120)=@occurdate and DATEPART(hh,[date])=@OCCH ‘;

 exec sp_executesql @qzerosql,N‘@m1value decimal(22,6) output,@m0value decimal(22,6) output,@m59value decimal(22,6) output,@occurdate date,@OCCH int,@pointname varchar(100)‘,
 @m1value output, @m0value output,@m59value output,@occurdate,@OCCH;

5、最后，返回计算好的数据和该数据所在的时间点，在报表上通过时间点在报表单元格进行判断处理即可。

 SET @ResetHourValue= (select @m1value -@m0value +@m59value );
 
 set @OccurHour=@OCCH;

这个问题的处理关键是定位清零时间点，方法是通过sql sever 列转行的方法。如果大家有其他简便可行的方法，也请一并赐教，O(∩_∩)O谢谢

本文出自 “7286556” 博客，请务必保留此出处http://7296556.blog.51cto.com/7286556/1576270