CODEWITHSUNDEEP
sqlamazon-redshift
Himberjack
Himberjack

Reputation: 5792

Redshift FULL OUTER JOIN doesn't output NULL

We have a 'numbers' table that holds 0-10000 values in its single value 'n'. We have tableX that has calculated_at datetime and a term. We are trying to fill the holes where in tableX doesnt have matches in the given dates. HOWEVER, this doesn't seem to yield NULL or 0 for the non-matching... 

select term
     , avg(total::float)
     , date_trunc('day', series.date) as date1
     , date_trunc('day', calculated_at) as date2
from (select 
        (current_timestamp - interval '1 day' * numbers.n)::date as date
      from numbers) as series
full outer join terms
   on series.date = date_trunc('day', calculated_at)
where series.date BETWEEN '2017-07-01' AND '2017-07-30'
AND (term in ('term111') or term is null)
group by term
       , date_trunc('day', series.date)
       , date_trunc('day', calculated_at)
 order by date_trunc('day', series.date) asc

Upvotes: 2

Views: 17585

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

The full outer join is fine. The problem is the filters. These are really tricky with a full outer join. I would recommend:

select t.term, avg(total::float),
       date_trunc('day', series.date) as date1,
       date_trunc('day', calculated_at) as date2
from (select (current_timestamp - interval '1 day' * numbers.n)::date as date
      from numbers
      where (current_timestamp - interval '1 day' * numbers.n)::date BETWEEN '2017-07-01' AND '2017-07-30'
     ) series full outer join
     (select t.*
      from terms
      where term = 'term111'
     ) t
     on series.date = date_trunc('day', t.calculated_at)
group by t.term, date_trunc('day', series.date), date_trunc('day', calculated_at)
 order by date_trunc('day', series.date) asc;

My guess though is that a left join would do what you want. I doubt a full outer join is what you really intend. If you have doubts, ask another question and provide sample data and desired results.

Upvotes: 3

Related Questions