Thread: Window Functions
Hi all. This is not very "hackers"-related, but related to the topic of window-funcitons, which seems to be discussed quite a biton "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 pORDER BY p.firstname ASCLIMIT 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 need todisplay the rist 20 results of several million, without having to do a separate count(*) query. -- 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 | | ------------------------+---------------------------------------------+
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 a biton "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) --------------------- Hannu Krosing
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 | | ------------------------+---------------------------------------------+
On Tue, 2008-10-14 at 19:04 +0200, Andreas Joseph Krogh wrote: > 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 exampleneed to 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 the query. ok, I misunderstood your intent I guess you can use the non-recursive variant WITH syntax (aka CTE aka Recursive queries) to get what you want. > 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. > > 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. >
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