友情提示:此篇文章大约需要阅读 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协议进行许可,转载请保留此文章链接