Re: Windowing Function Patch Review -> Standard Conformance

2008/12/6 David Rowley <dgrowley(at)gmail(dot)com>:
>
> I've spent last night and tonight trying to break the patch and I've not
> managed it.
>
> I spent 2 and a half hours on the train last night reading over the patch
> mainly for my own interest. I also went over the documentation and I have a
> few suggestions for improvement:
>
> + <para>
> + After <literal>WHERE</> and <literal>GROUP BY</> process,
> + rows might be windowed table, using the <literal>WINDOW</>
> + clause.
> + </para>
>
> I think I know what you mean here. My re-write seems to have turned the
> sentence into a paragraph. Please tell me if I've assumed the meaning
> wrongly:
>
>
> "After the <literal>WHERE</>, <literal>GROUP BY</> and <literal>HAVING</>
> clauses one or more <literal>WINDOW</> clauses can be specified. This will
> allow window functions to be specified in the <literal>SELECT</> clause.
> These window functions can make use of the <literal>WINDOW</> clauses by
> making reference to the alias name of the window rather than explicitly
> specifying the properties of the window in each <literal>OVER</> clause."

The "WINDOW clause" is a clause that starts with WINDOW, containing
some window definitions, syntactically. So I rewrote it as:

>>
After the <literal>WHERE</>, <literal>GROUP BY</> and
<literal>HAVING</> clauses one or more window definitions can be
specified by the <literal>WINDOW</> clause. This will allow window
functions to be specified in the <literal>SELECT</> clause. These
window functions can make use of the <literal>WINDOW</> clauses by
making reference to the alias name of the window rather than
explicitly specifying the properties of the window in each
<literal>OVER</> clause.
<<

>
>
> + Window functions are not placed in any of GROUP BY, HAVING and
> + WHERE clauses, which process values before any of the windows. If
> + there is need to qualify rows by the result of window functions,
> + whole of the query must be nested and append WHERE clause outer of
> + the current query.
>
> I think this one maybe needs an example to back it up. It's quite an
> important thing and I'm sure lots of people will need to do this. I'm not
> 100% happy with my new paragraph either but can't see how to word it any
> better.
>
> "Window functions cannot be used in the WHERE, GROUP BY or HAVING clauses
> of the query. If there is a need to filter rows, group results or filter
> rows after aggregation takes place (HAVING) then the query must be nested.
> The query should contain the window functions in the inner query and apply
> the additional clauses that contain the results from the window function in
> the outer query, such as:
>
> SELECT depname,
> empno,
> salary,
> enroll_date
> FROM (SELECT depname,
> empno,
> salary,
> enroll_date,
> ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary,empno)
> AS pos
> FROM empsalary
> ) AS e
> WHERE pos = 1;
>
> In the above query the we're filtering and only showing the results from the
> inner query where the ROW_NUMBER() value is equal to 1."
>
> But of course the above query would be more simple using DISTINCT ON. Maybe
> there is a better example... My previous marathon getting the person in 2nd
> place might be better but that's introducing another previously unknown
> table to the manual.

I use this query:

SELECT depname,
empno,
salary,
enroll_date
FROM (SELECT depname,
empno,
salary,
enroll_date,
ROW_NUMBER() OVER (PARTITION BY depname ORDER BY
salary,empno) AS pos
FROM empsalary
) AS e
WHERE pos < 3;

This isn't emulated by DISTINCT ON, is it?

For all other issues, thanks, applied to my patch.

Regards,

--
Hitoshi Harada