9. Cursor merge

meteorolog.txt stores meteorological data of the year 2023 at certain observation stations. Based on this file, find the date when the corresponding total PRCP value first exceeds half of the yearly precipitation for each station while listing the observed preciptation on that date.

Expected result:

SPL code:

A1 retrieves data from the file. A2 computes yearly precipitation for each station and has the following result set:

We find that there is something wrong with some stations’ precipitation data, no precipitation data for BINHAI station, for example. This cannot change the final result, but we can filter away such records in advance.

A3 joins the observation data and the summary data table through the foreign key for the convenience of using the yearly precipitation data.

A4 groups A3’s joining result set by station. A5 gets the target record from each group, during which select() function works with @1 option to get the first record meeting the specified condition, and concatenates result records of all stations to get the final result set.

In addition, we can use A.switch() in A3 to first associate the summarized observation data with the original observation data before performing the subsequent computations. This way in the final result set returned, STATION values will be the corresponding summary records. We can switch them back to the original primary key values, as the following shows:

11. Cumulative count of continuous values
Contents and Exercise Data