3 ms·
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
by 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 agoHuh? 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 agoThere 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.
- smallnamespace 10y agoI've worked on multiple true relational SQL and 'analytics' backends and they have all supported analytic functions of varying complexity. Everyone around me was using them as well because the queries were orders of magnitude faster, and smaller and more readable than using nested SELECT ... GROUP BY statements to achieve the same result. 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.
- gfody 10y agoThe reality is that windowing functions are well supported by all major RDBMSs (Oracle, SQL Server, DB2, Vertica, and to an extent Postgres and Volt - sqlite and MySQL are not serious contenders in this space). 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.
- bigger_cheese 10y ago>In which realistic scenario would you ever need to ask such questions in the first place? 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?'