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 by the number of adjacent rows as with the ROWS clause. Using ROWS would yield different results if, for example, there were no sales of some or all the products 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;
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|