Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kRGnv-0000sy-Jw for pgsql-sql@arkaria.postgresql.org; Sat, 10 Oct 2020 15:29:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kRGnN-00005P-DO for pgsql-sql@arkaria.postgresql.org; Sat, 10 Oct 2020 15:28:29 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kRGnM-0008WU-KO for pgsql-sql@lists.postgresql.org; Sat, 10 Oct 2020 15:28:28 +0000 Received: from p3plsmtpa12-08.prod.phx3.secureserver.net ([68.178.252.237]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kRGnJ-0002z1-QQ for pgsql-sql@postgresql.org; Sat, 10 Oct 2020 15:28:27 +0000 Received: from [192.168.1.3] ([186.241.25.164]) by :SMTPAUTH: with ESMTPSA id RGnFkgSg1KefiRGnGkHfwE; Sat, 10 Oct 2020 08:28:23 -0700 X-CMAE-Analysis: v=2.3 cv=f/s2+96M c=1 sm=1 tr=0 a=nbPSJA7ApY4wULB72PBkzw==:117 a=nbPSJA7ApY4wULB72PBkzw==:17 a=x7bEGLp0ZPQA:10 a=PkefZFhya44A:10 a=epTmVMiNAAAA:8 a=pGLkceISAAAA:8 a=nDH7OHY6fcCILxbUL5wA:9 a=QEXdDO2ut3YA:10 a=bicon6NVUiQA:10 a=vKbPdFxYzRkA:10 a=MmLLjHQauwqJKJiTmwoA:9 a=q-KatTaBLUqgOibX:21 a=_W_S_7VecoQA:10 a=ndEWmUVY6Yapc0oHF_P4:22 X-SECURESERVER-ACCT: iuri@iurix.com From: Iuri Sampaio Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_FB64C703-2133-4766-9BC9-CBBE208F088D" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.1\)) Subject: Re: total and partial sums in the same query?? Date: Sat, 10 Oct 2020 12:28:20 -0300 In-Reply-To: Cc: pgsql-sql To: "David G. Johnston" References: <88EE8FAD-AE7D-42A6-9DC1-43B2442960A4@gmail.com> <4D4046E1-6DC2-4A36-A469-0BDBC0F86406@gmail.com> X-Mailer: Apple Mail (2.3608.120.23.2.1) X-CMAE-Envelope: MS4wfKYtJDNFZ+OnelAGH29vIqeqCX8z4tEAqZtQGreB9s7xy+LIrda5UQ/Rj3PpbwfGTAT68m0P6DBiSuqk3SNbDUImHs1YrmFPb8IIyJXQs6MLfHolf+vG 2GFXoZrtj1oozxLIETwoXI69c60L1rcmkC6cSkj2JMy3qX2G2gwcWvkOSfSpnnfnuuL6W6odo4YXf7LmNnH/NGNVxxe14AHSHD1k9cF9GcGiSRZegcCippFm List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_FB64C703-2133-4766-9BC9-CBBE208F088D Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 David, Going further in our conversation. I went to PG's documentation and did = a better research in order to come out with better solution/approaches.=20= Furthermore, based on your previous email, your words were =E2=80=9Ckey=E2= =80=9D in the process. Thanks a lot! =E2=80=9C =E2=80=A6 you should construct a simplified self-contained = example and,=E2=80=A6 =E2=80=9C I reviewed the original query: SELECT split_part(v.description, ' ', 25) AS type, t.partial, COUNT(1) = AS total=20 FROM qt_vehicle_ti v=20 RIGHT OUTER JOIN (=20 SELECT split_part(description, ' ', 25) AS type1, COUNT(1) AS = partial=20 FROM qt_vehicle_ti=20 WHERE EXTRACT(MONTH FROM creation_date) =3D 10 GROUP BY type1) AS t=20 ON t.type1 =3D split_part(v.description, ' ', 25)=20 GROUP BY type, partial =20 and rewrote it to the following one: WITH=20 cte1 AS (SELECT split_part(description, ' ', 25) AS type1, = COUNT(1) AS partial FROM qt_vehicle_ti WHERE EXTRACT(MONTH FROM = creation_date) =3D 10 GROUP BY type1),=20 cte2 AS (SELECT split_part(description, ' ', 25) AS type2, = COUNT(1) AS total FROM qt_vehicle_ti GROUP BY type2)=20 SELECT type1, total, partial FROM cte1 JOIN cte2 ON cte1.type1 =3D = cte2.type2; Indeed! Performance is way better now. As well as readability, and less = code written! Nevertheless, I=E2=80=99m still reluctant to the necessity of using = v_normalized. On the other hand my reluctancy comes from not entirely = understanding this approach and the benefits of it. Your words were: =20 =E2=80=9C=20 A simple conditional (filter) count would be much easier to understand = and should be much faster: Select type, count(*) as total_count, count(*) filter (where month=3D10) = as m10_count from v_normalized_data group by type; = https://www.postgresql.org/docs/13/sql-expressions.html#SYNTAX-AGGREGATES = I added v_normalized because the expressions the decompose your data = tend to be better placed in a view and your main queries focus just on = their purpose and not structural data manipulation. Especially = something expensive like duplicating split_part. " So, feel free whether to go deeper continuing this discussion=20 Best wishes, I > On Saf. 23, 1442 AH, at 00:58, David G. Johnston = wrote: >=20 > On Friday, October 9, 2020, Iuri Sampaio > wrote: > Hi David,=20 >=20 > RIGHT OUTER JOIN is the key! >=20 > TOTAL > SELECT split_part(description, ' ', 25) AS type, COUNT(1) AS total = FROM qt_vehicle_ti GROUP BY type >=20 > OCTOBER > SELECT split_part(description, ' ', 25) AS type, COUNT(1) AS total = FROM qt_vehicle_ti WHERE EXTRACT(MONTH FROM creation_date) =3D 10 GROUP = BY type >=20 >=20 > FINAL=20 > SELECT split_part(v.description, ' ', 25) AS type, t.partial, COUNT(1) = AS total FROM qt_vehicle_ti v RIGHT OUTER JOIN ( SELECT = split_part(description, ' ', 25) AS type1, COUNT(1) AS partial FROM = qt_vehicle_ti WHERE EXTRACT(MONTH FROM creation_date) =3D 10 GROUP BY = type1) AS t ON t.type1 =3D split_part(v.description, ' ', 25) GROUP BY = type, partial > =20 >=20 >=20 > Let me know if you would use a different approach >=20 > The convention I try to observe when using outer joins is to use left = join, not right (outer is implied). That said, you seem to have written = a left join query since the totals, a superset of october, are on the = left. Also, count(*) is my learned convention instead of count(1). >=20 > A simple conditional (filter) count would be much easier to understand = and should be much faster: >=20 > Select type, count(*) as total_count, count(*) filter (where month=3D10)= as m10_count from v_normalized_data group by type; >=20 > = https://www.postgresql.org/docs/13/sql-expressions.html#SYNTAX-AGGREGATES = >=20 > I added v_normalized because the expressions the decompose your data = tend to be better placed in a view and your main queries focus just on = their purpose and not structural data manipulation. Especially = something expensive like duplicating split_part. >=20 > David J. >=20 >=20 --Apple-Mail=_FB64C703-2133-4766-9BC9-CBBE208F088D Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
David,

Going further in our conversation. I went to PG's = documentation and did a better research in order to come out with better = solution/approaches. 

Furthermore, based on your previous email, your words were = =E2=80=9Ckey=E2=80=9D in the process. Thanks a lot!

=E2=80=9C =E2=80=A6 you = should construct a simplified self-contained example = and,=E2=80=A6 =E2=80=9C


I reviewed the original query:

SELECT = split_part(v.description, ' ', 25) AS type, t.partial, COUNT(1) AS = total 
FROM qt_vehicle_ti v 
RIGHT OUTER JOIN ( 
SELECT = split_part(description, ' ', 25) AS type1, COUNT(1) AS = partial 
= FROM qt_vehicle_ti 
WHERE EXTRACT(MONTH FROM = creation_date) =3D 10
= GROUP BY type1) AS t 
ON t.type1 =3D split_part(v.description, ' ', = 25) 
GROUP BY type, partial
 



and rewrote it to the following one:


WITH 
cte1 AS (SELECT = split_part(description, ' ', 25) AS type1, COUNT(1) AS partial FROM = qt_vehicle_ti WHERE EXTRACT(MONTH FROM creation_date) =3D 10 GROUP BY = type1), 
= cte2 AS (SELECT split_part(description, ' ', 25) AS type2, = COUNT(1) AS total FROM qt_vehicle_ti GROUP BY = type2) 
SELECT type1, total, partial FROM cte1 JOIN cte2 ON = cte1.type1 =3D cte2.type2;


Indeed! Performance is way better now. As well as = readability, and less code written!

Nevertheless, I=E2=80=99m still reluctant to the necessity of = using v_normalized. On the other hand my reluctancy comes from not = entirely understanding this approach and the benefits of = it.


Your = words were:  
=E2=80=9C 
A simple conditional (filter) count would = be much easier to understand and should be much faster:

Select type, count(*) as total_count, count(*) filter (where = month=3D10) as m10_count from v_normalized_data group by type;


I added = v_normalized because the expressions the decompose your data tend to be = better placed in a view and your main queries focus just on their = purpose and not structural data manipulation.  Especially something = expensive like duplicating split_part.
"


So, feel free whether to go deeper = continuing this discussion 

Best = wishes,
I



On Saf. = 23, 1442 AH, at 00:58, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Friday, October = 9, 2020, Iuri Sampaio <iuri.sampaio@gmail.com> wrote:
Hi = David, 

RIGHT OUTER JOIN is the key!

TOTAL
SELECT split_part(description, ' ', = 25) AS type, COUNT(1) AS total FROM qt_vehicle_ti GROUP BY = type

OCTOBER
SELECT split_part(description, ' ', = 25) AS type, COUNT(1) AS total FROM qt_vehicle_ti WHERE EXTRACT(MONTH = FROM creation_date) =3D 10 GROUP BY type


FINAL 
SELECT split_part(v.description, ' = ', 25) AS type, t.partial, COUNT(1) AS total FROM qt_vehicle_ti v RIGHT = OUTER JOIN ( SELECT split_part(description, ' ', 25) AS type1, COUNT(1) = AS partial FROM qt_vehicle_ti WHERE EXTRACT(MONTH FROM creation_date) =3D = 10 GROUP BY type1) AS t ON t.type1 =3D split_part(v.description, ' ', = 25) GROUP BY type, partial
 


Let me know if you would use a different = approach

The convention I try to observe when = using outer joins is to use left join, not right (outer is = implied).  That said, you seem to have written a left join query = since the totals, a superset of october, are on the left.  Also, = count(*) is my learned convention instead of count(1).

A simple conditional = (filter) count would be much easier to understand and should be much = faster:

Select = type, count(*) as total_count, count(*) filter (where month=3D10) as = m10_count from v_normalized_data group by type;

https://www.postgresql.org/docs/13/sql-expressions.html#SYNTAX-= AGGREGATES

I added v_normalized because the = expressions the decompose your data tend to be better placed in a view = and your main queries focus just on their purpose and not structural = data manipulation.  Especially something expensive like duplicating = split_part.

David J.



= --Apple-Mail=_FB64C703-2133-4766-9BC9-CBBE208F088D--