Tell me why the request works so strangely?

with a as ( select 1 as id, 'a' as type from dual union select 2 as id, 'a' as type from dual union select 3 as id, 'b' as type from dual union select 4 as id, 'b' as type from dual union select 5 as id, 'b' as type from dual ) select type, count(1) over (partition by type order by id) from a;

Received:

 a | 1 a | 2 b | 1 b | 2 b | 3

Expected:

 a | 2 a | 2 b | 3 b | 3 b | 3

I thought that the number should be considered for the whole group, and not on the line. Why count line by line?

  • one
    Translate your question into Russian. In general, when order by by is specified in over (), the quantity should be considered cumulative in rows, which is actually observed. Now, if order by would not have been set, the result would have been the same as you assumed, there would be a total number for the group in each record - Mike
  • Thank you Mike, cumulatively yes, but why? why he does not evaluate the whole group (count (), max ()), but works in a row-wise way? - Mykhailo Marufenko
  • one
    Because it is intended. What other purpose would you come up with in this case for order by because if you print the same value (total), then there is no difference in what order to do it. Carefully read the item on order by in the over() clause in the docs.oracle.com/cd/B19306_01/server.102/b14200/… documentation - Mike

1 answer 1

The design of the analytical function indicates order by id. If you understand TYP as a group, then replace ID with TYP or delete it altogether.

 select type, count(1) over (partition by type ) from a

The result of the query

These are features of the analytic function.