Category: SPL learning materials
Loop Computations
A loop computation computes members of a set according to a specific order. During the loop, we can perform a series of computations from simple ones, like accessing the current member and assigning values to a member, to complex ones, such as the inter-row computation, nested loop, and iterative computation. This essay introduces esProc, a convenient and fast tool of handling loop computations, through a set of sample programs. Looking Loop Computations for details.
The Select Operation on Structured Data
A select operation gets one or more members from a set according to a specific condition. It is similar to a locate operation. But the latter is concerned with the positions of certain members while the select operation wants the detailed information of a member record. Getting the incomes of the employees whose hire durations are over 10 years and finding names of customers whose order amounts are above 10,000 are among the many examples of select operations. But to perform selections conveniently and efficiently, you’d better read this article carefully where various scenarios are listed and sample scripts in esProc SPL are provided. Looking The Select Operation on Structured Data for details.
SPL: One-to-many Table Associations
A JOIN operation is used to join records of two or more tables. This essay explains SPL’s way of handling JOINs and the underlying principles.
Table association relationships are data dependence relationships between tables. There are four types of table association relationships – one-to-one, many-to-one, one-to-many and many-to-many. We can combine two or more tables through one of those relationships to achieve the multi-table join query.
SPL: One-to-One Table Associations
A JOIN operation is used to join records of two or more tables. This essay explains SPL’s way of handling JOINs and the underlying principles.
Table association relationships are data dependence relationships between tables. There are four types of table association relationships – one-to-one, many-to-one, one-to-many and many-to-many. We can combine two or more tables through one of those relationships to achieve the multi-table join query.
SPL: Complicated Static Transposition
The transposition function is commonly presented in front-end such as reports, which transposes the queried data into a specific display layout. For example, row-to-column transposition, column-to-row transposition, more complex dynamictransposition, etc. In SPL: Static Transposition, we have introduced the static transposition. Sometimes, the structure of the transposed table is definite, but it can hardly be performed with the pivot function directly. So let’s take a look at how SPL handles such complicated static transposition.
1. Multirow-to-multirow transposition
[Example 1] Based on the following punch-in data table, generate a new table recording the daily activities of each employee. Each person corresponds to seven records per day:
SPL: Static Transposition
The transposition function is commonly presented in front-end such as reports, which transposes the queried data into a specific display layout. For example, row-to-column transposition, column-to-row transposition, and more complex dynamic transposition, etc. And static transposition means that the structure of the transposed table is determined in advance, and the target structure will not change dynamically according to the data.
1. Row-to-column transposition
As the name implies, this functionality is used to perform the transposition from row to column, that is, taking the values in the rows as the names of columns. In practice, the application of row-to-column transposition usually follows the grouping and aggregation operations. Through grouping, we process the data of rows in the column to be transposed into distinct values, and then display the values of rows as the names of the columns. In fact, the function of row-to-column transposition is to subdivide the aggregation result of one column into a number of more specific column aggregation results for a more intuitive display effect.
SPL: Set Operations
Set operations refer to the basic operations among various sets, including conjunction, intersection, union, difference, exclusive OR, containment and so forth. Structured data is often in the form of sets, and SPL provides rich methods and functions to facilitate the computation of such data. Let's take a look at how SPL solves set operation problems with some examples.
1. Conjunction
The conjunction of set A and set B is the set consisting of all members of set A or set B, duplicate members included. For example, the conjunction of set [1,2] and set [1,3] is the set [1,2,1,3], and the duplicate member 1 appears twice in the conjunction.
SPL: Recursively Search Referenced Records
Recursion refers to an operation or function that calls itself directly or indirectly. For example, the tower of Hanoi is a typical recursive operation. It is also very common to use recursion for queries in practice. For instance, we want to know the authorities that are in charge of a certain office. The superior authority that directly dominates the office is not difficult to find. However, the uncertain layers of affiliations are hard to find if we continue to query the superiors of the relative branches repeatedly, and that’s where recursion comes in.
1. Recursively search all referenced records
[e.g. 1] Query the level of each office based on the organizational structure table of the company (the head office is level 1, branch office is level 2, and so forth). Some of the data are as follows:
SPL: grouping by enumeration criteria
When grouping, we often need to assign records that meet the same criterion to the same group. For example, divide cities into large, medium and small ones according to the urban population, sort families into rich, middle class, well-off, and poor ones according to annual income, and so forth. According to the pre-defined enumeration criteria, the qualified members are assigned to the corresponding group, which is referred to as the enumeration grouping.
1. Saving each member in the first matching group
The members of the set to be grouped are taken as parameters to calculate the criteria according to the order of the enumeration criterion set defined in advance.
SPL: grouping and sorting aligned by sequence number
Sometimes we group and sort the data in order of sequence number, grouping members with the same sequence number into the same group. For example, calculate the total sales of each month last year in order from January to December, calculate the numbers of accessors to a website according to the order from Monday to Sunday, and so on.
This kind of grouping operation, which is aligned to a specified criterion, is collectively referred to as alignment grouping. Grouping by sequence alignment is a special case of alignment grouping where the base set is a sequence of integers starting from 1. Alignment groups may have empty groups or members that are not assigned to any of the groups.
