Nicely put. Window functions are one of the harder parts of SQL to explain.
I'm being picky but SQL dialects differ with the default row handling of an ORDER BY clause for a partitioned aggregate.
When I first looked at your example, in my head I just saw it as wrong. With some checking I've found that's because the default is different in the dialect I mainly code under - Teradata.
Here's a quote from the Teradata documentation:
"If there are no ROWS or ROWS BETWEEN clause, the default aggregation group is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING."
Which means that there, to achieve your running sum, one has to add "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" to make it work. Indeed, as that's what I'd usually write, I didn't even realise that other dialects are different. Ergo, I think that using an ORDER BY with a PARTITION requires adding the ROWS clause to not be caught by dialect variations. Alas, I guess.
As I also code for HiveQL I'd better go check its defaults too I guess, although I should be safe as I always put in the ROWS clause anyway.
p.s. CAPS from the quotes and for clarity, not being me shouty.
Thanks for the feedback. Syntax across platforms can be tricky. I've been burned by this before after it was pointed out that CTEs in PostgreSQL can slow things down to a crawl.
Nicely put. Window functions are one of the harder parts of SQL to explain.
I'm being picky but SQL dialects differ with the default row handling of an ORDER BY clause for a partitioned aggregate.
When I first looked at your example, in my head I just saw it as wrong. With some checking I've found that's because the default is different in the dialect I mainly code under - Teradata.
Here's a quote from the Teradata documentation:
"If there are no ROWS or ROWS BETWEEN clause, the default aggregation group is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING."
Which means that there, to achieve your running sum, one has to add "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" to make it work. Indeed, as that's what I'd usually write, I didn't even realise that other dialects are different. Ergo, I think that using an ORDER BY with a PARTITION requires adding the ROWS clause to not be caught by dialect variations. Alas, I guess.
As I also code for HiveQL I'd better go check its defaults too I guess, although I should be safe as I always put in the ROWS clause anyway.
p.s. CAPS from the quotes and for clarity, not being me shouty.
Thanks for the feedback. Syntax across platforms can be tricky. I've been burned by this before after it was pointed out that CTEs in PostgreSQL can slow things down to a crawl.
SQL 201: Why you should use SQL CTEs
Helen Anderson ・ Oct 20 '18 ・ 3 min read
I've updated the post to let folks know that I've written this with SQL Server in mind and have used their docs as guidance.