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.1 is 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:\mysql\scripts\grouping.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:\mysql\scripts\drilling.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. |
EAN: 2147483647
Pages: 149