Why does the sum() window function require order by, when it has "between unbounded preceding a-CodePudding

I got an error today attempting something like

sum(balance) over(rows between unbounded preceding and unbounded following)

The error was that the window requires an order by statement. But, when summing over all rows (which gives the same result as simply sum(balance) without a window function), the order by information is not necessary. Not only that, I don't want SQL to waste time ordering my rows just to sum them all.

Is there a good reason that SQL requires order by in this case? Am I naive, have I missed something, or is this actually unnecessary?

CodePudding user response：

In order to compute over entire window, there is no need to provide ORDER BY and window frame:

SELECT SUM(balance) OVER(PARTITION BY col)
FROM tab

SELECT SUM(balance) OVER()
FROM tab

CodePudding user response：

Yes, Snowflake, could see that your unbounded preceding and unbounded following makes it the same as SUM(balance) and thus rewrite it for you, like many other DBs,

OR you could learn to ask for what you want and not write poor SQL and then have the DB have to auto-magic-clean-up for every time that and every other piece of SQL is run.