Step 1: Add an Aggregate Window
-
Click on Aggregate in the Palette and drop onto canvas.
-
Rename the stream to AVG_TEMP
by clicking on Aggregate symbol. Then press the Enter key.
-
Select the Connector in the Palette. Drag the connector from DEVICE_EVENTS
Join to AVG_TEMP
.
-
Click Add Column Expression f(x) by clicking on the icon shown below.
-
Click the Copy Columns from Input menu item to execute it. You can also press c.
-
Click Select All or you can press Alt+s. Uncheck DEVICE_EVENTS.EVENT_NAME
, DEVICE_EVENTS.EVENT_DESCRIPTION
and DEVICE_EVENTS.MACHINETYPE
. Click OK.
Step 2: Configure the Aggregate Window
-
Now we will create a window on the input to this aggregation. Expand the Inputs tab and right click on DEVICE_EVENTS
.
-
Click the Keep Policy menu item to execute it. You can also press k.
-
Click Time and enter 30 seconds
in the entry box. Click OK.
-
To define the GROUP BY clause, expand the tab by clicking on +.
-
Double-click on GROUP BY unassigned_group_by
.
-
Select the entry DEVICE_EVENTS.MACHINEID
by clicking on it. Click Add >> and then click OK.
-
Now we need to add a GROUP filter, since we only want to aggregate temperature readings. Click Add Group Clause { } icon shown below.
Note that a GROUP FILTER filters the incoming events before aggregation. In this case, the filter will filter out “DOOR” and “POWER” events so that this element only process “TEMP” events.
-
Click the Group Filter Clause menu item to execute it. You can also press g.
-
Double-click on Group Filter 1.
-
Enter DEVICE_EVENTS.EVENT_NAME='TEMP'
as the filter expression in the text box. You can use Ctrl+Space for content assist. Confirm your entry by pressing Enter.
Step 3: Edit Column Expressions
-
Expand the Column Expressions tab to edit expressions.
-
Double-click on DEVICE_EVENTS.EVENT_TIME
.
-
Edit the expression for EVENT_TIME
. Change it to: last(DEVICE_EVENTS.EVENT_TIME)
. This will cause the aggregate values for the group to show the event time of the last event received in the group. Confirm your entry by pressing Enter.
-
Double click the name EVENT_VALUE
and rename this column to AVG_TEMP
by typing in the text field. Confirm your entry by pressing Enter.
-
Double-click on the expression for AVG_TEMP
, which is currently set to DEVICE_EVENTS.EVENT_VALUE
.
-
Edit this expression to compute an average. Also, since the value field is a string, before we can compute an average, we need to convert it to a number. Change the expression to: avg(to_decimal(DEVICE_EVENTS.EVENT_VALUE, 4, 2))
. Confirm your entry by pressing Enter.