Chart power vs time for official climb attempts

Snapshot average power per attempt in a nullable average_power_w column (backfilled from stored rides), plot each powered attempt on the climb page as duration vs average watts with ride hover and click-through, and show avg power in the attempts table.

1 files changed, 28 insertions, 8 deletions

diff --git a/official_climb/attempt.go b/official_climb/attempt.go
index c340fea..1164e01 100644
--- a/official_climb/attempt.go
+++ b/official_climb/attempt.go
@@ -9,7 +9,7 @@ import (
 	"github.com/martinlehoux/biking_home/climb"
 )
 
-// Attempt is one recorded passage of an official climb in a ride, keyed by (rideID, climbID, occurrence); stats are snapshotted so listings never re-parse ride files.
+// Attempt is one recorded passage of an official climb in a ride, keyed by (rideID, climbID, occurrence); stats are snapshotted so listings never re-parse ride files. AveragePowerW is nil for rides without a power meter.
 type Attempt struct {
 	RideID             int64
 	ClimbID            int64
@@ -18,13 +18,14 @@ type Attempt struct {
 	ElevationGainM     float64
 	SlopePercent       float64
 	Cotacol            float64
+	AveragePowerW      *float64
 	CotacolAlgoVersion string
 	StartedAt          time.Time
 	EndedAt            time.Time
 	CreatedAt          time.Time
 }
 
-const attemptColumns = "a.ride_id, a.climb_id, a.occurrence, a.distance_m, a.elevation_gain_m, a.slope_percent, a.cotacol, a.cotacol_algo_version, a.started_at, a.ended_at, a.created_at"
+const attemptColumns = "a.ride_id, a.climb_id, a.occurrence, a.distance_m, a.elevation_gain_m, a.slope_percent, a.cotacol, a.average_power_w, a.cotacol_algo_version, a.started_at, a.ended_at, a.created_at"
 
 // AttemptsForClimb returns every recorded attempt of one official climb, oldest ride first.
 func AttemptsForClimb(db *sql.DB, climbID int64) ([]Attempt, error) {
@@ -78,17 +79,18 @@ func ReplaceRideAttempts(db *sql.DB, rideID int64, attempts []Attempt) error {
 		keys = append(keys, "(?, ?)")
 		args = append(args, attempt.ClimbID, attempt.Occurrence)
 		if _, err := tx.Exec(`
-			INSERT INTO ride_official_climb_attempts (ride_id, climb_id, occurrence, distance_m, elevation_gain_m, slope_percent, cotacol, cotacol_algo_version, started_at, ended_at)
-			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+			INSERT INTO ride_official_climb_attempts (ride_id, climb_id, occurrence, distance_m, elevation_gain_m, slope_percent, cotacol, average_power_w, cotacol_algo_version, started_at, ended_at)
+			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
 			ON CONFLICT(ride_id, climb_id, occurrence) DO UPDATE SET
 				distance_m = excluded.distance_m,
 				elevation_gain_m = excluded.elevation_gain_m,
 				slope_percent = excluded.slope_percent,
 				cotacol = excluded.cotacol,
+				average_power_w = excluded.average_power_w,
 				cotacol_algo_version = excluded.cotacol_algo_version,
 				started_at = excluded.started_at,
 				ended_at = excluded.ended_at
-		`, attempt.RideID, attempt.ClimbID, attempt.Occurrence, attempt.DistanceM, attempt.ElevationGainM, attempt.SlopePercent, attempt.Cotacol, attempt.CotacolAlgoVersion,
+		`, attempt.RideID, attempt.ClimbID, attempt.Occurrence, attempt.DistanceM, attempt.ElevationGainM, attempt.SlopePercent, attempt.Cotacol, attempt.AveragePowerW, attempt.CotacolAlgoVersion,
 			attempt.StartedAt.UTC().Format(time.RFC3339), attempt.EndedAt.UTC().Format(time.RFC3339)); err != nil {
 			return fmt.Errorf("insert ride attempt: %w", err)
 		}
@@ -113,12 +115,16 @@ type attemptScanner interface {
 func scanAttempt(s attemptScanner) (Attempt, error) {
 	var (
 		attempt            Attempt
+		averagePower       sql.NullFloat64
 		startedAt, endedAt string
 		createdAt          sql.NullString
 	)
-	if err := s.Scan(&attempt.RideID, &attempt.ClimbID, &attempt.Occurrence, &attempt.DistanceM, &attempt.ElevationGainM, &attempt.SlopePercent, &attempt.Cotacol, &attempt.CotacolAlgoVersion, &startedAt, &endedAt, &createdAt); err != nil {
+	if err := s.Scan(&attempt.RideID, &attempt.ClimbID, &attempt.Occurrence, &attempt.DistanceM, &attempt.ElevationGainM, &attempt.SlopePercent, &attempt.Cotacol, &averagePower, &attempt.CotacolAlgoVersion, &startedAt, &endedAt, &createdAt); err != nil {
 		return Attempt{}, err
 	}
+	if averagePower.Valid {
+		attempt.AveragePowerW = &averagePower.Float64
+	}
 	var err error
 	if attempt.StartedAt, err = time.Parse(time.RFC3339, startedAt); err != nil {
 		return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.started_at: %w", err)
@@ -145,8 +151,8 @@ type AttemptStats struct {
 	EndIndex        int
 }
 
-// AttemptFromStats snapshots one analyzed climb occurrence; timestamps come from the ride points bounding the climb interval.
-func AttemptFromStats(rideID int64, occurrence int, stats AttemptStats, timestamps []time.Time) Attempt {
+// AttemptFromStats snapshots one analyzed climb occurrence; timestamps come from the ride points bounding the climb interval, and AveragePowerW is the mean of the power samples inside it (nil when the ride recorded no power).
+func AttemptFromStats(rideID int64, occurrence int, stats AttemptStats, timestamps []time.Time, powers []float64) Attempt {
 	startedAt, endedAt := time.Time{}, time.Time{}
 	if stats.StartIndex < len(timestamps) {
 		startedAt = timestamps[stats.StartIndex]
@@ -162,8 +168,22 @@ func AttemptFromStats(rideID int64, occurrence int, stats AttemptStats, timestam
 		ElevationGainM:     stats.ElevationGainM,
 		SlopePercent:       stats.SlopePercent,
 		Cotacol:            stats.Cotacol,
+		AveragePowerW:      meanPower(powers, stats.StartIndex, stats.EndIndex),
 		CotacolAlgoVersion: climb.CotacolAlgorithmVersion,
 		StartedAt:          startedAt,
 		EndedAt:            endedAt,
 	}
 }
+
+// meanPower averages the power samples inside the climb interval (bounds included); it is nil when the interval does not fit the column.
+func meanPower(powers []float64, start, end int) *float64 {
+	if start < 0 || end >= len(powers) || start > end {
+		return nil
+	}
+	sum := 0.0
+	for _, power := range powers[start : end+1] {
+		sum += power
+	}
+	mean := sum / float64(end-start+1)
+	return &mean
+}