By the way, I was in fact aware of the duplicate count for the "nb_schools" and other fields, this is why I used a count(DISTINCT ) to have a correct count in the first example. I kept the nb_schools and 2 other fields to illustrate the cost of using DISTINCT in the aggregate functions.
Le lun. 23 mai 2022 à 15:14, Frank Streitzig <fstreitzig@gmx.net> a écrit :
Am Mon, May 23, 2022 at 01:55:07PM +0200 schrieb kimaidou: > Hi list, > > I have a basic need, often encountered in spatial analysis: I have a list > of cities, parks, childcare centres, schools. I need to count the number of > items for each city (0 if no item exists for this city) > > I have tested 3 different SQL queries to achieve this goal: > > * one with several LEFT JOINS: http://sqlfiddle.com/#!17/fe902/3 > * one with sub-queries: http://sqlfiddle.com/#!17/fe902/4 > * one with several LATERAL JOINS: http://sqlfiddle.com/#!17/fe902/6
Hello,
Cost of queries see link "View Execution Plan" in fiddle
query 1: 134.62 query 2: 8522.32 query 3: 134.62
query 1 and 3 have wrong count in result (columns nb_school, nb_childcare, nb_park)
My try has cost of 81.83
select c.* , coalesce(s.cnt,0) as cnt_school , s.schools , coalesce(cc.cnt,0) as cnt_childcare , cc.childcares , coalesce(p.cnt,0) as cnt_park , p.parks from city c left outer join (select fk_id_city, count(*) as cnt ,string_agg(name, ', ') AS schools from school group by fk_id_city) s on s.fk_id_city = c.id left outer join (select fk_id_city, count(*) as cnt ,string_agg(name, ', ') AS childcares from childcare group by fk_id_city) cc on cc.fk_id_city = c.id left outer join (select fk_id_city, count(*) as cnt ,string_agg(name, ', ') AS parks from park group by fk_id_city) p on p.fk_id_city = c.id order by c.id ;
IMHO, but without a where clause, the cost will increase with the amount of data.
