Calculating Rates and Deltas

Derive velocity and change metrics from raw cumulative counter values.

In this lesson, you will learn how to transform raw, monotonically increasing cumulative counters into meaningful velocity and change metrics using SQL window functions.

The Problem with Cumulative Counters

In time-series databases, metrics are often stored as "cumulative counters"—values that only ever increase (like the total bytes sent by a network interface or total API requests). While great for preventing data loss, these raw numbers are rarely useful for business intelligence. To understand system performance, we don't care that the total is 1,000,000; we care that the system is currently processing 50 requests per second.

Calculating the Delta

To find out how much a metric changed between two points in time, we calculate the delta. This is the difference between the current value and the value from the previous row.

Using the LAG() window function, we can look back one record and subtract that value from the current one:

SELECT 
    timestamp,
    counter_value - LAG(counter_value) OVER (ORDER BY timestamp) AS delta
FROM server_metrics;

Deriving Velocity (Rates)

A "rate" is simply a delta normalized by time. If your data is sampled every minute, your delta is effectively "events per minute." To calculate the rate per second, we divide the delta by the difference in time between rows.

SELECT 
    timestamp,
    (counter_value - LAG(counter_value) OVER (ORDER BY timestamp)) / 
    EXTRACT(EPOCH FROM (timestamp - LAG(timestamp) OVER (ORDER BY timestamp))) AS rate_per_second
FROM server_metrics;

Why This Matters

By calculating these metrics at query time, you avoid storing redundant calculated columns that can become stale. However, keep in mind that if your system restarts, the cumulative counter might reset to zero. When performing these calculations, always include a logic check (like CASE WHEN current < previous THEN current ELSE current - previous END) to handle counter rollovers or resets, ensuring your velocity metrics don't produce massive, erroneous negative spikes.

Key Takeaway: Use the LAG() window function to calculate the difference between sequential rows, and normalize by the time interval to convert cumulative counters into actionable velocity metrics.