# 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 ,
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 )
FROM
(
SELECT *
FROM (
SELECT
MR.SK_MONTH ,
MR.SK_DATE  ,
MR.SK_DATE_HOUR,
MR.STATION_ID  ,
OVER    (   PARTITION BY S.CLASS_ID  ,SK_DATE_HOUR
-------------------------------------------------------------------------------------------------------------------------------------------------
OVER    (   PARTITION BY S.CLASS_ID  ,SK_DATE_HOUR
-------------------------------------------------------------------------------------------------------------------------------------------------
WHERE 1=1
--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.

Bests ,

Gunes