Aggregators¶
SDQL aggregators are as follows:
- Average is abbreviated as A
- Maximum is abbreviated as Max
- Minimum is abbreviated as Min
- Replace is abbreviated as R
- Sum is abbreviated as S
- Unique is abbreviated as U
- Record
- Streak
- PearsonCorrelation is abbreviated as PC
- StandardDeviation is abbreviated as SD
- Aggregators ignore values which are None. So that
- Min([None,10,20]) = 10
- and
- S([2,4,None]) = 6
The SDQL short cut tA(points) expands out to Average(points@team and season).
This is read, “The average points for each team and each season.” The text to the right of the @ sign defines the conditions under which the average is taken and can be any valid SDQL condition.
- For example, to query on NBA teams at home with a home court season-to-date average score of 110 points or more, use the SDQL:
- site=home and Average(points@team and season and site=home) >= 110
- To add the requirement that at least 20 home games had been played, use the SDQL:
- site=home and Average(points@team and season and site=home) >= 110 and Sum(1@team and season and site=home) >= 20
The general summative methods also allow comparison with league averages.
- To see how NBA teams have done when they are averaging 50% more three pointers than the league average and requiring that the team had played at least 30 games, use the SDQL:
- Average(three pointers made@team and season) > 1.5 * Average(three pointers made@season) and Sum(1@team and season) >= 30
- To see how teams in the NBA have done after averaging at least 130 points in their last three at home, use the SDQL:
- Average(points@team and season and site=home,N=3) >= 130
The SDQL Average(points@team and season) expands fully out to Average(points@team and season)[team and season]. This is read “Average points for each team and season evaluated at that team and season”.
- For example, to see how NFL teams have done in week 1 after losing 10 or more games in the previous season, use the SDQL:
- week=1 and Sum(L@team and season)[team and season-1] >= 10
Note that whatever conditions are to the right of the @ sign must appear in the same order within the square brackets.
- To see how teams have done in week 1 after winning 8 games at home during the previous regular season, use the SDQL:
- week=1 and Sum(W@team and season and site=home and playoffs=0)[team and season-1 and site=home and playoffs=0] = 8
The Maximum and Minumum methods are useful for checking a team’s recent performance.
- To see how NBA teams have done after scoring at least 100 points 20 games in a row, use the SDQL:
- Minimum(points@team and season,N=20) >= 100
- To see how NBA teams have done after their opponent scored fewer than 90 points 10 games in a row, use the SDQL:
- Maximum(o:points@o:team and o:season,N=10) < 90
- To isolate NHL games where the total is less than 6 and in the team’s last 4 games when the total was less than 6 there were at least 3 goals scored in the first period, use the SDQL:
- 3 <= Min( period scores[0] + o:period scores[0] @ team and season and total < 6, N = 4) and total < 6
The Record aggregator takes either an arguments of just the margin, or a tuple of (margin,odds,wager).
- To see the straight up record of each team in 2021, use the SDQL:
- Record(points-o:points),R(team)@season=2021 and team|
- To see each team’s ATS record, including the total amount invested, net profit, and average odds, use the SDQL:
- Record((points+line-o:points,odds,1)),R(team)@season=2021 and team| (Where it is assumed that your database has “odds” as a parameter and you are wagering 1 unit.)
The Streak aggregator returns the current streak of any SDQL term.
- To see how teams have performed SU after winning by double digits in their last 4+ games, use the SDQL:
- Record(points-o:points)@Streak(points>=o:points+10@team and season)>=4
- To see the average points scored by teams after scoring over 120 their last 5, use the SDQL:
- A(points)@Streak(points>120@team and season)>=5
The StandardDeviation aggregator returns the standard deviation of any SDQL term.
- To see the standard deviation of the ATS margin for each NBA team, use the SDQL:
- StandardDeviation(points+line-o:points)@team
The PearsonCorrelation aggregator returns the correlation bewteen any two SDQL terms.
- To see the correlation between hits and runs for each MLB team, use the SDQL:
- PearsonCorrelation((hits,runs))@team
The Sum aggregator defaults to zero while all others default to None.
- To see how MLB teams have performed on their first home game of the season, use either of the SDQL conditions:
tS(1@H) = 0
tA(1@H) is None
running¶
A summative is turned into a running-summative by using any math outside of the summative.
- To see the date and running average of points for the Patriots in 2017, use the SDQL:
- date,1*A(points)@team=Patriots and season=2017
