Combine two queries with monthly average

Question

I need to put together the results of these two queries into a single return with the following structure:

"date", avg(selic."Taxa"), avg(titulos."puVenda")

Partial structure of tables:

selic
"dtFechamento" date,
"pTaxa" real

titulos
"dtTitulo" date,
"puVenda" real,
"nomeTitulo" character(30)

Query table selic:

select to_char("dtFechamento", 'YYYY-MM') as data, avg("pTaxa")
from "selic"
group by data
order by data

Query table titulos:

select to_char("dtTitulo", 'YYYY-MM') as data, avg("puVenda")
from "titulos"
where "nomeTitulo" = 'LFT010321'
group by data
order by data

I tried a subquery, but it returned the fields next to each other and can not muster.

select *
from (select to_char("dtFechamento", 'YYYY-MM') as data, avg("pTaxa")
from "selic"
group by data
order by data) as selic,
(select to_char("dtTitulo", 'YYYY-MM') as data, avg("puVenda")
from "titulos"
where "nomeTitulo" = 'LFT010321'
group by data
order by data) as LFT010321;

What to return if only one of the queries has a result for a month? NULL for the other? What for months in between without results in either query? No row (skip the month)? Or return the month with two NULL values? — Erwin Brandstetter, Jun 25 '16 at 22:22
I took the liberty to fix your names and double quotes, which would distract from your actual question. — Erwin Brandstetter, Jun 25 '16 at 23:32

score 1 · Answer 1 · edited May 23 '17 at 12:08

Assuming you want to return one row per month where either of your two queries returns a row. And pad missing values from the other query with NULL.

Use a FULL [OUTER] JOIN:

SELECT to_char(mon, 'YYYY-MM') AS data, s.avg_taxa, t.avg_venda
FROM  (
   SELECT date_trunc('month', "dtFechamento") AS mon, avg("pTaxa")   AS avg_taxa
   FROM   selic
   GROUP  BY 1
   ) s
FULL JOIN (
   SELECT date_trunc('month', "dtTitulo")     AS mon, avg("puVenda") AS avg_venda
   FROM   titulos
   WHERE  "nomeTitulo" = 'LFT010321'
   GROUP  BY 1
   ) t USING (mon)
ORDER  BY mon;

It is substantially faster to join after aggregating than before (fewer join operations).

It is also faster to GROUP BY, JOIN and ORDER on timestamp values than on a text rendition. Typically also cleaner and less error prone (although text is unambiguous in this particular case). That's why I use date_trunc() instead of to_char() on lower levels.

If the format for the month is not important, you can just return the timestamp value. Else you can format any way you like after you are done processing.

Similar case with more explanation:

PostgreSQL merge two queries with COUNT and GROUP BY in each

score 1 · Answer 2 · answered Jun 26 '16 at 01:38

This should get what you need. The inner "PQ" (PreQuery) does a union all between each possible date, but also adds a flag column to identify which average it was associated with. Each part is grouped by date. So now, the outer query will AT MOST have 2 records for a given date... one for tax, the other be Venda. So now you dont need any full outer join, nor need to build some dynamic calendar data basis to get the details for all possible dates.

So, it is possible for only a Tax average OR a Venda average OR BOTH.

SELECT
      PQ.Data,
      SUM( CASE when PQ.SumType = 'T' then PQ.TypeAvg else 0 end ) as AvgTax,
      SUM( CASE when PQ.SumType = 'V' then PQ.TypeAvg else 0 end ) as AvgVenda
   from
      ( select 
              to_char( dtFechamento, 'YYYY-MM') as data,
              'T' as sumtype,
              avg( pTaxa ) as TypeAvg
           from 
              selic
           group by 
              to_char( dtFechamento, 'YYYY-MM') as data
        UNION ALL
        select 
              to_char( dtTitulo, 'YYYY-MM') as data, 
              'V' as sumType,
              avg( puVenda ) as TypeAvg
           from
              titulos
           where 
              nomeTitulo = 'LFT010321'
           group by 
              to_char( dtTitulo, 'YYYY-MM') ) PQ
   group by
      PQ.Data
   order by
      PQ.Data

Combine two queries with monthly average

2 Answers2