代码之家 › 专栏 › 技术社区 › Matthew Jones

为什么此SQL SUM语句正确?

sum sql

Matthew Jones · 技术社区 · 15 年前

在我的模式中,我有一个表项目和一个表任务。每个项目都由任务组成。每个任务都有小时和完成百分比。

实例表:

ProjectID    TaskID    Hours   PercentComplete
1            1         100     50
1            2         120     80

我正在努力使这个项目的加权百分比完成。我使用以下SQL语句执行此操作:

SELECT P.ProjectID, P.ProjectName, SUM(T.Hours) AS Hours, 
SUM(T.PercentComplete * T.Hours) / 100 AS CompleteHours, 
SUM(T.PercentComplete * T.Hours) / SUM(T.Hours) AS PercentComplete
FROM Projects AS P INNER JOIN
        Tasks AS T ON T.ProjectID = P.ProjectID
WHERE     (P.ProjectID = 1)

我的问题是关于这部分陈述:

SUM(T.PercentComplete * T.Hours) / SUM(T.Hours) AS PercentComplete

这给了我这个项目的正确的加权百分比(在样本数据高于66%的情况下)。但我似乎无法理解为什么会这样做。

为什么这个查询有效?

5 回复 | 直到 8 年前

Mark Byers 15 年前

SUM(T.PercentComplete * T.Hours) / 100 是完整小时数。
SUM(T.Hours) 是总小时数。
这两个金额的比率,即:
```
(SUM(T.PercentComplete * T.Hours) / 100) / SUM(T.Hours)
```
完成小时数的比例(应介于0和1之间)。
乘以100就得到了这个百分比。

我更喜欢将这样的百分比从数据库中保留出来,并将它们移到表示层。如果数据库存储了“完成的小时数”和“总小时数”,并且根本不存储百分比,则会容易得多。计算中额外的100个因素混淆了这个问题。

lavinio 15 年前

基本上,您是在总小时数上找到完成的小时数。

SUM(T.PercentComplete * T.Hours) 计算已完成的总小时数。 (100 * 50) = 50 * 100 + (120 * 80) = 146 * 100 是分子。 146 这项工作已经完成了几个小时,我们保留了 100 百分比乘数(因为它是 [0-100] 而不是 [0-1] )

然后我们发现总工作小时数, SUM(T.Hours) ,这就是 100 + 120 = 220 .

然后除以,我们得到加权平均数。 (146 * 100) / 220 = 0.663636364 * 100 = 66.4%

这就是你想知道的吗?

Martin Smith 15 年前

它通过将每一行的值相加,然后在末尾将它们分开,分别计算出这两个和。

总和(t.完成百分比*t.小时)

50*  100 +
80 * 120
-------
14,600

和(t小时)

100 +
120
---
220

然后是最后的分队

14,600 / 220
------------
66.3636

编辑根据HLGEM的评论,由于整数除法,它实际返回66。

GrandmasterB 15 年前

聚合函数(如sum())针对group by子句定义的数据集工作。因此,如果您按projectid、projectname分组,函数将按它进行分解。

marc_s 8 年前

求和运算首先乘以列,然后加上

( 100* 50+ 120* 80)  / (100+ 120)