Window Functions in PostgreSQL | Hacker News Reader

Window Functions in PostgreSQL

gab.lc

79 pointsgabriel_b24 comments

Window functions are often a good answer to the "why SQL?" question.

Here's a real-world case where I used them lately. Suppose a user has an inbox of messages from other users. I'd like to show the user her latest conversations (aka "message threads"). Ten conversations per page, and I'd like to show the three latest messages per conversation. That's fairly easy with window functions like dense_rank() and row_number() - and it's flippin' fast... just a few milliseconds on a table with thousands of rows and I could probably get it lower than that with some index optimization.

  // "conversations" is a CTE (common table expression)
  // "messages" is the actual table
  // we don't need a CTE here but it's probably the most readable way
  // query is a little hairy because of all the greatest() and least()'s
  WITH conversations 
    as (
    select
      row_number() over (partition by greatest(from_id,to_id),
        least(from_id,to_id) order by created_at desc) 
        as conversation_message_number,
      max(id) over (partition by greatest(from_id,to_id),
      least(from_id,to_id)) as conversation_latest_message_id,
    mes.*
  from messages mes where 1234 in (mes.to_id, from_id) // 1234 is the member's id
  )

  // get conversations 1 through 10, and get the 3 latest messages
  select 
    dense_rank() over (order by conversation_latest_message_id) 
      as conversation_number, *
  from conversations
  where (conversation_number between 1 and 10) 
    and (conversation_message_number <= 3)

The alternatives are not a lot of fun. You could fairly easily implement this logic in Ruby/Java/whatever but you'd have to pull a ton of rows out of the database and sort them in your app tier, which is kind of ugly performance-wise even if you're not paying the cost of converting each row into a model object w/ ActiveRecord/Hibernate/etc. I'm not familiar with NoSQL implementations but I'm guessing you'd have to pull all those records into your app tier and do it there as well?

> You could fairly easily implement this logic in Ruby/Java/whatever but you'd have to pull a ton of rows out of the database and sort them in your app tier, which is kind of ugly performance-wise even if you're not paying the cost of converting each row into a model object w/ ActiveRecord/Hibernate/etc.

This, a thousand times this. I have done a lot of performance work with a variety of web applications, and this is a common theme - pulling more data into the app tier than you need to.

Putting even a small amount of effort into writing good queries will almost always have a massive performance impact on your application. If performance is at all a concern, this is one of the first places you should start looking - you should be trying to bring over the smallest amount of data from the database as possible.

I've found, though, that a lot of developers have conceptual problems dealing with set-based operations. Not only does it look cleaner (depending on the framework and design) to front-load the application, I think that many developers simply find it much more intuitive to bring the whole dataset in and then filter with some loops and conditionals and then use the 25% of fields they pulled in that actually need to be displayed. If you forced them to push more work onto the database, they'd simply write imperative-looking SQL.

I'm not real sure how to get people to think more in set terms. Once you get there though, well-written SQL is beautiful. Query optimizations for the most part become obvious - though there will always be further optimizations database experts can make because they have a better understanding of the database engine - just try to make sure you're always operating on the smallest possible sets of data to get the desired results. It's often not hard at all to write highly performant queries even if they have huge numbers of joins in large tables. And window functions are a big help when it comes to doing this!

Sorry for the rant - I just see this so much. :)

  I've found, though, that a lot of developers have 
  conceptual problems dealing with set-based operations.

Yes! I can't help but think that a lot of people are drawn to NoSQL (or find themselves opposed to SQL for other reasons) simply because they don't understand SQL. As somebody who avoided databases early in his career I definitely understand this.

  well-written SQL is beautiful

Count me as a member of the club that believes this. Not every problem can or should be handled with a relational data store, but for those that do, I don't think the resultant set-based operations could be expressed much more cleanly or succinctly than SQL allows.

These NoSQL document stores seem to be a realization of that object database dream of years past. It's not exactly one-to-one (objects to documents) but it gets you there quicker than a relational database could.

I enjoy writing well crafted SQL queries as a currently employed database architect. But I don't find it to be all that elegant. I do think it bridges the gap between lay person and expert by being somewhat readable declarative language. For those programmers out there it offers some procedural aspects, differing from vendor to vendor.

Features like window functions and CTEs have helped to limit the temp tables, derived tables, subqueries, etc required to achieve the desired output. One can now make a much more terse query.

> well-written SQL is beautiful

I say often that software engineers/computer scientists produced good solutions for three and only three problems:

- Networking

- Compilers

- Databases

And that's it. On the one hand, it's pretty poor for ~70 years of serious effort. On the other hand, the solutions for each of these are beautiful -- mathematically and engineering-wise.

Then again, I think many, many problems actually break down into these categories if you look at them long enough, which is why we've had so much effort put into these.

The thing that makes me nervous about having that sort of business logic in the database is two-fold: first, finding people that can write good, clean SQL and document it so others can use it can be tricky; second, changing that logic means, effectively, more migrations.

The model of a SQL DB as dumb storage, while hardly making good use of all the (awesome!) functionality of query languages, seems to make it easier in production and in hiring.

That said, it is downright comical just how much mileage you can get from using functions or optimized queries instead of hoisting all that data up into the application layer and doing the work there.

Of course, you could always use ActiveRecord's facility to drop down to SQL, and continue to use ActiveRecord where it is actually more convenient.

Absolutely! I have the above SQL code in a Postgres stored function, and call it via ActiveRecord's find_by_sql() and use it in one of my Message model's methods to populate a bunch of Message instances.

I find that window functions are one of the least used aspects of modern SQL, despite them being incredibly useful. I've used them in T-SQL [1] and PL/SQL [2], as well (MySQL does not do window functions). If you're doing lots of SQL work, get very familiar with the OVER clause. It's a lifesaver.

[1]: http://msdn.microsoft.com/en-us/library/ms189461.aspx

[2]: http://www.java2s.com/Tutorial/Oracle/0320__Analytical-Funct...

Whenever I see functions like these and PostgreSQL's JSON functions, I get the feeling that MySQL/MariaDB is not progressing, or not nearly as fast.

For those who have worked with both, do you find the PostgreSQL-only functions and features to be extremely useful or does it not really matter much?

I moved away from MySQL a long time ago (to finally get sub selects which weren't supported by MySQL back then), so my opinion might not really count, but usually there's no day passing in which I'm not writing some query using features in Postgres that are not in MySQL.

Could I avoid these features? Probably. At the cost of query readability and sometimes possibly performance and/or server roundtrips.

Furthermore, for years now, PostgreSQL is adding features in places that are very interesting to my use case, so I'm usually jumping ahead to newer versions very quickly.

Case in point is the new jsonb support which I'll be using to displace an old, bulky and join-heavy (performance!) system for handling custom attributes of products and of course, once you've gotten a taste of the awesome, you will never want to go back.

From an operations standpoint, I wouldn't say Postgres is any harder to deal with than MySQL either, especially for smaller setups. Bigger ones are probably similar too, though I personally only have experience with bigger Postgres setups.

There's nothing stopping you from trying it out: At this point, both Postgres and MySQL have proven their reliability even in big setups, so when you start your next thing, you won't ever have to be afraid that your pick of database will cause you trouble later down the road.

As such, I would really stop asking questions and start using Postgres.

It really depends on your needs. I still am kind of in shock that Postgres is willing to add these baroque sorts of features, but still doesn't have an UPSERT.

It's bound to appear on the next release. If I recall correctly, it almost made the last one, except for a last minute bug.

Anyhow, if you really need it, you can easily write upserts with triggers.

UPSERT is not a simple feature to implement correctly:

http://www.pgcon.org/2014/schedule/events/661.en.html

They're incredibly useful. My company uses MySQL, and RedShift. It's hilarious and sad that many of my fellow engineers regularly extol the virtues of RedShift simply because it has CTEs, Window Functions, and other basic modern database amenities.

They don't understand how bad MySQL is, and how much is weakens our product.

There is not a day that goes by that I don't write at least one query with a windowing function. Features like that are extremely useful. For me, it's usually ROW_NUMBER(), but LAG() and LEAD() have been enormously helpful too.

IME, it's easy to express the direct utility of features like this - you get row numbers, okay, so what? - and hard to express the huge indirect utility - using them as building blocks in complex (but efficient) queries.

I get the idea that not too many people know how to use window functions, even though they're a slick solution to some potentially messy problems. I've used them several times to deal with duplicate data issues.

Got a table where you want to put a unique index on a column that has some duplicate values? It's a pretty easy fix to use a window query along with group by and having to get a list of rows with duplicate entries for the column and use row_number to assign a unique number to each duplicate. Then, use string concat to tack that number onto the end of the string. Select the new string and the item ID, put it in a CTE, and connect it to an update query to actually change all the names in one query.

Goes kinda like this: with DrawingDupes as (select d.DrawingId, d.RevTag, '_DUPLICATE_' + cast((ROW_NUMBER() over (partition by d.Number, d.RevTag, d.UnitId order by d.DrawingId)) as varchar) NewX from (select Number, RevTag, UnitId from Drawing group by Number, RevTag, UnitId having COUNT(*) > 1) t join Drawing d on d.Number = t.Number and d.RevTag = t.RevTag and d.UnitId = t.UnitId) update DrawingDupes set RevTag = RevTag + NewX;

Nice writeup, though I would have liked some examples using "partition by". The main gotcha I recall -- and it's somewhat confusing the first time you run across it -- is that things like last_row() don't work as expected without the magic incantation:

over (partition by foo order by blah BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

This forces the query to evaluate the function over the entire partition, rather then just over the current window. The article does mention this point, but it bears re-iteration.

I'm a huge fan of window functions, but one restriction surprises me: in Postgres, you can't put a window function into a WHERE clause. Does anyone know if there is a good reason for this, or is it just a bit of missing functionality?

I'd love to know this too. I provided an example elsewhere in this discussion of a potentially-complex query I implemented with window functions.

I wanted to do something like returning every row with a row_number between 1 and 3 - but discovered I couldn't put this into the WHERE clause.

The solution I came up with was to define a CTE where one of the columns is the result of the row_number() function, and then I did a SELECT on the CTE that included the WHERE functionality I needed.

cant u use any function as window function (not only 11 ) ? i do alot stuff like sum(1) over(PARTITION BY index1,index2 ORDER by ...) for creating statistics. I wish i had that infos some time before :-)

Yes, things like sum() can work on partitions. :)

"Window functions" refer to something a little different... specifically to functions like row_number() and rank() that refer to the position/ordering of rows inside a partition.

Thanks for sharing!

Reply on news.ycombinator.com