You Asked
Hi Sir - Trying to calculate the rolling sum or so.
When
create table tbl1 ( year number, month varchar2(3), col1 varchar2(10), col2 varchar2 (20), amount number)
I need to list the values of col1, col2 along with the value as the below calculation (I believe it is rolling sum)
{ SUM(amount) for year , month (Ex: 2016, 02) , col1, col2} / {sum (amount) for past 12 months Mar 15 - Feb 16}. Trying to do it in a single SQL or using any analytical functions or so.
create table tbl1 ( year number, month varchar2(3), col1 varchar2(10), col2 varchar2 (20), amount number);
insert into tbl1 values (2015, '1', 'abc','temp123',7259.1);
insert into tbl1 values (2015, '1', 'def','temp123',7259.1);
insert into tbl1 values (2015, '1', 'def','temp345',729.1);
insert into tbl1 values (2015, '3', 'abc','temp35',659);
insert into tbl1 values (2014, '1', 'abc','temp123',259);
insert into tbl1 values (2014, '2', 'def','temp123',7259.45);
insert into tbl1 values (2014, '1', 'def','temp345',874.8);
insert into tbl1 values (2013, '2', 'VAL2','temp123',6547.8);
insert into tbl1 values (2013, '3', 'VAL2','temp345',862.0);
insert into tbl1 values (2013, '3', 'VAL3','temp345',659);
hope I am clear enough
with LiveSQL Test Case:
and Chris said...
You can get a rolling sum (or count, or whatever) using the window clause of analytics. This specifies a number of rows or range of values to include in the calculation.
You don't know how many rows there will be in the past 12 months. So you need to use range. You use this to state an offset of values to include from your current row.
Your "dates" are separate year and month values. Calculating the offset using these is tricky. It's much easier to convert them to a date first. Then you can specify the number of months to look back (or forward) as an interval.
This gives you:
create table tbl1 (
year number, month varchar2(3), col1 varchar2(10), col2 varchar2 (20), amount number
);
insert into tbl1 values (2015, '1', 'abc','temp123',7259.1);
insert into tbl1 values (2015, '1', 'def','temp123',7259.1);
insert into tbl1 values (2015, '1', 'def','temp345',729.1);
insert into tbl1 values (2015, '3', 'abc','temp35',659);
insert into tbl1 values (2014, '1', 'abc','temp123',259);
insert into tbl1 values (2014, '2', 'def','temp123',7259.45);
insert into tbl1 values (2014, '1', 'def','temp345',874.8);
insert into tbl1 values (2013, '2', 'VAL2','temp123',6547.8);
insert into tbl1 values (2013, '3', 'VAL2','temp345',862.0);
insert into tbl1 values (2013, '3', 'VAL3','temp345',659);
select t.*,
sum(amount) over (
order by to_date(year || lpad(month, 2, '0') || '01', 'yyyymmdd')
range between interval '12' month preceding and current row
) sm_12_months
from tbl1 t
order by to_date(year || lpad(month, 2, '0') || '01', 'yyyymmdd');
YEAR MONTH COL1 COL2 AMOUNT SM_12_MONTHS
2,013 2 VAL2 temp123 6,547.8 6,547.8
2,013 3 VAL2 temp345 862 8,068.8
2,013 3 VAL3 temp345 659 8,068.8
2,014 1 abc temp123 259 9,202.6
2,014 1 def temp345 874.8 9,202.6
2,014 2 def temp123 7,259.45 16,462.05
2,015 1 abc temp123 7,259.1 23,640.55
2,015 1 def temp123 7,259.1 23,640.55
2,015 1 def temp345 729.1 23,640.55
2,015 3 abc temp35 659 15,906.3
You can read more about this at:
http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23sql-1906475.html Or watch Connor's videos at:
https://www.youtube.com/watch?v=dovPI166tcs https://www.youtube.com/watch?v=oeRZFZUXs1U PS - when sharing LiveSQL links, you can't just copy-paste the URL from your session! You need to save it as a public script first. Then share the public URL.
Rating
(4 ratings)
Is this answer out of date? If it is, please let us know via a Comment