Live Data Computation Agent – Example 1

<< Click to Display Table of Contents >>

Navigation:  Live > Agents > Transformation Agents > Data Computation > Examples >

Live Data Computation Agent – Example 1

The following SERIES_FUNCTION_INFO computes a datum value for each source series (with LOGGER_SERIES_IDs 130041, 130032, and 130033) datum (distinct) date-time:

 

Computed DT_LOGGER_DATUM.DATUM_VALUE = source DT_LOGGER_DATUM.DATUM_VALUE + equis.fn_logger_measure_datum(...).

 

If there is more than 1 source datum with the same DATUM_UTC_DT, it uses the MAX value.

 

The equis.sp_insert_dt_water_level_daily_max() stored procedure calculates the computed data's daily max value and adds it to DT_WATER_LEVEL.

 

 

{
"sourceSeriesId": [
  130041, 130032, 130033
],
"value": "MAX(d.datum_value + equis.fn_logger_measure_datum(@facility_id, @sys_loc_code, d.datum_utc_dt))"
"procedure": "EXEC [equis].[sp_insert_dt_water_level_daily_max] @target_series_id, @start_date, @end_date, @facility_id, @sys_loc_code;"
}

 

 

This example assumes that the DT_LOGGER_DATUM rows for the source datum are:

 

LOGGER_SERIES_ID

DATUM_UTC_DT

DATUM_VALUE

130041

2018-06-27 01:23:45

123.4

130041

2018-06-28 09:45:00

197.64

130032

2018-06-28 09:45:00

207.3


...

...

130041

2018-07-02 13:34:32

170.97

130033

2018-07-02 13:34:33

163

130041

2018-07-03 12:00:00

182.7

130041

2018-07-05 23:13:32

190.313

 

And the DT_LOGGER_MEASURE rows (where FACILITY_ID and SYS_LOC_CODE are the same as the computed series') are:

 

DATUM_UTC_DT

DATUM_VALUE

2018-06-23 07:13:00

70.2

2018-06-30 03:23:00

77.9

...

...

2018-07-03 12:00:00

75.3

 

And that there is no existing computed datum (nothing to delete, or update; only insert).

 

1.Create a table of date-times for the computed datum; add each distinct source datum date-time:

 

DATUM_UTC_DT

2018-06-27 01:23:45

2018-06-28 09:45:00

...

2018-07-02 13:34:32

2018-07-02 13:34:33

2018-07-03 12:00:00

2018-07-05 23:13:32

 

Note: There is only ONE 2018-06-28 09:45:00; duplicate date-times in the source datum are not added.

 

2.Calculate the datum values (computed datum value = MAX(source datum value + equis.fn_logger_measure_datum(...))):

 

DATUM_UTC_DT

DATUM_VALUE

2018-06-27 01:23:45

123.4 + 70.2 = 193.6

2018-06-28 09:45:00

MAX(197.64 + 70.2, 207.3 + 70.2) = 277.5

...

...

2018-07-02 13:34:32

170.97 + 77.9 = 248.87

2018-07-02 13:34:33

163 + 77.9 = 240.9

2018-07-03 12:00:00

182.7 + 75.3 = 258

2018-07-05 23:13:32

190.313 + 75.3 = 265.613

 

3.Insert computed datum into DT_LOGGER_DATUM (set LOGGER_SERIES_ID = @target_series_id).

 

4.Execute equis.sp_insert_dt_water_level_daily_max() stored procedure.

 

Note: The equis.fn_logger_measure_datum function is a simplified version of the equis.measure_datum function (already available in EQuIS); with only the 'step', no 'linear'.

 

 

-- Looks up the start datum date (the most recent datum prior to (or on) the measurement_date),

-- and returns the start datum value (the most recent datum prior to the measurement_date).

 

IF OBJECT_ID(N'[equis].[fn_logger_measure_datum]') IS NOT NULL

DROP FUNCTION [equis].[fn_logger_measure_datum];

GO

 

CREATE FUNCTION [equis].[fn_logger_measure_datum]

(

 @facility_id INT

,@sys_loc_code VARCHAR(20)

,@measurement_date SMALLDATETIME

)

RETURNS REAL

AS

BEGIN

DECLARE @start_datum_date SMALLDATETIME;

DECLARE @datum_value REAL;

 

-- Lookup the start datum date (the most recent datum prior to (or on) the measurement_date).

SELECT

   @start_datum_date = MAX(start_date)

FROM

   dt_measure_datum

WHERE

       facility_id = @facility_id

  AND sys_loc_code = @sys_loc_code

  AND start_date <= @measurement_date;

 

-- Return the start datum value (the most recent datum prior to the measurement_date).

SELECT

   @datum_value = datum_value

FROM

   dt_measure_datum

WHERE

       facility_id = @facility_id

  AND sys_loc_code = @sys_loc_code

  AND start_date = @start_datum_date;

 

RETURN @datum_value;

END

 

 

 

-- Inserts/updates daily max dt_logger_datum (datum_utc_dt, datum_value)

-- into dt_water_level.

 

IF OBJECT_ID(N'[equis].[sp_insert_dt_water_level_daily_max]') IS NOT NULL

DROP PROCEDURE [equis].[sp_insert_dt_water_level_daily_max];

GO

 

CREATE PROCEDURE [equis].[sp_insert_dt_water_level_daily_max]

(

  @target_series_id BIGINT

,@start_date       DATETIME2(0)

,@end_date         DATETIME2(0)

,@facility_id     BIGINT

,@sys_loc_code     VARCHAR(20)

)

AS

BEGIN

DECLARE @temp_datum TABLE

(

    datum_utc_dt DATE

  ,datum_value REAL

);

 

-- Get daily max.

INSERT INTO

   @temp_datum (datum_utc_dt, datum_value)

SELECT

    CAST(datum_utc_dt AS DATE)

  ,MAX(datum_value)

FROM

   dt_logger_datum

WHERE

       logger_series_id = @target_series_id

  AND datum_utc_dt BETWEEN @start_date AND @end_date

GROUP BY

  CAST(datum_utc_dt AS DATE);

 

-- dt_water_level.measurement_date, water_level_depth = @datum.datum_utc_dt, datum_value

MERGE

   dt_water_level w

USING

   @temp_datum t

ON

       w.facility_id = @facility_id

  AND w.sys_loc_code = @sys_loc_code

  AND w.measurement_date = t.datum_utc_dt

WHEN MATCHED THEN

  UPDATE SET w.water_level_depth = t.datum_value

WHEN NOT MATCHED THEN

  INSERT (facility_id, sys_loc_code, measurement_date, water_level_depth)

  VALUES (@facility_id, @sys_loc_code, t.datum_utc_dt, t.datum_value);

END