1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
|
package official_climb
import (
"database/sql"
"fmt"
"time"
"github.com/martinlehoux/biking_home/climb"
)
// SortColumn is a climbs-list ordering key.
type SortColumn string
const (
SortName SortColumn = "name"
SortAttempts SortColumn = "attempts"
SortDistance SortColumn = "distance"
SortSlope SortColumn = "slope"
SortCotacol SortColumn = "cotacol"
SortBestTime SortColumn = "best_time"
)
// ListedClimb is one climbs-list row: official climb identity plus aggregates
// over its recorded attempts. Medians are computed in Go from the attempt
// rows; best time is the minimum attempt duration.
type ListedClimb struct {
ID int64
Name string
Attempts int
DistanceM float64
ElevationGainM float64
SlopePercent float64
Cotacol float64
BestDurationS float64
HasBest bool
}
func (c ListedClimb) Category() string {
return climb.CategoryOf(c.Cotacol).String()
}
// ListWithAggregates returns every official climb with aggregates over its
// attempts. Attempts default to zero for climbs never ridden; climbs without
// attempts show no distance/slope stats.
func ListWithAggregates(db *sql.DB) ([]ListedClimb, error) {
climbRows, err := db.Query("SELECT id, name FROM official_climbs ORDER BY name ASC")
if err != nil {
return nil, fmt.Errorf("list climbs: %w", err)
}
defer climbRows.Close()
climbs := make([]ListedClimb, 0)
index := make(map[int64]int)
for climbRows.Next() {
var row ListedClimb
if err := climbRows.Scan(&row.ID, &row.Name); err != nil {
return nil, err
}
index[row.ID] = len(climbs)
climbs = append(climbs, row)
}
if err := climbRows.Err(); err != nil {
return nil, fmt.Errorf("iterate climbs: %w", err)
}
attemptRows, err := db.Query(`
SELECT climb_id, distance_m, elevation_gain_m, slope_percent, cotacol, started_at, ended_at
FROM ride_official_climb_attempts`)
if err != nil {
return nil, fmt.Errorf("list climb attempts: %w", err)
}
defer attemptRows.Close()
samples := make(map[int64]*attemptSamples)
for attemptRows.Next() {
var (
climbID int64
distanceM float64
gainM float64
slope float64
cotacol float64
startedAt string
endedAt string
)
if err := attemptRows.Scan(&climbID, &distanceM, &gainM, &slope, &cotacol, &startedAt, &endedAt); err != nil {
return nil, err
}
sample, known := samples[climbID]
if !known {
sample = &attemptSamples{}
samples[climbID] = sample
}
sample.distances = append(sample.distances, distanceM)
sample.gains = append(sample.gains, gainM)
sample.slopes = append(sample.slopes, slope)
sample.cotacols = append(sample.cotacols, cotacol)
start, err := time.Parse(time.RFC3339, startedAt)
if err != nil {
return nil, fmt.Errorf("invalid ride_official_climb_attempts.started_at: %w", err)
}
end, err := time.Parse(time.RFC3339, endedAt)
if err != nil {
return nil, fmt.Errorf("invalid ride_official_climb_attempts.ended_at: %w", err)
}
durationS := end.Sub(start).Seconds()
if durationS > 0 && (sample.bestDurationS == 0 || durationS < sample.bestDurationS) {
sample.bestDurationS = durationS
}
}
if err := attemptRows.Err(); err != nil {
return nil, fmt.Errorf("iterate climb attempts: %w", err)
}
for id, sample := range samples {
row := &climbs[index[id]]
row.Attempts = len(sample.distances)
row.DistanceM = Median(sample.distances)
row.ElevationGainM = Median(sample.gains)
row.SlopePercent = Median(sample.slopes)
row.Cotacol = Median(sample.cotacols)
if sample.bestDurationS > 0 {
row.BestDurationS = sample.bestDurationS
row.HasBest = true
}
}
return climbs, nil
}
type attemptSamples struct {
distances []float64
gains []float64
slopes []float64
cotacols []float64
bestDurationS float64
}
|
