You heard right. Up until now, the awesome window functions were a feature uniquely reserved to SQL. Even sophisticated functional programming languages still seem to lack this beautiful functionality (correct me if I'm wrong, Haskell folks). We've written tons of blog posts about window functions, evangelising them to our audience, in articles like: Probably the … Continue reading 2016 Will be the Year Remembered as When Java Finally Had Window Functions!
Tag: Window Functions
How to Fill Sparse Data With the Previous Non-Empty Value in SQL
The following is a very common problem in all data related technologies and we're going to look into two very lean, SQL-based solutions for it: How do I fill the cells of a sparse data set with the "previous non-empty value"? The problem The problem is really simple and I'm reusing the example provided by … Continue reading How to Fill Sparse Data With the Previous Non-Empty Value in SQL
How to Find the Longest Consecutive Series of Events in SQL
A very interesting problem that can be solved very easily with SQL is to find consecutive series of events in a time series. But what is a consecutive series of events in a time series? Take Stack Overflow, for example. Stack Overflow has a cool reputation system that uses badges to reward certain behaviour. As … Continue reading How to Find the Longest Consecutive Series of Events in SQL
How to Find the Closest Subset Sum with SQL
I've stumbled upon this very interesting question on Stack Overflow, recently. Its title is: [How to] compare a number with sum of subset of numbers In this article, we'll compare the user's imperative approach to the extremely elegant (Oracle) SQL approach. We'll be making use of any combination of these awesome SQL features: Window functions … Continue reading How to Find the Closest Subset Sum with SQL
Use this Neat Window Function Trick to Calculate Time Differences in a Time Series
Whenever you feel that itch... Can't I calculate this with SQL? The answer is: Yes you can! And you should! Let's see how... Calculating time differences between rows Let's consider the following database containing timestamps (e.g. in a log database). We're using PostgreSQL syntax for this: CREATE TABLE timestamps ( ts timestamp ); INSERT INTO … Continue reading Use this Neat Window Function Trick to Calculate Time Differences in a Time Series
10 SQL Articles Everyone Must Read
We've been blogging about Java and SQL for a while now, on the jOOQ blog. Over the years, while researching interesting blog topics, we've discovered a lot of SQL gems in the blogosphere that have inspired our work and our passion for SQL. Today, we're presenting to you a list of 10 articles that we … Continue reading 10 SQL Articles Everyone Must Read
Still Using Windows 3.1? So why stick to SQL-92?
We've been blogging a lot about the merits of modern SQL on the jOOQ blog. Specifically, window functions are one of the most fascinating features. But there are many many others. Markus Winand, author of the popular book SQL Performance Explained has recently given a very well-researched talk about modern SQL. We particularly like his … Continue reading Still Using Windows 3.1? So why stick to SQL-92?
Do You Really Understand SQL’s GROUP BY and HAVING clauses?
There are some things in SQL that we simply take for granted without thinking about them properly. One of these things are the GROUP BY and the less popular HAVING clauses. Let's look at a simple example. For this example, we'll reiterate the example database we've seen in this previous article about the awesome LEAD(), … Continue reading Do You Really Understand SQL’s GROUP BY and HAVING clauses?
Don’t Miss out on Awesome SQL Power with FIRST_VALUE(), LAST_VALUE(), LEAD(), and LAG()
If you're using a commercial database or PostgreSQL / Firebird / CUBRID, you will be able to take advantage of the full power of window functions. We've blogged about window functions' awesomeness a couple of times, in particular about ROW_NUMBER(), RANK(), DENSE_RANK(). Today, we're going to look into some awesome window functions that produce values … Continue reading Don’t Miss out on Awesome SQL Power with FIRST_VALUE(), LAST_VALUE(), LEAD(), and LAG()
The Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK()
One of the best features in SQL are window functions. Dimitri Fontaine put it bluntly: There was SQL before window functions and SQL after window functions If you're lucky enough to be using any of these databases, then you can use window functions yourself: CUBRIDDB2FirebirdH2InformixMariaDBMySQLOraclePostgreSQLSQLiteSQL ServerSybase SQL AnywhereTeradata (source here) One of the most obvious … Continue reading The Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK()