我正在构建一个零件库存系统,并有一个交易表来存储物品的使用频率。
假设项目 A 在 2021 年使用了 10 次,在 2022 年使用了 20 次,在 2023 年使用了 30 次。当然,每笔交易都在每年的不同日期进行。所以,通过使用
SELECT ItemNumber, SUM(QuantityUsed), YEAR(DateUsed)
FROM ItemTransactions
GROUP BY ItemNumber, YEAR(DateUsed)
WHERE ItemNumber = 1001
我可以知道每年使用了多少个。我的问题是如何从该数据集中获得平均年使用量?我一直在尝试使用子查询,但未能使其正常工作。
谢谢!
这里有几种给猫剥皮的方法。
1. 派生表
使用您的查询作为表并对结果应用额外的分组和聚合:
2.热膨胀系数
(在其他答案中也建议了。)
这与派生表选项基本相同,只是嵌套使用不同的语法表示:
YEAR(DateUsed)
对我在上面两个选项中所做的原始查询的一个轻微更正是,我从 SELECT 子句中删除了它,因为对于平均计算来说是不必要的。(不过,它确实需要保留在内部 GROUP BY 中。)这里还有一些不涉及嵌套的。
3.窗口聚合+DISTINCT
您可以将 的窗口版本
AVG
直接应用于SUM
表达式。结果将是将每个总和替换为相同的平均值ItemNumber
,并且您将获得包含多个重复项的行集。您可以通过将 SELECT 替换为 SELECT DISTINCT 来抑制重复项:4. SUM() / 计数(不同)
您还可以提醒自己,
QuantityUsed
per的年平均值本质上是per 的ItemNumber
总数除以 per 的不同年份的总数,并相应地重写查询:QuantityUsed
ItemNumber
ItemNumber
您可以使用 CTE 来实现这一点,如果您需要多个项目的信息,则外部选择的分组依据非常有用
到目前为止,此处发布的所有答案都假设未使用该项目的年份不应计入平均值。
换句话说,如果每年的使用量为
10, 0, 10
,您将得到的答案将为 10 (而不是 6.66)。如果您有物品列在库存中的某处,但实际上并未在交易中使用,您将不会得到它们的任何答案(而不是获取旁边带有零的物品 ID)。
这一切可能实际上是您想要的,或者对您的系统来说可能不是问题,因为大多数项目自引入以来实际上每年都在使用,并且您不关心偶尔的异常值。在这种情况下,请使用上面的任何答案。
如果您希望所有年份和所有项目都对平均值有所贡献,即使是那些没有任何交易的年份和项目,请使用下面的查询之一。
固定年限
此查询计算固定时间范围内系统中每个项目的年平均消耗量。由于系统不知道您想要计算平均值的时间跨度(也许您想要自 1900 年以来 iPhone 的平均年消耗量),因此您需要将其显式提供给查询。
请注意,在计算年度平均值时,您可能需要忽略当年的记录,因为它尚未完成并且可能会扭曲您的数据。
对于从未在交易中消耗过的项目,这也将为您提供正确的答案为零,而不是从结果集中忽略它们。
第一笔交易作为开始日期
您可能希望将与该项目发生的第一笔交易视为其引入日期。在这种情况下,请使用:
实际日期
您的系统还可能存储每个项目的实际推出日期和可能的停产日期。在这种情况下,只需将这些日期放入子句
DATEDIFF
中的函数中即可SELECT
。