If for no other reason than to delude myself into thinking my blogging is worthwhile, I hope to learn something important enough to blog at least once a week. Today I learned the power of the OVER command in Oracle SQL and how to apply it to get a moving average of data.

I picked a small dataset that reflects my regrets that I didn't pick a career path of collecting samples from rivers and doing data analysis. I have to content myself with going to meetings and doing data analysis but that is another story.

        SELECT TO_DATE(pil.sample_date, 'YYYYMMDD') AS sample_date, 
    	   pil.nh4,
    	   TRUNC(AVG(pil.nh4)
    	   	 OVER (ORDER BY pil.sample_date 
                           ROWS BETWEEN 1 PRECEDING 
    		       AND 1 FOLLOWING), 6) AS avg_nh4
        FROM plum_island_lter pil 
        WHERE permanent_id='YSI-CC'
    

The SQL statement will work well for very large datasets and fills in null data with the average. Here is a graph of the moving average. Notice that the breaks in the data (the blue line) are filled in nicely by the rolling averages.