Summative¶
SDQL summatives and their abbreviations are as follows:
- Average is abbreviated as A
- Column is abbreviated as C
- 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
- Summatives ignore values which are None. So that
- Min([None,10,20]) = 10
- and
- S([2,4,None]) = 6
Column is the default summative and hold the list of qualifying values.
- So that
- date as Date, points as Points @ team=Bears and season=2017
- is the same as
- Column(date), Column(points) @ team=Bears and season=2017
- The Column summative is called explicitly for setting the format argument.
- Column((date,points,o:points),format=’On %s the Bears score was %s - %s’) as Bears Games in 2017 @ team=Bears and season=2017
There are three flavors of summative methods: team’s season-to-date, general summative methods, and general summative methods with keyed access (square bracket notation).
season-to-date¶
The SDQL provides the short cuts tS and tA to access a team’s season-to-date sum and average. Likewise, oS and oA give the opponent’s season-to-date sum and average. Here are a few examples:
- To see how NFL teams have done after week 5 when they are averaging more than twice as many passing yards as their opponent, use the SDQL:
- week > 5 and tA(passing yards) > 2*oA(passing yards)
- To see how NBA teams have done when they had more than 50 wins while their opponent had fewer than 30, use the SDQL:
- tS(W) > 50 and oS(W) < 30
The SDQL allows constraints on the number of games over which to take the summative.
- To see how MLB teams have done after scoring more than 30 runs over their previous 3 games, use the SDQL:
- tS(runs,N=3) > 30
- Likewise, to see how MLB teams have done after allowing more than 30 runs over their previous 3 games, use the SDQL:
- tS(o:runs,N=3) > 30
previous seasons¶
The SDQL provides short cuts for Sums and Averages from previous seasons.
- To see how NBA teams have done in their home openers after winning more than 60 regular season games in each of their two previous seasons, use the SDQL:
- tpS(W@playoffs=0)>60 and tp2S(W@playoffs=0)>60 and tS(H) is None
The same form works for the team’s opponent.
- To see how NFL teams have done in week one against opponents with more than twice as many previous regular season wins, use the SDQL:
- 2*tpS(W@playoffs=0)<opS(W@playoffs=0) and week=1
general summative¶
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
summative keys¶
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
If one of the terms in the square brackets is a list of values then the summative method is taken over that list.
- To see how teams have done in week 1 after winning at least 15 home games during the previous two regular seasons, use the SDQL:
- week=1 and Sum(W@team and season and site=home and playoffs=0)[team and [season-1,season-2] and site=home and playoffs=0] >= 15
The use of lists inside of the square bracket is also useful for strength-of-opponent queries.
- To see how NFL teams have done in week 4 after having faced opponents who are still winless, use the SDQL:
- week=4 and Sum(W@team and season)[opponents and season] = 0
- To isolate games where a team is playing an opponent they lost to in their previous meeting to suffer their first defeat of the season, use the SDQL:
- Sum(L@team and season and week)[team and season and range(1,P:week)] = 0 and P:L and P:season=season
Note how the Python method range is used to take the sum over the weeks leading up to the previous matchup.
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 do 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
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
This form is especially useful for Graphics.
