首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >数据仓库与数据挖掘-使用SQL语句实现AdventureWorksDW数据仓库的多维数据分析

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

作者头像
Meng小羽
发布2019-12-23 17:02:44
1K0
发布2019-12-23 17:02:44
举报
文章被收录于专栏:Debug客栈Debug客栈

友情提示:此篇文章大约需要阅读 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://cloud.tencent.com/developer/article/1558131

本文采用CC BY-NC-SA 3.0 Unported协议进行许可,转载请保留此文章链接

本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 准备工作
  • 切片操作
  • 切块操作
  • ​旋转+切块
  • ​上转操作
  • ​下钻操作
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档