Time Weighted Average for Sensor Data

Hi All !

One of out projects we need to do apply time weighted average to aggregate sensor data.

Simple explain of TWA ;

Input data(quality=good)

12:00:00   – 10

12:00:02   – 12

12:00:05   – 15

  “TimeAverage is a slopped line between all of the points in the interval – in this case the line has the same slope – which makes it easy to calculate.  (10 + (15-10)/2).  Which would yield a result of 12.5.  technically it should be calculated by {([10 + (12-10)/2] * 2 ) + ([12 + (15-12)/2] * 3)} / 5  -> 12.5

In English, find the average value for the first sub interval – multiply it by the number of seconds (well actually milli-seconds – but since the time in the example is even second boundary – the calculation is identical) + find the average value for the second interval multiply by the number of seconds.  Add the two sub intervals together and then divide by the total time.  Note technically the number might be slightly different since the time stamps, might have more accurrancy in the clocks (milli-second time stamps) “

I get this from ; https://opcfoundation.org/forum/opc-ua-standard/time-average-calculation/

here is the sample code for that ;

SELECT 
 SK_DATE_HOUR  ,
 STATION_ID ,
 TRUNC( METER_READING_TIME , 'HH24' )   AS READING_TIME ,
 CASE  WHEN SUM (TMP_WATER_RATE) <> 0             
         THEN SUM(  TMP_WATER_RATE ) / ( CASE  WHEN  MAX(TIME_RANGE ) = 0 THEN 1 
         ELSE MAX(TIME_RANGE )   END )  
         END  METER_READING_LIQUID_RATE   --TWA_LIQUID,
 FROM
 (
 SELECT *
 ,TIMESTAMPDIFF( 4,  CHAR (  MAX_READING_TIME - MIN_READING_TIME  ))  TIME_RANGE
 ,TIMESTAMPDIFF( 4,  CHAR (  LEAD_METER_READING_TIME - METER_READING_TIME  ) ) AS DELTA_TIME
 ,( METER_READING_WATER_RATE +  ( ( LEAD_METER_READING_WATER_RATE - METER_READING_WATER_RATE )  / 2 ) ) 
         * ( CASE WHEN LEAD_METER_READING_TIME  IS NULL THEN  1 
                  ELSE TIMESTAMPDIFF( 4,  CHAR (  LEAD_METER_READING_TIME - METER_READING_TIME  ) ) END )  TMP_WATER_RATE
 FROM (
         SELECT
         MR.SK_MONTH ,
         MR.SK_DATE  ,
         MR.SK_DATE_HOUR,
         MR.STATION_ID  , 
         METER_READING_TIME ,
         LEAD( METER_READING_TIME ,1, '' ) 
                     OVER    (   PARTITION BY S.CLASS_ID  ,SK_DATE_HOUR    
                                 ORDER BY METER_READING_TIME ASC 
                             )   LEAD_METER_READING_TIME  ,
         -------------------------------------------------------------------------------------------------------------------------------------------------
         METER_READING_WATER_RATE  ,
         LEAD(METER_READING_WATER_RATE ,1,METER_READING_WATER_RATE ) 
                     OVER    (   PARTITION BY S.CLASS_ID  ,SK_DATE_HOUR  
                                 ORDER BY METER_READING_TIME ASC 
                             )   LEAD_METER_READING_WATER_RATE  ,
         -------------------------------------------------------------------------------------------------------------------------------------------------                  
         MAX(METER_READING_TIME) OVER (PARTITION BY SK_DATE_HOUR , S.CLASS_ID  ) MAX_READING_TIME ,
         MIN(METER_READING_TIME) OVER (PARTITION BY SK_DATE_HOUR , S.CLASS_ID  ) MIN_READING_TIME
         FROM  DWH.SENSOR_READING  MR 
         WHERE 1=1
         AND METER_READING_WATER_RATE IS NOT NULL  
         --AND MR.SK_DATE_HOUR >= #DWH_DATE_PARAMETERS.SK_START_DATE_HOUR#       --PARAMETER FOR DATASTAGE
         --AND MR.SK_DATE_HOUR <= #DWH_DATE_PARAMETERS.SK_END_DATE_HOUR#         --PARAMETER FOR DATASTAGE
 )
 )
 GROUP BY
 SK_DATE_HOUR  ,
 STATION_ID ,
 TRUNC( METER_READING_TIME , 'HH24' ) 

I hope it can give you idea ,

For writing this query you need to know LEAD , LAG , TIMESTAMPDIFF functions.

TIMESTAMPDIFF : https://www.ibm.com/support/producthub/db2/docs/content/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000861.html

LEAD , LAG : https://www.ibm.com/docs/en/informix-servers/12.10?topic=expressions-lag-lead-functions

Bests ,

Gunes