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_order_fact table and date_dim table is on their surrogate keys.
Listing 4.2. Daily Aggregation
/******************************************************************/ /* */ /* 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 */ |
Run the query using this command.
mysql> . c:mysqlscriptsdaily_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
/******************************************************************/
/* */
/* 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 */ |
Run the script as follows:
mysql> . c:mysqlscriptsannual_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.