Analytical counter above the section with the ORDER BY clause and without it

Question

I don’t understand why there are different results when using the ORDER BY in the COUNT analytic 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 following result:

 N CLS CNT 2 A 2 1 A 2 

While adding ORDER BY in the analytic sentence, 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; 

Changed CNT column:

 N CLS CNT 1 A 1 2 A 2 

Can someone please explain?

thanks

Answer 1

First, a link to the docs. However, it is somewhat unclear.

The analytic clause consists of query_partition_clause , order_by_clause and windowing_clause . And the really important thing in windowing_clause is

You cannot specify this section if you did not specify order_by_clause . Some window borders defined in the RANGE clause you can specify only one expression in order_by_clause . Refer to "Constraints in the ORDER BY Clause".

But you cannot help using windowing_clause without order_by_clause , they are related to each other.

If you omit windowing_clause completely, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW used by default.

The default windowing clause creates something like total execution. COUNT returns 1 for the first line, since there is only one line between the top of the window and the current line, 2 for the second line, etc.

So, in your first request there is no window at all, but in the second there is a default window.

And you can simulate the behavior of the first request by specifying a completely unlimited 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; 

Yes

 N CLS CNT 1 A 2 2 A 2 

Answer 2

The easiest way to think about this is to leave ORDER BY output equivalent to “ordering” so that all lines in the section are “equal” to each other. In fact, you can get the same effect by explicitly adding the ORDER BY as follows: ORDER BY 0 (or “sort” with any constant expression) or even more strongly ORDER BY NULL .

Why do you get COUNT() or SUM() , etc. for the entire section associated with the default windowing clause: RANGE between unbounded preceding and current row . “Range” (as opposed to “ROWS”) means that all lines “anchored” to the current line are also included, even if they do not precede it. Since all rows are anchored, this means that the entire section is included, regardless of which row is “current”.