sql 所有行的列值相同并经常更新
我想要实现的目标:
我有一个表格,表示每个 ID 的 ID 和积分。
我想对每个 ID 进行评分,rate(ID)=Credit(ID)/sum(Credit(ID))
总和是所有 ID 的总和
我将非常频繁地更新表格,并希望通过创建另一列并将此总和存储在表格中(例如 sigmaID
)来保持 sum(Credit(ID))
方便,所有行应始终具有完全相同的值。
每当我更改 ID 的 Credit(例如添加 100)时,我都可以简单地对此列值执行相同的操作(添加 100)。
- 我是否必须更新所有行的
sigmaID
?会有效率吗? - 我想定期检查
sigmaID
是否确实是sum(Credit(ID))
以确保一致性,我是否做得太过分了?会不会效率低下? - 还有其他方法吗(我担心效率)?
请提供纯 SQL 查询,因为我需要将所有这些放入 UPDATE 触发器中,该触发器将计算此评级(以及使用其他 ID 参数加载其他公式)。我可能可以访问脚本语言(PHP/python),但我不确定。因此是纯 SQL 请求。
What I want to achieve:
I have a table denoting ID and Credits of each individual ID.
I want to rate each ID as, rate(ID)=Credit(ID)/sum(Credit(ID))
the sum is over all IDs
I will be updating the table quite frequently and want to keep the sum(Credit(ID))
handy by creating another column and storing this sum in the table (say sigmaID
), which should always have exact same value for all rows.
Whenever I change Credit for an ID (say add 100), I can simply do the same operation on this column value (add 100)
- Do I have to update
sigmaID
for all rows? Will it be efficient? - I would like to periodically check if
sigmaID
is indeedsum(Credit(ID))
for consistency , am I overdoing it? Will it inefficient? - Is there any other approach to this (I am worried about efficiency)?
Kindly provide pure SQL queries as I need to put all of this in an UPDATE
trigger which will calculate this rating (and loads of other formulas with other parameters of ID). I may have access to scripting language (PHP/python) but I don't know for sure. Hence the pure SQL request.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
不幸的是我的英语非常非常差。
正如我所意识到的,您想获得有关过去和现在的列值的所有信息吗?
你想记录它吗?如果是这样,您可以创建日志表并通过触发器在其中记录您想要的所有内容。
最好的问候,
塔托·穆拉泽
Unfortunately my English is very very poor.
As i realized, you want to have all information about your column values, in past and present?
You want to log it ? If it so you can make journal table and log everything yo want in it by trigger.
best regards,
tato mumladze