Hi,
I have two tables with details about my last bike rides from the Strava API. The first one (strava) collects timestamp (start_date_local) and the unique bike ride id of each bike tour (bike_id):
| start_date_local | average_speed | max_speed | average_watts | kilojoules | suffer_score | ride_id |
|---|---|---|---|---|---|---|
| 2021-07-05 06:18:23+00 | 6.44 | 11.3 | 184.4 | 1148.8 | 145 | 5577229383 |
| 2021-07-03 07:27:07+00 | 6.221 | 10.5 | 172.5 | 1121.9 | 65 | 5566354380 |
| 2021-07-02 07:38:18+00 | 6.492 | 10.8 | 190.3 | 1186.7 | 132 | 5561835553 |
| 2021-06-29 07:19:21+00 | 6.22 | 11.8 | 177.7 | 1156.3 | 116 | 5546354060 |
| 2021-06-28 07:19:59+00 | 6.589 | 11.3 | 194.9 | 1154.6 | 165 | 5541394157 |
and another table (strava_heart_rate) has got the bike_id and the recorded time (content) in seconds of different heartrate zones (heart_rate_zone1):
| id | ride_id | heart_rate_zone1 | content |
|---|---|---|---|
| 1 | 5577229383 | 0_to_112 | 0 |
| 2 | 5577229383 | 112_to_148 | 2724 |
| 3 | 5577229383 | 148_to_166 | 3013 |
| 4 | 5577229383 | 166_to_184 | 493 |
| 5 | 5577229383 | 184_to_-1 | 0 |
| 6 | 5566354380 | 0_to_112 | 33 |
| 7 | 5566354380 | 112_to_148 | 5906 |
| 8 | 5566354380 | 148_to_166 | 565 |
| 9 | 5566354380 | 166_to_184 | 0 |
| 10 | 5566354380 | 184_to_-1 | 0 |
I want to display in Grafana a graph that shows these different heart rate zones of each bike ride, like this here:
This is what I have so far:
SELECT
start_date_local AS "time",
ride_id,
heart_rate_zone1 AS metric,
content AS value
FROM strava
JOIN strava_heart_rate USING (ride_id)
WHERE
$__timeFilter(start_date_local)
GROUP BY 1,2,3,4
ORDER BY 1
But unfortunately, it looks not right. I think Grafana sorts it by bike_id and not by the different heart zones:
Does anybody know how to change it?
