In this lesson, you will learn how to maintain an accurate running total when your data points occur at uneven, unpredictable time intervals.
The Challenge of Irregular Intervals
Up until now, you have likely worked with time-series data that arrives like a heartbeat—regular, predictable, and dense. However, in real-world scenarios, events like bank transactions, sensor triggers, or website clicks often happen at "irregular" intervals. One day you might have 50 transactions, and the next, only one.
When we calculate a running aggregate (like a cumulative balance or total sales), the time between these rows is inconsistent. If you simply use a standard SUM(...) OVER (...) window function, you get a running total of records, not a running total of time-weighted progress.
The Approach: Windowing by Time
To calculate a running aggregate correctly across irregular gaps, we rely on the ORDER BY clause within our window function to ensure the sequence is chronological, regardless of the physical storage order in the database.
Consider a table of payments where the payment_date has gaps:
| payment_date | amount |
|---|---|
| 2023-01-01 | 100 |
| 2023-01-05 | 200 |
| 2023-01-15 | 50 |
To calculate the running total, we use the ROWS BETWEEN or RANGE frame. While ROWS looks at a fixed number of lines, RANGE is particularly powerful for irregular intervals because it allows us to look at a specific logical time window:
SELECT
payment_date,
amount,
SUM(amount) OVER (
ORDER BY payment_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as running_total
FROM payments;
By using ORDER BY payment_date, the database forces the calculation to respect the timeline, even if the "gaps" between January 5th and January 15th are mathematically ignored by the accumulation.
Why this matters
When data points are sparse, a simple windowed sum provides a snapshot of "progress to date." If you need to account for the "idle" time between these irregular points (for instance, to calculate how much interest accrued during a gap), you would combine this with the LAG() function to identify the duration of the gaps themselves.
Key Takeaway: When dealing with irregular intervals, use the ORDER BY clause in your window function to ensure your running aggregates reflect the logical chronological sequence of events, regardless of the time gaps between records.