Example: Using RANK and AVG
Consider the result of the following SELECT statement using the following ordered analytical functions, RANK and AVG.
SELECT item, smonth, sales, RANK() OVER (PARTITION BY item ORDER BY sales DESC), AVG(sales) OVER (PARTITION BY item ORDER BY smonth ROWS 3 PRECEDING) FROM sales_tbl ORDER BY item, smonth;
The results table might look like the following.
Item | SMonth | Sales | Rank(Sales) | Moving Avg(Sales) |
---|---|---|---|---|
A | 1996-01 | 110 | 13 | 110 |
A | 1996-02 | 130 | 10 | 120 |
A | 1996-03 | 170 | 6 | 137 |
A | 1996-04 | 210 | 3 | 155 |
A | 1996-05 | 270 | 1 | 195 |
A | 1996-06 | 250 | 2 | 225 |
A | 1996-07 | 190 | 4 | 230 |
A | 1996-08 | 180 | 5 | 222 |
A | 1996-09 | 160 | 7 | 195 |
A | 1996-10 | 140 | 9 | 168 |
A | 1996-11 | 150 | 8 | 158 |
A | 1996-12 | 120 | 11 | 142 |
A | 1997-01 | 120 | 11 | 132 |
B | 1996-02 | 30 | 5 | 30 |
... | ... | ... | ... | ... |