Feature proposal: speed histogram enhancements

[Nicnl]

Hello all,

I'm writing this feature proposal for two improvements of the "speed histogram" panel in the "drive" dashboard.

1. Arbitrary time range:
   Currently, the histogram always groups the data for the entire drive, ignoring the selected time range in Grafana.
   This prevents users from zooming in on specific segments of a trip.
   (Other panels already allows this, notably the map + temperature + elevation)

2. Switch from time-weighted to distance weighted:
   The current histogram groups speed by duration (time spent) instead of distance.
   This often misrepresents the nature of a trip.

   • Example: let's imagine a 2 hour trip with:
     • 1 hour in traffic at 10km/h
     • 1 hour on highway at 90 km/h
   • Currently: the speed histogram shows 50%/50% implying the trip is equal city and highway.
     It makes sense from a "driver comfort" perspective, but it misrepresents the distance driven, and more importantly it doesn't match the reality of the consumed energy.
     (because consumed energy depends on the total driven distance)
   • Proposal: switch to distance weighted.
     We drove a total of 100km, with 90km on the highway.
     Hence the speed histogram shows a split of 10%/90% because 90% of the trip (90km) was driven at 90km/h.
     Also distance-weighted "explains" the efficiency and energy consumed better.

I made two separate SQL requests that fixes each of those points.
As you can see below, the new speed histogram (by distance) feels more natural and fits the map better.

---

In this first example, the time-based histogram says I drove 33% at "high speed" ( over 70km/h ) while it was actually 68% of the driven distance.

---

In this second example, the time-based histogram says I spent 15% at 10 km/h and only drove 51% at high speed while it was actually 82% of the driven distance.

---

1. Request that allows an arbitrary time range, but still has the existing "by time" logic:
   It keeps the existing time-weight logic but adds support for the grafana time picker.

WITH DriveData AS (
    SELECT
        ROUND(convert_km(p.speed::numeric, '$length_unit') / 10,0) * 10 AS speed_section_$length_unit,
        EXTRACT(EPOCH FROM (
            LEAD(p.date) OVER (PARTITION BY p.drive_id ORDER BY p.date) - p.date
        )) AS seconds_elapsed
    FROM positions p
    WHERE $__timeFilter(p.date) AND p.drive_id IS NOT NULL AND p.car_id = $car_id
),
AggregatedData AS (
    SELECT
        speed_section_$length_unit,
        SUM(seconds_elapsed) AS time_in_bucket,
        SUM(seconds_elapsed) * 100.0 / SUM(SUM(seconds_elapsed)) OVER () AS elapsed_percentage
    FROM DriveData
    WHERE seconds_elapsed IS NOT NULL
    GROUP BY 1
)
SELECT
    speed_section_$length_unit AS "Speed",
    elapsed_percentage AS "Elapsed",
    TO_CHAR((time_in_bucket || ' second')::interval, 'HH24:MI:SS') AS "Time"
FROM AggregatedData
WHERE speed_section_$length_unit > 0
ORDER BY 1;

2. Switch to distance weighted:
   This one fixies the second point I made above, which produces a more natural speed histogram that fits the trip better.
   Note: in this one, I renamed the distance columns back into "elapsed" and "time" so that the query can be dropped in the existing panel without changing the panel configuration.

WITH DriveData AS (
    SELECT
        ROUND(convert_km(p.speed::numeric, '$length_unit') / 10,0) * 10 AS speed_section_$length_unit,
        EXTRACT(EPOCH FROM (
            LEAD(p.date) OVER (PARTITION BY p.drive_id ORDER BY p.date) - p.date
        )) AS seconds_elapsed,
        p.speed AS raw_speed_kmh
    FROM positions p
    WHERE $__timeFilter(p.date) AND p.drive_id IS NOT NULL AND p.car_id = $car_id
),
AggregatedData AS (
    SELECT
        speed_section_$length_unit,
        SUM(raw_speed_kmh * (seconds_elapsed / 3600.0)) AS distance_km_in_bucket,
        SUM(raw_speed_kmh * (seconds_elapsed / 3600.0)) * 100.0 / NULLIF(SUM(SUM(raw_speed_kmh * (seconds_elapsed / 3600.0))) OVER (), 0) AS distance_percentage
    FROM DriveData
    WHERE seconds_elapsed IS NOT NULL
    GROUP BY 1
)
SELECT
    speed_section_$length_unit AS "Speed",
    distance_percentage::float AS "Elapsed",
    ROUND(convert_km(distance_km_in_bucket::numeric, '$length_unit'), 2) || ' $length_unit' AS "Time"
FROM AggregatedData
WHERE speed_section_$length_unit > 0
ORDER BY 1;

[eliluong]

I like the analysis and suggestion for histogram. For the y-axis, I think having a y-axis (or labels) indicate the amount of time or the distance can be helpful. It was initially confusing to me looking at the percentages. But then I thought it made more sense to say, oh I drove 50 miles at 90 km/hr, 15 miles at 40 km/hr, so the majority of my driving was at 90 km/hr. I'm not sure which is better to make the data more understandable, though.