Shulou(Shulou.com)06/03 Report--

It is a common operation to take out the first N rows of data from each group, such as which is the five days with the highest sales of each product each month, which is the highest salary increase for each employee, which is the worst three times for golf members, and so on. In SQL, this kind of operation needs to be handled indirectly by advanced techniques such as window function and keep/top/rownumber, so it is difficult to code. And many databases (such as MySQL) do not have these advanced features, so they can only be implemented with more complex JOIN statements and nested subqueries. If multi-layer grouping and multi-level association are also involved, the calculation process will be more complex.

In SPL, because the top function can take the first N lines in the grouping by line number, maximum value, minimum value, etc., it is easier and clearer to solve this kind of problem. Here is an example to illustrate.

The database table golf stores the golf scores of several members, some of which are as follows:

IDUSER_IDSCOREDATETIME11352014-07-01 111472014-07-011214031482014-07-03181482014-03181403181482014-07-03181482014-07-03181482014-07-03181482014-07-03181482014-03181482014-03181482014-03181482014-03181482014-07-0181482014-03181482014-03181482014-07-01 12141482014-03181482014-03181482014-0313772014-07-01 12141482014-0313682014-07-01214042014-07-0121482014-03121462014-07-01 12141482014-03181482014-07-01 121482014-03 181482014-07-03 181482014-07-03 181482014-03 181482014-07-03 181482014-03 181482014-07-03 181482014-03 181482014-03 181482014-07-03 181482014-03 181482014-03 181482014-07-03 181482014-03 181482014-03 181482014-03 181482014-07-03 18

Please take out the three best scores of each member.

SPL Code:

A1=db.query ("select * from golf") 2=A1.group (USER_ID) 3=A2. (~ .top (- 3tschore)) 4=A3.union () 5 > file ("golf.csv") .export @ ct (A4)

A1: fetch the number from the database. Click on the cell to see the fetch result:

A2:=A1.group (USER_ID). Group the results of A1 according to USER_ID, that is, members, and the results are as follows:

Each line represents a group, where all the scores of a member are recorded. Double-click the light blue box to see the members of the group, as follows:

A3VOR = A2. (~ .top (- 3persSCORE). Calculate the top three records in the SCORE field of each set of data. The "~" here represents each set of data, and ~ .top () indicates that the function top is applied to each set of data in turn. The function top can get the first N records of the data set. For example, top (3 position scale) indicates that the first 3 records are arranged in ascending order of SCORE, taking the first 3 items (that is, the minimum value); top (- 3 position scale) indicates that they are arranged in descending order, taking the first 3 items (that is, the maximum value, that is, the best result here). The calculation results of this step are as follows:

A4:=A3.union (). The data of each group are merged, and the results are as follows:

A5: > file ("golf.csv") .export @ ct (A4)

Export the calculation results to a "golf.csv" file for viewing through tools such as excel:

In addition to exporting data, SPL can also be directly called by report tools or java programs, the calling method is similar to the ordinary database, using the JDBC interface it provides can return the calculation results in the form of ResultSet to the java main program, the specific method can refer to the relevant documents. [how does Java invoke the SPL script]

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.