Aggregate Queries


Aggregate queries summarize (aggregate) individual facts. The measure values are typically summed, even though count is also a common aggregate. Two examples are discussed in this section.

Daily Sales Aggregation

The dimensional query in Listing 4.2 gives you the daily sales summary. The aggregation of the order amounts and number of orders is done by date. Note that the join between the sales_prder_fact table and date_dim table is on their surrogate keys.

Listing 4.2: Daily Aggregation

image from book
 /*****************************************************************/ /*                                                               */ /* daily_aggregation.sql                                         */ /*                                                               */ /*****************************************************************/ SELECT   date , SUM (order_amount) , COUNT(*) FROM   sales_order_fact a , date_dim b WHERE a.order_date_sk = b.date_sk GROUP BY date ORDER BY date ; /* end of script                                                 */
image from book

Run the query using this command.

 mysql> \. c:\mysql\scripts\daily_aggregation.sql

Here is the output of this query.

 +----------------+--------------------+-----------+ | date           | SUM (order_amount) |  COUNT(*) | +----------------+--------------------+-----------+ | 2007-02-05     |           58000.00 |        10 | | 2007-02-06     |          195000.00 |         6 | +----------------+--------------------+-----------+ 2 rows in set (0.03 sec)

The query result shows the daily total order amounts (sum) and the number of orders (count) of all orders.

Annual Aggregation

The dimensional query in Listing 4.3 gives you the annual sales summary. The order amounts and the number of orders are not only aggregated by date, but also by product and customer city. The three joins, between the fact table and each of the three dimension tables (date, product, and customer dimensions), are on the surrogate keys.

Listing 4.3: Annual aggregation

image from book
 /*****************************************************************/ /*                                                               */ /* annual_aggregation.sql                                        */ /*                                                               */ /*****************************************************************/ SELECT year, product_name, customer_city, SUM (order_amount),   COUNT(*) FROM   sales_order_fact a , date_dim b , product_dim c , customer_dim d WHERE     a.order_date_sk = b.date_sk AND a.product_sk = c.product_sk AND a.customer_sk = d.customer_sk GROUP BY year, product_name, customer_city ORDER BY year, product_name, customer_city ; /* end of script                                                 */
image from book

Run the script as follows:

 mysql> \. c:\mysql\scripts\annual_aggregation.sql

Here is the output of the query

 +------+-----------------+---------------+---------------+---------+ | year | product_name    | customer_city | SUM           |COUNT(*) | |      |                 |               |(order_amount) |         | +------+-----------------+---------------+---------------+---------+ | 2007 | Floppy Drive    | Mechanicsburg |      70000.00 |       5 | | 2007 | Floppy Drive    | Pittsburgh    |       8000.00 |       1 | | 2007 | Hard Disk Drive | Mechanicsburg |      46000.00 |       2 | | 2007 | Hard Disk Drive | Pittsburgh    |      34000.00 |       3 | | 2007 | LCD Panel       | Mechanicsburg |      61000.00 |       3 | | 2007 | LCD Panel       | Pittsburgh    |      34000.00 |       2 | +------+-----------------+---------------+---------------+---------+ 6 rows in set (0.03 sec)

The query result presents the annual total order amounts (sum) and number of orders (count) of all orders grouped by years, products, and cities.



Dimensional Data Warehousing with MySQL. A Tutorial
Dimensional Data Warehousing with MySQL: A Tutorial
ISBN: 0975212826
EAN: 2147483647
Year: 2004
Pages: 149

Similar book on Amazon
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
Successful Business Intelligence: Secrets to Making BI a Killer App
Successful Business Intelligence: Secrets to Making BI a Killer App

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net