Question 19893 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Reuse the SQL with varying date range

I need to create a single table with same column metrics aggregated for different time periods in Redshift SQL. Instead of repeating the code so many times with varying "WHERE" clause, is there a way I can reuse the code and keep it simple?

select 
c1 as c1,
sum(c2) t30_c2,
sum(c3) t30_c3,
max(c4) t30_c4,
from t1 
join t2 ()
join t3()
join date_tbl
where date between current_date -30 and current_date;

select 
c1 as c1,
sum(c2) t90_c2,
sum(c3) t90_c3,
max(c4) t90_c4,
from t1 
join t2 ()
join t3()
join date_tbl
where date between current_date -90 and current_date;
.
.
where date between current_date -120 and current_date

Finally place all these column level metrics in a single table.

Tags:

2

Answers


  1. 0

    I think the missing step you have here is a group-by:

    select 
c1 as c1,
sum(x) as summary
,round(datediff(day, current_date, date)/30,0)
from {tables}
group by 
round(datediff(day, current_date, date)/30,0)

    If you want many columns, then have a look at pivoting the dataset from here.
    Hopefully the functions work the same – I’m only familiar with T-SQL. in any case it should only require minor syntax changes to make it work.

    You may also need to check windowing functions

    Login or Signup to reply.
  2. 0

    You can make clever use of a case statement:

    select 
  c1 as c1,
  sum(case when date between current_date -30  and current_date then c2 end) as t30_c2,
  sum(case when date between current_date -30  and current_date then c3 end) as t30_c3,
  max(case when date between current_date -30  and current_date then c4 end) as t30_c4,
  sum(case when date between current_date -90  and current_date then c2 end) as t90_c2,
  sum(case when date between current_date -90  and current_date then c3 end) as t90_c3,
  max(case when date between current_date -90  and current_date then c4 end) as t90_c4,
  sum(case when date between current_date -120 and current_date then c2 end) as t120_c2,
  sum(case when date between current_date -120 and current_date then c3 end) as t120_c3,
  max(case when date between current_date -120 and current_date then c4 end) as t120_c4
from table

    This hard-codes the date ranges, but lets you do it in a single table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search