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

mysql:用不存在的数据的占位符初始化摘要表

triggers stored-procedures mysql

AvatarKava · 技术社区 · 17 年前

我会尽我所能地将它一般化,这样它就可以重用了。

我正在运行一个具有相当大的MySQL数据库的站点,该数据库已经增长到需要初始化一些汇总/汇总表。为了这个例子,我们假设它是足球统计数据。因为我在同一个数据库中处理多个足球联赛,所以他们中的许多人都玩不同长度的比赛——例如,室内足球联赛玩四个四分之一的比赛,而大多数室外联赛玩半个。

我有三张对这个练习很重要的桌子。我已经修改了所有我认为对我正在寻找的答案不重要的领域。

GAME
`game`.id
`game`.home_team_id
`game`.away_team_id
`game`.number_of_periods

GOAL 
// Records for each goal scored in the game   
`goal`.id
`goal`.game_id
`goal`.team_id
`goal`.period_number
`goal`.player_id
`goal`.assist_player_id

PERIOD_SUMMARY
`period`.id
`period`.game_id
`period`.team_id
`period`.number
`period`.goals_scored

最后,我应该在时段汇总表中记录下每个时段的比赛记录,不管是否有进球。此表只需要初始化一次,因为通过游戏创建时的触发器添加适当的零填充记录和触发插入/更新请求来更新period_摘要表非常容易。

对我来说,将所有目标分组并用sum()初始化周期汇总表也相当容易,因为我很难找到一种有效的方法来“填充”没有目标得分为0的任何周期。

我想知道的是,是否更容易/更有效地:

编写触发器并用0填充的值预先填充整个期间摘要表,然后运行我已经知道的查询以更新目标计分期间的相应记录。
使用其他方法(可能是临时存储过程?)如果目标表中没有匹配项,则只填充0条记录。

3 回复 | 直到 17 年前

tpdi 17 年前

您已经有了一个占位符。“占位符” 未知的 SQL中的“数据”为空。

您不需要预先填充任何内容:要么您有一行,其中某些列的值未知(空),要么您根本没有行,这样做一个外部联接将得到一个全为空的行。不管怎样, 属性数据 (本质上,非ID字段)将为空。

以及 sum() 聚合将忽略空值。

所以说你做有一个游戏行(因为它是预先安排的),但没有相应的行为它的周期(因为他们还没有被玩)。然后你做一个从外部加入到周期的游戏(外部,这样你就包括了两个游戏具有游戏没有期间数据):

select a.*, sum(b.goals_scored)
from game a left outer join period b on (b.game_id = a.id)
group by a.id;

这将按游戏显示总目标(对于两个团队);对于没有句点的游戏,返回空值(这在SQL中意味着,“我们还不知道”)。

此查询只显示已完成游戏的总目标 和正在进行的游戏(至少玩过一段时间的游戏) :

select a.*, sum(b.goals_scored)
from game a join period b on (b.game_id = a.id)
group by a.id;

此视图过滤掉未完成的游戏(假设您总是在后期之前添加早期时段):

create view complete_games as
select a.* from games a
where exists (select * from period b 
where b.game_id = a.id and b.number = a.number_of_periods)

使用该视图,我们可以只对已完成的游戏进行求和:

select a.*, sum(b.goals_scored)
from complete_games a join period b on (b.game_id = a.id)
group by a.id;

因此,不需要预先填充,不需要触发,最重要的是,不需要添加错误数据(在实际上还没有执行期间的情况下声明零目标),不需要使用正确的数据更新。只需在有数据时插入句点即可。

Martin v. Löwis 17 年前

选项1显然更容易:如果您可以信任计数器已经存在,那么您已经知道如何增加计数器的通气量。假设你将使用选项2,不仅很难填写缺少的零(我假设应该在期末完成),如果没有前一个条目并且第一个目标得分,你还必须找到一种方法以1开始计数器。

至于空间效率:最终,无论哪种方式,您都需要相同的磁盘空间。只在一个周期结束时填充零会稍微更有效一些,但可以肯定的是,完成周期所占的空间比运行周期所占的空间要大。

至于插入/更新效率:无论哪种方式,当目标得分时,都需要执行查找,因为可能已经有一个非零计数器了。因此,您需要创建一个索引,允许按游戏、团队和时段进行有效的查找。考虑到总是进行更新的查询较短,很有可能它也更高效。

Philippe Carriere 17 年前

您知道可以设置列的默认值吗?使用默认关键字。

前任。:

创建表人员( Age Int默认值为0, 名称varchar(35)默认为“bob” )

默认值将是您想要的值(例如0),而不是空值。

它并不能解决所有问题,但会有所帮助。