On Thu, Sep 23, 2010 at 11:34 PM, Dennis Björklund <db@zigo.dhs.org> wrote:
>> On Wed, Sep 22, 2010 at 6:03 AM, Dennis Björklund <db@zigo.dhs.org> wrote:
>> But I confess that I'm sort of murky on how ORDER affects the window
>> frame, or how to rephrase this more sensibly.
>
> The rows included in the calculation of the window function are per default
>
> RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
>
> where CURRENT ROW include all the rows that are equal to the row you are
> at according to the ordering. So if you say order by name then all the
> rows up to your name and all rows with the same name are included, not
> later rows.
>
> If you don't have any ordering, then all rows are "equal" and all rows are
> included in the computation. That's why your example behaved like it did.
>
> At least that's my understanding of how these things work. I've not used
> window functions very much myself.
>
> This is fairly difficult stuff and it probably don't belong in a tutorial
> but the current wording suggest that you can add any ordering and it won't
> affect the result. That is also a bad since it might teach people the
> wrong thing.
Hmm... it is true that average will produce the same results on any
ordering of the same set of input values, though. Perhaps the word
"partition" emcompass that, though then again maybe not.
I'd be happy to fix this if I understand what to fix it to.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company