Problem description
A table stores status values of an object at different timestamps, with one record per timestamp. Segment rows based on changes in the status value and output the effective start timestamp and the effective end timestamp for each status segment – that is, merge consecutive records with the same status into a single time interval.
Source data:
Expected output (intervals where each value remains constant):
Take object 1 as an example:
The first three records contain the same value A, so they can be merged into a single interval: 2024-01-01 – 2024-01-04 (the timestamp when the next record begins).
Value B remains for record 4 and record 5 and they can be merged into a single interval: 2024-01-04 – 2024-01-06 (the timestamp when the next record begins).
Record 6 and record 7 have value A again, the same as before. However, since value B intervenes between them, a new group starts: 2024-01-06 – infinity (the end date of the last interval is 9999-12-31).
Step-by-step implementation with SQLazy
Key approach: sort rows by timestamp, and check whether the value in the current row has changed relative to the previous row. If it has changed, start a new group; otherwise, put it in the current group. Finally, take the minimum timestamp in each group as the start timestamp and the minimum timestamp in the next group as its end timestamp.
[Run this example online]
Let me walk through each step below:
Step 1: Sort rows in chronological order in ascending order
sort timestamp
Sort rows by timestamp in ascending order, ensuring intervals in each group are processed in chronological order.
Step 2: Start a new partition when value changes
segment value; change; as gid
This is the core step. The “segment” statement segment rows by the “value” field: each time the value changes, a new group is started and assigned a group number (gid). Within each group, the value remains unchanged.
Step 3: Aggregate by group number and value, and take the start timestamp
summarize timestamp first as effective_from, id first as id; group gid, value
Group rows by gid and value:
Take the timestamp of the first record in each group and use it as the effective start timestamp for that status.
id: Take the first value in each group.
Step 4: Calculate the effective end timestamp
compute nvl(effective_from[1], datetime("9999-12-31 00:00:00")) as effective_to
effective_from[1] takes the effective_from value of the next row after the current group as the end timestamp for the current group. If the current group is the last group (i.e., effective_from[1] is empty), use nvl to set it to a maximum date (9999-12-31), which represents “to present”.
Finally, remove the helper column gid and output the final result.
Compile the steps into SQL
Once the above steps are complete and verified, SQLazy’s compiler can automatically generate the equivalent native SQL (The Oracle syntax for this example):
WITH Value AS (
SELECT
id,
value,
timestamp
FROM
events
),
Value2 AS (
SELECT
gid,
id AS id,
value AS value,
timestamp AS effective_from
FROM
(
SELECT
id,
value,
timestamp,
SUM(
CASE
WHEN value <> col__5 THEN 1
ELSE 0
END
) OVER (
ORDER BY
CASE
WHEN timestamp IS NULL THEN 1
ELSE 0
END,
timestamp ASC
) + 1 AS gid
FROM
(
SELECT
Value.*,
LAG(value) OVER (
ORDER BY
CASE
WHEN timestamp IS NULL THEN 1
ELSE 0
END,
timestamp ASC
) AS col__5
FROM
Value
) sub__6
) Value1
GROUP BY
gid
)
SELECT
gid,
id,
value,
effective_from,
LEAD(
effective_from,
1,
TO_DATE('9999-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
) OVER (
ORDER BY
gid
) AS effective_to
FROM
Value2
ORDER BY
gid;
You only need to verify the logic of each of the above four steps – no need to manually debug the SQL – and the compiler generates the production-ready code.
SQLazy lets you describe logic using business language, rather than writing nested queries in SQL syntax. This example of “dividing time intervals based on value change” has one core statement: segment value; change, which directly corresponds to the business requirement of “starting a new group whenever the value changes”. To write SQL manually, you need to understand the window boundaries of LAG/LEAD, manually handle NULLs, and adjust for syntax differences across different databases. SQLazy, however, compresses this complex logic into 4 intuitive steps – sort, segment, aggregate, and take the the next row’s timestamp as the end timestamp. The intermediate result of each step can be verified independently, and the compiler generates the final runnable SQL. You just need to verify the business meaning of each step, and leave the rest to the compiler.
Try SQLazy online: sqlazy.com (Free to use, signup not required)
SQLazy project repository: github.com/SPLWare/SQLazy
SOCIAL SHARE CARD GENERATOR