I need to run a query on some data that brings back the moving 12 month total. I’ve already performed the average function on the data… so now I just need to loop through and get a moving 12 month period of data and SUM it up so I can graph it.
Below is the current query i’m issuing but it isn’t adding up correctly. Basically I want to have January of 1996 show the SUM of the prior 12 months averaged data. So January of 1996 would be the sum of January 1995 + February 1995 …. all the way through December 1995. Then February of 1996 would be the SUM of February 1995 + March 1995 ….
Is this possible using the OVER function in Oracle 11g? Seems like it is but I’m not getting the right results for some reason.
Here is my current query:
/* Formatted on 9/5/2013 3:20:55 PM (QP5 v5.149.1003.31008) */
SELECT MONTH_DT,
YEAR_DT,
AVERAGE_TOTAL_RAINFALL,
(SUM (AVERAGE_TOTAL_RAINFALL)
OVER (ORDER BY MONTH_DT ROWS BETWEEN 12 PRECEDING AND CURRENT ROW)) as "12_MONTH_TOTAL"
FROM ( SELECT month_dt,
year_dt,
TRUNC ( (SUM (tsvalue_ms)
/ (SELECT COUNT (FEATURE_ID)
FROM nexrad.springshed_featureid
WHERE springshed_id = 1)), 3)
AS average_total_rainfall
FROM ts_monthly_agg a, nexrad.springshed_featureid b
WHERE a.FEATURE_ID = b.FEATURE_ID AND b.springshed_id = 1
GROUP BY year_dt, month_dt
ORDER BY year_dt, month_dt)
ORDER BY year_dt, month_dt;
Here is a few rows of the sample data and the current way the OVER function is calculating.
MONTH YEAR AVERAGE 12_MONTH_TOTAL ------------------------------------ 1 1995 4.31 4.31 2 1995 1.932 27.473 3 1995 3.733 47.523 4 1995 4.216 44.841 5 1995 3.721 31.573 6 1995 8.379 50.459 7 1995 6.028 102.591 8 1995 7.918 105.076 9 1995 3.516 97.507 10 1995 5.623 74.056 11 1995 1.813 30.904 12 1995 1.881 23.03 1 1996 2.625 6.935 2 1996 1.165 27.459 3 1996 9.374 55.274 4 1996 2.84 45.388 5 1996 2.538 32.714 6 1996 5.952 55.483 7 1996 6.562 102.816 8 1996 8.428 108.123 9 1996 3.364 95.583 10 1996 4.222 72.252 11 1996 0.453 31.116 12 1996 4.968 26.6
Here is a dataset incase that helps.
Thanks so much for any assistance! Josh
Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 1995, 4.31); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 1995, 1.932); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 1995, 3.733); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 1995, 4.216); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 1995, 3.721); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 1995, 8.379); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 1995, 6.028); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 1995, 7.918); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 1995, 3.516); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 1995, 5.623); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 1995, 1.813); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 1995, 1.881); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 1996, 2.625); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 1996, 1.165); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 1996, 9.374); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 1996, 2.84); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 1996, 2.538); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 1996, 5.952); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 1996, 6.562); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 1996, 8.428); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 1996, 3.364); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 1996, 4.222); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 1996, 0.453); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 1996, 4.968); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 1997, 2.384); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 1997, 0.903); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 1997, 2.575); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 1997, 4.692); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 1997, 1.786); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 1997, 6.301); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 1997, 6.515); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 1997, 5.053); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 1997, 5.01); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 1997, 7.267); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 1997, 4.254); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 1997, 8.082); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 1998, 3.205); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 1998, 10.933); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 1998, 4.298); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 1998, 0.385); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 1998, 1.037); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 1998, 2.544); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 1998, 8.21); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 1998, 6.244); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 1998, 10.461); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 1998, 0.378); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 1998, 1.35); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 1998, 0.969); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 1999, 5.353); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 1999, 1.116); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 1999, 1.749); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 1999, 2.02); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 1999, 2.605); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 1999, 8.07); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 1999, 4.378); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 1999, 7.373); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 1999, 5.294); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 1999, 1.281); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 1999, 1.35); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 1999, 1.279); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 2000, 2.217); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 2000, 0.862); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 2000, 1.45); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 2000, 0.698); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 2000, 0.205); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 2000, 8.584); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 2000, 5.381); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 2000, 5.288); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 2000, 6.026); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 2000, 0.241); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 2000, 1.398); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 2000, 0.814); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 2001, 2.005); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 2001, 0.953); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 2001, 6.8); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 2001, 0.648); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 2001, 0.484); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 2001, 6.337); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 2001, 8.872); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 2001, 5.446); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 2001, 8.764); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 2001, 0.532); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 2001, 0.842); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 2001, 1.388); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 2002, 1.179); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 2002, 1.623); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 2002, 2.293); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 2002, 1.397); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 2002, 0.928); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 2002, 9.158); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 2002, 7.98); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 2002, 8.007); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 2002, 4.902); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 2002, 2.282); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 2002, 3.611); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 2002, 7.606); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 2003, 0.172); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 2003, 4.871); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 2003, 7.315); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 2003, 1.408); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 2003, 3.083); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 2003, 12.093); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 2003, 5.617); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 2003, 6.838); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 2003, 3.103); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 2003, 2.773); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 2003, 1.191); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 2003, 0.658); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 2004, 1.769); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 2004, 5.733); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 2004, 1.565); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 2004, 1.688); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 2004, 1.694); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 2004, 7.389); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 2004, 9.441); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 2004, 7.223); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 2004, 18.424); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 2004, 2.368); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 2004, 2.131); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 2004, 1.699); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 2005, 0.958); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 2005, 2.1); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 2005, 6.085); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 2005, 6.194); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 2005, 5.947); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 2005, 10.413); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 2005, 11.105); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 2005, 11.823); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 2005, 2.737); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 2005, 4.933); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 2005, 2.659); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 2005, 4.525); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 2006, 1.48); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 2006, 6.679); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 2006, 0.261); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 2006, 2.652); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 2006, 2.194); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 2006, 6.536); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 2006, 7.587); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 2006, 8.422); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 2006, 5.141); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 2006, 1.583); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 2006, 1.845); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 2006, 4.588); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 2007, 3.543); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 2007, 5.29); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 2007, 2.623); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 2007, 1.931); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 2007, 1.404); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 2007, 5.479); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 2007, 10.63); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 2007, 5.385); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 2007, 6.448); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 2007, 6.419); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 2007, 0.845); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 2007, 2.127); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 2008, 3.357); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 2008, 4.253); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 2008, 5.22); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 2008, 2.192); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 2008, 0.541); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 2008, 8.029); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 2008, 9.963); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 2008, 13.26); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 2008, 2.199); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 2008, 1.179); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 2008, 1.49); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 2008, 0.69); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 2009, 3.007); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 2009, 1.456); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 2009, 1.693); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 2009, 2.359); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 2009, 10.714); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 2009, 5.592); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 2009, 8.076); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 2009, 5.85); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 2009, 4.032); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 2009, 1.501); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 2009, 3.027); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 2009, 2.909); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 2010, 3.812); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 2010, 5.226); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 2010, 6.655); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 2010, 1.09); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 2010, 5.203); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 2010, 5.846); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 2010, 6.793); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 2010, 9.743); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 2010, 2.599); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 2010, 0.01); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 2010, 0.94); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 2010, 0.677); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 2011, 4.928); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 2011, 2.23); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 2011, 3.986); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 2011, 2.12); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 2011, 2.214); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 2011, 5.476); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (7, 2011, 5.713); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (8, 2011, 6.706); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (9, 2011, 4.058); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (10, 2011, 5.27); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (11, 2011, 1.17); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 2011, 0.363); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 2012, 1.034); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 2012, 2.375); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 2012, 2.148); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 2012, 1.658); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 2012, 5.773); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (6, 2012, 14.215); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (12, 2012, 3.889); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (1, 2013, 0.302); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (2, 2013, 1.954); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (3, 2013, 0.781); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (4, 2013, 3.163); Insert into RAINFALL_DATA (MONTH_DT, YEAR_DT, AVERAGE_TOTAL_RAINFALL) Values (5, 2013, 2.385);
Advertisement
Answer
To get the desired result you need to order by year_dt column or combination of year_dt and month_dt columns:
select month_dt
, year_dt
, average_total_rainfall
, sum(average_total_rainfall) over(order by year_dt, month_dt
rows between 12 preceding
and current row) res
from rainfall_data
MONTH_DT YEAR_DT AVERAGE_TOTAL_RAINFALL RES
---------- ---------- ---------------------- ----------
1 1995 4,31 4,31
2 1995 1,932 6,242
3 1995 3,733 9,975
4 1995 4,216 14,191
5 1995 3,721 17,912
6 1995 8,379 26,291
7 1995 6,028 32,319
8 1995 7,918 40,237
9 1995 3,516 43,753
10 1995 5,623 49,376
11 1995 1,813 51,189
12 1995 1,881 53,07
1 1996 2,625 55,695
2 1996 1,165 52,55
3 1996 9,374 59,992
4 1996 2,84 59,099
5 1996 2,538 57,421
6 1996 5,952 59,652
7 1996 6,562 57,835
8 1996 8,428 60,235
When you are ordering only by month_dt the rows will be processed by analytic function in the following order:
month_dt year_dt average -------------------------- 1 1995 4.31 1 1996 2.625 1 1997 2.384 1 1998 3.205 1 1999 5.353 1 2000 2.217 1 2001 2.005 1 2002 1.179 1 2003 0.172 1 2004 1.769 1 2005 0.958 1 2006 1.48 1 2007 3.543
producing result that doesn’t meet your expectations.