mysql - SQL - 添加到现有平均值

Question

我正在尝试构建一个报告表来跟踪服务器流量和总体受欢迎程度。每个 SID 都是托管特定游戏的唯一游戏服务器，每个 UCID 都是连接到该服务器的唯一玩家 key 。

假设我有一个这样的表:

SID  UCID            AvgTime  NumConnects 
-----------------------------------------
1    AIE9348ietjg    300.55   5  
1    Po328gieijge    500.66   7
2    AIE9348ietjg    234.55   3
3    Po328gieijge    1049.88  18    

我们可以看到有 2 个独特的玩家和 3 个独特的服务器，SID 1 有 2 个玩家在过去的某个时间连接到它。 AvgTime 是这些玩家在该服务器上花费的平均时间(以秒为单位)，NumConnects 是平均值的大小(即 300.55 是 5 个元素的平均值)。

现在我在后台运行一个作业，处理原始连接表并像这样拉出播放器连接:

SID  UCID            ConnectTime  DisconnectTime 
-----------------------------------------
1    AIE9348ietjg    90.35         458.32
2    Po328gieijge    30.12         87.15
2    AIE9348ietjg    173.12        345.35 

此表没有 ID 或其他有助于简化我的示例的内容。该表中可能有多个播放器的连接/断开记录。我想要做的是将这些新值添加到每个 SID 的现有 AvgTime 中。

我正在尝试使用这里的一个公式(取自这个数学堆栈交换:https://math.stackexchange.com/questions/1153794/adding-to-an-average-without-unknown-total-sum/1153800#1153800)

平均值 =(平均值 * 尺寸 + 新值)/尺寸 + 1

我如何编写更新查询来更新上面的每个 ServerIDs 流量表，并使用上面的公式为每对记录添加到平均值。我尝试了类似以下的方法，但没有成功(返回 null):

UPDATE server_traffic st
LEFT JOIN connect_log l
    ON st.SID = l.SID AND st.UCID = l.UCID
    SET AvgTime = (AvgTime * NumConnects + SUM(l.DisconnectTime - l.ConnectTime) / NumConnects + COUNT(l.UCID)

我更喜欢 MySql 中的答案，但我也接受 MS SQL。

编辑

我了解统计信息和计算通常不会存储在表格中，您可以运行会为您处理数字的报告。我的需求是用户可以去一个网站，查看各个服务器的热度。这需要以一种方式完成

答:为每个用户运行一个复杂的查询不会导致系统崩溃或减慢

B:页面最多在几秒内返回数据

请在此处查看此示例:https://bf4stats.com/pc/shinku555555

这是《战地风云 4》统计数据的网页 - 请注意，该玩家的加载几乎是即时的，我无需等待某些复杂的报告查询返回数据就可以取回大量统计数据。我假设他们将这些计算存储在预处理表中，网页只需要进行简单的选择即可返回值。这与我想在我的数据库和 Web 应用程序设计中采用的方法相同。

很抱歉，如果这与原始问题无关 - 但希望这会增加额外的上下文以帮助人们理解我的需求。

Answer 1

由于您不能在 SQL 的单元级别单独运行聚合函数，如 SUM 和 COUNT 但包含在聚合查询中，请考虑加入聚合子查询更新...左连接。此外，调整 SET 中的括号以匹配上述公式。

另外，请注意，由于您使用了 LEFT JOIN，具有不匹配 ID 的行将为聚合字段呈现 NULL，并且该实体不能用于算术运算，并且将返回 NULL。您可以使用 IFNULL() 转换为零，但可能会因公式除法而失败。

UPDATE server_traffic s
LEFT JOIN 
  (SELECT SID, UCID, COUNT(UCID) As GrpCount, 
          SUM(DisconnectTime - ConnectTime) AS SumTimeDiff
   FROM connect_log
   GROUP BY SID, UCID) l
ON s.SID = l.SID AND s.UCID = l.UCID

SET s.AvgTime = (s.AvgTime * s.NumConnects + l.SumTimeDiff) / s.NumConnects + l.GrpCount

此外 - 重新考虑在表中保存计算/统计数据，因为它们始终可以通过查询运行，甚至通过时间戳。理想情况下，数据库表应存储原始值。