当前位置:网站首页>Examples of unconventional aggregation

Examples of unconventional aggregation

2020-11-08 13:52:20 osc_u9wft6hh

1.     Aggregate sum after enumeration grouping

【 example 1】 From the city GDP In the table , Separate statistics of municipalities directly under the central government 、 Per capita in first tier and second tier cities GDP. City GDP Some of the data in the table are as follows :

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") / Check the city GDP surface
3 [["Beijing","Shanghai","Tianjing","Chongqing"].pos(?)>0,["Beijing","Shanghai","Guangzhou","Shenzhen"].pos(?)>0,["Chengdu","Hangzhou","Chongqing","Wuhan","Xian","Suzhou","Tianjing","Nanjing","Changsha","Zhengzhou","Dongguan","Qingdao","Shenyang","Ningbo","Kunming"].pos(?)>0] / Enumerate municipalities 、 First tier cities and second tier cities
4 =A2.enum@r(A3,City) / Group by city enumeration
5 =A4.new(A3(#):Area,~.sum(GDP)/~.sum(Population)*10000:CapitaGDP) / Count the average person in each group GDP. It uses functions sum() Sum up

    A5 The results are as follows :

Area CapitaGDP
["Beijing","Shanghai","Tianjing","Chongqing"].pos(?)>0 107345.03
["Beijing","Shanghai","Guangzhou","Shenzhen"].pos(?)>0 151796.49
["Chengdu","Hangzhou","Chongqing","Wuhan","Xian","Suzhou","Tianjing","Nanjing","Changsha","Zhengzhou","Dongguan","Qingdao","Shenyang","Ningbo","Kunming"].pos(?)>0 106040.57

 

2.     Merge overlapping time intervals

【 example 2】 Take the customer ANATR Merge order records with recurring time periods . Some data in the customer table are as follows :

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") / Select customers ANATR Order information for , Sort by order date
3 =A2.group@i(OrderDate>max(FinishDate[,-1])) / When the order date is greater than the completion date of all previous orders, the new group
4 =A3.new(Customer,~.min(OrderDate):OrderDate,~.max(FinishDate):FinishDate) / Using functions min() Calculate the earliest order date of each group as the order date , Using functions max Calculate the latest order date as the completion date

    A4 The results are as follows :

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.     Count the number of satisfied conditions in group aggregation

【 example 4】 Ask for the number of students who failed in each subject in a class . Some of the data in the report form are as follows :

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'") / Check the results of class one students
3 =A2.groups(SUBJECT;   count(SCORE<60):FailCount) / Group summary , It uses functions count() Count the number of people who failed

    A3 The results are as follows :

SUBJECT FailCount
English 2
Math 0
PE 2

 

4.     In a set of Boolean values , Aggregation performs logic and operations

【 example 5】 According to a series of primary school online teaching terminal Questionnaire , See if all students have access to mobile phones . The questionnaire and summary contents of each class are as follows :

..

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")
/ Recursively traversing directories , List all files
2 for A1 =file(A2).xlsimport@t() / Circularly import the questionnaire of each class excel file
3
=B2.([TERMINAL,"Phone"].ifn().split@c().pos("Phone")   > 0)|@ / When the terminal in the questionnaire is not filled in, it is not considered that the mobile phone terminal is not supported , Using functions ifn() Guarantee that this item is true.
4 =B3.cand()
/ Using functions A.cand() Calculation B3 Are all members of true

    A4 The results are as follows :

Value
false

 

5.     In a set of Boolean values , To perform logic or operations in aggregation

【 example 6】 Check with customers RATTC, stay 2014 Whether it has been ranked in the top three of monthly sales . The data in the sales table are as follows :

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 data source , Read the sales list
2 =A1.select(year(OrderDate)==2014) / elect 2014 Annual data
3 =A2.group(month(OrderDate)) / take 2014 Year data are grouped by month
4 =A3.(~.groups(Customer;   sum(Amount):Amount)) / The members after grouping summarize the sales amount according to the customer group
5 =A4.new(~.top(-3; Amount):Top3) / Cycle the data for each month , Before calculating the monthly sales 3 The customer
6 =A5.(Top3.(Customer).pos("RATTC")>0) / Judge whether the top three of each month include customers RATTC
7 =A6.cor() / Using functions A.cor() Calculation A6 Whether there are members of true

    A7 The results are as follows :

Value
false

 《SPL CookBook》 There are more examples of relevant calculations in .


版权声明
本文为[osc_u9wft6hh]所创,转载请带上原文链接,感谢