Debug客栈
做一个爱分享的程序猿
Debug客栈
数据仓库与数据挖掘-使用SQL语句实现AdventureWorksDW数据仓库的多维数据分析

准备工作

AdventureWork各种版本下载链接:

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

切片操作

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

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);
https://www.debuginn.cn/wp-content/uploads/2019/05/0-2.png

切块操作

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

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);
https://www.debuginn.cn/wp-content/uploads/2019/05/1-1.png

​旋转操作

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

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);
https://www.debuginn.cn/wp-content/uploads/2019/05/3-2.png

​旋转+切块

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);
https://www.debuginn.cn/wp-content/uploads/2019/05/4-2.png

​上转操作

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

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);
https://www.debuginn.cn/wp-content/uploads/2019/05/3-2.png

​下钻操作

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

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);
赞赏
本文链接:https://www.debuginn.cn/2392.html
本文采用 CC BY-NC-SA 3.0 Unported 协议进行许可,转载请保留此文章链接
相关链接: Debug客栈  |  Debug客栈引导页  |  加入交流群  |  赞助本站  |  博主微博  |  博主推特  |  音乐下载器
社会主义核心价值观: 富强、民主、文明、和谐、自由、平等、公正、法治、爱国、敬业、诚信、友善
首页      技术文章      数据分析      数据仓库与数据挖掘-使用SQL语句实现AdventureWorksDW数据仓库的多维数据分析

发表评论

textsms
account_circle
email

Debug客栈

数据仓库与数据挖掘-使用SQL语句实现AdventureWorksDW数据仓库的多维数据分析
准备工作 AdventureWork各种版本下载链接: 此操作数据库版本为:2014版本。 切片操作 进行切片操作切片。选择地点维、产品维和时间维查看2012年3月份的销售额 SELECT D…
扫描二维码继续阅读
2019-03-27