当前位置:网站首页>Grouping operation aligned with specified datum

Grouping operation aligned with specified datum

2020-11-06 01:16:04 raqsoft

【 Abstract 】
     When grouping, it is often required that the result set must appear in the order of the benchmark set , This alignment grouping is very common in daily statistics . We can also generalize alignment grouping to more general enumeration grouping . How to deal with alignment groups easily and quickly , Here's the whole process , And provide esProc Sample code . A grouping operation aligned to a specified datum

Against a set of benchmarks , Compare a field or expression of a member of the set to be grouped with a member of the benchmark set , The same people are divided into the same group , Finally, the number of groups split out is the same as the number of base set members . This grouping is called alignment grouping . Alignment groups may have empty groups , There may also be members who are not assigned to any group .

1. Common alignment groups


1.1 Each group retains at most one matching member

In the specified order of a field , Group all the records in the table and sum them up .

【 example 1】 According to the interrelated curriculum and course selection schedule , Query which courses are not selected according to the order of the curriculum :

【SPL Script 】

A5 The results are as follows :

1.2 Each group retains all matching members

In the specified order of a field , Group all the records in the table and sum them up .

【 example 2】 According to the related employee table and department table , Count the number of people in each department according to the order in the Department table :

【SPL Script 】

A5 The results are as follows :

1.3 The mismatched records are put into the new group

In the specified order of a field , Group all records in the table , The mismatched records are put into the new group .

【 example 3】 According to the employee salary scale , Statistics [California, Texas, New York, Florida] The average wage of , An unnamed state acts as “Other” Statistics . Part of the data of employee salary table is as follows :

【SPL Script 】

A5 The results are as follows :

2. Number alignment grouping


Number alignment grouping , Refers to grouping according to the specified sequence number , Members with the same serial number are grouped into the same group .

2.1 Each group retains at most one matching member

In two related tables , Find unreferenced records .

【 example 4】 According to the interrelated sales table and customer table , List in order 2014 Customers without sales records in :

【SPL Script 】

A6 The results are as follows :

2.2 Each group retains all matching members

Group all the records in the table by serial number and sum them up .

【 example 5】 According to the order form , List in order 2013 The total number of orders per month in . Part of the order form is as follows :

【SPL Script 】

A4 The results are as follows :

2.3 Group by sequence number repeatability

Group according to the calculated sequence repeatability and calculate .

【 example 6】 According to the posting record form , Group posts by tag , And count the frequency of each tag . Part of the data in the post record table is as follows :

【SPL Script 】

A5 The results are as follows :

2.4 Segment and group

According to the value of the specified field , Segment and group and aggregate count .

【 example 7】 According to the employee salary scale , By salary 8000 following 、8000~12000 and 12000 The above groups , And count the number of people in each group . Part of the data of employee salary table is as follows :

【SPL Script 】

A5 The results are as follows :

According to the calculation of the expression , Divide the records into groups and summarize them to calculate the average .

【 example 8】 According to the staff table , According to the entry time 10 In the following ,10~20 Years and 20 More than years in groups , And calculate the average wage of each group . Some of the data in the employee table are as follows :

【SPL Script 】

A6 The results are as follows :

3. Enumerate groups


Enumeration grouping means , Specify a set of enumeration conditions in advance , Take the members of the set to be grouped as parameters to calculate the conditions , All the conditions are divided into a subset corresponding to the condition , Subsets of the result set correspond to the conditions specified in advance .

3.1 Each member is only stored in the first matching group

By enumerating conditional expressions , Group records , When grouping, records are placed only in the first matching group .

【 example 9】 According to the population table of China's major cities , Classify cities by population . Part of the population table of China's major cities is as follows :

【SPL Script 】

A4 The results are as follows :

3.2 Unmatched members are stored in the new group

By enumerating conditional expressions , Group records , The mismatched records are put into the new group .

【 example 10】 According to the employee salary scale , By age [ Less than 35 year , Less than 45 year ] Group employees , Count the average wage , Those who do not meet the conditions are assigned to a new group . Part of the data of employee salary table is as follows :

【SPL Script 】

A5 The results are as follows :

3.3 Repeatable grouping according to enumeration conditions

According to different assigned sequences , Group records and calculate , Records can be repeated when grouped .

【 example 11】 According to the city GDP surface , Separate statistics of municipalities directly under the central government 、 first-tier cities 、 Per capita in second tier cities GDP, It may be repeated in groups . City GDP Some of the data in the table are as follows :

【SPL Script 】

A5 The results are as follows :

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

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