当前位置:网站首页>Subordination judgment in structured data

Subordination judgment in structured data

2020-11-06 01:16:23 raqsoft

【 Abstract 】
     When selecting data from a data table , Sometimes it is necessary to determine whether members belong to a collection . For example, select the price information of important cities from the price table , Choose from the sales list VIP Customer sales records and so on . How to deal with the subordination judgment in structured data easily and quickly , Here's the whole process , And provide esProc Sample code . Subordination judgment in structured data

1. Set inclusion detection

In a table , Find records according to the inclusion of the set .

【 example 1】 In the employee list , Statistics of the average wages of various departments in the first tier cities . Some of the data are as follows :

【 Their thinking 】

When selecting data from the employee table , It is necessary to judge whether the city of the employee belongs to Beijing 、 Shanghai 、 Guangzhou 、 The set of constants formed by Shenzhen . When the number of members of a set is less than 10 Time , You can use functions A.contain() To filter .

【SPL Script 】

A4 The results are as follows :

In a table , Find records according to the inclusion judgment of a larger set .

【 example 2】 In the sales list , Statistics 2014 Monthly sales of key customers in . Some of the data are as follows :

【 Their thinking 】

Inherent 【 example 1】 similar , When selecting data from the sales table , It is necessary to determine whether the sales customer belongs to the constant set of key customers . When the set has more members ( exceed 10 individual ), You can sort the set of constants first , And then use the function A.contain() Of @b Options , Do a binary search .

【SPL Script 】

A4 The results are as follows :

2. Inclusion detection of foreign key mappings

In two tables , Find records based on inclusion detection of foreign key mappings .

【 example 3】 Find out how many students in each class have taken elective courses “Matlab” Course . The course selection and curriculum are as follows :

【 Their thinking 】

When selecting data from the course selection table , We need to judge whether the name of the course is equal to “Matlab”. You can first filter out the course name from the course schedule “Matlab” A collection of courses for , Then choose the course of the course schedule ID Records that belong to this collection .

【SPL Script 】

A6 The results are as follows :

3. Inclusion detection of non foreign keys

In two tables , Find records based on non foreign key inclusion detection .

【 example 4】 Query all the subjects in each class with scores over 80 The number of students divided . The grade and student charts are as follows :

【SQL sentence 】

When selecting data from the student table , It is necessary to judge whether a student has achieved more than 80 Points of . You can first select all greater than in the score sheet 80 A minute's record , Then press student ID duplicate removal , Get a higher score than 80 The students of grade ID Set . Next, just pick the students ID Records that belong to this collection .

【SPL Script 】

A7 The results are as follows :

In two tables , Find records based on non foreign key matching detection , Optimize and speed up .

【 example 5】 Inquire about 2014 Number of customers with sales records by city in . The sales list and customer list are as follows :

【 Their thinking 】

When selecting data from the customer table , We need to judge whether the customer is in 2014 Whether there is a sales record in . You can choose from the sales list first 2014 Record of , Then click on the customer ID duplicate removal , obtain 2014 Customers with sales records in ID Set . Next, just select the customer table ID Records that belong to this collection .

【SPL Script 】

A6 The results are as follows :

4. Exclusion detection of foreign key mappings

In two tables , Find records based on the exclusion detection of foreign key mappings .

【 example 6】 Inquire about 2014 Total sales per new customer per year . The sales list and customer list are as follows :

【 Their thinking 】

When selecting data from the customer table , It needs to be judged that the customer is in 2014 No sales record in . You can first filter out 2014 A collection of sales records in , Then select the customer table ID Records that are not subordinate to this collection .

【SPL Script 】

A5 The results are as follows :

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

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