Re: Window Functions

2008/10/15 Andreas Joseph Krogh <andreak@officenet.no>:
> On Tuesday 14 October 2008 18:19:07 Hannu Krosing wrote:
>> On Tue, 2008-10-14 at 11:05 +0200, Andreas Joseph Krogh wrote:
>> > Hi all.
>> > This is not very "hackers"-related, but related to the topic of window-funcitons, which seems to be discussed
quitea bit on "hackers" these days.
 
>> >
>> > Can window-functions in PG be used to return "total number of rows" in a "paged result"?
>> > Say you have:
>> > SELECT p.id, p.firstname
>> >   FROM person p
>> >  ORDER BY p.firstname ASC
>> >  LIMIT 10 OFFSET 10
>> >
>> > Is it possible to use some window-function to return the "total-number of columns" in a separate column?
>> >
>> > In Oracle one can do
>> > SELECT q.*, max(rownum) over() as total_rows FROM (subquery)
>> > which returns the total number or columns in a separate column. This is very handy for web-pages which for example
needto display the rist 20 results of several million, without having to do a separate count(*) query.
 
>>
>> no need to use window functions here, just ask for max inline:
>>
>>
>> hannu=# select rownum, word, (select max(rownum) from words) as maxrow
>> from words limit 10;
>>  rownum |   word    | maxrow
>> --------+-----------+--------
>>       1 |           |  98569
>>       2 | A         |  98569
>>       3 | A's       |  98569
>>       4 | AOL       |  98569
>>       5 | AOL's     |  98569
>>       6 | Aachen    |  98569
>>       7 | Aachen's  |  98569
>>       8 | Aaliyah   |  98569
>>       9 | Aaliyah's |  98569
>>      10 | Aaron     |  98569
>> (10 rows)
>
> Where do you get your "rownum"-column from here? It's a pseudo-column in Oracle which is computed for each row in the
"result-set",it's not a column in a table somewhere, which is why I figured I must use window-funciton, or "analytical
function"as Oracle calls them, to operate on the *result-set* to retrieve the maximum number of rows which satisfies
thequery.
 
>
> As far as I understand the ROW_NUMBER() window-funciton can be used to construct "limit with offset"-queries in a
SQL-spec-compliantway.
 
>
> Say I want to retrieve an ordered list of persons (by name):
>
> SELECT * FROM (
> SELECT ROW_NUMBER() OVER (order by p.name) as rnum, q.*
>  FROM (
> SELECT p.id, p.name FROM person p where p.birth_date > '2000-01-01'
> ) q
> ) r
>  WHERE r.rnum between 11 AND 20
> ;
>
> This is good and works in Oracle, PG >= 8.4 and others that implements spec-compliant window-functions. This is fine,
butin Oracle I can extend this query to this for getting the total-number (not just the "page" 11-20) of persons
matchingin a separate column:
 
>
> SELECT * FROM (
> SELECT ROW_NUMBER() OVER (order by p.name) as rnum, q.*, max(rownum) over() as total_rows
>  FROM (
> SELECT p.id, p.name FROM person p where p.birth_date > '2000-01-01'
> ) q
> ) r
>  WHERE r.rnum between 11 AND 20
> ;
>
> So my question is: Will PG, with window functions, provide a similar mechanism for retrieving the total number of
rowsin the "result-set" without actually retrieving them all? I understand that PG might have to visit them all in
orderto retrieve that count, but that's OK.
 

Yeah, the half part of my purpose is for that. Manytimes we want
values based on cross-row without reducing or aggregate rows. The rest
of my purpose is for analytical methods such as cumulative aggregates.
As you point, internally postgres must see all the rows to determine
the maximum of row_number() so it's not so efficiently as you feel but
I beleive (and hope) it is elegant enough and perform well
considerablely.

Regards,

-- 
Hitoshi Harada