For example, the table below contains some sales data:
Now we need to select customers who entered Top 20 revenue contributors (Top 20 customers) in every month of 2013. To solve this problem we can first select all sales data for 2013, group them and to get the statistics for each month. Then we can do a loop to select the Top 20 customers for each month. The intersection of the Top 20 lists for all 12 months will contain the name of customers we wanted. Such complex problems are too difficult to be handled by SQL or stored procedures.
esProc's grouping of data is real grouping, which actually separates data into different groups according to the criterion. This is different from SQL, in which the "group by" command can only return the aggregated result of a grouping. After grouping, the data in A3 is as following:
Before grouping, all data will be sorted automatically. Each group is a set of sales records. For example, the data for March is as following:
To know the total sales revenue for each customer in every month, we need to further split the data by customers. In esProc, we only need to do loop on data for each month, and group them by customers respectively. We can use A.(x) to to do loop on set members, without the need to code for loop.
After further grouping, the monthly data in A4 is a set of sets：
Now, the data for March is as following:
We can see that each group in data for March is the transaction data for certain customer.
The set used in esProc is different from that in mathematical concepts. They are ordered setsand therefore can meet the statistical needs of sorting and selection by position, etc. Then we can find the Top 20 customers for each month:
In A5, do loop on the data for each month to get the Top 20 customers for each month. And in A6, list the names and monthly revenues of these customers. The computation result in A6 is as following:
Finally, we can further solve the problem:
Generate the name lists of Top 20 customers in A7 for each month. And finally in A8 we can find the intersection of the Top 20 lists for each month as following: