我正在尝试构建一个报告表来跟踪服务器流量和总体流行度。每个sid是一个托管特定游戏的唯一游戏服务器,每个ucid是一个连接到该服务器的唯一玩家密钥。
假设我有一张这样的table:
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个惟一的服务器,sid1有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的答案,但我也会接受mssql。
编辑
我知道统计数据和计算通常不会存储在表中,您可以运行报告来处理数据。我的要求是,用户可以去一个网站,查看各种服务器的流行程度。这需要以一种
答:对每个用户运行一个复杂的查询不会使系统崩溃或减慢速度
b:页面最多在几秒钟内返回数据
请看下面的示例:https://bf4stats.com/pc/shinku555555
这是一个战地4统计数据的网页-注意,对于这个玩家来说,负载几乎是即时的,我不需要等待一些复杂的报告查询返回数据就可以得到一个负载的统计数据。我假设他们将这些计算存储在预处理的表中,网页只需要做一个简单的选择就可以返回值。这正是我在数据库和web应用程序设计中希望采用的方法。
抱歉,如果这是离题的原始问题-但希望这增加了额外的背景,帮助人们了解我的需要。
1条答案
按热度按时间svgewumm1#
因为你不能像这样运行聚合函数
SUM
以及COUNT
在sql的单元级别上,它们本身包含在聚合查询中,请考虑连接到UPDATE...LEFT JOIN
. 另外,调整中的括号SET
符合上述公式。另外,请注意
LEFT JOIN
,将呈现ID不匹配的行NULL
对于聚合字段,此实体不能用于算术运算,将返回NULL
. 您可以使用IFNULL()
但可能会因公式的除法而失败。旁白-重新考虑在表中保存计算/统计信息,因为它们总是可以通过查询运行,甚至可以通过时间戳运行。理想情况下,数据库表应该存储原始值。