7 ms·

A Little Known SQL Feature: Use Logical Windowing to Aggregate Sliding Ranges

90 points by mariuz 10y ago

est 10y ago
tl;dr Oracle.
- deleted 10y ago
  [deleted]
- samch 10y ago
  The article highlights the Oracle flavor of this functionality, but similar windowing operations are available in PostgreSQL[1] and SQL Server[2]. Edit to add that I have no idea if JOOQ supports windowing functions in non-Oracle systems. 1: https://www.postgresql.org/docs/current/static/functions-window.html https://www.postgresql.org/docs/current/static/functions-win... 2: https://msdn.microsoft.com/en-us/library/ms189461.aspx https://msdn.microsoft.com/en-us/library/ms189461.aspx
  - wmfiv 10y ago
    If you're a SQL fan and using the JVM, JOOQ is pretty much the best thing since sliced bread. It has excellent cross database window support. http://www.jooq.org/doc/3.8/manual/sql-building/sql-statements/select-statement/window-clause/ http://www.jooq.org/doc/3.8/manual/sql-building/sql-statemen...
  - lukaseder 10y ago
    Logical windowing is not available in those other databases, i.e. only Oracle can specify a window whose size depends on the content of the current row, rather than a fixed number of rows.
    - samch 10y ago
      I'm not sure that's entirely correct. This is from the SQL Server MSDN docs, "Alternatively, the RANGE clause logically limits the rows within a partition by specifying a range of values with respect to the value in the current row." Source: https://msdn.microsoft.com/en-us/library/ms189461.aspx https://msdn.microsoft.com/en-us/library/ms189461.aspx
- btilly 10y ago
  No, it is in the SQL standard. Best supported in Oracle because that is where it came from.
- dx034 10y ago
  SQL Server can do something similar, although they introduced it later (think SQL Server 2012).
eranation 10y ago
This (in perhaps a slightly different syntax flavor) works on Hive/Presto/SparkSQL as well by the way. Also in Apache drill. https://drill.apache.org/docs/sql-window-functions-introduction/ https://drill.apache.org/docs/sql-window-functions-introduct...
- electrum 10y ago
  Presto implements the SQL standard, so the syntax is identical: https://prestodb.io/docs/current/functions/window.html https://prestodb.io/docs/current/functions/window.html
epalmer 10y ago
I don't use SQL that much in my day to day work but I have a need that Windowing will satisfy. Thanks to @mariuz for submitting this. I use Oracle mostly so I don't even have to worry about syntax differences.
teilo 10y ago
I have made a lot of window function converts. It's amazing how many devs don't know about them. When I see a whole series of CASE statements + subqueries, it is almost always possible to dramatically simplify the query with one or more window functions.
datahead 10y ago
This technique applies to many RDBMS, not just Oracle (as others have noted). Teradata, PostgreSQL, MS SQL all have 'analytical' functions like this. Analytical functions (over, partition by, etc) are extremely powerful and can help simplify architecture/design for the data science/analytical communities. One of the persistent issues on my team is the reliance upon a dataframe representation w/ R or python to do this type of aggregation and windowing. Most people will eschew learning the 'advanced' SQL and instead bring data locally to do imperative style munging on it. This creates a few issues, mainly adding complexity to the analytical stack: - Instead of querying the data and doing ETL/feature engineering in the db- you are moving data around (usually to less powerful machines, such as a laptop) for simple exploration. - This wastes time and usually results in more dependencies (dplyr for example- no hate Hadley), sometimes even limiting you to single threaded operations. Teradata, for example, is massively parallel and will perform these operations in short order. I've seen Data Scientists wait 6hr for R to do the same thing a SQL query against a prod system returns in 3min. - Code is not portable. A query can be executed and results retrieved through ODBC, JDBC or native connections. Without these, data engineers are often asked to install R (including libs) on some intermediate machine just to do munging/ETL/feature engineering. If SQL driven, moving from quantitative exploration to operational is quite easy (maybe just a query tune). All that to say, I'm glad this post is highlighting some of the advanced SQL that I hope more people rely upon. All of these ideas are better articulated in MAD Skills [0] [0] http://db.cs.berkeley.edu/papers/vldb09-madskills.pdf http://db.cs.berkeley.edu/papers/vldb09-madskills.pdf
- saosebastiao 10y ago
  I'm right there with you about R and imperative transformation and munging. Get your data scientists using sqldf...it is by far the best solution out there for this problem for R users.
  - resolaibohp 10y ago
    I agree with using sqldf, it is a wonderful tool in R. I am sure you are aware already, but for those who are not, sqldf does not support window functions with it being based on sqlite. Just wanted to point this out as the original article emphasizes them.
RyanHamilton 10y ago
Windowing functions in standard SQL databases are like putting lipstick on a pig. You created a design based on sets then later tried to slap on the idea of order to sets. kdb is based on the concept of ordered lists, not sets and makes windowing functions on time and order based queries ridiculously easy. If this kind of thing interests you I recommend checking it out these comparison queries between kdb and standard SQL: http://www.timestored.com/b/kdb-qsql-query-vs-sql/ http://www.timestored.com/b/kdb-qsql-query-vs-sql/ Unfortunately kdb is very expensive.
- saosebastiao 10y ago
  There is far more utility to window functions than ordering.
- gshulegaard 10y ago
  > Windowing functions in standard SQL databases are like putting lipstick on a pig. I have always thought the underlying Relational Algebra of SQL databases to be quite beautiful (https://en.wikipedia.org/wiki/Relational_algebra https://en.wikipedia.org/wiki/Relational_algebra). This doesn't make it the right tool for every job, but let's try to shy away from calling it a, "pig". > You created a design based on sets then later tried to slap on the idea of order to sets. I have some questions here. It sounds like you are implying that sets and order are mutually exclusive, but this is not my understanding of the math. Could you elaborate? * https://en.wikipedia.org/wiki/Partially_ordered_set https://en.wikipedia.org/wiki/Partially_ordered_set * https://en.wikipedia.org/wiki/Total_order https://en.wikipedia.org/wiki/Total_order From the second reference on Total Order: > "The lexicographical order on the Cartesian product of a family of totally ordered sets, indexed by a well ordered set, is itself a total order." This quote seems to actually contradict this implication...especially in respect to Relational Algebra which relies heavily on Cartesian Product. Thanks in advance for your response! I enjoy improving my own understanding of the underlying Computer Science/Mathematics whenever I can.
  - RyanHamilton 10y ago
    >>It sounds like you are implying that sets and order are mutually exclusive, but this is not my understanding of the math. Could you elaborate? The concept of sets and order may not be mutually exclusive in the mathematical theory but in terms of how actual databases were conceived and implemented they were very far apart. Early on many databases actually took advantage of the fact the operations were set based and that order was not guaranteed to achieve a number of speed optimizations. How long did many databases take to get row number support? "partition by" support? Take a look at this SO post for doing a running sums calculation: http://stackoverflow.com/questions/14953294/how-to-get-running-sum-of-a-column-in-sql-server http://stackoverflow.com/questions/14953294/how-to-get-runni... This is the same kdb code: update runningSumB:sums b by a from t Instead many people ended up turning to cursors to allow ordered calculations.
contingencies 10y ago
How many payments were there in the same hour as any given payment? How many payments were there in the same hour before any given payment? How many payments were there within one hour before any given payment? I have two serious questions. (1) In which realistic scenario would you ever need to ask such questions in the first place? (2) Even if you did need to do so, for example for risk management, occasional reporting or some kind of adaptive scaling supposition, then surely you would be calculating once per time unit (eg. hour/day) or recomputing a sliding-total? As I have actually personally written a number of relatively high profile, large scale, publicly deployed, major brand digital entertainment content rental and purchase systems (including complexities resulting from things like multi-device, multi-DRM cryptography) for brands like LG, Nokia, Samsung, etc. - these are not flippant questions. Looking further... uh-oh... author uses Java. Sakila example database was originally developed by Mike Hillyer of the MySQL AB documentation team. This project is designed to help database administrators to decide which database to use for development of new products. The user can run the same SQL against different kind of databases and compare the performance. So the Sakila project referenced is not supposed to be a real project, it's a personal re-interpretation of a test project designed specifically by a database nerd with zero domain knowledge to show off database features... jOOQ is an innovative solution for a better integration of Java applications with popular databases ... and this is what the author is working on, the world's billionth RDBMS ORM layer. IMHO readability is far more important than efficiency. Programmers should generally avoid using these types of obscure, frequently partly platform-linked techniques and consider their drawbacks against other potential solutions. Five minutes of life wasted.
- infinite8s 10y ago
  Huh? Windowing functions are part of the SQL standard (as of SQL2003). Also, these types of queries are the bread and butter of analytical queries (not really as useful for transactional purposes).
  - contingencies 10y ago
    There is 'standard' and there is 'reality'. If it doesn't work everywhere (eg. on sqlite) then it's not 'standard' in that it's not familiar to all developers (minimum subset). The further you go from that subset, the higher price your developers pay to grok your code. Yes, that includes stuff as simple as foreign keys, indices, etc. Contrary to your bread and butter assertions, in my experience this stuff is end-of-the-branch, end-of-the-twig, end-of-the-leaf, only-present-at-winter-solstice level common in web and CRUD applications.
garyclarke27 10y ago
Itzik Ben-Gan wrote a whole book just on SQL windows functions and it's brilliant. He writes for SQL server usually, but he made this one more generic, he had to because at the time SQL server 2012 was missing much of the functionality he described, unlike Postgres or Oracle. Postgres has incredible capability here - Window AND Frame partitions/ordering, also row based ranges or value based ranges. And as a huge bonus unique to Postgres, the wondeful - Filter by clause - so much more elegant than sub queries.
- j_s 10y ago
  This one? Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Developer Reference) https://www.amazon.com/dp/B00JDMPHKY https://www.amazon.com/dp/B00JDMPHKY

90 points by mariuz 10y ago

est 10y ago

tl;dr Oracle.

deleted 10y ago
[deleted]
samch 10y ago
The article highlights the Oracle flavor of this functionality, but similar windowing operations are available in PostgreSQL[1] and SQL Server[2]. Edit to add that I have no idea if JOOQ supports windowing functions in non-Oracle systems. 1: https://www.postgresql.org/docs/current/static/functions-window.html https://www.postgresql.org/docs/current/static/functions-win... 2: https://msdn.microsoft.com/en-us/library/ms189461.aspx https://msdn.microsoft.com/en-us/library/ms189461.aspx
- wmfiv 10y ago
  If you're a SQL fan and using the JVM, JOOQ is pretty much the best thing since sliced bread. It has excellent cross database window support. http://www.jooq.org/doc/3.8/manual/sql-building/sql-statements/select-statement/window-clause/ http://www.jooq.org/doc/3.8/manual/sql-building/sql-statemen...
- lukaseder 10y ago
  Logical windowing is not available in those other databases, i.e. only Oracle can specify a window whose size depends on the content of the current row, rather than a fixed number of rows.
  - samch 10y ago
    I'm not sure that's entirely correct. This is from the SQL Server MSDN docs, "Alternatively, the RANGE clause logically limits the rows within a partition by specifying a range of values with respect to the value in the current row." Source: https://msdn.microsoft.com/en-us/library/ms189461.aspx https://msdn.microsoft.com/en-us/library/ms189461.aspx
btilly 10y ago
No, it is in the SQL standard. Best supported in Oracle because that is where it came from.
dx034 10y ago
SQL Server can do something similar, although they introduced it later (think SQL Server 2012).

eranation 10y ago

This (in perhaps a slightly different syntax flavor) works on Hive/Presto/SparkSQL as well by the way. Also in Apache drill. https://drill.apache.org/docs/sql-window-functions-introduction/ https://drill.apache.org/docs/sql-window-functions-introduct...

electrum 10y ago
Presto implements the SQL standard, so the syntax is identical: https://prestodb.io/docs/current/functions/window.html https://prestodb.io/docs/current/functions/window.html

epalmer 10y ago

I don't use SQL that much in my day to day work but I have a need that Windowing will satisfy. Thanks to @mariuz for submitting this. I use Oracle mostly so I don't even have to worry about syntax differences.

teilo 10y ago

I have made a lot of window function converts. It's amazing how many devs don't know about them. When I see a whole series of CASE statements + subqueries, it is almost always possible to dramatically simplify the query with one or more window functions.

datahead 10y ago

This technique applies to many RDBMS, not just Oracle (as others have noted). Teradata, PostgreSQL, MS SQL all have 'analytical' functions like this. Analytical functions (over, partition by, etc) are extremely powerful and can help simplify architecture/design for the data science/analytical communities. One of the persistent issues on my team is the reliance upon a dataframe representation w/ R or python to do this type of aggregation and windowing. Most people will eschew learning the 'advanced' SQL and instead bring data locally to do imperative style munging on it. This creates a few issues, mainly adding complexity to the analytical stack: - Instead of querying the data and doing ETL/feature engineering in the db- you are moving data around (usually to less powerful machines, such as a laptop) for simple exploration. - This wastes time and usually results in more dependencies (dplyr for example- no hate Hadley), sometimes even limiting you to single threaded operations. Teradata, for example, is massively parallel and will perform these operations in short order. I've seen Data Scientists wait 6hr for R to do the same thing a SQL query against a prod system returns in 3min. - Code is not portable. A query can be executed and results retrieved through ODBC, JDBC or native connections. Without these, data engineers are often asked to install R (including libs) on some intermediate machine just to do munging/ETL/feature engineering. If SQL driven, moving from quantitative exploration to operational is quite easy (maybe just a query tune). All that to say, I'm glad this post is highlighting some of the advanced SQL that I hope more people rely upon. All of these ideas are better articulated in MAD Skills [0] [0] http://db.cs.berkeley.edu/papers/vldb09-madskills.pdf http://db.cs.berkeley.edu/papers/vldb09-madskills.pdf

saosebastiao 10y ago
I'm right there with you about R and imperative transformation and munging. Get your data scientists using sqldf...it is by far the best solution out there for this problem for R users.
- resolaibohp 10y ago
  I agree with using sqldf, it is a wonderful tool in R. I am sure you are aware already, but for those who are not, sqldf does not support window functions with it being based on sqlite. Just wanted to point this out as the original article emphasizes them.

RyanHamilton 10y ago

Windowing functions in standard SQL databases are like putting lipstick on a pig. You created a design based on sets then later tried to slap on the idea of order to sets. kdb is based on the concept of ordered lists, not sets and makes windowing functions on time and order based queries ridiculously easy. If this kind of thing interests you I recommend checking it out these comparison queries between kdb and standard SQL: http://www.timestored.com/b/kdb-qsql-query-vs-sql/ http://www.timestored.com/b/kdb-qsql-query-vs-sql/ Unfortunately kdb is very expensive.

saosebastiao 10y ago
There is far more utility to window functions than ordering.
gshulegaard 10y ago
> Windowing functions in standard SQL databases are like putting lipstick on a pig. I have always thought the underlying Relational Algebra of SQL databases to be quite beautiful (https://en.wikipedia.org/wiki/Relational_algebra https://en.wikipedia.org/wiki/Relational_algebra). This doesn't make it the right tool for every job, but let's try to shy away from calling it a, "pig". > You created a design based on sets then later tried to slap on the idea of order to sets. I have some questions here. It sounds like you are implying that sets and order are mutually exclusive, but this is not my understanding of the math. Could you elaborate? * https://en.wikipedia.org/wiki/Partially_ordered_set https://en.wikipedia.org/wiki/Partially_ordered_set * https://en.wikipedia.org/wiki/Total_order https://en.wikipedia.org/wiki/Total_order From the second reference on Total Order: > "The lexicographical order on the Cartesian product of a family of totally ordered sets, indexed by a well ordered set, is itself a total order." This quote seems to actually contradict this implication...especially in respect to Relational Algebra which relies heavily on Cartesian Product. Thanks in advance for your response! I enjoy improving my own understanding of the underlying Computer Science/Mathematics whenever I can.
- RyanHamilton 10y ago
  >>It sounds like you are implying that sets and order are mutually exclusive, but this is not my understanding of the math. Could you elaborate? The concept of sets and order may not be mutually exclusive in the mathematical theory but in terms of how actual databases were conceived and implemented they were very far apart. Early on many databases actually took advantage of the fact the operations were set based and that order was not guaranteed to achieve a number of speed optimizations. How long did many databases take to get row number support? "partition by" support? Take a look at this SO post for doing a running sums calculation: http://stackoverflow.com/questions/14953294/how-to-get-running-sum-of-a-column-in-sql-server http://stackoverflow.com/questions/14953294/how-to-get-runni... This is the same kdb code: update runningSumB:sums b by a from t Instead many people ended up turning to cursors to allow ordered calculations.

contingencies 10y ago

How many payments were there in the same hour as any given payment? How many payments were there in the same hour before any given payment? How many payments were there within one hour before any given payment? I have two serious questions. (1) In which realistic scenario would you ever need to ask such questions in the first place? (2) Even if you did need to do so, for example for risk management, occasional reporting or some kind of adaptive scaling supposition, then surely you would be calculating once per time unit (eg. hour/day) or recomputing a sliding-total? As I have actually personally written a number of relatively high profile, large scale, publicly deployed, major brand digital entertainment content rental and purchase systems (including complexities resulting from things like multi-device, multi-DRM cryptography) for brands like LG, Nokia, Samsung, etc. - these are not flippant questions. Looking further... uh-oh... author uses Java. Sakila example database was originally developed by Mike Hillyer of the MySQL AB documentation team. This project is designed to help database administrators to decide which database to use for development of new products. The user can run the same SQL against different kind of databases and compare the performance. So the Sakila project referenced is not supposed to be a real project, it's a personal re-interpretation of a test project designed specifically by a database nerd with zero domain knowledge to show off database features... jOOQ is an innovative solution for a better integration of Java applications with popular databases ... and this is what the author is working on, the world's billionth RDBMS ORM layer. IMHO readability is far more important than efficiency. Programmers should generally avoid using these types of obscure, frequently partly platform-linked techniques and consider their drawbacks against other potential solutions. Five minutes of life wasted.

infinite8s 10y ago
Huh? Windowing functions are part of the SQL standard (as of SQL2003). Also, these types of queries are the bread and butter of analytical queries (not really as useful for transactional purposes).
- contingencies 10y ago
  There is 'standard' and there is 'reality'. If it doesn't work everywhere (eg. on sqlite) then it's not 'standard' in that it's not familiar to all developers (minimum subset). The further you go from that subset, the higher price your developers pay to grok your code. Yes, that includes stuff as simple as foreign keys, indices, etc. Contrary to your bread and butter assertions, in my experience this stuff is end-of-the-branch, end-of-the-twig, end-of-the-leaf, only-present-at-winter-solstice level common in web and CRUD applications.

garyclarke27 10y ago

Itzik Ben-Gan wrote a whole book just on SQL windows functions and it's brilliant. He writes for SQL server usually, but he made this one more generic, he had to because at the time SQL server 2012 was missing much of the functionality he described, unlike Postgres or Oracle. Postgres has incredible capability here - Window AND Frame partitions/ordering, also row based ranges or value based ranges. And as a huge bonus unique to Postgres, the wondeful - Filter by clause - so much more elegant than sub queries.

j_s 10y ago
This one? Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Developer Reference) https://www.amazon.com/dp/B00JDMPHKY https://www.amazon.com/dp/B00JDMPHKY