Grouping and Drilling Queries
You can do grouping and drilling queries on hierarchies. A grouping query groups the facts on one or more levels of a dimension. The script in Listing 15.1is an example of a grouping query. It is a query that retrieves the sales amount grouped by products (product_category) and the three hierarchy levels (columns) of the date dimension (year, quarter, and month_name).
Listing 15.1. A grouping query
/******************************************************************/ /* */ /* grouping.sql */ /* */ /******************************************************************/ USE dw; SELECT product_category , year , quarter , month_name , SUM(order_amount) 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_category , year , quarter , month ORDER BY product_category , year , quarter , month ; /* end of script */ |
You can run the grouping.sql script in Listing 15.1 by using this command.
mysql> . c:mysqlscriptsgrouping.sql
If you’ve been following the instructions in Chapter 1 to Chapter 14, you’ll get the following output.
Database changed +----------------+------+---------+------------+-------------------+ |product_category| year | quarter | month_name | SUM(order_amount) | +----------------+------+---------+------------+-------------------+ | Monitor | 2005 | 1 | March | 4000.00 | | Monitor | 2005 | 3 | July | 6000.00 | | Monitor | 2006 | 1 | January | 1000.00 | | Monitor | 2006 | 2 | April | 2500.00 | | Monitor | 2006 | 3 | July | 4000.00 | | Monitor | 2006 | 4 | October | 1000.00 | | Monitor | 2007 | 1 | February | 4000.00 | | Monitor | 2007 | 1 | March | 32000.00 | | Peripheral | 2007 | 1 | March | 25000.00 | | Storage | 2005 | 2 | April | 4000.00 | | Storage | 2005 | 2 | May | 6000.00 | | Storage | 2005 | 3 | September | 8000.00 | | Storage | 2005 | 4 | November | 8000.00 | | Storage | 2006 | 1 | February | 1000.00 | | Storage | 2006 | 1 | March | 2000.00 | | Storage | 2006 | 2 | May | 3000.00 | | Storage | 2006 | 2 | June | 3500.00 | | Storage | 2006 | 3 | August | 4500.00 | | Storage | 2006 | 3 | September | 1000.00 | | Storage | 2007 | 1 | January | 4000.00 | | Storage | 2007 | 1 | February | 4000.00 | | Storage | 2007 | 1 | March | 46000.00 | +----------------+------+---------+------------+-------------------+ 22 rows in set (0.42 sec)
The grouping query output shows the measure (sales order amount) grouped along the year-quarter-month hierarchy on each row.
Like a grouping query, a drilling query also groups its facts on one or more levels of a dimension. However, unlike a grouping query that shows the grouped facts (e.g. the sum of order amounts) of only the dimension’s lowest level (e.g. the month level), a drilling query shows the grouped facts of each level of the dimension. The drilling query in Listing 15.2 shows the sum of order amounts at each of the date dimension levels (year, quarter, and month levels).
Listing 15.2. A drilling query
/******************************************************************/
/* */
/* drilling.sql */
/* */
/******************************************************************/
USE dw;
SELECT
product_category
, time
, order_amount
FROM (
( SELECT
product_category
, date
, year time
, 1 sequence
, SUM(order_amount) order_amount
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_category
, year
ORDER BY date
)
UNION ALL
( SELECT
product_category
, date
, quarter time
, 2 sequence
, SUM(order_amount)
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_category, year, quarter
ORDER BY date
)
UNION ALL
( SELECT
product_category
, date
, month_name time
, 3 sequence
, SUM(order_amount)
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_category
, year
, quarter
, month_name
ORDER BY date
)
) x
ORDER BY
product_category
, date
, sequence
, time
;
/* end of script */ |
You can run the drilling.sql script by using this command.
mysql> . c:mysqlscriptsdrilling.sql
Here is the output of the drilling query.
Database changed +------------------+-----------+--------------+ | product_category | time | order_amount | +------------------+-----------+--------------+ | Monitor | 2005 | 10000.00 | | Monitor | 1 | 4000.00 | | Monitor | March | 4000.00 | | Monitor | 3 | 6000.00 | | Monitor | July | 6000.00 | | Monitor | 2006 | 8500.00 | | Monitor | 1 | 1000.00 | | Monitor | January | 1000.00 | | Monitor | 2 | 2500.00 | | Monitor | April | 2500.00 | | Monitor | 3 | 4000.00 | | Monitor | July | 4000.00 | | Monitor | 4 | 1000.00 | | Monitor | October | 1000.00 | | Monitor | 2007 | 36000.00 | | Monitor | 1 | 36000.00 | | Monitor | February | 4000.00 | | Monitor | March | 32000.00 | | Peripheral | 2007 | 25000.00 | | Peripheral | 1 | 25000.00 | | Peripheral | March | 25000.00 | | Storage | 2005 | 26000.00 | | Storage | 2 | 10000.00 | | Storage | April | 4000.00 | | Storage | May | 6000.00 | | Storage | 3 | 8000.00 | | Storage | September | 8000.00 | | Storage | 4 | 8000.00 | | Storage | November | 8000.00 | | Storage | 2006 | 15000.00 | | Storage | 1 | 3000.00 | | Storage | February | 1000.00 | | Storage | March | 2000.00 | | Storage | 2 | 6500.00 | | Storage | May | 3000.00 | | Storage | June | 3500.00 | | Storage | 3 | 5500.00 | | Storage | August | 4500.00 | | Storage | September | 1000.00 | | Storage | 2007 | 54000.00 | | Storage | 1 | 54000.00 | | Storage | January | 4000.00 | | Storage | February | 4000.00 | | Storage | March | 46000.00 | +------------------+-----------+--------------+ 44 rows in set (0.03 sec)
Note
Drilling queries use the UNION set operator. Each of the three unions in the drilling query in Listing 15.2 gives you the rows of each of the three levels. The sequence column helps order the monthly sales orders from the year to the quarter to the month.