准备工作
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);
|