数据仓库与数据挖掘使用SQL语句实现AdventureWorksDW数据仓库的多维数据分析

准备工作

AdventureWork各种版本下载链接：

此操作数据库版本为：2014版本。

切片操作

进行切片操作切片。选择地点维、产品维和时间维查看2012年3月份的销售额

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
SELECT 
	DimProduct.EnglishProductName AS 产品名称, 
	DimSalesTerritory.SalesTerritoryRegion AS 产品地区,
	MONTH(FactInternetSales.OrderDate) AS 月份,
	SUM(FactInternetSales.SalesAmount) AS 销售额
FROM DimProduct, DimSalesTerritory, FactInternetSales
WHERE
	DimProduct.ProductKey = FactInternetSales.ProductKey
	AND DimSalesTerritory.SalesTerritoryKey = FactInternetSales.SalesTerritoryKey
	AND MONTH(FactInternetSales.OrderDate) = 3
	AND YEAR(FactInternetSales.OrderDate) = 2012
GROUP BY 
	DimProduct.EnglishProductName,
	DimSalesTerritory.SalesTerritoryRegion,
	MONTH(FactInternetSales.OrderDate);

result

切块操作

切块操作切块。选择地点维、产品维和时间维查看2011年3月份和4月份的销售额

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
SELECT 
	DimProduct.EnglishProductName AS 产品名称, 
	DimSalesTerritory.SalesTerritoryRegion AS 产品地区,
	MONTH(FactInternetSales.OrderDate) AS 月份,
	SUM(FactInternetSales.SalesAmount) AS 销售额
FROM DimProduct, DimSalesTerritory, FactInternetSales
WHERE
	DimProduct.ProductKey = FactInternetSales.ProductKey
	AND DimSalesTerritory.SalesTerritoryKey = FactInternetSales.SalesTerritoryKey
	AND MONTH(FactInternetSales.OrderDate)BETWEEN 5 and 7
	AND YEAR(FactInternetSales.OrderDate) = 2012
GROUP BY 
	DimProduct.EnglishProductName,
	DimSalesTerritory.SalesTerritoryRegion,
	MONTH(FactInternetSales.OrderDate);

切块操作切块

旋转操作

旋转操作旋转。选择地点维、产品维和时间维,以地区维为主视图查看销售额

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT 
	DimSalesTerritory.SalesTerritoryRegion AS 产品地区,
	DimProduct.EnglishProductName AS 产品名称, 
	YEAR(FactInternetSales.OrderDate) AS 年份,
	MONTH(FactInternetSales.OrderDate) AS 月份,
	SUM(FactInternetSales.SalesAmount) AS 销售额
FROM 
	-- 产品表
	DimProduct,
	-- 销售地区表 
	DimSalesTerritory, 
	-- 销售量
	FactInternetSales
WHERE
	DimProduct.ProductKey = FactInternetSales.ProductKey
	AND DimSalesTerritory.SalesTerritoryKey = FactInternetSales.SalesTerritoryKey
	AND YEAR(FactInternetSales.OrderDate) = 2011
GROUP BY 
	DimProduct.EnglishProductName,
	DimSalesTerritory.SalesTerritoryRegion,
	YEAR(FactInternetSales.OrderDate),
	MONTH(FactInternetSales.OrderDate);

旋转操作旋转

旋转+切块

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT 
	DimSalesTerritory.SalesTerritoryRegion AS 产品地区,
	DimProduct.EnglishProductName AS 产品名称, 
	YEAR(FactInternetSales.OrderDate) AS 年份,
	MONTH(FactInternetSales.OrderDate) AS 月份,
	SUM(FactInternetSales.SalesAmount) AS 销售额
FROM 
	-- 产品表
	DimProduct,
	-- 销售地区表 
	DimSalesTerritory, 
	-- 销售量
	FactInternetSales
WHERE
	DimProduct.ProductKey = FactInternetSales.ProductKey
	AND DimSalesTerritory.SalesTerritoryKey = FactInternetSales.SalesTerritoryKey
	AND YEAR(FactInternetSales.OrderDate) BETWEEN 2011 AND 2014
GROUP BY 
	DimProduct.EnglishProductName,
	DimSalesTerritory.SalesTerritoryRegion,
	YEAR(FactInternetSales.OrderDate),
	MONTH(FactInternetSales.OrderDate);

旋转+切块

上钻操作

上钻。选择地点维、产品维和时间维查看不同年份的销售额

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT 
	DimProduct.EnglishProductName AS 产品名称, 
	DimSalesTerritory.SalesTerritoryRegion AS 产品地区,
	MONTH(FactInternetSales.OrderDate) AS 月份,
	SUM(FactInternetSales.SalesAmount) AS 销售额
FROM DimProduct, DimSalesTerritory, FactInternetSales
WHERE
	DimProduct.ProductKey = FactInternetSales.ProductKey
	AND DimSalesTerritory.SalesTerritoryKey = FactInternetSales.SalesTerritoryKey
GROUP BY 
	DimProduct.EnglishProductName,
	DimSalesTerritory.SalesTerritoryRegion,
	MONTH(FactInternetSales.OrderDate);

下钻操作

下钻。选择地点维、产品维和时间维查看不同日期的销售额

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT 
	DimProduct.EnglishProductName AS 产品名称, 
	DimSalesTerritory.SalesTerritoryRegion AS 产品地区,
	MONTH(FactInternetSales.OrderDate) AS 月份,
	SUM(FactInternetSales.SalesAmount) AS 销售额
FROM DimProduct, DimSalesTerritory, FactInternetSales
WHERE
	DimProduct.ProductKey = FactInternetSales.ProductKey
GROUP BY 
	DimProduct.EnglishProductName,
	DimSalesTerritory.SalesTerritoryRegion,
	MONTH(FactInternetSales.OrderDate);