Hierarchical Queries
In this section I present two example queries that use the two hierarchical paths of the month dimension. The first query, shown in Listing 16.4, drills on the year-quarter-month path. This query is similar to the drilling query in Chapter 15, “Dimension Hierarchies” except that this one queries the month_end_sales_order_fact table, whereas the one in Chapter 15 queried the sales_order_fact table.
Listing 16.4. Quarter path drilling query
/******************************************************************/
/* */
/* quarter_path.sql */
/* */
/******************************************************************/
USE dw;
SELECT
product_category
, time
, order_amount
, order_quantity
FROM (
( SELECT
product_category
, year
, 1 month
, year time
, 1 sequence
, SUM(month_order_amount) order_amount
, SUM(month_order_quantity) order_quantity
FROM
month_end_sales_order_fact a
, product_dim b
, month_dim c
WHERE
a.product_sk = b.product_sk
AND a.month_order_sk = c.month_sk
AND year = 2006
GROUP BY
product_category
, year
)
UNION ALL
( SELECT
product_category
, year
, month
, quarter time
, 2 sequence
, SUM(month_order_amount) order_amount
, SUM(month_order_quantity) order_quantity
FROM
month_end_sales_order_fact a
, product_dim b
, month_dim c
WHERE
a.product_sk = b.product_sk
AND a.month_order_sk = c.month_sk
AND year = 2006
GROUP BY product_category, year, quarter
)
UNION ALL
( SELECT
product_category
, year
, month
, month_name time
, 3 sequence
, SUM(month_order_amount) order_amount
, SUM(month_order_quantity) order_quantity
FROM
month_end_sales_order_fact a
, product_dim b
, month_dim c
WHERE
a.product_sk = b.product_sk
AND a.month_order_sk = c.month_sk
AND year = 2006
GROUP BY
product_category
, year
, quarter
, month
)
) x
ORDER BY
product_category
, year
, month
, sequence
;
/* end of script */ |
Run the script in Listing 16.4 using this command.
mysql> . c:mysqlscriptsquarter_path.sql
Here is the query result.
Database changed +------------------+-----------+--------------+----------------+ | product_category | time | order_amount | order_quantity | +------------------+-----------+--------------+----------------+ | Monitor | 2006 | 8500.00 | NULL | | Monitor | 1 | 1000.00 | NULL | | Monitor | January | 1000.00 | NULL | | Monitor | 2 | 2500.00 | NULL | | Monitor | April | 2500.00 | NULL | | Monitor | 3 | 4000.00 | NULL | | Monitor | July | 4000.00 | NULL | | Monitor | 4 | 1000.00 | NULL | | Monitor | October | 1000.00 | NULL | | Storage | 2006 | 15000.00 | NULL | | Storage | 1 | 3000.00 | NULL | | Storage | February | 1000.00 | NULL | | Storage | March | 2000.00 | NULL | | Storage | 2 | 6500.00 | NULL | | Storage | May | 3000.00 | NULL | | Storage | June | 3500.00 | NULL | | Storage | 3 | 5500.00 | NULL | | Storage | August | 4500.00 | NULL | | Storage | September | 1000.00 | NULL | +------------------+-----------+--------------+----------------+ 19 rows in set (0.02 sec)
The second query, presented in Listing 16.5, drills the campaign session year-campaign-month hierarchy. This query has the same structure as the first one, except that it groups by campaign and not by quarter.
Listing 16.5. Drilling the campaign session path
/******************************************************************/
/* */
/* campaign_session_path.sql */
/* */
/******************************************************************/
SELECT
product_category pc
, time
, order_amount amt
, order_quantity qty
FROM (
( SELECT
product_category
, year
, 1 month
, year time
, 1 sequence
, SUM(month_order_amount) order_amount
, SUM(month_order_quantity) order_quantity
FROM
month_end_sales_order_fact a
, product_dim b
, month_dim c
WHERE
a.product_sk = b.product_sk
AND a.month_order_sk = c.month_sk
AND year = 2006
GROUP BY
product_category
, year
)
UNION ALL
( SELECT
product_category
, year
, month
, campaign_session time
, 2 sequence
, SUM(month_order_amount) order_amount
, SUM(month_order_quantity) order_quantity
FROM
month_end_sales_order_fact a
, product_dim b
, month_dim c
WHERE
a.product_sk = b.product_sk
AND a.month_order_sk = c.month_sk
AND year = 2006
GROUP BY
product_category
, year
, campaign_session
)
UNION ALL
( SELECT
product_category
, year
, month
, month_name time
, 3 sequence
, SUM(month_order_amount) order_amount
, SUM(month_order_quantity) order_quantity
FROM
month_end_sales_order_fact a
, product_dim b
, month_dim c
WHERE
a.product_sk = b.product_sk
AND a.month_order_sk = c.month_sk
AND year = 2006
GROUP BY
product_category
, year
, campaign_session
, month_name
)
) x
ORDER BY
product_category
, year
, month
, sequence
;
/* end of script */ |
Run the query in Listing 16.5 using this command.
mysql> . c:mysqlscriptscampaign_session_path.sql
Here is the query result.
+------------+----------------------+----------+------+ | pc | time | amt | qty | +------------+----------------------+----------+------+ | Monitor | 2006 | 8500.00 | NULL | | Monitor | 2006 First Campaign | 3500.00 | NULL | | Monitor | January | 1000.00 | NULL | | Monitor | April | 2500.00 | NULL | | Monitor | 2006 Second Campaign | 4000.00 | NULL | | Monitor | July | 4000.00 | NULL | | Monitor | 2006 Last Campaign | 1000.00 | NULL | | Monitor | October | 1000.00 | NULL | | Storage | 2006 | 15000.00 | NULL | | Storage | 2006 First Campaign | 3000.00 | NULL | | Storage | February | 1000.00 | NULL | | Storage | March | 2000.00 | NULL | | Storage | 2006 Second Campaign | 6500.00 | NULL | | Storage | May | 3000.00 | NULL | | Storage | June | 3500.00 | NULL | | Storage | 2006 Third Campaign | 4500.00 | NULL | | Storage | August | 4500.00 | NULL | | Storage | 2006 Last Campaign | 1000.00 | NULL | | Storage | September | 1000.00 | NULL | +------------+----------------------+----------+------+ 19 rows in set (0.00 sec)
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.