mysql多维数据仓库指南--第一篇第4章(1)

再次感谢jiangdm的翻译和分享 :http://blog.chinaunix.net/uid-43642-id-2124577.html

第四章 维度查询

       维度查询是指在数据仓库中，通过一个或者多个代理键将事实表和它的维度表进行关联的查询。本章将教你维度查询的模式，以及以及如何应用其最常见的三种查询类型：聚集，特定和翻转。

       聚集查询将个体的事实进行合计，比如，通过累加度量值。在特定查询中，你通过指定维度值来查询事实表。虽然大部分的查询会指定一个或者多个维度的值作为查询标准（约束），翻转查询的约束则是度量值。理解了这三种最基本的查询类型，你也将能应用其他类型的维度查询模式。

 

应用维度查询

       本节我将向你解释如何应用维度查询中最基本的三种查询类型：聚集，特定和翻转。

       为了应用维度查询，你首先必须运行列表4-1中的脚本以向你的数据仓库增加数据记录。你需要这些新增的数据来测试脚本4-2――4-7中的维度查询。

       列表4-1 为测试维度查询增加数据的脚本：

/*****************************************************************/
/*                                                               */
/* dimensional_query_data.sql                                    */
/*                                                               */
/*****************************************************************/
 
USE dw;
 
INSERT INTO order dim VALUES
  (NULL, 11, CURRENT_DATE, '9999-12-31')
, (NULL, 12, CURRENT_DATE, '9999-12-31')
, (NULL, 13, CURRENT_DATE, '9999-12-31')
, (NULL, 14, CURRENT_DATE, '9999-12-31')
, (NULL, 15, CURRENT_DATE, '9999-12-31')
, (NULL, 16, CURRENT_DATE, '9999-12-31')
;
 
INSERT INTO date_dim VALUES
  (NULL, '20075-0211-016', 'FebruaryNovember', 112, 41, 20057,
      CURRENT_DATE, '9999-12-31')
;
 
INSERT INTO sales_order_fact VALUES
  (11, 1, 2, 2, 20000)
, (12, 2, 3, 2, 25000)
, (13, 3, 4, 2, 30000)
, (14, 4, 2, 2, 35000)
, (15, 5, 3, 2, 40000)
, (16, 1, 4, 2, 45000)
;
 

/* end of script 

       在你开始之前，改变你的mysql数据库日期为2007-02-06;然后运行列表4-1 的脚本插入6条记录到order_dim表，一个日期记录到date_dim表，6个订单记录到sales_order_fact表。

        mysql> \. c:\mysql\scripts\dimensional_query_data.sql

你将在mysql控制台上看到：

Database changed

Query OK, 6 rows affected (0.05 sec)

Records: 6  Duplicates: 0  Warnings: 0

 

Query OK, 1 row affected (0.06 sec)

 

Query OK, 6 rows affected (0.06 sec)

Records: 6  Duplicates: 0  Warnings: 0

现在你已经有了必需的数据，你已经可以准备应用之前提及的三种维度查询类型。

聚集查询

       聚集查询对个体的事实进行总结（聚集）。最典型的是累加度量值，甚至count（计数）也是一种常见的聚集。本节将讨论两个示例。

日营业统计

       列表4-2所列的维度查询给出日营业情况的总结。订单金额和数量将按日期（每天）进行合计。注意，sales_prder_fact 表和 date_dim 表间的连接将通过它们的代理键。

列表4-2 日合计

/*****************************************************************/
/*                                                               */
/* 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

用如下形式运行该查询

mysql> \. c:\mysql\scripts\daily_aggregation.sql

下面是查询的结果显示

+----------------+--------------------+-----------+

| date           | SUM (order_amount) |  COUNT(*) |

+----------------+--------------------+-----------+

| 2007-02-05     |           58000.00 |        10 |

| 2007-02-06     |          195000.00 |         6 |

+----------------+--------------------+-----------+

2 rows in set (0.03 sec)

该结果显示了每日所有订单总额（累加）和总数（计数）的值。

 

年度统计

       列表4-3将给出年度营业情况总结。订单总额和订单总数不仅根据日期还根据产品类型及客户城市进行汇总。在事实表和对应三个维表（日期，产品，客户维）之间的三个关联也是通过代理键。

列表4-3:年度汇总

/*****************************************************************/
/*                                                               */
/* 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        

按下面所示运行该脚本

mysql> \. c:\mysql\scripts\annual_aggregation.sql

结果显示如下：

+------+-----------------+---------------+---------------+---------+

| 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)

       该查询通过年，产品，和城市进行分组统计，给出了订单总额（累加）和订单总数（计数）的汇总值。

 

特定查询

       特定查询基于某个特定的维度值对事实进行挑选和聚集。下面将给出两个例子关于特定查询类型的维度查询。

每月存储类商品营业情况

       列表4-4所示的monthly_storage.sql脚本汇总每个月销售额和订单数。

列表4-4：特定查询(每月存储类商品营业情况)

/*****************************************************************/
/*                                                               */
/* 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       

用以下命令运行该脚本:

mysql> \. c:\mysql\scripts\monthly_storage.sql

结果显示如下：

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)

该结果显示了通过将每个存储类产品分组统计，每月的订单金额（累加）和订单总数（计数）的汇总值。

 

每季度Mechanisburg城市的营业情况

列表4-5的查询是另一个特定查询。它给出了每个季度在Mechanisburg城市发生的订单总数。

列表4-5：特定查询（每季度Mechanisburg城市的营业情况）

/*****************************************************************/
/*                                                               */
/* 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  

   

以如下命令运行该脚本:

mysql> \. c:\mysql\scripts\quarterly_mechanicsburg.sql

结果显示如下：

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)

该结果显示了每个季度的Mechanisburg城市的订单金额（累加）和订单总数（计数）的汇总值。