10. Cumulative sum query

meteorolog.txt stores meteorological data. Based on it find the largest counts of days of continuous average temperature (TEMP) increase and decrease in the whole year for each obvservation station.

Expected result:

SPL code:

A1 retreives certain fields from the data file:

A2 adds new fields ascDays and desDays to A1’s table to record the cumulative counts of days of continuous temperature increase and decrease respectively as of each day. A3 groups A2’s result set by station.

A4 loops records of each group to assign values to ascDays field and desDays field. If temperature increases on the current day, add 1 to ascDays value and reset desDays value to 0; if temperature decreases, reset ascDays value to 0 and add 1 to desDays value; and if temperature does not change, reset both values to 0. Execute A4 and then we can view detail data in any of A3’s groups. Here are detailed records at TAISHAN station:

Then A5 generates the result table according to each group’s largest ascDay value and desDays value.

12. Intra-group operations and rankings
Contents and Exercise Data