nr: #1 dodano: 2016-12-28 17:12
First, a link to docs. It's somewhat obscure, however.
Analytic clause consists of
windowing_clause. And, a really important thing about
You cannot specify this clause unless you have specified the
order_by_clause. Some window boundaries defined by the
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.
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
select 2 as n, 'A' as cls
select req.*, count(*) over(partition by cls order by n RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as cnt from req;
N CLS CNT
1 A 2
2 A 2