Category: 1 SPL&Code

Microservices architecture, celebrated for its flexible deployment and strong scalability, has become a mainstream approach in modern enterprise applications. Yet in practice, it often faces the dilemma of being praised but seldom adopted. The root cause lies in traditional Java-based implementations: microservice code tends to be verbose, business logic is hard to decouple or update, and complex logic often ends up being pushed into the database layer—resulting in increased system coupling. SPL (Structured Process Language) offers a solution. SPL is an interpreted scripting language built purely in Java, exceptionally good at processing complex data logic and supporting hot-swapping of business

A certain database table has multiple accounts, each with multiple time intervals that overlap. account_id start_date end_date A 2019-06-20 2019-06-29 A 2019-06-25 2019-07-25 A 2019-07-20 2019-08-26 A 2019-12-25 2020-01-25 A 2021-04-27 2021-07-27 A 2021-06-25 2021-07-14 A 2021-07-10 2021-08-14 A 2021-09-10 2021-11-12 B 2019-07-13 2020-07-14 B 2019-06-25 2019-08-26 Now we need to merge the overlapping time intervals in each account to generate new intervals that do not overlap. account_id start_date end_date A 2019-06-20 2019-08-26 A 2019-12-25 2020-01-25 A 2021-04-27 2021-08-14 A 2021-09-10 2021-11-12 B 2019-06-25 2020-07-14 After SQL grouping, it is necessary to aggregate immediately, which makes it inconvenient to generate

After sorting the event table of a certain database table by timestamp, adjacent value fields may sometimes be continuously identical. id value timestamp 1 1 2023-11-10 13:00:00 2 2 2023-11-11 13:00:00 3 2 2023-11-12 13:00:00 4 1 2023-11-13 13:00:00 5 1 2023-11-14 13:00:00 6 1 2023-11-15 13:00:00 7 2 2023-11-16 13:00:00 8 2 2023-11-17 13:00:00 9 1 2023-11-18 13:00:00 Now we need to group adjacent records with the same value, extract the start time of this group and the start time of the next group as the start and end time of this group, and form a new two-dimensional table.

The definition of association in SQL is overly simplistic: association is essentially performing a Cartesian product of two tables and then filtering the result, expressed in syntax like A JOIN B ON …. Python’s approach to association largely follows the SQL approach, with similar concepts and methods. However, esProc SPL understands association differently, with a definition that is no longer related to the Cartesian product. SPL divides association into two categories. One is called foreign key association, which refers to the association between a regular field (foreign key) of one table and the primary key of the other. For example,

Business data often exhibits hierarchical relationships. For example, an order contains date, customer, and order details, with order details further containing price, quantity and product, and product encompassing name and category. JSON’s nestable structure is well-suited for describing such relationships. For example, here’s the order data: An order has both regular attributes “order ID, date, freight rate, weight” and nested attributes “order details, customer”. Order details, in turn, contain the nested attribute “product.” For this nestable structure, the common data organization method is a multi-layer nested table, where fields can be tables or records. When calculating, tables or records at

Data analysis often involves cross-row calculations, such as Link Relative Ratio (LRR), Year-over-Year (YoY) and moving averages. Implementing cross-row calculations on ordered datasets will involve the issue of referencing adjacent members within a set. For example, a merchant has prepared its sales data for 12 months of a year, arranged by month, and now wants to calculate the maximum monthly growth. Code in SQL: Window functions can reference the previous month’s sales, but it requires writing a subquery, making the code verbose. Moreover, SQL is based on unordered sets, resulting in its inability to utilize the original order of the

Performing calculations on each member of a dataset is a common task. While achievable with loop statements, it is quite cumbersome; even a simple summation requires multiple lines of code. Programming languages often encapsulate these operations as functions, such as Python’s sum function. Calculating the total price of orders is written as follows: It can also be written in SQL: Of course, there’s no problem with SPL: They all appear quite concise. However, tasks are not always this simple. Let’s see a more complex example: calculate a label column for employees, where managers with salaries above 5000 are labeled ‘yes,’

In computers, sets are generally stored as arrays, and their members naturally have positions. Data tables, which are essentially sets of records, are also often stored as arrays, so their member records also have the concept of position. In practice, many analytical computations are indeed position-dependent. However, SQL treats data tables as unordered sets, thus leading to significant inconvenience. esProc SPL preserves the ordered nature of arrays and provides a rich set of positional operation methods, enabling such computational requirements to be easily implemented with concise and understandable code. 1. Position index In an ordered dataset, a record’s position inherently

Standard SQL provides five fundamental aggregation operations: SUM, COUNT, AVG, MIN, and MAX—all compute a single value from a set. For example, finding the first login time for user 1001 in a log table is simple using SQL: Aggregation operations are also frequently used along with grouping operations, such as determining the first login time for each user: The only difference in syntax is the addition of the grouping field. Python is similar: These two lines of code seem to have a consistent style, but the result of the grouped aggregation is not a two-dimensional DataFrame. If you want to

Early SQL was extremely unsuited for ordered computations. While theoretically possible, the practical difficulties made it essentially unusable. The introduction of window functions improved ordered computations to some extent. However, SQL’s foundation is still based on unordered sets. Even with patches like window functions, it remains cumbersome. Yet, there were no better alternatives, so database-based operations still require using SQL combined with window functions. While loading data to process in Python can mitigate some of the difficulties, Python’s poor integration makes it difficult to work with Java applications. If coding directly in Java, it would be more cumbersome than using