5 ms·

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

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.
- smallnamespace 10y ago
  I'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 ago
  The 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.

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.

smallnamespace 10y ago
I'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 ago
The 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.