2008/11/5 Vladimir Sitnikov <sitnikov.vladimir@gmail.com>:
>>
>> Even though I understand the definition, your suggestion of COUNT(*)
>> OVER (ORDER BY salary) doesn't make sense.
>
> Why does not that make sense?
> I have not read the spec, however Oracle has a default window specification
> in case there is only an order by clause. The default window is "range
> between unbounded preceding and current row".
>
> "count(*) over (order by salary range between unbounded preceding and
> current row)" is perfectly identical to the "number of rows preceding or
> peers to R" by the definition, isn't it? I see here a word-by-word
> translation from SQL to the English and vice versa.
>
> If the patch returns "row_number" it is wrong since there is no way for
> row_number to be a "number of rows preceding or peer with R", is there?
>
I've got it.
I had thought that implicit window framing specified by ORDER BY
clause (such like above) would mean ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW. But actually reading the spec more closely it says:
Otherwise, WF consists of all rows of the partition of R that precede
R or are peers of R in the
window ordering of the window partition defined by the window ordering clause.
So it means RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW as you
pointed. And the result of count(*) OVER (ORDER BY salary) doesn't
equal to row_number().
Now my assumption is broken. Let me take time to think about how to solve it...
Regards,
--
Hitoshi Harada