A certain table in the MS SQL database has three fields: account, date and time of string type.

Now we need to add a sequence number column Seq for the group. When a new event occurs for an account within one hour, Seq+1; If a new event occurs after one hour, reset Seq to 1.

When referencing set members in relative positions, SQL needs to write tedious window functions and often involves multiple layers of nesting; The logical judgment of sequence number calculation is also quite complex, involving multiple layers of case when forms, and the code is very cumbersome. The sequence numbers need to be accumulated within a specified interval, which requires grouping these intervals or embedding window functions in case when, which can be very convoluted.

It is also possible to reference set members at relative positions in a loop and make logical judgments, but SQL does not support loop structures and requires the use of stored procedures, making the structure more complex.

SPL supports a complete procedural syntax, which can handle complex business logic in loops and conveniently reference set members using relative positions.

A1: Use JDBC to query the database, spell out the calculated column DT of date and time type, and sort it by account and DT.

A2: Create a new two-dimensional table and add a new calculated column Seq. When the account number of the current record is the same as the previous record and the time interval is within one hour, Seq+1; Otherwise, reset Seq to 1. [-1] represents the previous record of relative position.

Question source：https://stackoverflow.com/questions/78380050/creating-sequence-numbers-with-hourly-reset