Database Reference
In-Depth Information
.
.
.
In this case, the subquery determines each customer's sales in the respective
product category. The outer
SELECT
clause then ranks the customer's sales within
each category. The provided portions of the SQL query output illustrate that the
ranking begins at 1 for each category and demonstrate how the rankings are
affected by ties in the amount of
sales
.
A second use of windowing functions is to perform calculations over a sliding
window in time. For example, moving averages can be used to smooth weekly
sales figures that may exhibit large week-to-week variation, as shown in the plot in
Figure 11.2
Weekly sales for an online retailer
The following SQL query illustrates how moving averages can be implemented
using window functions:
SELECT year,
week,
sales,
AVG(sales)
OVER (
ORDER BY year, week
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS moving_avg
