OBC Time Trial Query Help Page
This
TT query
facility allows you to search, filter, subgroup and sort time trial results.
Searching for Patterns Within A Particular Data Field:
There are text boxes for each data field and you may
type in the patterns which you wish to match in those particular fields.
There is also a big text box at the top in which you can put patterns that
involve multiple fields.
Note that the search will be an AND'ing of the blank delimited strings in
the text box.
A "!" at the start of the pattern indicates that you want results which do
NOT match the pattern following the "!".
There is a checkbox which allows you to specify whether the search should
be case-sensitive or not.
Each of the blank delimited search patterns may be a perl regular expression.
Don't worry about this, most of the time you'll just be typing words to search
for. I have included a few more complicated examples in the table below.
Case-sensitive Search Across All Fields:
As well you may do a case-sensitive search for patterns spanning all fields.
The order of the fields is the same as that in the output table
(if you show all fields).
Note that decisions in 2003 to only allow the OBC BAR rankings to include
the OTT 15km, and in particular not the ATT 15km, require the use of this field.
This is so since we need to allow the ATT series for the 40km results,
but want to exclude the ATT (and the WTT) series for the 15km results.
The following pattern for this field will serve the function:
40km|OTT.*15km
(Note that the decision to make this field case sensitive is a great
convenience to avoid matching parts of names (like Potter) when using a series
name such as OTT).
Filtering:
There are a number of options which may help you limit your search -
for instance, to just the Gender Bests or Series Bests for the Entrants which
match your specified search strings.
Sorting:
You can easily sort on any of the fields by pressing the button with the field name on it.
Where it's interesting to do so (ie., for anything other than Time and Average Speed), you can show the averages of this subgrouped data by clicking the "Show Subgroup Averages" checkbox.
Subgrouping Data:
Aside from the above ability to sort or subgroup on any of the fields, there is
also the ability to subgroup an individual's best performance of
any specified number of events of any number of the different TT distances.
You need only specify the number of best performances you want of each distance.
You can show averages of this subgrouped data by clicking the "Show Subgroup Averages" checkbox.
Right now you must have the "All" radio button checked.
When I get some time, I'll expand this functionality to the other filter options.
Event Factor:
The Event Factor indicates how "good" a day is,
by which we mean how close people are to their year-series personal bests
in this particular event.
Note that such a measure will reflect both weather and fitness
(which tend to improve as the season progresses) states.
The maximum Event Factor possible is 1 and
this occurs only if all of the riders (who have ridden at least 3 times in
the current year in the particular series) ride their fastest time on this day.
The Event Factor is calculated as the average of the ratio of the cubes
of each rider's speed in this event to his/her best speed at this distance in
this series this year. As already mentioned, the average is only done over
those riders who have ridden at least 3 times in the current year in
the series. As well, an attempt is made to remove riders who have run into
mechanical difficulties, etc. (as indicated in the comment field of their
result record).
Note, of course, that Event Factors can only be calculated after at least
three time trials are held in a particular series at a particular distance.
Once this number of time trials occur, Event Factors are show for all of the
earlier time trials in the series (at least if there are riders in that day
who have done at least 3 time trials).
Performance Ratios:
Power output is said to be proportional to the cube of the speed, and hence
we calculate some ratios of cubes of speeds to measure performance.
There is a select list which allows you to compare the cube of the speed to
various other cubes of speed. An explanation of the entries in the list is given in the table which is included below.
As well, there is an EF Checkbox which divides the Performance Ratios by the Event Factor Ratio, ie., the ratio of the Event Factors for the two events involved in the Performance Ratio.
This is done as a reasonable attempt for comparing performances on different days.
Note that in the case where more than one result exists with the speed used in the denomimator of the performance ratio, the smallest Event Factor of those results is used (since this would correspond to the "better" result - ie., same time under on a more difficult day).
| Comparison | Description |
|---|
| PB Yr-Ser Pre | Cube of speed divided by cube of the best speed the person has done previously in the current year in the current series for the current distance |
| PB Yr-Ser | Cube of speed divided by cube of the best speed the person has done in the current year (before or after) in the current series for the current distance |
| PB Yr | Cube of speed divided by cube of the best speed the person has done in the current year (before or after) series for the current distance |
| PB Db | Cube of speed divided by cube of the best speed stored in the database for the person series for the current distance |
| CB Yr-Ser | Cube of speed divided by cube of the category best speed done in the current year (before or after) in the current series for the current distance |
| CB Yr | Cube of speed divided by cube of the category best speed done in the current year (before or after) for the current distance |
| CB Db | Cube of speed divided by cube of the category best speed stored in the database for the current distance |
| CB Shown | Cube of speed divided by cube of the category best speed shown in the current results for the current distance |
| GB Yr-Ser | Cube of speed divided by cube of the gender best speed done in the current year (before or after) in the current series for the current distance |
| GB Yr | Cube of speed divided by cube of the gender best speed done in the current year (before or after) for the current distance |
| GB Db | Cube of speed divided by cube of the gender best speed stored in the database for the current distance |
| PB Spd Shown | Cube of speed divided by cube of the person's best speed shown in the current results for the current distance |
| CB Spd Shown | Cube of speed divided by cube of the same-category best speed shown in the current results for the current distance |
| GB Spd Shown | Cube of speed divided by cube of the gender best speed shown in the current results for the current distance |
| B Spd Shown | Cube of speed divided by cube of the best speed shown in the current results for the current distance |
Search Examples:
Most times, you will be able to find what you want with no difficulty.
Please don't let the examples or discussion in the following table scare you off!
Note that if you wish to search for a non-alphanumeric character which (also) happens to be a "special" character for Perl (eg., brackets), you will have to precede that character by a backslash. The last example in the table shows this.
| Search For | | Other Settings | Explanation |
|---|
| Entries containing "Paul" in "1999" |
|
Check the "Case-Sensitive" checkbox if you care about the capitalization. |
You will match "Paula" as well as just "Paul". To avoid matching Paula, you could use a Perl regular expression (regexp) to specify that "Paul" must end at a word boundary via - ie., by replacing the text entry "Paul" with "Paul\b". |
| Entries other than Team Time Trial or Tandem ones for "Moerman" |
|
Name
|
Moerman
|
|---|
|
Category
|
!TTT !TAN
|
|---|
|
Check the "Case-Sensitive" checkbox if you care about the capitalization. |
Here you will match all of the Moerman's individual (ie., not team and not tandem) results.
|
| Entries for Allison or Andrea McKay in 2000-2002. |
|
Name
|
allison|andrea mckay
|
|---|
|
Date
|
200[0-2]
|
|---|
|
Uncheck the "Case-Sensitive" checkbox. |
Again, if you want to, you could require proper capitalization and require that the strings start and end on word boundaries, but normally you just won't care. |
| Mary Ajersch's personal season's bests in the WTT series. |
|
Name
|
Mary Ajersch
|
|---|
|
Series
|
WTT
|
|---|
|
The "Entrant Series Bests" checkbox must be checked. |
You could type less of Mary's name and still get the same results. You could also type everything in lower case if the "Case-Sensitive" checkbox is not checked, and in this case you would not have received extraneous matches. |
| All Tandem entrants in the Jul 5 2001 time trial. |
|
Date
|
2002-Jul-05
|
|---|
|
Category
|
TAN
|
|---|
|
|
|
| Find all individual female entrants who have ridden a 15km time trial in between 20:00 and 20:59. |
|
Distance
|
15km
|
|---|
|
Time
|
20:
|
|---|
|
Category
|
\b[A-Z]{2}F\b
|
|---|
|
|
Here, for the category, we're looking for a three character alphabetic string ending in "F"
(which would correspond to the female category abbreviations - eg., VAF, SEF and so on). We could also just have looked for any string ending in "F" (ie., "F$"), or an "F" preceeded by 2 other characters (ie., "..F"), and we could have done a case-insensitive search. |
| Look for all entries which contain parentheses or quotes (since these occur around team names or excuse fields!). |
|
|
We could also have written the pattern as \\(|". The two backslashes preceding the opening parenthesis are needed to escape the special meaning which the open parenthesis would otherwise have as a perl and mysql regular expression. If you did not include these two backslashes here, you would receive an error from the perl DBI interface. |
Please send your comments/suggestions to Celia McInnis