按问题分组的Sql查询，创建月平均值

Question

我正试图编译一个表，其中显示三列:产品名称、月平均销售量及月平均销售价格

我用的是adventureworks 2019。

根据我以前收到的一些帮助，我写了下面的查询。我已经将每个产品的订单数量和单价相加，然后在外部查询中取平均值。

SELECT
Product_Name,
AVG(Sales_Volume) AS Avg_Sales_Volume,
AVG(Price) AS Avg_Price

FROM
(SELECT
PP.[Name] AS Product_Name,
SUM(SSOD.[OrderQty]) AS Sales_Volume,
SUM(SSOD.[UnitPrice]) AS Price,
FORMAT(SSOH.[OrderDate], 'MM-yyyy') AS Month_Year
FROM
[Sales].[SalesOrderHeader] AS SSOH
LEFT JOIN Sales.SalesOrderDetail AS SSOD
ON SSOH.SalesOrderID = SSOD.SalesOrderID
LEFT JOIN production.product AS PP
ON SSOD.ProductID = PP.ProductID
GROUP BY PP.[Name], SSOH.[OrderDate]) AS T
--WHERE Product_Name = 'Road-150 Red, 44' (doing this for reference)
GROUP BY T.Product_Name, Month-Year

如果我按月-年分组，我得到大约3600行，如果我不分组，我得到266行。我对此感到困惑。基本上，我不确定它实际上显示了什么。对于你们中的一些人来说，这似乎是非常基本的，但我感觉我无法理解它。

谁能花点时间给我解释一下吗?

感谢

---

Product_Name    Avg_Sales_Volume    Avg_Price
LL Mountain Frame - Silver, 48  14  844.96
LL Touring Frame - Blue, 50 26  2100.546
Women's Mountain Shorts, L  13  161.494
Road-550-W Yellow, 44   3   1890.7332
HL Road Frame - Red, 48 18  6025.3137
Mountain-500 Silver, 42 8   1395.0116

(266行)按月-年也查询组:

Product_Name    Avg_Sales_Volume    Avg_Price
Road-150 Red, 44    1   6758.9544
HL Mountain Frame - Silver, 46  15  4465.6362
AWC Logo Cap    14  76.4672
Long-Sleeve Logo Jersey, L  2   102.611
Road-150 Red, 56    2   6817.546
Mountain-500 Silver, 52 13  2118.7125
LL Touring Frame - Yellow, 62   62  5001.30
ML Mountain Frame-W - Silver, 40    115 6546.3382

(3862行)

Answer 1

先做一些注释，然后解释一下你所看到的。

首先，您的外部查询具有带破折号的GROUP BY Month-Year，但我认为这打算是子查询中的列Month_Year(带下划线)。

接下来，我喜欢您使用AS来显式地定义列上的别名，以及在对象名称周围使用方括号[和]而不是用引号括起来的标识符。

最后，开始练习在代码中使用模式标识符和别名标识符——特别是在处理子查询时。它们将使应该返回的对象更加清晰，并将防止"模棱两可的列"。错误。

解释:两个查询返回的行数的差异是由于这些查询指示分组发生的方式。

GROUP BY T.Product_Name;返回266行，因为它聚合了给定产品在所有时间内的所有销量和定价信息。将子查询更改为:

SELECT COUNT(DISTINCT pp.Name)
FROM [Sales].[SalesOrderHeader] AS SSOH
LEFT JOIN Sales.SalesOrderDetail AS SSOD
ON SSOH.SalesOrderID = SSOD.SalesOrderID
LEFT JOIN production.product AS PP
ON SSOD.ProductID = PP.ProductID

返回266行结果，表明该集合中包含266个不同的产品名。

当你添加更多的分组条件时(比如T.Month_Year)，你是在告诉引擎创建"子组"。在聚合结构中。GROUP BY T.Product_Name, T.Month_Year为这些不同的266个产品名称中的每一个运行聚合，并为您的266个产品名称组的每个中出现的每个不同的T.Month_Year值聚合数据。

仔细检查这些行计数，双条件分组返回3862行，而单条件分组返回266行。在这3,862行中，有266个不同的产品名称平均代表14.52次(3862 / 266 = 15.518...)。如果您假设每个产品每月至少有一次销售，那么您可能会得出结论，我们在这里查看的是略多于一年的销售数据。更有可能的是，这是一组几年的销售数据，产品之间的销售量和频率有很大的变化。

附录:将GROUP BY列添加到SELECT中可以说明结果集的差异:

SELECT Product_Name,
T.Month_Year,
AVG(Sales_Volume) AS Avg_Sales_Volume,
AVG(Price) AS Avg_Price
FROM (SELECT PP.[Name] AS Product_Name,
SUM(SSOD.[OrderQty]) AS Sales_Volume,
SUM(SSOD.[UnitPrice]) AS Price,
FORMAT(SSOH.[OrderDate], 'MM-yyyy') AS Month_Year
FROM [Sales].[SalesOrderHeader] AS SSOH
LEFT JOIN Sales.SalesOrderDetail AS SSOD
ON SSOH.SalesOrderID = SSOD.SalesOrderID
LEFT JOIN production.product AS PP
ON SSOD.ProductID = PP.ProductID
GROUP BY PP.[Name], SSOH.[OrderDate]) AS T
--WHERE Product_Name = 'Road-150 Red, 44' (doing this for reference)
GROUP BY T.Product_Name,
T.Month_Year
ORDER BY Product_Name;

检查结果显示，每个产品名称也有该产品销售的任何月份的记录:

+--------------------------+-----------+-------+-----------+
|        ProductName       | Month_Year|Avg_Vol| Avg_Price |
+--------------------------+-----------+-------+-----------+
|  All-Purpose Bike Stand  |  12-2013  |   1   | 193.0714  |
|  All-Purpose Bike Stand  |  06-2014  |   1   | 218.625   |
|  All-Purpose Bike Stand  |  05-2014  |   1   | 187.909   |
|  All-Purpose Bike Stand  |  10-2013  |   1   | 212.00    |
|  AWC Logo Cap            |  02-2014  |   6   | 57.7928   |
|  AWC Logo Cap            |  02-2012  |   48  | 93.357    |
|  AWC Logo Cap            |  08-2011  |   68  | 103.73    |
|  AWC Logo Cap            |  01-2013  |   124 | 129.4896  |
|  AWC Logo Cap            |  03-2014  |   21  | 71.1747   |
+--------------------------+-----------+-------+-----------+