Non-basic Aggregation Examples
An aggregate operation summarizes data and returns the result. An aggregation is always preceded by a grouping operation. Common aggregate calculations include sum, max, min, count and logic operations. In this article, you’ll find illustrations of aggregate problems and their simple and efficient solutions with esProc scripts. Looking Non-basic Aggregation Examples for details.
1. SUM aggregate after enumeration grouping
【Example 1】 Based on the GDP table, calculate the GDP per capita for direct-controlled municipalities, first-tier cities and second-tier cities respectively. Below is part of the source table:
ID |
City |
GDP |
Population |
1 |
Shanghai |
32679 |
2418 |
2 |
Beijing |
30320 |
2171 |
3 |
Shenzhen |
24691 |
1253 |
4 |
Guangzhou |
23000 |
1450 |
5 |
Chongqing |
20363 |
3372 |
… |
… |
… |
… |
【SPL script】
A |
B |
|
1 |
=connect("db") |
/ Connect to database |
2 |
=A1.query("select * from GDP") |
/ Query GDP table |
3 |
[["Beijing","Shanghai","Tianjin","Chongqing"].pos(?)>0, |
/ Enumerate direct-controlled cities, first-tier cities and second-tier cities respectively |
4 |
=A2.enum@r(A3,City) |
/ Group records in GDP table according to the enumerated sequences of cities |
5 |
=A4.new(A3(#):Area,~.sum(GDP)/~.sum(Population)*10000:CapitaGDP) |
/ Calculate GDP per capita in each group, during which sum() function is used to calculate sum |
A5’s result:
Area |
CapitaGDP |
["Beijing","Shanghai","Tianjin","Chongqing"].pos(?)>0 |
107345.03 |
["Beijing","Shanghai","Guangzhou","Shenzhen"].pos(?)>0 |
151796.49 |
["Chengdu","Hangzhou","Chongqing","Wuhan","Xi’an","Suzhou","Tianjin","Nanjing", |
106040.57 |
2. Merge overlapping time intervals
【Example 2】 The following is part of the orders table. We want to merge records of customer ANATR that have overlapping time periods (between order date and finish date).
OrderID |
Customer |
SellerId |
OrderDate |
FinishDate |
10308 |
ANATR |
7 |
2012/09/18 |
2012/10/16 |
10309 |
ANATR |
3 |
2012/09/19 |
2012/10/17 |
10625 |
ANATR |
3 |
2013/08/08 |
2013/09/05 |
10702 |
ANATR |
1 |
2013/10/13 |
2013/11/24 |
10759 |
ANATR |
3 |
2013/11/28 |
2013/12/26 |
… |
… |
… |
… |
… |
【SPL script】
A |
B |
|
1 |
=connect("db") |
/ Connect to data source |
2 |
=A1.query("select * from Orders where Customer='ANATR'order by OrderDate") |
/ Get orders records of customer ANATR and sort them by order date |
3 |
=A2.group@i(OrderDate>max(FinishDate[,-1])) |
/ Group the selected orders records, during which a new group is created when the order date of the current record is later than all previous finish dates |
4 |
=A3.new(Customer,~.min(OrderDate):OrderDate,~.max(FinishDate):FinishDate) |
/ For each group, min()function gets the earliest order date and max() function gets the latest finish date, and use them respectively as the new order date and the new finish date |
A4’s result:
Customer |
OrderDate |
FinishDate |
ANATR |
2012/09/18 |
2012/10/17 |
ANATR |
2013/08/08 |
2013/09/05 |
ANATR |
2013/10/13 |
2013/11/24 |
ANATR |
2013/11/28 |
2013/12/29 |
… |
… |
… |
3. Grouping & conditional COUNT aggregate
【Example 4】 The following is part of the scores table. We want to, for each subject, find the number of students in class one who fail in this subject.
CLASS |
STUDENTID |
SUBJECT |
SCORE |
Class one |
1 |
English |
84 |
Class one |
1 |
Math |
77 |
Class one |
1 |
PE |
69 |
Class one |
2 |
English |
81 |
Class one |
2 |
Math |
80 |
… |
… |
… |
… |
【SPL script】
A |
B |
|
1 |
=connect("db") |
/ Connect to database |
2 |
=A1.query("select * from Scores where CLASS='Class one'") |
/ Get scores records of students in class one |
3 |
=A2.groups(SUBJECT; count(SCORE<60):FailCount) |
/ Perform grouping & aggregation, during which count() function calculates the number of students who fail in the subject |
A3’s result:
SUBJECT |
FailCount |
English |
2 |
Math |
0 |
PE |
2 |
4. Perform logical AND on a set of Boolean values
【Example 5】 Based on the following primary school online-learning terminal tables (as shown by pic 2), we want to find if all students use mobile phones to learn. Pic 1 is the directory where the tables for classes of all grades are stored.
ID |
STUDENT_NAME |
TERMINAL |
1 |
Rebecca Moore |
Phone |
2 |
Ashley Wilson |
Phone,PC,Pad |
3 |
Rachel Johnson |
Phone,PC,Pad |
4 |
Emily Smith |
Phone,Pad |
5 |
Ashley Smith |
Phone,PC |
6 |
Matthew Johnson |
Phone |
7 |
Alexis Smith |
Phone,PC |
8 |
Megan Wilson |
Phone,PC,Pad |
… |
… |
… |
【SPL script】
A |
B |
C |
|
1 |
=directory@ps("D:/Primary School") |
/ Traverse the target directory recursively to list all files |
|
2 |
for A1 |
=file(A2).xlsimport@t() |
/ Import the Excel files of all classes circularly |
3 |
=B2.([TERMINAL,"Phone"].ifn().split@c().pos("Phone") > 0)|@ |
/ ifn() function makes sure that a null terminal value is treated as being able to use phone and returns True |
|
4 |
=B3.cand() |
/ A.cand() function checks whether B3’s members are all True |
A4’s result:
Value |
false |
5. Perform logical OR on a set of Boolean values
【Example 6】 The following is part of the sales data. We want to find if there is at least one month in 2014 when customer RATTC’s amount ranks in top 3.
OrderID |
Customer |
SellerId |
OrderDate |
Amount |
10400 |
EASTC |
1 |
2014/01/01 |
3063.0 |
10401 |
HANAR |
1 |
2014/01/01 |
3868.6 |
10402 |
ERNSH |
8 |
2014/01/02 |
2713.5 |
10403 |
ERNSH |
4 |
2014/01/03 |
1005.9 |
10404 |
MAGAA |
2 |
2014/01/03 |
1675.0 |
… |
… |
… |
… |
… |
【SPL script】
A |
B |
|
1 |
=connect("db").query("select * from sales") |
/ Connect to the data source to query sales table |
2 |
=A1.select(year(OrderDate)==2014) |
/ Get records of 2014 |
3 |
=A2.group(month(OrderDate)) |
/ Group records of 2014 by months |
4 |
=A3.(~.groups(Customer; sum(Amount):Amount)) |
/ Group records in each group by customers and calculate each customer’s total amount |
5 |
=A4.new(~.top(-3; Amount):Top3) |
/ Loop through records of each month to get customers whose total amounts rank in top 3 |
6 |
=A5.(Top3.(Customer).pos("RATTC")>0) |
/ Among each month’s top 3, check whether customer RATTC is included |
7 |
=A6.cor() |
/ A.cor() function checks if there is a True in A6’s members |
A7’s result:
Value |
false |
Find more examples in SPL CookBook.
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
