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. Try.DEMO.

A2： Group but do not aggregate.

A3： Search for the records of each grouped subset, find the records within the interval of 1 record before and 1 record after (a total of 3 records) with the Cardboard_Number field equal to the specified string, and merge the calculation results of each group. [-1:1] represents the relative interval range.

esProc SPL — Free Trial Available, Download Now! esProc SPL FREE Download.