1.17 Grouping: filter records according to the specified aggregate value
Find every product whose total sales amount is above the average sales amount in the category it belongs to based on the following table recording product sales in each category.
| CategoryName | ProductName | ProductSales |
|---|---|---|
| Beverages | C?te de Blaye | 46563.09 |
| Beverages | Chai | 4887 |
| Beverages | Chang | 7038.55 |
| Beverages | Chartreuse verte | 4475.7 |
| Beverages | Guaraná Fantástica | 1553.63 |
| … | … | … |
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from ProductSales”) |
| 3 | =A2.group(CategoryName) |
| 4 | =A3.((a=~.avg(ProductSales),~.select(ProductSales>a))).conj() |
A1 Connect to the database;
A2 Retrieve ProductSales table;
A3 Group A2’s records by product category and keep the grouped subsets at the same time;
A4 Loop to calculate the average sales amount on each grouped subset, assign the result to temporary variable a, get records from each group whose sales amounts are greater than a, and concatenate all eligible records.
Execution result:
| CategoryName | ProductName | ProductSales |
|---|---|---|
| Beverages | C?te de Blaye | 46563.09 |
| Beverages | Ipoh Coffee | 11069.9 |
| Condiments | Chef Anton’s Cajun Seasoning | 5214.88 |
| Condiments | Gula Malacca | 6543.45 |
| Condiments | Louisiana Fiery Hot Pepper Sauce | 9331.08 |
| … | … | … |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
