Changes to DB Results or Query Structure

[SDB]

Hey Joe or someone else familiar with changes that have been made with the SDB,

 

This query used to work to determine how many times a MLB team has been a dog in their last 12 games and what were their respective results:

 

nnnnnnnnnnnn:date>=today and team and line>=100

 

The query no longer gives the results as it had in the past. It now gives only a few games, (a max of 3 games per team) probably based on the query only including a certain number of games or only going back a certain number of days.  I have tried various additions in the web address to get the query working like using max_rows, games_played, etc. but there's nothing that I have tried which will give me the similar old results, typically around 175 games before versus now it is declining on a daily basis (today it was 45 games).

 

I'm sure there is some syntax change because all games are not included even with the "and line>=100" removed, so it has to be something with the "nnnnnnnnnnnn:date>=today" but what is the correct or new syntax to get this query working again?

 

Thanks for any help, it might be similar for every sport, but this one is very easy to spot in this MLB scenario.

[matthe...@comcast.net]

I thin you used n instead of p. n means future games p means past games.

---

From: "SDB" <zzzs...@gmail.com>
To: sportsd...@googlegroups.com
Sent: Tuesday, October 2, 2012 1:04:43 AM
Subject: Changes to DB Results or Query Structure

--
You received this message because you are subscribed to the Google Groups "SportsDataBase" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sportsdatabase/-/p_5Ot-5SyHgJ.
To post to this group, send email to sportsd...@googlegroups.com.
To unsubscribe from this group, send email to sportsdatabas...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sportsdatabase?hl=en.

[Joe Meyer]

zzz,

nnnnnnnnnnnn:date>=today

and is a clever use of the SDQL.

However, it will not work toward the end of the season since teams do not have that many future games.

Also, the query is better written in SDQL2.0 as:
n12:date>=today

Best wishes,

Joe Meyer

PS:
Joe doesn't like it when you address questions to Joe and usually just deletes these.
The best way to get your questions answered is to answer other's questions and address your questions to 'Group' or 'Friends'

--

[SDB]

My apologies Joe on the address issue....

How about My Fellow Colleagues, which I am using here with the utmost respect for everyone (which I agree is much better than Hey Joe):

This query is performed automatically in an Excel Web Query, so it has not changed, but it is not giving the desired results in SDQL or SDQL2.0 the way it used to work. Here is a view of how the results used to look in Excel:

HOT PUPS - DOGS OVER THE LAST 12 DAYS (SORTED BY WINS)
# games	Record	$ On	$ Against	Over/Under	$ Over	$ Under	Team
	W-L (marg, % win)			O-U-P (marg, % over)
8	5-3 (1.25, 62.5%)	370	-450	5-3-0 (3.31, 62.5%)	150	-225	Padres
10	5-5 (0.90, 50.0%)	248	-328	6-4-0 (1.70, 60.0%)	170	-280	Marlins
11	5-6 (-0.45, 45.5%)	96	-156	6-5-0 (0.77, 54.5%)	75	-155	Cubs

The problem Joe indicated is because there are fewer games remaining this season, but I would like something that would work the same way.

Matt - Just changing from "n" to "p" the query does not work, even removing the date qualification, the query only runs if I qualify the previous game like using "p:W and team and line>=100" but I'm only interested in finding the results from the previous 12 (or x) number of games. if you know another way around this please give me a hint with the correct syntax.

Joe - the "n12:date>=today and team and line>=100" only works in SDQL2.0, but the "nnnnnnnnnnnn:date>=today and team and line>=100" appears to work the same in SDQL and SDQL2.0. Is it easier on the database if I convert all my queries to SDQL2.0 or is it okay to use my old queries as they currently exist as long as they are working? It is much easier to use "n12" for new queries.

In any case, "n12" and "nnnnnnnnnnn" give me the same results which are not the results I want to see at this point of the season. What I'm trying to do is get the proper syntax for this query on a daily basis in a format similar to the above example:

How many times a MLB team has been a dog in their last 12 games and what were their respective results

Thanks for any help or suggestions...

[matthe...@comcast.net]

pppppppppppp:date<=today and team and line>=100 and season=2012

Is this what you mean?

---

From: "SDB" <zzzs...@gmail.com>
To: sportsd...@googlegroups.com

--

You received this message because you are subscribed to the Google Groups "SportsDataBase" group.

To view this discussion on the web visit https://groups.google.com/d/msg/sportsdatabase/-/dOhhn5FiW3wJ.

[zzzstats]

No, because running that query gives me "all" games in the 2012 season where the respective team was a dog, and I only want to see the last 12 games that team played. It may be 0 games if it is the Yankees and they wouldn't appear, but let's say it may be 1 game for Washington and all 12 games for Houston. I say "all" because removing the "ppp...today and" syntax gives me even more games, possibly including preseason games, but maybe it's just that your "ppp..." excludes the most recent games. In any case the number should not be higher than 12 for any particular team. 

 

Asking for the last 12 games, I wouldn't typically need a season parameter.

 

This situation has all types of variations using x number of previous games: D, F, H, A, O, U... very helpful to show especially over/under and team total trends over 4, 8, 12 games.

 

Thanks for the answer.

.

[matthe...@comcast.net]

I just added the season on my own.

How about this: Sum(W@team and D and season,N=12) and team and line>100

if this is wrong then I have no idea what you are looking for i was not a math geek LOL or a computer geek for that matter

---

[Joe Meyer]

My Fellow Colleagues,

This is an ugly hack, however the SDQL form is: 

 (_tnnnnnnnnnnnn is None or n12:date>=today) and team and season=2012

This solves the problem of teams not having 12 future games by first checking that the prefix itself is defined.

Note that the prefixes in the actual Python code always start with `_t` and that the convenient n12 form is expanded out with all those `n`s.

Also, `season=2012` is needed here to avoid the last 12 games of previous seasons.

Best wishes,

Joe Meyer

http://SportsDatabase.com

 

--

You received this message because you are subscribed to the Google Groups "SportsDataBase" group.

To view this discussion on the web visit https://groups.google.com/d/msg/sportsdatabase/-/dOhhn5FiW3wJ.

[zzzstats]

Matt, the last option you suggested still does not produce the desired results, even adding back the season=2012 it does not give me the correct dataset, but thanks for offering your assistance and suggestions.

 

Perhaps this solution from Joe will help others, because even if it is "an ugly hack" it does work in SDQL2.0 very similar to the previous query.  There is a little difference with how it handles today's games and a little bit of a format change, but the dataset looks correct. Here is the query as I am now using it, adding in the line parameter:

 

(_tnnnnnnnnnnnn is None or n12:date>=today) and team and line>=100 and season=2012

 

Thanks again Joe, I will need to convert all my queries like this to SDQL2.0, but at least I have something that will work as a long-term.solution.