From SQL to SPL: Get the records after and before the searched one

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.

id

Cardboard_Number

date_Time

ProductionLine_Number

2

WDL-005943998-1

2024-02-29 17:13:50

1

4

spL1ml82N4o

2024-02-29 17:13:54

1

5

WDL-005943998-1

2024-03-01 09:44:42

1

6

WDL-005943998-1

2024-03-01 10:34:57

1

7

950024027237

2024-03-01 10:44:57

1

8

950024027237

2024-03-01 10:52:57

1

9

WDL-005943998-1

2024-03-01 13:58:43

2

10

WDL-005943998-1

2024-03-01 13:58:46

2

11

spL1ml82N4o

2024-03-01 14:09:43

2

12

WDL-005943998-1

2024-03-12 15:48:36

2

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:

id

Cardboard_Number

date_Time

ProductionLine_Number

2

WDL-005943998-1

2024-02-29 17:13:50

1

4

spL1ml82N4o

2024-02-29 17:13:54

1

5

WDL-005943998-1

2024-03-01 09:44:42

1

6

WDL-005943998-1

2024-03-01 10:34:57

1

7

950024027237

2024-03-01 10:44:57

1

9

WDL-005943998-1

2024-03-01 13:58:43

2

10

WDL-005943998-1

2024-03-01 13:58:46

2

11

spL1ml82N4o

2024-03-01 14:09:43

2

12

WDL-005943998-1

2024-03-12 15:48:36

2

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 has grouped subsets and a positional reference mechanism, with simple code: https://try.esproc.com/splx?3Io


 A

1

$select * from table1.txt order by ProductionLine_Number, date_Time

2

=A1.group(ProductionLine_Number)

3

=A2.conj(~.select(Cardboard_Number[-1:1].contain("WDL-005943998-1")))

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.

Question sourcehttps://stackoverflow.com/questions/78224006/sql-how-to-get-elements-after-and-before-the-searched-one