TL; DR:本文的大部分内容都是为了使我尽可能清楚而包含的示例,但是问题的核心包含在中间部分“实际问题”中,其中的示例被简化了。
我有一个数据库,其中包含有关足球比赛的数据,我试图从中提取一些统计数据。
该数据库仅包含一个称为“ allMatches”的表,其中每个条目都代表一个匹配项,该表的字段(我只是包括了必不可少的字段,以了解问题所在)是:
对于数据库中的每个条目,我都必须提取一些有关客队和主队的统计数据。当您考虑有关所有先前比赛的统计信息时,这很容易实现,例如,要获得进球得分和失球统计信息,请首先运行以下查询:
singleTeamAllMatches=
select ID as MatchID,
Date as Date,
HT as Team,
HG as Scored,
AG as Conceded
from allMatches
UNION ALL
select ID as MatchID,
Date as Date,
AT as Team,
AG as Scored,
HG as Conceded
from allMatches;
这不是绝对必要的,因为它只是以这种方式转换原始表:
this row in allMatches:
|ID |Date | HT |AT |HG | AG|
|42 |2011-05-08 |Genoa |Sampdoria | 2 | 1 |
"becomes" two rows in singleTeamAllMatches:
|MatchID |Date |Team |Scored|Conceded|
|42 |2011-05-08 |Genoa | 2 | 1 |
|42 |2011-05-08 |Sampdoria | 1 | 2 |
但允许我通过一个非常简单的查询来获取所需的统计信息:
select a.MatchID as MatchID,
a.Team as Team,
Sum(b.Scored) as totalScored,
Sum(b.Conceded) as totalConceded
from singleTeamAllMatches a, singleTeamAllMatches b
where a.Team == b.Team AND b.Date < a.Date
我最后得到一个查询,该查询在运行时返回:
换句话说,如果我在最后一个查询中获得:
|MatchID| Team |totalScored|totalConceded|
|42 | Genoa |38 | 40 |
|42 | Sampdoria |30 | 42 |
这意味着热那亚和桑普多利亚在ID为42的比赛中互相对战,在那场比赛之前,热那亚打进38球并失球40,而桑普多利亚则进30球并失球42。
现在,这非常容易,因为我考虑了所有以前的比赛,我不知道如何完成仅考虑6个以前的比赛就如何获得完全相同的统计信息。例如,假设在singleTeamAllMatches中,我有:
|MatchID |Date |Team |Scored|Conceded|
|1 |2011-05-08 |TeamA | 1 | 5 |
|2 |2011-06-08 |TeamA | 0 | 2 |
|3 |2011-07-08 |TeamA | 3 | 0 |
|4 |2011-08-08 |TeamA | 4 | 0 |
|5 |2011-09-08 |TeamA | 1 | 0 |
|6 |2011-10-08 |TeamA | 0 | 1 |
|7 |2011-11-08 |TeamA | 0 | 1 |
|8 |2011-12-08 |TeamA | 1 | 1 |
我需要找到一种方法来获得这样的东西:
|MatchID| Team |totalScored|totalConceded|
|1 | TeamA |0 | 0 |
|2 | TeamA |1 | 5 |
|3 | TeamA |1 | 7 |
|4 | TeamA |4 | 7 |
|5 | TeamA |8 | 7 |
|6 | TeamA |9 | 7 |
|7 | TeamA |9 | 8 |
|8 | TeamA |8 | 4 |
让我们看一下该查询的最后两行:
第7行表示在第7场比赛(第1-6场比赛)之前的最后6场比赛中,teamA进球9球,失球8
。比赛1,因为它只是告诉我们在比赛8(比赛2-7)之前的最后6场比赛中,teamA攻入8球并承认4。
有没有办法通过sqldf包使用sql来实现?(编辑:实际上,任何解决方案都可以,使用dplyr软件包,任务几乎是微不足道的,并且有效完成了)
目前,我唯一能想到的就是使用sql'LIMIT'和sqldf R包在R中导入数据并遍历allMatches中的所有行。
以下是我在此使用的示例代码的改编。这只是一个仅获取主队统计数据的示例,但是完整的代码很长,在这里没有用。
allMatches和singleTeamAllMatches是数据帧,其结构和内容与我上面描述的表和查询相同。
lastMatchesData <- NULL
for(match in (1:nrow(allMatches))){
matchRow <- allMatches[match,]
T <- matchRow$HT
Date <- matchRow$Date
ID <- matchRow$ID
lastMatches <- singleTeamAllMatches[singleTeamAllMatches$T == T & singleTeamAllMatches$Date < Date ,]
TPerformance <- sqldf("select sum(Scored) as Scored,
sum(Conceded) as Conceded
from
(select * from lastMatches order by Date DESC limit 6)")
newRow <- cbind(ID,TPerformance)
lastMatchesData <- rbind(lastMatchesData,newRow)
}
我不喜欢此解决方案有两个原因:首先,它确实很丑陋且凌乱,请记住,这只是一个示例,但是将来我想我将修改此代码以及一个全SQL解决方案会好得多。第二个原因是它很慢,我的意思是说真的很慢,再次使用全SQL解决方案会更好。
这是我使用dplyr提出的一种解决方案:
library(dplyr)
df <- df %>% group_by(Team) %>% mutate(cumScored = cumsum(Scored), totalScored = cumScored - ifelse(row_number() >= 7, lag(cumScored, 6), 0), cumConceded = cumsum(Conceded), totalConceded = cumConceded - ifelse(row_number() >= 7, lag(cumConceded, 6), 0)) %>% select(-cumScored, -cumConceded)
这个想法是先计算分数和让步的累积总和,然后仅保留最后六个匹配项,然后从当前累积总和中减去累积总和的第六个滞后,这样您就可以得出最近六个匹配滞后的部分累积总和。我找不到一种方法可以对任意数量的滞后进行动态累计。因此,使用一种技巧来添加新列,然后取消选择它。希望这可以帮助。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句