Analytic count over partition with and without ORDER BY clause

Question

I don't understand why there are different results when using an ORDER BY clause in an analytic COUNT function.

Using a simple example:

with req as
 (select 1 as n, 'A' as cls
    from dual
  union
  select 2 as n, 'A' as cls
    from dual)
select req.*, count(*) over(partition by cls) as cnt from req;

gives the the following result:

N   CLS CNT
2   A   2
1   A   2

Whereas, when adding an ORDER BY in the analytic clause, the result is different!

with req as
 (select 1 as n, 'A' as cls
    from dual
  union
  select 2 as n, 'A' as cls
    from dual)
select req.*, count(*) over(partition by cls order by n) as cnt from req;

CNT column changed:

N   CLS CNT
1   A   1
2   A   2

Can someone explain please?

Thanks

Answer 1

First, a link to docs. It's somewhat obscure, however.

Analytic clause consists of query_partition_clause, order_by_clause and windowing_clause. And, a really important thing about windowing_clause is

You cannot specify this clause unless you have specified the order_by_clause. Some window boundaries defined by the RANGE clause let you specify only one expression in the order_by_clause. Refer to "Restrictions on the ORDER BY Clause".

But not only can you not use windowing_clause without the order_by_clause, they are tied together.

If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

The default windowing clause produces something like running total. COUNT returns 1 for first row, as there is only one row between the top of the window and the current row, 2 for the second row and so on.

So in your first query there is no windowing at all, but there is the default windowing in the second one.

And you can simulate the behavior of the first query by specifying fully unbounded window.

with req as
 (select 1 as n, 'A' as cls
    from dual
  union
  select 2 as n, 'A' as cls
    from dual)
select req.*, count(*) over(partition by cls order by n RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as cnt from req;

Yep

N   CLS CNT
1   A   2
2   A   2

Answer 2

The easiest way to think about this - leaving the ORDER BY out is equivalent to "ordering" in a way that all rows in the partition are "equal" to each other. Indeed, you can get the same effect by explicitly adding the ORDER BY clause like this: ORDER BY 0 (or "order by" any constant expression), or even, more emphatically, ORDER BY NULL.

Why you get the COUNT() or SUM() etc. for the entire partition has to do with the default windowing clause: RANGE between unbounded preceding and current row. "Range" (as opposed to "ROWS") means all rows "tied" with the current row are also included, even if they don't precede it. Since all rows are tied, this means the entire partition is included, no matter which row is "current."

Answer 3

Window functions will perform the aggregation over partition by (split by) value, when you omit ORDER BY clause the result will be similar to GROUP BY with output of each row . It is also possible to omit PARTITION BY, in which case there is just one partition containing all the rows

When you add ORDER BY clause to window function then it will perform the calculation in subsequent order within the same partition and start over with a different partition (group of values)

Values that are not distinct in the ORDER BY ordering are said to be peers, in COUNT() they will have the same calculated result of its last peer that will create gaps that maintain the total