Database Reference
In-Depth Information
5.8.3 Window Functions
A very common OLAP need is to compare detailed data with aggregate
values. For example, we may need to compare the sales of a product to a
customer against the maximum sales of this product to any customer. Thus,
we could obtain the relevance of each customer with respect to the sales of the
product. SQL/OLAP provides the means to perform this through a feature
called
window partitioning
. This query would be written as follows:
SELECT ProductKey, CustomerKey, SalesAmount, MAX(SalesAmount) OVER
(PARTITION BY ProductKey) AS MaxAmount
FROM Sales
The result of the query is given in Fig.
5.18
. The first three columns are
obtained from the initial
Sales
table. The fourth one is obtained as follows.
For each tuple, a window is defined, called
partition
, containing all the tuples
pertaining to the same product. The attribute
SalesAmount
is then aggregated
over this group using the corresponding function (in this case
MAX
), and the
result is written in the
MaxAmount
column. Note that the first three tuples,
corresponding to product
p1
,havea
MaxAmount
of 105, that is, the maximum
amount sold of this product to customer
c2
.
ProductKey CustomerKey SalesAmount MaxAmount
p1
c1
100
105
p1
c2
105
105
p1
c3
100
105
p2
c1
70
70
p2
c2
60
70
p2
c3
40
70
p3
c1
30
50
p3
c2
40
50
p3
c3
50
50
Fig. 5.18
Sales of products to customers compared with the maximum amount sold
for that product
A second SQL/OLAP feature, called
window ordering
,isusedto
order the rows within a partition. This feature is useful, in particular, to
compute rankings. Two common aggregate functions applied in this respect
are
ROW NUMBER
and
RANK
. For example, the next query shows how does
each product rank in the sales of each customer. For this, we can partition
the table by customer and apply the
ROW NUMBER
function as follows:
SELECT ProductKey, CustomerKey, SalesAmount, ROW NUMBER() OVER
(PARTITION BY CustomerKey ORDER BY SalesAmount DESC) AS RowNo
FROM Sales
Search WWH ::
Custom Search