The top N customers who accounted for half of the sales that year
The following is the historical sales contract record table of a certain enterprise:
Below is their client information table:
Customers ranked by sales revenue within a certain year, and the top n customers who reach half of the sales revenue are called the "major customers" of that year. Please list the major customers of the company in 1998.
Group the contract table by customer, calculate the total amount for each customer and sort them in descending order, then calculate half of the total sales value. Finally, scan the table and continuously accumulate sales until reaching half of the sales value. The previous customers are considered "Major customers"./span>
| A | |
|---|---|
| 1 | =T("Contract.txt").select(year(SellDate)==1998) |
| 2 | =T("Client.txt").keys(ID) |
| 3 | >A1.switch(Client,A2) |
| 4 | =A1.groups(Client;round(sum(Amount),3):Amount).sort(-Amount) |
| 5 | =a=0,half=A4.sum(Amount)/2,A4.pselect((a+=Amount,a>=half)) |
| 6 | =A4.to(A5).(Client.Name) |
https://try.esproc.com/splx?4ZA
A1 and A2 respectively read the sales records and customer information for 1998.
A3 performs foreign key association by replacing the customer field in the contract table with a customer record to facilitate searching for the customer's name.
A4 Group and aggregate by customer, calculate the total sales of each customer, and sort them in descending order of total sales:
A5 first defines the initial value of cumulative sales a as 0, and half as half of the total sales of all customers. Then, search for the results of A4 and accumulate the total sales until the cumulative total just exceeds half of the total sum.
A6 retrieves the list of major clients:
