In this example, AVG is used as a window function to compute the moving average of all product sales, by month, in the year 2000. Note that the WINDOW specification uses a RANGE clause, which causes the window bounds to be computed based on the month value, and not simply by a number of adjacent rows as with the ROWS clause. Using ROWS would yield different results if, for example, some or all of the products happened to have no sales at all in a particular month.
SELECT * FROM ( SELECT s.ProductID, Month( o.OrderDate ) AS julian_month, SUM( s.Quantity ) AS sales, AVG( SUM( s.Quantity ) ) OVER ( PARTITION BY s.ProductID ORDER BY Month( o.OrderDate ) ASC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS average_sales FROM SalesOrderItems s KEY JOIN SalesOrders o WHERE Year( o.OrderDate ) = 2000 GROUP BY s.ProductID, Month( o.OrderDate ) ) AS DT ORDER BY 1,2;
|Send feedback about this page via email or DocCommentXchange||Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0|