160 High-Function Business Intelligence in e-business
Figure 4-5 Top 5 sales persons by region
4.2.4 Compare and rank the sales results by state and country
This query compares the sales results rolled up to country and state level for
1994. Its purpose is to view global sales ranking, peer to peer ranking among
states and countries, with ranking within parent levels.
Data
Our data is sourced from sales transactions, using the same tables as used
earlier. The key attributes are transaction date, transaction amount, state, and
country. Our data resides in the following tables:
򐂰 TRANS
򐂰 TRANSITEM
򐂰 LOC
BI functions showcased
GROUPING, RANK, OVER, ORDER BY, ROLLUP
Steps
We executed multiple queries, each addressing a particular requirement.
Chapter 4. Statistics, analytic, OLAP functions in business scenarios 161
Query 1
The query shown in Example 4-8 globally ranks the countries and states by the
sales revenues:
Example 4-8 Globally rank the countries & states by sales revenues
SELECT SUM(ti.amount) AS sum, loc.country, loc.state,
GROUPING(loc.country) + GROUPING(loc.state) AS level,
RANK() OVER (ORDER BY SUM(ti.amount) DESC ) AS global_rank
FROM trans t, transitem ti, loc loc
WHERE t.transid =ti.transid AND loc.locid = t.locid AND YEAR(pdate) = 1994
GROUP BY ROLLUP (loc.country, loc.state)
ORDER BY global_rank
Figure 4-6 shows the results of this query.
Figure 4-6 Global ranking
162 High-Function Business Intelligence in e-business
States, countries, and the world have a level by hierarchy. The world has level 2,
states have level 1, and the countries have a level 0 within the LEVEL as shown
in Figure 4-7. The world is ranked 1 (GLOBAL_RANK) by virtue of the total sales
transactions.
Figure 4-7 Levels in hierarchy
Query 2
The query shown in Example 4-9 ranks the sales among peers, that is, rank all
the countries, and then the states, across multiple countries.
Example 4-9 Sales among peers
SELECT SUM(ti.amount) AS sum, loc.country, loc.state,
GROUPING(loc.country) + GROUPING(loc.state) AS level,
RANK () OVER (PARITION BY GROUPING (loc.country) + GROUPING(loc.state)
ORDER BY SUM(ti.amount) DESC) AS rank_within_peers
FROM trans t, transitem ti, loc loc
WHERE t.transid = ti.transid AND loc.locid = t.locid AND YEAR(pdate) = 1994
GROUP BY ROLLUP (loc.country, loc.state)
ORDER BY level DESC, rank_within_peers
Figure 4-8 shows the results of this query.
Chapter 4. Statistics, analytic, OLAP functions in business scenarios 163
Figure 4-8 Ranking within peers
Query 3
The query shown in Example 4-10 ranks the sales within each parent, that is,
rank all the countries, and then states within each country.
Example 4-10 Sales within each parent
SELECT SUM(ti.amount) AS sum, loc.country, loc.state,
GROUPING (loc.country) + GROUPING(loc.state) AS level,
RANK() OVER (PARTITION BY GROUPING (loc.country) + GROUPING(loc.state),
CASE WHEN GROUPING(loc.state) = 0 THEN loc.country END
ORDER BY SUM (ti.amount) DESC) AS rank_within_parent
FROM trans t, transitem ti, loc loc
WHERE t.transid = ti.transid AND loc.locid = t.locid
AND YEAR(pdate) = 1994

Get DB2 UDB's High-Function Business Intelligence in e-business now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.