Database Reference
In-Depth Information
in the first tuple, the average is computed over the current tuple (there is no
preceding tuple), while in the second tuple, the average is computed over the
current tuple, and the preceding one. Finally, in the third tuple, the average
is computed over the current tuple and the two preceding ones.
a
b
Product
Sales
Amount
Product
Sales
Amount
Year Month
MovAvg
Year Month
YTD
Key
Key
p1
2011
10
100
100
p1
2011
10
100
100
p1
2011
11
105
102.5
p1
2011
11
105
205
p1
2011
12
100
101.67
p1
2011
12
100
305
p2
2011
12
60
60
p2
2011
12
60
60
p2
2012
1
40
50
p2
2012
1
40
40
p2
2012
2
70
56.67
p2
2012
2
70
110
p3
2012
1
30
30
p3
2012
1
30
30
p3
2012
2
50
40
p3
2012
2
50
80
p3
2012
3
40
40
p3
2012
3
40
120
Fig. 5.20
(
a
) Three-month moving average of the sales per product. (
b
)Year-to-date
sum of the sales per product
As another example, the following query computes the year-to-date sum
of sales by product.
SELECT ProductKey, Year, Month, SalesAmount, AVG(SalesAmount) OVER
(PARTITION BY ProductKey, Year ORDER BY Month
ROWS UNBOUNDED PRECEDING) AS YTD
FROM Sales
The result is shown in Fig.
5.20
b. For each tuple, the query evaluator opens a
window that contains the tuples pertaining to the current product and year
ordered by month. Unlike in the previous query, the aggregation function
SUM
is applied to all the tuples before the current tuple, as indicated by
ROWS UNBOUNDED PRECEDING
.
It is worth noting that queries that use window functions can be expressed
without them, although the resulting queries are harder to read and may be
less e
cient. For example, the query above computing the year-to-date sales
can be equivalently written as follows:
SELECT ProductKey, Year, Month, SalesAmount, AVG(SalesAmount) AS YTD
FROM Sales S1, Sales S2
WHERE S1.ProductKey = S2.ProductKey AND
S1.Year = S2.Year AND S1.Month
>
=
S2.Month
Of course, there are many other functions provided in the SQL/OLAP
extension, which the interested reader can find in the standard.
Search WWH ::
Custom Search