public inbox for [email protected]  

help / color / mirror / Atom feed

From: kimaidou <[email protected]>
To: Frank Streitzig <[email protected]>
Cc: [email protected]
Subject: Re: Count child objects for each line of a table: LEFT JOIN, LATERAL JOIN or subqueries ?
Date: Mon, 23 May 2022 16:33:16 +0200
Message-ID: <CAMKXKO67oxVv9mBBEkve9YP7Urv53oFHyWY9qthrmEDCi-5HHA@mail.gmail.com> (raw)
In-Reply-To: <CAMKXKO56Kc9Y32GEscw4F=mjkB9N3+aO8gVX1w9HmNC9T=1OrA@mail.gmail.com>
References: <CAMKXKO6Sam3WrzCb12yRyW+=OT_7K9zz3MfAQy4PNywuqhkGTQ@mail.gmail.com>
	<YouIuUVD9ivC03pj@frastr-dev>
	<CAMKXKO4xgawFHoVpz6hjeV-uGNf2W0NU90PkVBuCxJDLDUVmWg@mail.gmail.com>
	<CAMKXKO4UoPKACXYVWMxDAn-ZukRVD9S3TV52P93QOYHzC6AqbQ@mail.gmail.com>
	<CAMKXKO56Kc9Y32GEscw4F=mjkB9N3+aO8gVX1w9HmNC9T=1OrA@mail.gmail.com>

Here is the 4th SQL fiddle with your proposal organized with "WITH" clauses
http://sqlfiddle.com/#!17/fe902/31/0

Le lun. 23 mai 2022 à 16:22, kimaidou <[email protected]> a écrit :

> 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 à 16:20, kimaidou <[email protected]> a écrit :
>
>> Hi Frank,
>>
>> Thanks for your answer !
>>
>> It seems it would perform better to aggregate as soon as possible, like
>> you illustrated in your example.
>> I will rewrite the query with "WITH" clauses to improve readability.
>>
>> Thanks also for the Coalesce idea. It is better to see 0 instead of NULL.
>>
>> Michaël
>>
>> Le lun. 23 mai 2022 à 16:15, kimaidou <[email protected]> a écrit :
>>
>>> So you
>>>
>>> Le lun. 23 mai 2022 à 15:14, Frank Streitzig <[email protected]> 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.
>>>>
>>>> Regards,
>>>> Frank
>>>>
>>>>

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Count child objects for each line of a table: LEFT JOIN, LATERAL JOIN or subqueries ?
  In-Reply-To: <CAMKXKO67oxVv9mBBEkve9YP7Urv53oFHyWY9qthrmEDCi-5HHA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox