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

友情提示:此篇文章大约需要阅读 9分钟21秒,不足之处请多指教,感谢你的阅读。 订阅本站

准备工作

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

切块操作

切块操作切块。选择地点维、产品维和时间维查看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);

​旋转操作

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

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

​旋转+切块

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

​上转操作

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

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

​下钻操作

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

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 协议进行许可,转载请保留此文章链接
首页      技术文章      数据分析      数据仓库与数据挖掘 使用SQL语句实现AdventureWorksDW数据仓库的多维数据分析

发表评论

textsms
account_circle
email

Debug客栈

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