DEV Community

Cover image for Search for the Closest Matching Record within the Group — From SQL to SPL #9
Judith-Data-Processing-Hacks
Judith-Data-Processing-Hacks

Posted on • Edited on

2 1 1 1 1

Search for the Closest Matching Record within the Group — From SQL to SPL #9

Problem description & analysis:

The table mytable in the MS SQL database has one ConfirmationStarted and multiple Closed statuses for each ID.

source table

Task: Now we need to find the record closest to ConfirmationStarted among all the Closed records before ConfirmationStarted in each ID, and retrieve the ID and time fields of the record.

expected table

Code comparisons:

SQL solution

WITH cte AS (
    SELECT ID, CreatedAt, NewStatus,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CreatedAt DESC) AS rn
    FROM mytable
    WHERE NewStatus = 'Closed'
    AND CreatedAt < (
        SELECT CreatedAt FROM mytable AS sub
        WHERE sub.ID = mytable.ID AND sub.NewStatus = 'ConfirmationStarted'
    )
)
SELECT ID, CreatedAt as xdate
FROM cte
WHERE rn = 1
ORDER BY ID;
With cte AS (
    SELECT ID, CreatedAt, NewStatus,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CreatedAt DESC) AS rn
    FROM mytable
    WHERE NewStatus = 'Closed'
    AND CreatedAt < (
        SELECT CreatedAt FROM mytable AS sub
        WHERE sub.ID = mytable.ID AND sub.NewStatus = 'ConfirmationStarted'
    )
)
SELECT ID, CreatedAt as xdate
FROM cte
WHERE rn = 1
ORDER BY ID;
Enter fullscreen mode Exit fullscreen mode

SQL does not have natural sequence numbers, so it needs to generate sequence numbers using window functions first. After SQL grouping, it must aggregate immediately and records within the group cannot be filtered. It can only be solved in a roundabout way by filtering repeatedly using multi-level subqueries. The overall code is a bit cumbersome and difficult to understand.

SPL solution:

SPL has natural sequence numbers and provides rich position related calculations. SPL grouping can retain subsets after grouping, making it easier to process data within the group. 👉🏻 try.DEMO

spl code

A1: Load data and sort it by time.

A2: Group by ID, but do not aggregate.

A3: Filter each group of data, first find the records before ConfirmationStarted, and then filter out Closed from them, getting the last one. The select function is used for conditional filtering, which supports position related calculations during filtering, @c represents starting from the first record that makes the condition true and stopping when encountering a record that makes the condition false. @1 represents getting the first piece of the results, and @z represents filtering from back to front.

A2-A4 can be combined into one statement: =A1.group(ID;~.select@c(NewStatus!=”ConfirmationStarted”).select@z1(NewStatus==”Closed”).CreatedAt:xdate)


esProc SPL is now Free to Download, and please feel free to give it a try on your own: esProc SPL FREE Download

Redis image

Short-term memory for faster
AI agents 🤖💨

AI agents struggle with latency and context switching. Redis fixes it with a fast, in-memory layer for short-term context—plus native support for vectors and semi-structured data to keep real-time workflows on track.

Start building

Top comments (1)

Collapse
 
judith677 profile image
Judith-Data-Processing-Hacks

Feel free to download esProc SPL and share your thoughts with us!

🌼Discord
🌼Reddit

Tiger Data image

🐯 🚀 Timescale is now TigerData: Building the Modern PostgreSQL for the Analytical and Agentic Era

We’ve quietly evolved from a time-series database into the modern PostgreSQL for today’s and tomorrow’s computing, built for performance, scale, and the agentic future.

So we’re changing our name: from Timescale to TigerData. Not to change who we are, but to reflect who we’ve become. TigerData is bold, fast, and built to power the next era of software.

Read more

👋 Kindness is contagious

Embark on this engaging article, highly regarded by the DEV Community. Whether you're a newcomer or a seasoned pro, your contributions help us grow together.

A heartfelt "thank you" can make someone’s day—drop your kudos below!

On DEV, sharing insights ignites innovation and strengthens our bonds. If this post resonated with you, a quick note of appreciation goes a long way.

Get Started