A Little Known SQL Feature: Use Logical Windowing to Aggregate Sliding Ranges
blog.jooq.org
blog.jooq.org
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]
Window functions are extraordinarily powerful, as are Common Table Expressions (CTE's). I encourage anyone who uses SQL with any regularity to learn them immediately once they are comfortable with the more straightforward queries and clauses SQL offers. Once you've mastered windowing and CTE's, you'll wonder how you functioned without them.
The workflow I use is to run simple select queries on prod databases. To bring the subset of data I'm interested in down locally Once I have the "extracts" I'm interested in I'll transform the data locally using SAS.
SAS has pretty powerful time interval routines which is what I'd typically use to compute something like the example in the article.
My work has just started looking at "big data" we have a new hadoop databases which is supposed to be used for that so I assume if I ever need to start looking into data stored there it will make more sense to run the SQL "on the database". So the article has some useful info there.
So yeah, the end result is DBA's give the generic "don't run SELECT * and use a WHERE clause when you're querying large tables"
https://drill.apache.org/docs/sql-window-functions-introduct...
Unfortunately kdb is very expensive.
I have always thought the underlying Relational Algebra of SQL databases to be quite beautiful (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/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.
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-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.
I may be wrong, but it was my understanding the original Relational Databases were very much based on the Relational Algebra originally pioneered by E.F. Codd[1]. So I think it is a stretch to say that they were "conceived" separate from the mathematics.
Further, it was my understanding that E.F. Codd took his relational algebra which he developed while working as a researcher at IBM and applied it to an implementation of an RDBMS also at IBM (now known as IBM DB2)[2]. So similarly, I think it is a stretch to claim that databases were originally implemented "far apart" from the mathematics.
I think there is a valid point that actual DB implementations may be lacking, but isn't that a reason not to use that particular implementation rather than a knock against Relational models?
I may be wrong, but it seems like your problems aren't with the Relational Model but rather with potentially lacking/incomplete implementations of it.
I use Oracle mostly so I don't even have to worry about syntax differences.
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Developer Reference)
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.
Also, these types of queries are the bread and butter of analytical queries (not really as useful for transactional purposes).
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.
Note that for reporting and analytics workloads, 1) even batch queries can be much faster using analytics functions because the optimizer can do many of them for essentially free as part of the scan rather than requiring a subquery and 2) interactive workflows become much, much quicker in my experience. Data exploration is much less useful if running a query takes 5 hours rather than 30 seconds.
Defining sqlite as the 'standard' SQL and refusing to use any other features available on your platform out of fear of confusing new developers is misguided. Sometimes analytics functions are the best tool for the task, and good engineers should judiciously weigh their utility against the risk of losing access to that tool.
Keep in mind though that the cost of migrating your db backend is generally so high that if you are forced to do so, rewriting queries that depended on analytics functions will likely be a small rounding error within the total migration cost.
As for readability I think it's subjective - I certainly prefer staring at a single complex SQL statement until I understand it over say reading through pages of imperative low-level code that accomplishes the same thing.
Not related to payment processing but I do this kind of sliding time aggregation a lot at my work we have two 12 hour shifts. Often you need to be able to compute data across shift boundaries and batch things to gether at set intervals. I.E 'How many alarms events occurred this shift?' 'What about the previous shift?' 'How many four nightshift's previously?'
1: https://www.postgresql.org/docs/current/static/functions-win...
http://www.jooq.org/doc/3.8/manual/sql-building/sql-statemen...
Source: https://msdn.microsoft.com/en-us/library/ms189461.aspx