0

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.

GSerg
  • 76,472
  • 17
  • 159
  • 346
David542
  • 104,438
  • 178
  • 489
  • 842
  • Without an order by, it's across the entire window. With order by, it's from the first row of the window to the current row. (SQL Server example: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017#order-by) – GSerg Nov 14 '21 at 07:17
  • Does this answer your question? [The field in ORDER BY affects the result of window functions](https://stackoverflow.com/questions/57243391/the-field-in-order-by-affects-the-result-of-window-functions) – GSerg Nov 14 '21 at 07:22
  • @GSerg oh it seems to implicitly add the `ROWS BETWEEN PRECENDING AND UNBOUNDED` . – David542 Nov 14 '21 at 07:38
  • Hello @David542, Indeed, if you check the [official bigquery documentation](https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts#def_over_clause) if we just use over a set of default parameters will run alongside it. – Betjens Nov 15 '21 at 13:32
  • It's that enough to solve your question? or you are looking for something additional on it? – Betjens Nov 15 '21 at 14:30

0 Answers0