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