Tag: Operational logic

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. In a one-to-one association relationship, each record of a table corresponds to one and only one record in the other table and vice versa. The relationship often exists between two tables using the same

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

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

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

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.

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

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

Grouping often requires the result set to appear in the order of a criterion set, and this kind of alignment groups are quite common in everyday statistics. For example, according to the order of Beijing, Shanghai, Guangzhou, and Shenzhen, calculate the total sales of a company in these cities; according to the specified department order, query the average salary of each department, and so on.

Sometimes the order of the data makes sense when grouping. We at times group the adjacent records that have the same field values or that meet certain conditions. For example, find out the nation that ranks in the first of consecutive Olympic gold medals, find out how many days at most that the closing price of a stock has been increased, and so on. This is where order-related grouping comes in. 1.Grouping by consecutive the same values When grouping an ordered set, a new group will be created when the values of the fields for grouping change. [e.g. 1] According

To calculate data that are arranged in a certain order, we often encounter cross-row references. For example, calculate the increase of stock price compared to the previous day, the moving average price of a stock for 20 days and so on.