DECLARE @LocalRID int
DECLARE @AveragingPeriod int
DECLARE @WarmupSecond int
DECLARE @PowerWeightingFactor float
SET @LocalRID = 14847
SET @AveragingPeriod = 30
SET @PowerWeightingFactor = 4
/****1. Calculate a 30 second SMA for watts
The SMo2 recorded for any given second pretty obviously results from effort incurred
prior to that second. To that end, this formula still makes use of watts
averaging. Both the Coggan & Skiba formulas involve an averaging period
to account for the body's reaction to the physiologic stress of a ride, although they
disagree as to averaging method and averaging period.
The averaging method doesn't seem to make too much difference overall in
terms of deriving weighted average power (the exponentially weighted moving
average (EMA) will be a a watt or 2 less). However the EMA calculation does
involve significantly more memory and processor time so I decided to use
a simple moving average (SMA) for this.
Changing the averaging period from 30 to 25 seconds resulted in as much as
a 5 watt gain to the SMA for my rides. As a user of the formula I like this because it
would make the rides seem tougher! For my own usage though I decided to
make the averaging period 30 seconds because this period results in
a better correlation to Sm02. For the implementation into the KOM Informatics
system I made averaging period a parameter. What if an optimal choice ends
up being dependent on an individual's physiology? */
SELECT
T.SecondsQty, Watts, SMAWatts,
S.SMo2
INTO #BaseSet
FROM
(SELECT
SecondsQty, Watts,
AVG(CAST(MAWatts AS FLOAT)) AS SMAWatts
FROM
(SELECT
SecondsQty, Watts
FROM
YourBigRideTable
WHERE
RID = @LocalRID AND
SecondsQty >= @AveragingPeriod) AS R
INNER JOIN
(SELECT
SecondsQty AS MASecondsQty,
Watts AS MAWatts
FROM
YourBigRideTable
WHERE
RID = @LocalRID ) AS P ON
R.SecondsQty >= P.MASecondsQty AND
P.MASecondsQty > R.SecondsQty - @AveragingPeriod
GROUP BY
SecondsQty,Watts) AS T
INNER JOIN
(SELECT
SecondsQty, SMo2
FROM
YourBigRideTable
WHERE
RID = @LocalRID) AS S ON
T.SecondsQty = S.SecondsQty
/****2. Calculate the earliest point in the ride where the average SmO2 is attained
After looking at a bunch of ride graphs I realized that warmup periods for me are characterized
by an association between low SmO2 values and low Watts. My instinct here is that
not a lot of fitness building work is occurring in this stage of the ride and that
including this data in the later stages of this might unfairly dilute the results.
*/
--@WarmupSecond is the earliest time SMo2 reaches ride average
SELECT
@WarmupSecond = MIN(SecondsQty)
FROM #BaseSet
WHERE
SMo2 >= (SELECT Avg(SMo2) AS AvgSMo2 FROM #BaseSet)
/**** 3. Calculate average SMA watts per SMo2 Level
The formula figures out the worth of a change in SMo2 Level in terms
of SMA watts. The first step is to calculate the average SMA watts per SMo2
Level for all of those rows in the BaseSet where the seconds > WarmupSecond.
If the results are returned in SMo2 ascending order then for most of my rides
it's pretty easy to see the pattern of higher SMA watts associated with lower
SMo2 levels.
*/
SELECT
ROW_NUMBER() OVER(Order By SMo2 ASC) AS RowNum,
SMo2,
AVG(SMAWatts) AS AvgSMAWattsPerSMo2Level
INTO
#AvgSMAWattsPerSMo2LevelSet
FROM
#BaseSet
WHERE
SecondsQty > @WarmupSecond
GROUP BY
SMo2
/**** 4. Calculate difference in SMA watts for contiguous SMo2 levels
The next step in the formula is to calculate the difference
in average SMA watts for contiguous SMo2 levels.
First calculate the CountToAverage.
For example assume that the lowest SMo2 level in your ride is 65.5 and
your second lowest is 65.6. That would work out to (65.6 - 65.5) * 10 or 1
Another example that skips levels: assume that the lowest SMo2 level in your ride is 65.5 and
your second lowest is 65.7. That would work out to (65.7 - 65.5) * 10 or 2
For my data 95% + of the time CountToAverage works out to 1.0. For those times when
it doesn't CountToAverage allows a little more weight to those rows.
Next calculate the average watts associated with the difference in SMo2 levels (AvgSMAWattsPerSMo2LevelDiff)
For example assume that the lowest SMo2 Level in your ride is 65.5 and
your second lowest is 65.6. Subtract the AvgSMAWatts (250 AvgSMAWatts) associated
with the lowest SMo2 Level from the AvgSMAWatts associated with the second lowest SMo2 Level (240 SMAWatts). The
AvgSMAWattsPerSMo2LevelDiff for this row would be -10
Do the same calculations
for each set of contiguous SMo2 Levels. The results of each calculation are stored
in the AvgSMAWattsPerSMo2LevelDiff column. If you ride like me, then for most rides, and
especially interval rides you will generally see a drop in AvgSMAWattsPerSMo2LevelDiff
as SMo2 ascends. However, the drop won't be steady, and for
some of the contiguous pairs there will be a gain instead of
a drop.
*/
SELECT
*,
(T2.SMo22 - T1.SMo21) * 10 AS CountToAverage, -- The number of units apart SMo21 and SMo22 are.
(T2.AvgSMAWattsPerSMo2Level2 - T1.AvgSMAWattsPerSMo2Level1) AS AvgSMAWattsPerSMo2LevelDiff
INTO #AvgSMAWattsPerSMo2LevelDiffSet
FROM
(SELECT
RowNum AS RowNum1,
SMo2 AS SMo21,
AvgSMAWattsPerSMo2Level AS AvgSMAWattsPerSMo2Level1
FROM
#AvgSMAWattsPerSMo2LevelSet) AS T1
INNER JOIN
(SELECT
RowNum AS RowNum2,
SMo2 AS SMo22,
AvgSMAWattsPerSMo2Level AS AvgSMAWattsPerSMo2Level2
FROM
#AvgSMAWattsPerSMo2LevelSet) AS T2 ON
T2.RowNum2 = T1.RowNum1 + 1
/**** 5. Calculate the average AvgSMAWattsPerSMo2LevelDiff for the entire ride
This number is what the a change in SMo2 Level is worth in watts, on average, for the
ride. For 92% of my rides, this is a negative number. The higher watts are
associated with lower SMo2 Levels. It's easy to see this in real time using
the Humon Hex app or watching the SMo2 readings drop on the ELEMNT while
doing intervals.
For 8% of my rides (endurance involving mostly zone 2 efforts), this number is positive
meaning that higher watts are associated with higher SMo2 Levels.
The problem we're trying to solve - where a formula doesn't do full justice to rides that involve high
intensity efforts - isn't occurring here. Higher watts are not resulting in lower SmO2 levels
and presumably the increased lactate associated with those lower levels. Therefore we won't
assign a positive watts adjustment at all for this type of ride. It'll be a negative adjustment
based on how close the recorded SmO2 is to the minimum SmO2. The closer it is, the smaller the
adjustment.
*/
/**** 6. Calculate the average SMo2 for the ride
The average SMo2 for the ride will be used in the next step as a tipping point
to decide whether to apply a positive or negative SMo2 based watts differential
in the next step. This is based on the entire ride. (Implemented as a join in step 7)
*/
/**** 7. Calculate the SMo2AdjustedWatts & SMo2WattsDifferential for the ride
Next the formula calculates the SMo2AdjustedWatts & SMo2WattsDifferential for the ride.
1. First, evaluate the overall AvgSMAWattsPerSMo2LevelDiff from step 5.
When AvgSMAWattsPerSMo2LevelDiff <= 0 then this is a harder ride where as SMo2 increases, watts decrease.
If this is the case for the ride then For each second of the ride:
a. Evaluate whether SMo2 <= AvgSMo2 for that second.
1. If it is then we want to apply a positive SMo2WattsAdjustment
as a lower then average SMo2 generally means we're working harder.
Take the absolute value of the AvgSMAWattsPerSMo2LevelDiff and multiply that by the AvgSMo2 - SMo2.
The second term just evaluates how far from the average the SMo2 for the current second is. The
further away from average, the lower the SMo2, the greater the multiplier effect.
(ABS(AvgSMAWattsPerSMo2LevelDiff) * (AvgSMo2 - SMo2)) is the SMo2WattsDifferential for the second.
To get the SMo2AdjustedWatts for the second, just add the raw watts value for the second.
2. If SMo2 > AvgSMo2 we want to apply a negative SMo2WattsAdjustment as a higher then average
SMo2 generally means we aren't working as hard.
Take the AvgSMAWattsPerSMo2LevelDiff and multiply that by the SMo2 - AvgSMo2.
The second term just evaluates how far from the average the SMo2 for the current second is. The
further away from average, the higher the SMo2, the greater the multiplier effect.
(AvgSMAWattsPerSMo2LevelDiff * (SMo2 - AvgSMo2)) is the SMo2WattsDifferential for the second.
To get the SMo2AdjustedWatts for the second, just add the raw watts value for the second.
When AvgSMAWattsPerSMo2LevelDiff > 0:
Endurance ride where as SMo2 increases watts increase. This case is happening for me about 8% of the time.
The problem we're trying to solve - where a formula doesn't do full justice to rides that involve high
intensity efforts - isn't occurring here. Higher watts are not resulting in lower SmO2 levels
and presumably the increased lactate associated with those lower levels. Therefore we won't
assign a positive watts adjustment at all for this type of ride. It'll be a negative adjustment
based on how close the recorded SmO2 is to the minimum SmO2. The closer it is, the smaller the
adjustment.
1. SMo2AdjustedWatts = Watts - (AvgSMAWattsPerSMo2LevelDiff * (SMo2 - MinSMo2)) for this case.
The watts adjustment is (AvgSMAWattsPerSMo2LevelDiff * (SMo2 - MinSMo2))
*/
SELECT *,
SMAWatts - Watts AS SMAWattsDifferential,
AvgSMo2 - SMo2 AS SMo2Diff,
CASE
WHEN AvgSMAWattsPerSMo2LevelDiff <= 0 THEN
-- Harder ride where as SMo2 increases, watts decrease
CASE
WHEN SMo2 < AvgSMo2 THEN Watts + (ABS(AvgSMAWattsPerSMo2LevelDiff) * (AvgSMo2 - SMo2))
--We want to credit watts here as a low SMo2 means we're working harder. Using ABS on
--the first term ensures a positive, and the WHEN stipulates that the second term
-- will result in a positive as well. A positive * a postive results in a positvie
WHEN SMo2 > AvgSMo2 THEN Watts + (AvgSMAWattsPerSMo2LevelDiff * (SMo2 - AvgSMo2))
--We want to debit watts here as a high SMo2 means we're not working as hard
--The first term is a negative, so that means the second term has to be a positive
--for the final result to be negative.
WHEN SMo2 = AvgSMo2 THEN Watts
END
WHEN AvgSMAWattsPerSMo2LevelDiff > 0 THEN Watts - (AvgSMAWattsPerSMo2LevelDiff * (SMo2 - MinSMo2))
-- Endurance ride where as SMo2 increases watts increase. This case is happening for me about 8% of the time.
-- The problem we're trying to solve - where a formula doesn't do full justice to rides that involve high
-- intensity efforts - isn't occurring here. Higher watts are not resulting in lower SmO2 levels
-- and presumably the increased lactate associated with those lower levels. Therefore we won't
-- assign a positive watts adjustment at all for this type of ride. It'll be a negative adjustment
-- based on how close the recorded SmO2 is to the minimum SmO2. The closer it is, the smaller the
--adjustment.
END AS SMo2AdjustedWatts,
CASE
WHEN AvgSMAWattsPerSMo2LevelDiff < 0 THEN
CASE
WHEN SMo2 < AvgSMo2 THEN (ABS(AvgSMAWattsPerSMo2LevelDiff) * (AvgSMo2 - SMo2))
WHEN SMo2 > AvgSMo2 THEN (AvgSMAWattsPerSMo2LevelDiff * (SMo2 - AvgSMo2))
WHEN SMo2 = AvgSMo2 THEN 0
END
WHEN AvgSMAWattsPerSMo2LevelDiff > 0 THEN (AvgSMAWattsPerSMo2LevelDiff * (SMo2 - MinSMo2))
WHEN AvgSMAWattsPerSMo2LevelDiff = 0 THEN 0
END AS SMo2WattsDifferential
INTO #SMo2AdjustedWattsCalculator
FROM
#BaseSet
JOIN
(SELECT CAST(SUM(AvgSMAWattsPerSMo2LevelDiff)/SUM(CountToAverage) AS FLOAT) AS AvgSMAWattsPerSMo2LevelDiff FROM #AvgSMAWattsPerSMo2LevelDiffSet) AS T ON
1=1
JOIN
(SELECT Avg(SMo2) AS AvgSMo2 FROM #BaseSet) AS A ON
1=1
JOIN
(SELECT Min(SMo2) AS MinSMo2 FROM #BaseSet) AS B ON
1=1
ORDER BY SMo2
/**** 8. Weight the higher watts using an exponential boost and derive the SMo2 Adjusted Weighted Average Power
Both SMA and EMA use a
"power dependent intensity weighting factor derived directly from a plot of blood lactate concentration as a percentage of
concentration at threshold against % of threshold power. His [Coggan's] work indicated a near 4th power relationship between the two."
This formula continues to use this insight. I made the power dependent intensity weighting factor a parameter. If it ends up
being there are individual differences the code will adapt automatically.
The steps to attain this are
1. For rows in which the SMo2AdjustedWatts >= 0:
Raise the SMo2AdjustedWatts for each second to the @PowerWeightingFactor power
For rows in which the SMo2AdjustedWatts < 0:
Raise the SMo2AdjustedWatts for each second to the @PowerWeightingFactor power,
then subtract this number from 0
2. Calculate the average of the values in step 1
3. Calculate the @PowerWeightingFactor root on the number from step 2.
The SMo2WeightedAveragePower for the ride is the number from step 3
*/
SELECT @LocalRID AS RID, * FROM
(SELECT
POWER(AVG(SMo2AdjustedWatts4), 1/@PowerWeightingFactor) AS SMo2WeightedAveragePower,
POWER(AVG(SMAWatts4), 1/@PowerWeightingFactor) AS WeightedAveragePower
FROM
(SELECT
CASE
WHEN SMo2AdjustedWatts > 0 THEN POWER(SMo2AdjustedWatts, @PowerWeightingFactor)
ELSE
0 - POWER(SMo2AdjustedWatts, @PowerWeightingFactor)
END AS SMo2AdjustedWatts4,
POWER(SMAWatts, @PowerWeightingFactor) AS SMAWatts4
FROM
#SMo2AdjustedWattsCalculator) AS T) AS J
JOIN
(SELECT CAST(SUM(AvgSMAWattsPerSMo2LevelDiff)/SUM(CountToAverage) AS FLOAT) AS AvgSMAWattsPerSMo2LevelDiff FROM #AvgSMAWattsPerSMo2LevelDiffSet) AS A ON
1=1
DROP TABLE #BaseSet
DROP TABLE #AvgSMAWattsPerSMo2LevelSet
DROP TABLE #AvgSMAWattsPerSMo2LevelDiffSet
DROP TABLE #SMo2AdjustedWattsCalculator