Reuse the SQL with varying date range-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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.