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

When developing database applications, it is often necessary to calculate the data in the group after grouping, for example, to list the students who have published papers in each of the past 3 years (the list of students who appear each year after the publication of the papers). Statistics of all employees who have participated in all previous training (the number of employees each time after training groups) Select the three days with the highest golf scores for each customer (Top3 calculated by day after grouping by customers), and so on.

For SQL, completing this kind of operation is generally more complex and requires nesting multiple layers, which often makes the code difficult to understand and difficult to maintain. SPL is very good at this kind of intra-group computing, and it is easy to integrate with JAVA and reporting tools. Let's use an example to illustrate.

Table sales stores order data for many years, some of which are as follows:

OrderIDClientSellerIdOrderDateAmount10808OLDWO22015/1/1166010809WELLI72015/1/114010810LAUGB22015/1/118710811LINOD82015/1/285210812REGGC52015/1/2185210813RICAR12015/1/564810814VICTE32015/1/5207010815SAVEA22015/1/54010816GREAL42015/1/68891

The requirement is to count the names of the top 20 customers with monthly sales in the specified year (for example, 2015) according to this table.

The idea to solve this problem is to first select the 2015 sales data, statistics by monthly groups, and then cycle to select the top 20 customers per month, and finally seek the intersection of each group.

This kind of calculation is difficult to express directly in SQL, while SPL can naturally calculate step by step to get the final result after splitting the complex problem.

SPL Code:

A1=db.query ("select * from sales") 2=A1.select (year (ORDERDATE) = = YEAR) 3=A2.group (month (ORDERDATE)) 4=A3. (~ .group (CLIENT)) 5=A4. (~ .top (- 20 (AMOUNT) 6=A5. (~ .new (CLIENT,sum (AMOUNT): MONTH_AMOUNT)) 7=A6. (~. (CLIENT)) 8=A7.isect ()

A1: read the sales table from the database

A2:=A1.select (year (ORDERDATE) = = YEAR) to extract the data for the specified year from the sales data. Adapt to different query requirements flexibly through the grid parameter YEAR, in this case YEAR=2015. Of course, the filtering here can also be done directly through SQL in A1.

A3:=A2.group (month (ORDERDATE)), which uses the group function to group 2015 data by month. What needs to be noted here is that the data grouping of SPL is a real grouping, and the data will actually be divided into multiple groups as needed. This is different from the situation in SQL, where the group by command in SQL calculates the summary value of the packet directly and does not retain the intermediate grouping result. Therefore, it is not possible to further calculate the grouped data. After grouping, the data in A3 is as follows:

We can also continue to double-click to view detailed data, such as the following data for March:

In order to count the total monthly sales of each customer in each month, it is necessary to subgroup by customer. In SPL, you only need to cycle through the monthly data and group it by customer. When you loop through the members of a group, you can also use a concise A. (X) to execute without having to write loop code.

A4:=A3. (~ .group (CLIENT))

After grouping again, the data of each month in A4 will be grouped:

The data grouped by customer in March are as follows:

As you can see, each group in the March data is a customer's transaction data.

Note that the "~" in the above code represents each member of the group, and the code written for "~" is the intra-group operation code, such as ~ .group (CLIENT) above.

Next, we will continue to find out the top 20 customers each month through intra-group calculations:

A5:=A4. (~ .top (- 20 AMOUNT sum (top))

A6:=A5. (.new (CLIENT,sum (AMOUNT): MONTH_AMOUNT))

In A5, the top 20 customers with the largest monthly sales are calculated using the top function by looping through the monthly data. The names and monthly sales of these customers are listed in A6. The results calculated in A6 and the statistics for March are as follows:

Finally, the Client fields in the group are listed, and the intersection of each group is calculated:

A7:==A6. ((CLIENT))

A8:=A7.isect ()

List the 20 customer names with the largest monthly sales in A7. Finally, we seek the intersection of customer names for 12 months in A8, and the results we need are as follows:

From this problem, we can see that SPL can easily realize the intra-group calculation of structured data, and the idea of solving the problem is very intuitive. Intra-group calculation can also easily complete re-grouping, sorting and other calculations, each step of the data processing is more clear and natural. In addition, the operations such as group member loop and intersection provided by SPL also make the calculation easier and greatly reduce the amount of code.

For the calculation results, in addition to exporting data, SPL can also directly provide data to the report tool or java program in the called way, 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 documentation. [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.