Re: Window function docs

On Thu, Apr 6, 2017 at 03:53:24PM -0700, David G. Johnston wrote:
> On Thu, Apr 6, 2017 at 2:49 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> As part of writing a talk about window functions, I have done some
> cleanups of the window function docs, attached, that I would like to
> apply to head.
>
>
> +1 Overall.  Some minor technical items noted below.  The only conceptual item
> is whether (and, if so, how) to deal with partitions vs. frames.
>
> ​-    But unlike regular aggregate functions, use of a window function does not
> +    But unlike normal aggregate functions, use of a window function does not
>
> How broadly did you look for usage of normal versus regular?  The window
> function doc page uses "normal" once, vis-a-vis "ordered-set", I haven't looked
> further.  Note on that page the intro paragraph doesn't mention

I did a grep looking for 'regular.*agg'.

> hypothetical-set aggregates as being distinct from ordered-set aggregates yet
> the tables and the docs here do.

Do you have any suggested text for that?

> https://www.postgresql.org/docs/current/static/functions-aggregate.html
>
> +    treated as a window function and computed across the entire partition.)
>
> Would introducing the concept of frame here trade precision for comprehension?

I modified this to say "window frame" as part of the patch for Tom.

> -    <firstterm>window frame</>.  Many (but not all) window functions act only
> +    <firstterm>window frame</>.  Some window functions act only
>
> This is a semantic change as to whether the dominate behavior is to act on
> frames or partitions.  IIUC very few window functions, when presented with a
> ORDER BY'd OVER and thus a limited frame, will still act on the entire
> partition instead of the just the frame they are provided.  In particular don't

If you look at slide 79 here you can get a good overview of what
operates on frames vs. partitions:

http://momjian.us/main/writings/pgsql/window.pdf

> all normal aggregates (and user-defined ones) operate on frames?  The cume_dist
> function noted later is one of the few exceptions since it effectively operates
> on both - the frame for the numerator and the partition for the denominator.

Actually, cume_dist does not operate on frames because if you specify
ORDER BY x ROWS CURRENT ROW, cume_dist still operates on the peer and
lesser rows. So, the frame clause is ignored by cume_dist --- in a way
it has RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW wired into the
window function.

> +   aggregate function (i.e. not ordered-set or hypothetical-set aggregates)
>
> I, and others, subscribe to the believe that both i.e., and e.g., should have
> trailing commas...
>
> http://english.stackexchange.com/questions/6894/
> should-there-be-a-comma-after-i-e

Done.

> +   functions (includes <function>cume_dist</>) are defined so that they
>
> s/b "including <function>cume_dist</>"

Done.

> +   give the same answer for any two peer rows.
>
> for all peer rows?

Much better, done.

> +   assuming the current row is does not exist in the partition.
>
> remove "is"

Fixed in Tom's patch.

Updated patch attached. Thanks for the review.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +