Thread: Re : Query "top 10 and others"

Re : Query "top 10 and others"

From

Edson Richter

Date:

05 July 2014, 02:34:25

<span style="font-size:10pt;"><p style="margin-top:0;margin-bottom:0;">Would you please provide an example, even
hypothetical? <pstyle="margin-top:0;margin-bottom:0;"> Atenciosamente,<br /><br />Edson Richter <br /><br />------
Mensagemoriginal ------<br /><b>De: </b>David G Johnston <br /><b>Data: </b>04/07/2014 19h22<br
/><b>Para: </b>pgsql-general@postgresql.org;<br/><b>Assunto:</b>Re: [GENERAL] Query "top 10 and others"<br /><br
/><pre>EdsonRichter wrote
 
> I would like to construct a query, to be used in graphics (Pie Chart, to
> be more precise), and to avoid having 500 slices, I would like to make a
> query that returns the top ten, and then all the rest summed.
> 
> I know I can do it by using some repetition, like:
> 
> a) Assume "places" table with population with structure
> 
> create table places (
>   id as integer primary key,
>   country as varchar(3),
>   state as varchar(50),
>   city as varchar(50),
>   population integer
> )
> 
> b) There are not so many records in table (my country have ~5500 cities,
> and 27 states), and initially, there will be only 1 country.
> 
> with QRY as (select C1.country, C1.state, sum(C1.population)
>   from places C1
>   group by 1, 2
>    order by 3 DESC
>   limit 10)
> 
> select * from QRY
> union
> select 'others' as "country", '' as "state", sum(population)
>   from places
>  where not exists (select 1 from QRY where country = QRY.country and state
> = QRY.state)
> 
> 
> Can I simplify the query by using some sort of window function or other
> PostgreSQL feature I don't know yet?

This is the best solution; though it may be faster to calculate all the sums
in the CTE then limit 10 the first union part and sum the remaining sums in
the second part.  That way you do not have to scan the entire places table
twice.

David J.






--
View this message in context: http://postgresql.1045698.n5.nabble.com/Query-top-10-and-others-tp5810597p5810601.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

</pre></span>

Re: Re : Query "top 10 and others"

From

David Johnston

Date:

05 July 2014, 03:20:45

> with QRY as (select C1.country, C1.state, sum(C1.population)
>   from places C1
>   group by 1, 2
>    order by 3 DESC
>   limit 10)
> 
> select * from QRY
> union
> select 'others' as "country", '' as "state", sum(population)
>   from places
>  where not exists (select 1 from QRY where country = QRY.country and state
> = QRY.state)
>

(not tested)

with QRY as ( SELECT country, state, sum(population) as st_pop FROM places GROUP BY country, state )

, u1 AS ( SELECT country, state, st_pop FROM QRY ORDER BY st_pop DESC LIMIT 10 )

, u2 AS ( SELECT 'other' AS country, '' AS state, sum(st_pop) FROM QRY WHERE NOT EXISTS (

SELECT 1 FROM u1 WHERE (QRY.country, QRY.state) = (u1.country, u1,state)

)

SELECT * FROM u1

UNION ALL

SELECT * FROM u2

;

David J.