代码之家 › 专栏 › 技术社区 › Igneous01

SQL-添加到现有平均值

aggregate-functions sql mysql

Igneous01 · 技术社区 · 6 年前

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

假设我有一张这样的桌子:

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是玩家在该服务器上花费的平均时间(以秒为单位),NumConnections是平均值的大小(即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。

编辑

我知道统计数据和计算数据通常不会存储在表格中,您可以运行报告来为您处理数字。我的要求是,用户可以访问网站,查看各种服务器的流行程度。这需要以一种

A: 每个用户运行一个复杂的查询不会使系统崩溃或减慢速度

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

请参见以下示例: https://bf4stats.com/pc/shinku555555

这是一个关于战场4统计数据的网页-请注意,对于这个玩家来说,加载几乎是即时的,我可以在不等待复杂的报告查询返回数据的情况下返回统计数据。我假设他们将这些计算存储在预处理的表中,网页只需进行简单的选择即可返回值。这与我希望在数据库和Web应用程序设计中采用的方法相同。

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

1 回复 | 直到 6 年前

Parfait 6 年前

因为您无法运行聚合函数,如 SUM 和 COUNT 在SQL的单元级别,但包含在聚合查询中,请考虑加入到 UPDATE...LEFT JOIN . 此外,调整括号 SET 以匹配上述公式。

另外,请注意,由于您使用 LEFT JOIN ,将呈现ID不匹配的行 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

旁白-重新考虑在表中保存计算/统计信息,因为它们总是可以通过查询运行,甚至可以通过时间戳运行。理想情况下,数据库表应该存储原始值。