Sum of Last 10 days Column Data

Database
Enthusiast

Sum of Last 10 days Column Data

Hi Guys

 

Source    Target 
DTTRACK_1 DTTRACK_1T_L10 
1/12/2015  10 1/12/20151010-->Sum between 1/2/2015-1/12/2015 
1/16/2015  25 1/16/20152535-->Sum between 1/6/2015-1/16/2015
1/18/2015  20 1/18/20152055-->Sum between 1/8/2015-1/18/2015
1/24/2015  15 1/24/20151560-->Sum between 1/14/2015-1/24/2015
1/28/2015  30 1/28/20153065-->Sum between 1/18/2015-1/28/2015
1/29/2015  25 1/29/20152570-->Sum between 1/19/2015-1/29/2015 
2/2/2015  5 2/2/2015575-->Sum between 1/23/2015-2/2/2015 
2/6/2015   0 2/6/2015060-->Sum between 1/27/2015-2/6/2015 
2/9/2015  32 2/9/20153237-->Sum between 1/30/2015-2/9/2015 
2/14/2015  10 2/14/20151042-->Sum between 2/4/2015-2/14/2015 
2/19/2015  40 2/19/20154082-->Sum between 2/9/2015-2/19/2015 

 

 

As shown Above, I am excepting ouptput with sum of Track_1 for last 10 days only.

Can some one helps me to achive this.

 

Thanks in Advance.

 

 


Accepted Solutions
Junior Contributor

Re: Sum of Last 10 days Column Data

You can't use SUM OVER because Standard SQL's RANGE syntax is not implemented. 

 

It's easy to write using old SQL:

SELECT t1.DT, Sum(t2.TRACK_1)
FROM tab AS t1 JOIN tab AS t2
  ON t2.DT BETWEEN T1.DT - 10 AND T1.DT
GROUP BY t1.DT

 

If the number of rows is small performance will be ok, but if it's large the product join will cause a huge intermediate spool before join.

Hopefully this was just an example and there is another column to GROUP/PARTITION BY, then performance depends on the number of rows per group. For larger sets you should materialize the data with this group column as PI, at least this will speed up the join part.

 

1 ACCEPTED SOLUTION
3 REPLIES
Enthusiast

Re: Sum of Last 10 days Column Data

+++ Attached Screen Shot++

 

Capture.PNG

Junior Contributor

Re: Sum of Last 10 days Column Data

You can't use SUM OVER because Standard SQL's RANGE syntax is not implemented. 

 

It's easy to write using old SQL:

SELECT t1.DT, Sum(t2.TRACK_1)
FROM tab AS t1 JOIN tab AS t2
  ON t2.DT BETWEEN T1.DT - 10 AND T1.DT
GROUP BY t1.DT

 

If the number of rows is small performance will be ok, but if it's large the product join will cause a huge intermediate spool before join.

Hopefully this was just an example and there is another column to GROUP/PARTITION BY, then performance depends on the number of rows per group. For larger sets you should materialize the data with this group column as PI, at least this will speed up the join part.

 

Enthusiast

Re: Sum of Last 10 days Column Data

That's Working fine.

We have Below 10000 rows only.

May be it not might be a problem in Prodution Too..

 

Thank You Dnoeth..