JCapper Message Board
Adding together two factors
|| Adding together two factors
|Is it possible to add two factors in SQL mode and rank the sum of those two factors against the field? Also, FastSlowPace confuses me just a bit. When I look at the FSP number value I am curious what that number actually represents. I don't use this factor in any of my UDMs currently but have read about this factor when it comes to horses coming out of a contested pace race.|
"Is it possible to add two factors in SQL mode and rank the sum of those two factors against the field? "--end quote
Let's break this into two separate parts - and from there look at each part separately.
re: "Is it possible to add two factors in SQL mode"
The short answer: Yes. (I'll provide some examples momentarily.)
re: "and rank the sum of those two factors against the field?"
The table design is that fields such as VALF01, VALF02, VALF03, etc. contain attributes of the current horse being read from the table. Put another way: These attributes exist at the horse level.
Other fields in the table, such as PaceIndex, PacePressure, FieldSize, IntSurface, Dist, etc. contain the attributes of the current race. Put another way: These attributes exist at the race level.
The rank and gap fields such as RANKF01, GAPF02, etc. cross the line between horse level and race level a bit.
For example, RANKF01 and GAPF01 contain values that are: A. attributes of the current horse being read from the table and B. attributes that also describe how strong or how weak the current horse being read from the table is in relation to the other horses in the race.
Internally within JCapper, the values for fields such as RANKF01 and GAPF01 only become possible after executing a code block that first captures VALF01 for every horse in the race - and next cycles through all of those captured VALF01 values while determining RANK for each horse - and next cycles through every VALF01 value while determining GAPF01 for each horse.
You can certainly apply math (subtraction, multiplication, division, or exponents, etc.) in a sql expression to fields such as VALF01, RANKF02, GAPF03, etc.
But keep in mind that the values resulting from your math are still going to exist as attributes of the current horse being read from the table.
In order to get rank for the current horse values that result from your math, you would have to do something similar (outside of JCapper) to what JCapper does (internaly) to get rank for the current horse being read from the table.
All of that said, you absolutely can use math in a sql expression.
Imo, if you are creative you can get your result to act as an indicator of how the current horse being read from the table compares to the rest of the field.
Also, Imo, doing this can somethimes be a difference maker.
A simple example:
AND (RANKF01 + RANKF02 + RANKF03 + RANKF04) <= 5
In the above expression I am adding the ranks for F01 through F04 together and insisting that the total of the ranks be less than or exqual to 5.
If you think about this, it means:
Any horse that is rank=1 for F01, F02, F03, and F04 will be flagged by the expression.
Any horse that is rank=1 for any three of the four fields being evaluated - but is rank=2 for the fourth field: That horse gets flagged by the expression.
You can also do something similar with numeric value.
AND ((VALF01 + VALF02 + VALF03 + VALF04) / 4) >= 82.5
Here I am summing numeric value for F01 through F04 and dividing the result by 4 and then insisting that the result be greater than or equal to 82.5.
Put another way, I am insisting that the current horse being read from the table has an average of numeric value for F01 through F04 that is at least 82.5.
You can also do something similar with gap.
AND ((GAPF01 + GAPF02 + GAPF03 + GAPF04) / 4) >= -5
Here I am summing gap for F01 through F04 and dividing the result by 4 and then insisting that the result be greater than or equal to negative 5.
Or put another way, I am insisting that the current horse being read from the table has an average of gaps for F01 through F04 that is within 5 points of the theoretical race top for those factors.
Note: Positioning of the parenthesis characters in the above example expressions is crucial. Keep in mind that everything enclosed inside of parenthesis characters gets evaluated as if it were a single expression.
~Edited by: jeff on: 4/22/2015 at: 11:09:30 AM~
|Thanks Jeff. This is very helpful.|
|I have tried to do:|
valf01 / valf33 > 1
and could never get it to work.
Can you divide one value by another?
"valf01 / valf33 > 1"--end quote
Division should be doable in a sql expression.
The thing you have to be careful about is that when the db driver is asked to divide a number by zero you are going to get an error instead of the intended result.
A common example might be when you are performing division for a first time starter whose valf33 (from the above example) is zero.
The way to avoid division by zero is to include something like the following in the sql expression before the line that performs the division itself:
and valf33 > 0
and (valf01 / valf33) > 1
Of course, in order for the above to work and flag some starters, valf01 must be larger than valf33.
~Edited by: jeff on: 5/3/2015 at: 5:34:39 PM~