Get the Records after and before the Searched One — From SQL to SPL #18

Problem description & analysis: The ProductionLine_Number in a certain table of the Mariadb database is a grouping field, and there are duplicate values in the Cardboard_Number field within the group. Task: Group by ProductionLine_Number, sort by date_Time within the group, and search for all records in each group with Cardboard_Number equal to the specified string. Retrieve the records before and after the specified offset and remove duplicate records. For example, Cardboard_Number=”WDL-005943998–1", with an offset of 1, the result is as follows: Code comparisons: SQL solution: If Cardboard_Number= “spL1ml82N4o” with an offset of 2, then the IDs of the result are 2,4,5,6,9,10,11,12. SQL: with ranked_table AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ProductionLine_Number ORDER BY date_Time) AS rn FROM table1 ), filtered_table AS ( SELECT id, Cardboard_Number, date_Time, ProductionLine_Number,rn FROM ranked_table WHERE Cardboard_Number = 'WDL-005943998-1' ) SELECT DISTINCT t1.id, t1.Cardboard_Number, t1.date_Time, t1.ProductionLine_Number FROM ranked_table t1 JOIN filtered_table t2 ON t1.ProductionLine_Number = t2.ProductionLine_Number AND (t1.rn = t2.rn OR t1.rn = t2.rn - 1 OR t1.rn = t2.rn + 1) ORDER BY ProductionLine_Number, date_Time; SQL requires using window functions to spell out sequence numbers, and then implementing interval association using JOIN, which can be quite lengthy in code. SPL solution: SPL has grouped subsets and a positional reference mechanism, with simple code.

Apr 14, 2025 - 03:46

Get the Records after and before the Searched One — From SQL to SPL #18

Problem description & analysis:

The ProductionLine_Number in a certain table of the Mariadb database is a grouping field, and there are duplicate values in the Cardboard_Number field within the group.

Task: Group by ProductionLine_Number, sort by date_Time within the group, and search for all records in each group with Cardboard_Number equal to the specified string. Retrieve the records before and after the specified offset and remove duplicate records. For example, Cardboard_Number=”WDL-005943998–1", with an offset of 1, the result is as follows:

Code comparisons:

SQL solution: If Cardboard_Number= “spL1ml82N4o” with an offset of 2, then the IDs of the result are 2,4,5,6,9,10,11,12.

SQL:
with ranked_table AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY ProductionLine_Number ORDER BY date_Time) AS rn
    FROM table1
),
filtered_table AS (
    SELECT id, Cardboard_Number, date_Time, ProductionLine_Number,rn
    FROM ranked_table
    WHERE Cardboard_Number = 'WDL-005943998-1'
)
SELECT DISTINCT t1.id, t1.Cardboard_Number, t1.date_Time, t1.ProductionLine_Number
    FROM ranked_table t1
    JOIN filtered_table t2
    ON t1.ProductionLine_Number = t2.ProductionLine_Number
    AND (t1.rn = t2.rn OR t1.rn = t2.rn - 1 OR t1.rn = t2.rn + 1)
ORDER BY ProductionLine_Number, date_Time;

SQL requires using window functions to spell out sequence numbers, and then implementing interval association using JOIN, which can be quite lengthy in code.

SPL solution: SPL has grouped subsets and a positional reference mechanism, with simple code.