Re: total and partial sums in the same query??

agora inbox for pgsql-sql@postgresql.org  
help / color / mirror / Atom feed

From: Iuri Sampaio <iuri.sampaio@gmail.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: total and partial sums in the same query??
Date: Sat, 10 Oct 2020 12:28:20 -0300
Message-ID: <ACAD364B-3DB0-49B1-835B-67F8C877A746@gmail.com> (raw)
In-Reply-To: <CAKFQuwZK=ESYy3_0Aw66Q5a9zJKdzqye-59+fWBzj=VSED=P=g@mail.gmail.com>
References: <88EE8FAD-AE7D-42A6-9DC1-43B2442960A4@gmail.com>
	<CAKFQuwZg9Wpt_BsxvAFStBcvBO6DkM7yCt1hGe5A9PmLLY1PUQ@mail.gmail.com>
	<4D4046E1-6DC2-4A36-A469-0BDBC0F86406@gmail.com>
	<CAKFQuwZK=ESYy3_0Aw66Q5a9zJKdzqye-59+fWBzj=VSED=P=g@mail.gmail.com>

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 “key” in the process. Thanks a lot!

“ … you should construct a simplified self-contained example and,… “


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) = 10
	GROUP BY type1) AS t 
ON t.type1 = 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) = 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 = cte2.type2;


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

Nevertheless, I’m 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:  
“ 
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=10) as m10_count from v_normalized_data group by type;

https://www.postgresql.org/docs/13/sql-expressions.html#SYNTAX-AGGREGATES <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 

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 <mailto: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) = 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) = 10 GROUP BY type1) AS t ON t.type1 = 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=10) as m10_count from v_normalized_data group by type;
> 
> https://www.postgresql.org/docs/13/sql-expressions.html#SYNTAX-AGGREGATES <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.
> 
>

view thread (6+ messages)  latest in thread

Message-ID: <ACAD364B-3DB0-49B1-835B-67F8C877A746@gmail.com>
Permalink:  ../ACAD364B-3DB0-49B1-835B-67F8C877A746@gmail.com/
Also on:    postgresql.org/message-id/ACAD364B-3DB0-49B1-835B-67F8C877A746@gmail.com

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: pgsql-sql@postgresql.org
  Cc: iuri.sampaio@gmail.com, david.g.johnston@gmail.com
  Subject: Re: total and partial sums in the same query??
  In-Reply-To: <ACAD364B-3DB0-49B1-835B-67F8C877A746@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