Category: 5 SPL Learning Material

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.

Grouping operation is to assign the members of a set with the same attributes to the same group. For example, employee tables are grouped by department, and each group of employees has the same department; sales tables can be grouped by sales year, and each group has sales records of the same year, and so on. Sometimes we need to calculate the data by different groups, and it is when grouping & aggregation is needed. Grouping & aggregation means grouping the data according to certain regulations, and then performing aggregation operation for each group. [e.g. 1] Query the total sales

Selecting data by segment refers to finding the data in a group according to the specified interval. For example, return to assessments (excellent, good, medium, poor) according to the test results; return to the levels of assets (poor, well-off, middle-class, rich, etc.) according to the annual income of the family.

TopN query is to look up the first Ns / last Ns from the data. TopN query can not only directly get a value, but also inquire the detailed information of the record where TopN is located, and sometimes it also looks for the row number of TopN. Additionally, TopN query may also be used in groups to query the first Ns / last Ns within the group.

Grouping operation is to assign the members in a set with the same attributes to the same group. For example, employee tables are grouped by department, and each group of employees has the same department; sales tables can be grouped by year, and each group has sales records of the same year, and so on.

Sometimes, we don’t care about what the exact maximum value is, we are rather concerned about the record in which the maximum value is located. For example, look up the student ID with the highest math score, select the order with the highest monthly sales, and so on.

SPL offers a wealth of functions for date, time and datetime handling, including calculating the current time/date, extracting a component from a date/time/datetime, combining components to generate a date/time/datetime, format conversion, precision tuning, getting a related date, calculating the time interval, calculating the relative date, equivalence comparison, workday-related calculations, evenly spaced time series handling, etc. These functions can be divided into two categories – basic handling and computations.