The collector simplifies the intra-group operations of SQL-based computing

in the development of database applications, often encountered in the group after the calculation of data in groups, such as: List of nearly 3 years published each year the student list of papers, statistics all participated in the training of the staff, select each customer's golf results of the highest three days and so on. SQL Completion of this kind of operation is more complex, generally need to nest multiple layers, resulting in code difficult to understand and maintain. The collector is good at expressing such intra-group calculations and is easy to integrate with JAVA or reporting tools. Here is an example to illustrate.

according to the database table Saledata Statistics of the year, the monthly sales amount is ranked in the first name of the customer. Some of the data for SalesData are as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4C/79/wKiom1Q-ItOR7q6-AAGuEkR37qA131.jpg "style=" float: none; "title=" esproc_sql_group_compute_1.jpg "alt=" Wkiom1q-itor7q6-aaguekr37qa131.jpg "/>

to solve this problem, we need to select the sales data for year, and then the monthly group statistics, and then cycle to select the monthly sales before the customer, and finally seek the intersection of each group.

with the collector, you can split the complex problem and calculate the final result gradually. First, take the year data from the sales data and group by month:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4C/7A/wKioL1Q-IwqyLP7dAABdmp5zNrs749.jpg "style=" float: none; "title=" esproc_sql_group_compute_2.jpg "alt=" Wkiol1q-iwqylp7daabdmp5znrs749.jpg "/>

Note: filtering in the A2 can also be done with SQL .

Grouping data with a collector is a real grouping that divides the data into groups as needed. This is different from what happensin SQL, and the group by command in SQL calculates the aggregated value of the group directly, not the intermediate result of the grouping. After grouping, the data in the A3 is as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4C/79/wKiom1Q-ItSiH5IzAACK2GBG8LY499.jpg "style=" float: none; "title=" esproc_sql_group_compute_3.jpg "alt=" Wkiom1q-itsih5izaack2gbg8ly499.jpg "/>

sorting is done automatically before grouping, and each grouping is a collection of sales records, such as 3 months of data as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4C/7A/wKioL1Q-IwzjRQhiAAGnGeHoZGE501.jpg "style=" float: none; "title=" esproc_sql_group_compute_4.jpg "alt=" Wkiol1q-iwzjrqhiaagngehozge501.jpg "/>

in order to count each month, each customer's monthly sales amount needs to be grouped by customer. In the collector, you only need to cycle the data for each month, grouped by customer. You can use A when you loop A member in agroup. (x) to execute without having to write the loop code again.

A4 : =a3. (~group (Client))

after grouping again, in A4, the data for each month is grouped into groups :

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4C/79/wKiom1Q-ItbDWrL9AAFgEStBXvA237.jpg "style=" float: none; "title=" esproc_sql_group_compute_5.jpg "alt=" Wkiom1q-itbdwrl9aafgestbxva237.jpg "/>

at this point, the data for 3 months is as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4C/79/wKiom1Q-ItjBZ5ipAAHWnwhxtyc981.jpg "style=" float: none; "title=" esproc_sql_group_compute_6.jpg "alt=" Wkiom1q-itjbz5ipaahwnwhxtyc981.jpg "/>

As you can see, each grouping in the 3-month data is a customer's transaction data.

Note that the "~" in the preceding coderepresents each member in the grouping, andthe code written for "~" is the intra-group operation code, such as the ~.group (Client)above.

Next, continue to calculate the monthly top -ranked customers through the intra- group operations:

A5 : =a4. (~.top (-sum (Amount)))

A6 : =a5. (~.new (Client,sum (Amount): Monthamount))

in the in A5, each month's data is recycled, calculating the top ten customers with the largest monthly sales , and the names and monthly sales of these customers are listed in A6. The results of the calculations in A6 are as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4C/79/wKiom1Q-ItrTr8D7AAHilMVy6eA829.jpg "style=" float: none; "title=" esproc_sql_group_compute_7.jpg "alt=" Wkiom1q-itrtr8d7aahilmvy6ea829.jpg "/>

in the last list, the The Client field, and the intersection of each group:

A7 : =a6. (~. (Client))

A8 : =a7.isect ()

in the The top ten customer names for monthly sales are found in A7 . Finally, in A8, we ask for the intersection of customer names for months, and the result is as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4C/7A/wKioL1Q-IxGhkuLGAAAzk_pDiF8667.jpg "style=" float: none; "title=" esproc_sql_group_compute_8.jpg "alt=" Wkiol1q-ixghkulgaaazk_pdif8667.jpg "/>

From this problem can be seen, the collector can easily achieve the structure of data in-group computing, can make the problem-solving ideas more intuitive, in the group can easily complete the sub-grouping, sorting and other calculations, so that each step of the data processing more clear and natural. In addition, the collector can make the group members of the loop or intersection and other operations become more simple, greatly reducing the amount of code.

The method that the collector is called by the Java program is similar to the normal database, using the JDBC interface it provides to return the ResultSet form of the calculation to the Java Main program . Refer to the relevant documentation for specific methods.

The collector simplifies the intra-group operations of SQL-based computing