用R获取足球(足球)的统计数据
博士:这篇文章的大部分内容都是我所包含的例子，以尽可能清楚地说明，但是问题的核心是包含在中间部分“实际问题”中，其中的例子被简化为基本的例子。
我的问题是：
我有一个数据库，其中包含足球比赛的数据，我正试图从中提取一些统计数据。
数据库只包含一个表，名为“allMatches”，其中每个eache条目表示匹配，表中的字段(我只是包括绝对必要的字段，以了解问题所在)如下：
- ID: int，表的主键
- 日期:日期，比赛开始日期
- HT: varchar，家队
- 瓦查尔，客场队
- HG: int，主队得分
- AG: int，客场得分。
对于数据库中的每个条目，我必须提取一些关于客场和主队的统计数据。当您考虑以前所有比赛的统计数据时，可以很容易地做到这一点，例如，为了获得进球和失球的统计数据，首先我要执行以下查询：
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:原始数据库中相应匹配的ID
- Team:这一行中的数据是关于
- totalScored:球队在所有比赛中打进的进球，在ID表示的前一场比赛中
- totalConceded:球队在所有比赛中打进的进球，在ID表示的前一场比赛中
换句话说，如果在最后一次查询中获得：
|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排意味着在前6场比赛中，teamA进了9球，丢了8球。
第8排不受第1场比赛进球的影响，因为它只是告诉我们，在比赛前的6场比赛(2-7场)中，teamA进了8个球，丢了4个球。
有没有一种方法可以通过sqldf包使用sql来获得这个结果？(编辑:实际上，任何解决方案都可以，使用dplyr包，任务几乎是琐碎和有效地完成)
我做了什么，为什么我不喜欢
目前，我唯一能想到的就是导入R中的数据，并使用sql 'LIMIT‘和sqldf包循环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解决方案会好得多。
回答 2
Stack Overflow用户
发布于 2016-01-04 01:50:25
下面是我提出的一个使用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)其思想是先计算分数和让步的累积和，然后只保留最后六场比赛，从当前累积和减去累积和的第六次滞后，这样就可以得到最后六次比赛的部分累积和。我无法找到一种方法，在任意数目的滞后情况下进行累积和。因此，使用添加新列的技巧，然后取消它的选择。希望这能有所帮助。
Stack Overflow用户
发布于 2016-01-04 01:00:17
考虑一下相关聚合子查询分别用于totalScored和totalConceded字段，在最后6场比赛中进行调整。检查此操作的性能，因为派生表子查询在聚合查询中使用。
SELECT t1.Date, t1.MatchID, t1.Team,
(SELECT Sum(t2.Scored)
FROM (SELECT t2sub.MatchID, t2sub.Team, t2sub.Scored
FROM singleTeamAllMatches t2sub
WHERE t2sub.Team = t1.Team
AND t2sub.Date < t1.Date
ORDER BY t2sub.Date DESC
LIMIT 6) As t2
) As totalScored,
(SELECT Sum(t3.Conceded)
FROM (SELECT t3sub.MatchID, t3sub.Team, t3sub.Conceded
FROM singleTeamAllMatches t3sub
WHERE t3sub.Team = t1.Team
AND t3sub.Date < t1.Date
ORDER BY t3sub.Date DESC
LIMIT 6) As t3
) As totalConceded
FROM singleTeamAllMatches t1https://stackoverflow.com/questions/34582508
复制相似问题
腾讯云开发者
