友情提示:此篇文章大约需要阅读 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);
发表评论