Specific Queries
A specific query selects and aggregates the facts on a specific dimension value. The two examples show the application of dimensional queries in specific queries.
Monthly Storage Product Sales
The monthly_storage.sql script in Listing 4.4 aggregates sales amounts and the number of orders every month.
Listing 4.4. Specific query (monthly storage product sales)
/******************************************************************/
/* */
/* monthly_storage.sql */
/* */
/******************************************************************/
USE dw;
SELECT
product_name
, month_name
, year
, SUM(order_amount)
, COUNT(*)
FROM
sales_order_fact a
, product_dim b
, date_dim c
WHERE
a.product_sk = b.product_sk
AND a.order_date_sk = c.date_sk
GROUP BY
product_name
, product_category
, month_name
, year
HAVING product_category = 'Storage'
ORDER BY
year
, month_name
;
/* end of script */ |
Run the script using this command.
mysql> . c:mysqlscriptsmonthly_storage.sql
Here is the output of the query:
Database changed +-----------------+------------+------+------------------+---------+ | product_name | month_name | year |SUM(order_amount) | COUNT(*)| +-----------------+------------+------+------------------+---------+ | Hard Disk Drive | February | 2007 | 65000.00 | 2 | | Floppy Drive | February | 2007 | 55000.00 | 2 | | Hard Disk Drive | February | 2007 | 15000.00 | 3 | | Floppy Drive | February | 2007 | 23000.00 | 4 | +-----------------+------------+------+------------------+---------+ 4 rows in set (0.00 sec)
The query result shows the monthly total order amounts (sum) and the number of orders (count), grouped by the individual storage products.
Quarterly Sales in Mechanisburg
The query in Listing 4.5 is another specific query. It produces the quarterly aggregation of the order amounts in Mechanicsburg.
Listing 4.5. Specific query (quarterly sales in Mechanicsburg)
/******************************************************************/
/* */
/* quarterly_mechanicsburg.sql */
/* */
/******************************************************************/
USE dw;
SELECT
customer_city
, quarter
, year
, SUM(order_amount)
, COUNT(order_sk)
FROM
sales_order_fact a
, customer_dim b
, date_dim c
WHERE
a.customer_sk = b.customer_sk
AND a.order_date_sk = c.date_sk
GROUP BY
customer_city
, quarter
, year
HAVING customer_city = 'Mechanicsburg'
ORDER BY
year
, quarter;
/* end of script */ |
Run the script using this command.
mysql> . c:mysqlscriptsquarterly_mechanicsburg.sql
Here is the query result.
Database changed +---------------+---------+------+-----------------+---------------+ | customer_city | quarter | year |SUM(order_amount)|COUNT(order_sk)| +---------------+---------+------+-----------------+---------------+ | Mechanicsburg | 4 | 2007 | 177000.00 | 10 | +---------------+---------+------+-----------------+---------------+ 1 row in set (0.00 sec)
The query result shows the quarterly total order amounts (sum) and the number of orders (count) for Mechanicsburg.