Hacker News Reader

Comment by cameronh90 | Hacker News Reader

Out of curiosity: what is the use case for SQL window functions in application programming? Unlike most SQL, it doesn’t seem to reduce the order of the data coming back from the server, nor do anything especially faster than can be done on the client - and has the disadvantage of extra load on the database (which is harder to scale).

Is it only useful for ad hoc/analytical queries, or am I missing something?

View on HN

It allows you to "group by/aggregate/etc" on a row by row basis rather than once over the entire table. So whle GROUP BY defines a set to aggregate over the entire table ( and hence you get 1 row back ), window functions creates a criteria by which each row gets a set ( aka window ) to aggregate over for that particular row. In addition, you can define a "frame" over that "window" for even more refined aggregation. I suppose window functions are used more in the OLAP space than OLTP, but it is a very useful part of SQL. In my opinion, windows function is the most impressive and important part of the language.

People can define, describe and provide examples but with SQL, it won't sink in until you try it out yourself and have the "Ah ha" moment. If you have a database server and some test data, try writing a few window function queries or try some online examples.

Why do you think it's not "especially faster" than doing it on the client?

The data is already sorted, partitioned, in memory .. adding a moving average calc or max value per category is certainly faster than fetching the set to disk, recreating it in an intermediate structure and then calculating the new value with all that partitioning, sorting, etc. to be redone client side.

You can certainly use them in app dev for things like:

* figuring out how many things are ahead of you in a queue * snapshotting (what's changed since the last time you were here) * comparing something to some other sample for outliers or inconsistencies

These may be quasi analytical still, but ultimately they can manifest as properties of an object model like any other property to be developed against.

I suppose it depends on the language. Normally I’m writing in either Java, C#, Rust or C - and in those I’ve never found that having the DB run a moving average to be any faster, provided the result comes back in the right order. Indeed, if I need to use another column in the result set, the deserialisation overhead means it’s normally slower. For reference I normally write financial time series processing code which is where window functions ostensibly could be quite useful.

However certainly if your app is written in Python or Ruby I can see there being a big difference.

The reason I mention analytics is more to do with the ease of scaling out the DB than the nature of the queries. In busy OLTP databases usually one wants to keep the work off the database because they’re difficult and expensive to scale.

I use them for cursor based pagination. You can use rank and a where clause to apply the limit. You can also compute max rank to determine whether there are more data to fetch after limit. Finally, if you do left joins where you may have multiple rows per objet, you use dense rank instead of rank

Reply on news.ycombinator.com