Are you stuck in the 90s?

If you are only using the SQL-92 language reference, then you are overlooking so many great features like:

• Window Functions
• PIVOT
• MERGE
• INSTEAD OF triggers

Some test data

In my previous article I imported some CSV Dropwizard metrics into PostgreSQL for further analysis.

Our time series data consists of a total request count and a data recording time stamp:

Arrival velocity

I want to calculate the arrival velocity which can be defined as:

λ = arrival_velocity = Δcount / Δt

For each time event, we need to subtract the current and previous count and timestamp values.

Window functions allow us to aggregate/reference previous/next rows without restricting the SELECT clause to a single result row:

SELECT
    t as "Current time stamp",
    prev_t as "Previous time stamp",
    current_count as "Current total request count",
    prev_count as "Previous total request count",
    ROUND(
        ((current_count - prev_count)::numeric/
        (t - prev_t)::numeric), 3
    ) as "Velocity [req/sec]"   
FROM (
    SELECT
        t,
        lag(t, 1) over (ORDER BY t) as prev_t,                
        count as current_count,
        lag(count, 1) over (ORDER BY t) as prev_count
    FROM
        connection_lease_millis
) raw_data

Giving us the arrival velocity:

Arrival acceleration

But what if we want to calculate the arrival acceleration (e.g. so we can figure out how the arrival rate fluctuates), which is

arrival_acceleration = Δarrival_velocity/ Δt

This is how we can do it:

SELECT
    t as "Current time stamp",
    prev_t as "Previous time stamp",
    velocity "Velocity [Req/sec]",
    ROUND(
        (velocity - lag(velocity, 1) over (ORDER BY t))::numeric / 
        (t - prev_t)::numeric, 3
    ) as "Acceleration [req/sec2]"     
FROM (
    SELECT
        t,
        prev_t,
        current_count,
        prev_count,
        ROUND(
            ((current_count - prev_count)::numeric/
            (t - prev_t)::numeric), 3
        ) as velocity 
    FROM (
        SELECT
            t,
            lag(t, 1) over (ORDER BY t) as prev_t,                
            count as current_count,
            lag(count, 1) over (ORDER BY t) as prev_count
        FROM
            connection_lease_millis
    ) raw_data  
) velocity_data 	

Giving us:

Giving us a nice overview over the arrival rate distribution:

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

SQL has more to offer than the standard aggregate functions. The window functions allow you to group rows while still retaining the selection criteria.

How many of you are still using the 1.0 versions of Java, C# or Python? Shouldn’t we benefit from the latest SQL features the same way we do with any other programming language we use on a daily basis?

In case you’re still skeptic, this great article may shatter your doubts.

Insert details about how the information is going to be processed

DOWNLOAD NOW