SPL: grouping & aggregation

 

Grouping operation is to assign the members of a set with the same attributes to the same group. For example, employee tables are grouped by department, and each group of employees has the same department; sales tables can be grouped by sales year, and each group has sales records of the same year, and so on.

Sometimes we need to calculate the data by different groups, and it is when grouping & aggregation is needed. Grouping & aggregation means grouping the data according to certain regulations, and then performing aggregation operation for each group.

 

[e.g. 1] Query the total sales of each month in 2014 according to the sales table. Some of the data are as follows:

ID

CUSTOMERID

ORDERDATE

SELLERID

PRODUCTID

AMOUNT

10400

EASTC

2014/01/01

1

27

3063.0

10401

HANAR

2014/01/01

1

17

3868.6

10402

ERNSH

2014/01/02

8

70

2713.5

10403

ERNSH

2014/01/03

4

42

1005.9

10404

MAGAA

2014/01/03

2

74

1675.0

The A.groups() function is provided in SPL for grouping & aggregation.

The SPL script looks like this:

A

1

=T("Sales.csv").select(year(ORDERDATE)==2014)

2

=A1.groups(month(ORDERDATE):MONTH; sum(AMOUNT):AMOUNT)

A1: import sales table and select the records of 2014.

A2: use the A.groups() function to group data by month and aggregate the total sales of each month.

 

[e.g. 2] Query the departments with an average age of less than 45 in New York State according to the employee table. Some of the data are as follows:

ID

NAME

BIRTHDAY

STATE

DEPT

SALARY

1

Rebecca

1974/11/20

California

R&D

7000

2

Ashley

1980/07/19

New York

Finance

11000

3

Rachel

1970/12/17

New Mexico

Sales

9000

4

Emily

1985/03/07

Texas

HR

7000

5

Ashley

1975/05/13

Texas

R&D

16000

 The SPL script looks like this:

A

1

=T("Employee.csv").select(STATE=="New York")

2

=A1.groups(DEPT; avg(age(BIRTHDAY)):AVGAGE)

3

=A2.select(AVGAGE<45)

A1: import the employee table and select the records of New York State.

A2: use the A.groups() function to calculate the average age by department.

A3: select the records with an average age of less than 45 in the result set after grouping & aggregation.

In SPL, the A.select() function can be used to select data both before and after grouping.

 

In SPL, the A.groups()function not only supports aggregation functions supported by SQL, such as sum, count, avg, max, min,etc., but also supports some of the more common ones that are not supported by the Group BY statement of SQL: top (first Ns/last Ns) , iterate (iteration function) , icount (count distinct value) , median (median, some database support) , and so on.

 

[e.g. 3] According to the sales table, summarize the times that customers are ranked as the top three sales of each month in 2014. Some of the data are as follows:

ID

CUSTOMERID

ORDERDATE

SELLERID

PRODUCTID

AMOUNT

10400

EASTC

2014/01/01

1

27

3063.0

10401

HANAR

2014/01/01

1

17

3868.6

10402

ERNSH

2014/01/02

8

70

2713.5

10403

ERNSH

2014/01/03

4

42

1005.9

10404

MAGAA

2014/01/03

2

74

1675.0

The SPL script looks like this:

A

1

=T("Sales.csv").select(year(ORDERDATE)==2014)

2

=A1.groups(month(ORDERDATE):MONTH; top(-3;AMOUNT):TOP3)

3

=A2.conj(TOP3).groups(CUSTOMERID; count(~):COUNT)

A1: import the sales table and select the records of 2014.

A2: use the A.groups() function to aggregate the top three by month.

A3: after concatenating the top three records of each month, use the A.groups() function to count the times for each customer.

 

[e.g. 4] Summarize the number of students who get the median scores or fail the exam (less than 60 marks) of each class in every subject. Some of the data are as follows:

CLASS

STUDENTID

SUBJECT

SCORE

1

1

English

84

1

1

Math

77

1

1

PE

69

1

2

English

81

1

2

Math

80

The solution of is:

A

1

=T("Scores.csv")

2

=A1.groups(CLASS,SUBJECT; median(,SCORE):MEDIAN_SCORE, count(SCORE<60):FAIL_COUNT)

A1: import the score table.

A2: use the A.groups() function to aggregate the number of median and failed students by class and subject.

 

In SPL, the count() function can count the number of failed students directly by using the parameter “SCORE<60” without selecting the failed records in advance. This makes it easy to calculate both the median and the number.

Sales.csv

Employee.csv

Scores.csv