# Comparison of SQL & SPL: Non-equi-grouping

A grouping operation is called equi-grouping if the grouping condition is same values or expression results. Equi-grouping operations are the most seen and they divide a data set completely. Their features are as follows:

1. No empty subset in the grouping result set;
2. Each member in the original set belongs to one and only one subset.

Does there exist the non-equi grouping and incomplete division? The answer is yes. Their features are as follows:

1. The grouping result set contains at least one empty subset;
2. The grouping result set does not contain all members of the original set;
3. One member may belong to more than one subset.

## Ⅰ. Alignment grouping

An alignment grouping compares a specific field or expression of each member in the to-be-grouped set with every member in a predefined base set and puts members matching same member in the base set in the same group. The number of groups in the result set is the same as that of members in the base set. The alignment grouping may generate empty group or leaves one or more members in outside of any group.

【Example 1】According to the Course table and Select_Course table below, find the unselected courses according to the order in Course table.

COURSE：

SELECT_COURSE：

SQL solution:

SQL does not support the alignment grouping. We get the distinct selected courses from the Select_Course table and then select records that do not contain these selected courses. Below are the SQL queries:

```select *
from COURSE
where
ID not in
(select DISTINCT COURSEID
from SELECT_COURSE)
order by rownum```

SPL solution:

SPL offers A.align() function to perform alignment grouping.

A1: Import Course table.
A2: Import SelectCourse table.
A3: Use A.align() function to group SelectCourse table according to Course table’s ID field.
A4: Get the courses that no students select from Course table.

【Example 2】According to DEPARTMENT table and EMPLOYEE table below, calculate the number of employees in each department according to the order of DEPARTMENT table. Below is part of the source tables:

DEPARTMENT：

EMPLOYEE：

SQL solution:

For the alignment grouping operation, each group will contain all matching members in the to-be-grouped set. Since SQL does not directly support this type of grouping, we group EMPLOYEE table by department and count employees in each department, perform a left join between DEPARTMENT table and the result set of grouping and summarizing EMPLOYEE table, and query each department to get the number of employees. Note that the SQL JOIN operation between two result sets does not ensure that records are arranged in a specific order, so we need to sort them by row numbers in DEPARTMENT table; otherwise, the query result may not be ordered in the same way as DEPARTMENT table. Below are the SQL queries:

```select
t1.DEPT, t2.COUNT
from
(select DEPT,rownum ordernum from DEPARTMENT) t1
left join
(select
DEPT, count(*) COUNT
from EMPLOYEE
group by DEPT) t2
on t1.DEPT=t2.DEPT
order by ordernum```

SPL solution:

SPL offers A.align() function to handle the alignment grouping. It works with @a option to return all matching members for each group.

A1: Import Department table.
A2: Import Employee table.
A3: Group Employee table according to the order in Department table and return all matching members for each group.
A4: Count employees in each department.

【Example 3】According to Employee table (Below is a part of it), calculate the average salary for each state of [California, Texas, New York, Florida] and for all the other states as a whole, which are classified as “Other”.

SQL solution:

In this case, the non-matching members are required to put into a new group. SQL does not have a method to do this. First, we define a set of the four specified state groups and the “Other” group, and then check which group each employee record belongs to using CASE WHEN statement. Below are SQL queries:

```with cte1(ID,STATE) as
(select 1,'California' from DUAL
UNION ALL select 2,'Texas' from DUAL
UNION ALL select 3,'New York' from DUAL
UNION ALL select 4,'Florida' from DUAL
UNION ALL select 5,'OTHER' from DUAL)
select
t1.STATE, t2.AVG_SALARY
from cte1 t1
left join
(select
STATE,avg(SALARY) AVG_SALARY
from
( select
CASE WHEN
STATE IN ('California','Texas','New York','Florida')
THEN STATE
ELSE 'OTHER' END STATE,
SALARY
from EMPLOYEE)
group by STATE) t2
on t1.STATE=t2.STATE
order by t1.ID```

SPL solution:

SPL has A.align() function to do this. It uses @n option to put the non-matching members into a new group.

A1: Import Employee table.
A2: Define a constant set of regions.
A3: A.align() groups Employee table according to A2’s set of states. @a option is used to return all matching members for each group; @n option enables putting the non-matching members into a new group.
A4: Name the new group “OTHER” and calculate the average salary of each group.

SQL uses JOION to achieve an alignment grouping because it does not have a method to do it directly. As a SQL result set is unordered, we record the order of the records in the original table through their original row numbers. This makes SQL solution to an alignment grouping problem complicated. SPL offers A.align() function for specifically handling alignment grouping operations, which features concise syntax and efficient execution.

## Ⅱ. Enumeration grouping

An enumeration grouping prespecifies a set of enumerated conditions, computes each conditional expression using every member of the to-be-grouped set as the parameter, and puts members that make the same condition true into the corresponding subset. The subsets in the result set correspond to the prespecified conditions one by one.

【Example 4】According to the following China urban population table, divide cities into groups and count the number in each group. Megacity: above 2 million population; Large city: between 1 to 2 million population; Major city: between 0.5 to 1 million population; Other cities. Below is part of the source table:

SQL solution:

SQL lacks a direct method of handling enumeration grouping operations. We segment the source table using CASE WHEN statement, then group data by comparing it with the segmentation values and perform aggregation. CASE WHEN statement put each record into the first matching grouping only. Below are SQL queries:

```with cte1(ID,CITY_TYPE) as
(select 1,'Megacity' from DUAL
UNION ALL select 2,'Large city' from DUAL
UNION ALL select 3,'Major city' from DUAL
UNION ALL select 4,'Other cities' from DUAL)
select
t1.CITY_TYPE, nvl(t2.COUNT,0) COUNT
from cte1 t1
left join
(select CITY_TYPE, count(*) COUNT from
(select
case when POPULATION>2000000 then 'Megacity'
when POPULATION>1000000 then 'Large city'
when POPULATION>500000 then 'Major city'
when POPULATION<=500000 then 'Other cities'
else 'OTHER' end CITY_TYPE
from URBAN_POPULATION)
group by CITY_TYPE) t2
on t1.CITY_TYPE=t2.CITY_TYPE
order by t1.ID```

SPL solution:

SPL provides A.enum() function to perform grouping according to the enumerated conditions.

A1: Import UrbanPopulation table.
A2: Define grouping conditions.
A3: A.enum() function performs enumeration grouping on A1’s table according to A1’s conditions and, by default, puts each record in the first matching grouping only.
A4: Define a name for each group.
A5: Count cities in each group according to the enumeration grouping result.

【Example 5】Divide the following employee table into three groups according to age groups <35, <45 and other ages. Below is part of the source table:

SQL solution:

It is required, in this case, that the non-matching records be put into a new group. We use CASE WHEN … ELSE statement to define an “Others” group to achieve this. Below are SQL queries:

```with cte1(ID,AGE_AREA) as
(select 1,'Below 35' from DUAL
UNION ALL select 2,'Below 45' from DUAL
UNION ALL select 3,'Others' from DUAL)
select
t1.AGE_AREA, nvl(t2.AVG_SALARY,0) AVG_SALARY
from cte1 t1
left join
(select
AGE_AREA, avg(SALARY) AVG_SALARY
from
(select
case when TRUNC(months_between(sysdate, BIRTHDAY)/12)<35
then 'Below 35'
when TRUNC(months_between(sysdate, BIRTHDAY)/12)<45
then 'Below 45'
else 'Others' end AGE_AREA,
SALARY
from EMPLOYEE)
group by AGE_AREA) t2
on t1.AGE_AREA=t2.AGE_AREA
order by t1.ID```

SPL solution:

A.enum() function uses @n option to put the non-matching records into a new group.

A1: Import Employee table.
A2: Define grouping conditions.
A3: A.enum() function performs enumeration grouping by age groups and works with @n option to put non-matching records into a new group.
A4: Define a name for each group.
A5: Calculate the average salary in each group according to A3’s result.

【Example 6】According to the following cities’ GDB table, calculate per capita GDP for the direct-controlled municipalities, first-tier cities and second-tier cities. Groups may contain common members as Beijing is both a direct-controlled city and a first-tier city. Below is part of the source table:

SQL solution:

In this case, one record could be put into more than one group. CASE WHEN statement can only put a record into the first matching group and does not support re-putting it to another group, so we need to calculate per capita GDP for each group and then concatenate the results. Below are SQL queries:

```select
'Direct-controlled municipality' AREA,
SUM(GDP*1000)/SUM(POPULATION) CAPITA_GDP
from CITY_GDP
where
CITY in ('Beijing','Shanghai','Tianjin','Chongqing')
UNION ALL
select
'First-tier city' AREA,
SUM(GDP*1000)/SUM(POPULATION) CAPITA_GDP
from CITY_GDP
where
CITY in ('Beijing','Shanghai','Guangzhou','Shenzhen')
UNION ALL
select
'Second-tier city' AREA,
SUM(GDP*1000)/SUM(POPULATION) CAPITA_GDP
from CITY_GDP
where
CITY in ('Chengdu','Hangzhou','Chongqing','Wuhan','Xian','Suzhou','Tianjin','Nanjing','Changsha','Zhengzhou','Dongguan','Qingdao','Shenyang','Ningbo','Kunming')```

In the SQL query, groups are respectively queried and summarized, and their results are concatenated one by one. Each group requires a piece of SQL code. When there are many groups, it’s complicated to write and maintain the SQL query.

SPL solution:

A.enum() function uses @r function to check whether all members in a group match a condition.

A1: Import CityGDP table.
A2: Enumerate conditions for direct-controlled city, first-tier city and second-tier ciy.
A3: A.enum() groups A1’s table according to the enumerated conditions and works with @r option to check whether all members in a group match a condition.
A4: Define a name for each group.
A5: Calculate per capita GDP in each group according to A3’s result.

SPL needs just one option to handle the re-grouping of a member in an enumeration grouping operation. No matter how many groups there are, users only need to maintain the enumerated conditions, instead of modifying the code for grouping & aggregation.

## Summary

SQL is not fit to handle both alignment grouping and enumeration grouping. There are two causes for this. One is SQL does not provide a special statement or function to handle them, the other is that SQL’s theoretical foundation, the relational algebra, is based on unordered sets, which makes it hard to handle computations that involve the fixed order. SPL, however, is based on ordered sets, and is thus naturally good at handling order-based computations. Besides, it offers functions to specifically deal with the two types of grouping operations.

When the query is complicated, the complexity of SQL query increases by multiple times. It involves the use of temporary table and nested query, etc, which makes it harder to write and maintain the SQL query. SPL, however, can compose succinct code step by step according to the natural way of thinking.

The SPL-driven esProc is the professional data computation engine. It is ordered-set-based and offers a complete set of grouping functions, which makes the marriage of both Java and SQL advantages. A grouping operation in SPL will thus become simple and easy.