[Original] streaminsight query series (8)-sorting by groups for basic query operations

In the previous blog, we introduced the basic sorting section in the basic query operations of streaminsight. This articleArticleThis section describes how to sort groups (topk) in a streaminsight query ).

Test data preparation

To facilitate test query, we first prepare a static test data source:

 VaR Weatherdata = New [] { New {Timestamp = New  Datetime (2010, 1, 1, 0, 00, 00, Datetimekind . UTC), temperature =-9.0, stationcode = 71395, windspeed = 4 }, New {Timestamp = New  Datetime (2010, 1, 1, 0, 30, 00,Datetimekind . UTC), temperature =-4.5, stationcode = 71801, windspeed = 41 }, New {Timestamp = New  Datetime (2010, 1, 1, 1, 00, 00, Datetimekind . UTC), temperature =-8.8, stationcode = 71395, windspeed = 6 }, New {Timestamp = New  Datetime (2010, 1, 1, 1, 30, 00, Datetimekind . UTC), temperature =-4.4, stationcode = 71801, windspeed = 39 }, New {Timestamp =New  Datetime (2010, 1, 1, 2, 00, 00, Datetimekind . UTC), temperature =-9.7, stationcode = 71395, windspeed = 9 }, New {Timestamp = New  Datetime (2010, 1, 1, 2, 30, 00, Datetimekind . UTC), temperature =-4.6, stationcode = 71801, windspeed = 59 }, New {Timestamp = New  Datetime (2010, 1, 1, 3, 00, 00, Datetimekind . UTC), temperature =-9.6, stationcode = 71395, windspeed = 9 },};

Weatherdata represents a series of weather information (timestamp, temperature, weather station code, and wind speed ).

Next we will transform weatherdata into a complex event stream of point type:

 
VaRWeatherstream = weatherdata. topointstream (application, t =>Pointevent. Createinsert (T. timestamp, T ),Advancetimesettings. Increasingstarttime );
 

Group sorting

Question 1: how to find the event group with the maximum average value in the past two hours?

To complete this query, take two steps: first, group the events and obtain the average value. Second, calculate the event group with the largest average value in the same time period. The specific query is as follows:

Step 1: group events and obtain the average value:

VaRAveragequery =FromEInWeatherstreamGroupEByE. stationcodeIntoStationgroupsFromWinInStationgroups. tumblingwindow (Timespan. Fromhours (2 ),Hoppingwindowoutputpolicy. Cliptowindowend)Select New{Stationcode = stationgroups. Key, averagetemperature = win. AVG (E => E. Temperature )};
 

Export the averagequery content as follows:

Step 2: Calculate the event group with the largest average value in the same time period:

VaRTopkgroupquery1 = (FromWinInAveragequery. snapshotwindow (Snapshotwindowoutputpolicy. Clip)FromEInWinOrderbyE. averagetemperatureDescending selectE). Take (1 );
 

The result of step 2 is as follows:

Question 2: how to find the event group with the maximum value in the past 8 hours every four hours?

Similar to problem 1, two steps are required to solve the problem:

Step 1: group events and obtain the maximum value,CodeAs follows:

  var  maxvaluequery =  from  E  in  weatherstream  group  E  by  E. stationcode  into  stationgroups  from  win  in  stationgroups. hoppingwindow ( timespan . fromhours (8),  timespan . fromhours (4),  hoppingwindowoutputpolicy . cliptowindowend)  select new  {stationcode = stationgroups. key, maxtemperature = win. max (E => E. temperature), maxwindspeed = win. max (E => E. windspeed) }; 

The result in linqpad is as follows:

The second step calculates the Maximum Event Group for the same time period. The query is as follows:

 
VaRTopkgroupquery2 = (FromWinInMaxvaluequery. snapshotwindow (Snapshotwindowoutputpolicy. Clip)FromEInWinOrderbyE. maxtemperatureDescending selectE). Take (1 );
 

The result of step 2 is:

The next article will introduce the ranking and tiebreaking sections in the basic query operations of streaminsight.