Time to break free from the SQL-92 mindset

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
• MERGE
• TRUNCATE
• INSTEAD OF triggers

Some test data

In my previous article I imported some CSV Codahale 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 time stamp 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 () as prev_t,				
		count as current_count,
		lag(count, 1) over () as prev_count
	FROM
		connection_lease_millis
	WINDOW grouping AS (			
		ORDER BY t
		ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
	)
) 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 flactuates), 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 ())::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 () as prev_t,				
			count as current_count,
			lag(count, 1) over () as prev_count
		FROM
			connection_lease_millis
		WINDOW grouping AS (			
			ORDER BY t
			ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
		)
	) raw_data	
) velocity_data

Giving us:

Giving us a nice overview over the arrival rate distribution:

Conclusion

SQL has more to offer than the standard aggregate functions. The window functions allow you to group rows while still retaining the select 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.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.