Re: Window Functions
От | Andreas Joseph Krogh |
---|---|
Тема | Re: Window Functions |
Дата | |
Msg-id | 200810141904.32596.andreak@officenet.no обсуждение исходный текст |
Ответ на | Re: Window Functions (Hannu Krosing <hannu@2ndQuadrant.com>) |
Ответы |
Re: Window Functions
Re: Window Functions |
Список | pgsql-hackers |
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 quite abit 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 ) rWHERE 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 ) rWHERE 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 rows inthe "result-set" without actually retrieving them all? I understand that PG might have to visit them all in order to retrievethat count, but that's OK. What I'm looking for is an elegant solution to what's becomming a more common requirement in web-applications these days:To display pageable lists with a "total-count", and to do that with *one* query, preferrably using standard-compliantSQL. -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / CEO ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment. | NORWAY | | Tlf: +47 24 15 38 90 | | Fax: +47 24 15 38 91 | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+
В списке pgsql-hackers по дате отправления: