ATG/ATG/RScript/.Rhistory

sqlRacesIds = "SELECT GROUP_CONCAT(g.id) FROM (SELECT id, raceNumber, raceDate, Result FROM `Results` group BY RaceDate, RaceNumber HAVING count(RaceNumber) > 5 AND Result = 1 ORDER BY `Results`.`raceDate` DESC) AS g"
test = dbSendQuery(sqlRacesIds)
library(RMySQL)
mydb = dbConnect(MySQL(), user="atg", password="CvWKY34DqtlVgjt_9", host="nordh.xyz", dbname="atg")
sqlRacesWithEnoughParticipants = "SELECT raceNumber, raceDate, Result FROM `Results` group BY RaceDate, RaceNumber HAVING count(RaceNumber) > 5 AND Result = 1
ORDER BY `Results`.`raceDate` DESC"
sqlRacesIds = "SELECT GROUP_CONCAT(g.id) FROM (SELECT id, raceNumber, raceDate, Result FROM `Results` group BY RaceDate, RaceNumber HAVING count(RaceNumber) > 5 AND Result = 1 ORDER BY `Results`.`raceDate` DESC) AS g"
test = dbSendQuery(sqlRacesIds)
sqlRacesIds = "SELECT GROUP_CONCAT(g.id) FROM (SELECT id, raceNumber, raceDate, Result FROM `Results` group BY RaceDate, RaceNumber HAVING count(RaceNumber) > 5 AND Result = 1 ORDER BY `Results`.`raceDate` DESC) AS g"
test = dbSendQuery(mydb, sqlRacesIds)
View(test)
View(test)
testRs = fetch(test, n=-1)
View(testRs)
View(testRs)
View(testRs)
View(testRs)
View(testRs)
inspect(testRs)
Inspect(testRs)
print(testRs)
sqlRacesIds = "SET SESSION group_concat_max_len = 1000000;SELECT GROUP_CONCAT(g.id) FROM (SELECT id, raceNumber, raceDate, Result FROM `Results` group BY RaceDate, RaceNumber HAVING count(RaceNumber) > 5 AND Result = 1 ORDER BY `Results`.`raceDate` DESC) AS g"
test = dbSendQuery(mydb, sqlRacesIds)
testRs = fetch(test, n=-1)
print(testRs)
dbSendQuery(mydb, "SET SESSION group_concat_max_len = 1000000")
sqlRacesIds = "SELECT GROUP_CONCAT(g.id) FROM (SELECT id, rac
""
)
""
sqlRacesWithEnoughParticipants = "SELECT raceNumber, raceDate, Result FROM `Results` group BY RaceDate, RaceNumber HAVING count(RaceNumber) > 5 AND Result = 1
ORDER BY `Results`.`raceDate` DESC"
dbSendQuery(mydb, "SET SESSION group_concat_max_len = 1000000")
sqlRacesIds = "SELECT GROUP_CONCAT(g.id) FROM (SELECT id, raceNumber, raceDate, Result FROM `Results` group BY RaceDate, RaceNumber HAVING count(RaceNumber) > 5 AND Result = 1 ORDER BY `Results`.`raceDate` DESC) AS g"
test = dbSendQuery(mydb, sqlRacesIds)
testRs = fetch(test, n=-1)
print(testRs)
View(testRs)
View(testRs)
newSql = "SELECT r1.*,
ROUND(AVG(horseResults.time),4) as HorseAvgTime, ROUND(AVG(driverResults.time),4) horseAvgTime,
ROUND(AVG(IF(horseResults.Distance = r1.Distance, horseResults.time, null)),4) driverAvgTime,
ROUND(AVG(IF((horseResults.Distance = r1.Distance AND horseResults.Time > 0), horseResults.time, null)),4) horseAvgByDistance
ROUND(AVG(IF((driverResults.Distance = r1.Distance AND driverResults.Time > 0), driverResults.time, null)),4) driverAvgByDistance
FROM Results r1
INNER JOIN Results horseResults ON r1.RaceDate > horseResults.RaceDate AND r1.HorseId = horseResults.HorseId
INNER JOIN Results driverResults ON r1.RaceDate > driverResults.RaceDate AND r1.DriverId = driverResults.DriverId
WHERE r1.raceNumber = 10 AND r1.RaceDate = '2021-02-08'
GROUP BY r1.horseId"
newSql = "SELECT r1.*, ROUND(AVG(horseResults.time),4) as HorseAvgTime, ROUND(AVG(driverResults.time),4) horseAvgTime, ROUND(AVG(IF(horseResults.Distance = r1.Distance, horseResults.time, null)),4) driverAvgTime,ROUND(AVG(IF((horseResults.Distance = r1.Distance AND horseResults.Time > 0), horseResults.time, null)),4) horseAvgByDistance, ROUND(AVG(IF((driverResults.Distance = r1.Distance AND driverResults.Time > 0), driverResults.time, null)),4) driverAvgByDistance, FROM Results r1 INNER JOIN Results horseResults ON r1.RaceDate > horseResults.RaceDate AND r1.HorseId = horseResults.HorseId INNER JOIN Results driverResults ON r1.RaceDate > driverResults.RaceDate AND r1.DriverId = driverResults.DriverId WHERE r1.raceNumber = 10 AND r1.RaceDate = '2021-02-08' GROUP BY r1.horseId"
newRs = dbSendQuery(mydb, newSql)
# With AVG per distance
newSql = "SELECT r1.*, ROUND(AVG(horseResults.time),4) as HorseAvgTime, ROUND(AVG(driverResults.time),4) as horseAvgTime, ROUND(AVG(IF(horseResults.Distance = r1.Distance, horseResults.time, null)),4) as driverAvgTime, ROUND(AVG(IF((horseResults.Distance = r1.Distance AND horseResults.Time > 0), horseResults.time, null)),4) as horseAvgByDistance, ROUND(AVG(IF((driverResults.Distance = r1.Distance AND driverResults.Time > 0), driverResults.time, null)),4) as driverAvgByDistance, FROM Results r1 INNER JOIN Results horseResults ON r1.RaceDate > horseResults.RaceDate AND r1.HorseId = horseResults.HorseId INNER JOIN Results driverResults ON r1.RaceDate > driverResults.RaceDate AND r1.DriverId = driverResults.DriverId WHERE r1.raceNumber = 10 AND r1.RaceDate = '2021-02-08' GROUP BY r1.horseId"
newSql = "SELECT r1.*, ROUND(AVG(horseResults.time),4) as HorseAvgTime, ROUND(AVG(driverResults.time),4) as horseAvgTime, ROUND(AVG(IF(horseResults.Distance = r1.Distance, horseResults.time, null)),4) as driverAvgTime, ROUND(AVG(IF((horseResults.Distance = r1.Distance AND horseResults.Time > 0), horseResults.time, null)),4) as horseAvgByDistance, ROUND(AVG(IF((driverResults.Distance = r1.Distance AND driverResults.Time > 0), driverResults.time, null)),4) as driverAvgByDistance, FROM Results r1 INNER JOIN Results horseResults ON r1.RaceDate > horseResults.RaceDate AND r1.HorseId = horseResults.HorseId INNER JOIN Results driverResults ON r1.RaceDate > driverResults.RaceDate AND r1.DriverId = driverResults.DriverId WHERE r1.raceNumber = 10 AND r1.RaceDate = '2021-02-08' GROUP BY r1.horseId"
newRs = dbSendQuery(mydb, newSql)
newSql = "SELECT r1.*, ROUND(AVG(horseResults.time),4) as HorseAvgTime, ROUND(AVG(driverResults.time),4) as horseAvgTime, ROUND(AVG(IF(horseResults.Distance = r1.Distance, horseResults.time, null)),4) as driverAvgTime, ROUND(AVG(IF((horseResults.Distance = r1.Distance AND horseResults.Time > 0), horseResults.time, null)),4) as horseAvgByDistance, ROUND(AVG(IF((driverResults.Distance = r1.Distance AND driverResults.Time > 0), driverResults.time, null)),4) as driverAvgByDistance FROM Results r1 INNER JOIN Results horseResults ON r1.RaceDate > horseResults.RaceDate AND r1.HorseId = horseResults.HorseId INNER JOIN Results driverResults ON r1.RaceDate > driverResults.RaceDate AND r1.DriverId = driverResults.DriverId WHERE r1.raceNumber = 10 AND r1.RaceDate = '2021-02-08' GROUP BY r1.horseId"
newRs = dbSendQuery(mydb, newSql)
newRes = fetch(newRs, -1)
View(newRs)
View(newRes)