数据仓库与数据挖掘 使用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); 切块操作 切块操作切块。选择地点维、产品维和时间维查看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); 关注微信公众号，第一时间获取最新内容，让我们一起变得更强！Debug客栈：订阅本站· 文章归档· 我的项目· 友情链接· 我的使用· 飞湾计划· 摄影展集· 我的主页· 知盈官网