Logical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 146

The result is shown in Fig. 5.19 a. The first tuple, for example, was evaluated
by opening a window with all the tuples of customer c1 , ordered by the sales
amount. We see that product p1 is the one most demanded by customer c1 .

a b

Product Customer

Key

Sales
Amount

Product Customer

Key

Sales
Amount

RowNo Rank

Key Key

p1 c1 100 1 p1 c2 105 1

p2 c1 70 2 p1 c3 100 2

p3 c1 30 3 p1 c1 100 2

p1 c2 105 1 p2 c1 70 1

p2 c2 60 2 p2 c2 60 2

p3 c2 40 3 p2 c3 40 3

p1 c3 100 1 p3 c3 50 1

p3 c3 50 2 p3 c2 40 2

p2 c3 40 3 p3 c1 30 3

Fig. 5.19 ( a ) Ranking of products in the sales of customers. ( b ) Ranking of
customers in the sales of products

We could instead partition by product and study how each customer ranks
in the sales of each product, using the function RANK .

SELECT ProductKey, CustomerKey, SalesAmount, RANK() OVER
(PARTITION BY ProductKey ORDER BY SalesAmount DESC) AS Rank
FROM Sales

As shown in the result given in Fig. 5.19 b, the first tuple was evaluated
opening a window with all the tuples with product p1 , ordered by the sales
amount. We can see that customer c2 is the one with highest purchases of
p1 , and customers c3 and c1 are in the second place, with the same ranking.
A third kind of feature of SQL/OLAP is window framing , which defines
the size of the partition. This is used to compute statistical functions over
time series, like moving averages. To give an example, let us assume that we
add two columns, Year and Month ,tothe Sales table. The following query
computes the 3-month moving average of sales by product.

SELECT ProductKey, Year, Month, SalesAmount, AVG(SalesAmount) OVER
(PARTITION BY ProductKey ORDER BY Year, Month
ROWS 2 PRECEDING) AS MovAvg
FROM Sales

The result is shown in Fig. 5.20 a. For each tuple, the query evaluator opens
a window that contains the tuples pertaining to the current product. Then,
it orders the window by year and month and computes the average over the
current tuple and the preceding two ones, provided they exist. For example,