Why does ORDER by itself change the results in an Analytic function?

127 views Asked by At

In the following query:

with SalesX as (
    select 'Office Supplies' Category , 2014 Year,22593.42 Profit UNION all
    select 'Technology', 2014, 21492.83 UNION all
    select 'Furniture', 2014,   5457.73 UNION all
    select 'Office Supplies',   2015,   25099.53  UNION all
    select 'Technology',    2015,   33503.87  UNION all
    select 'Furniture', 2015,   50000.00  UNION all
    select 'Office Supplies',   2016,   35061.23  UNION all
    select 'Technology',    2016,   39773.99  UNION all
    select 'Furniture', 2016,   6959.95
) select Category, Year, Profit,
    SUM(Profit) OVER (),
    SUM(Profit) OVER (ORDER BY Category, Year)
 from SalesX order by category, year

We see that using only an ORDER BY <field> in the query changes the results drastically (from a constant grand total to a running total):

enter image description here

My question is what is the reasoning behind the ORDER changing how the analytic function works. I know it does, but I'm more interested in why or how it does that (for example, a normal ORDER BY doesn't change the results, only if something like a limit is applied after it). Note the backend here is BigQuery for the tests but I think this question should apply to any DB that supports these functions.

0

There are 0 answers