42 lines
1.7 KiB
MySQL
42 lines
1.7 KiB
MySQL
|
-- 006_trips_viz_view_v2.sql
|
||
|
|
-- Extend trips_viz_v1 with the enrichment columns added in upstream
|
||
|
|
-- migration 09 (tracksolid.trips: route_geom, start_address, end_address,
|
||
|
|
-- vehicle_plate, waypoints_count) plus webhook-supplied trip metrics from
|
||
|
|
-- migration 03 (idle_time_s, driving_time_s, fuel_consumed_l), surfaced via
|
||
|
|
-- tracksolid.v_trips_enriched (which adds trip_date_eat + daily_seq).
|
||
|
|
--
|
||
|
|
-- Columns are appended only — CREATE OR REPLACE VIEW is safe (no column
|
||
|
|
-- removed, no type changed). Existing consumers see the same first ten
|
||
|
|
-- columns in the same positions.
|
||
|
|
|
||
|
|
CREATE OR REPLACE VIEW public.trips_viz_v1 AS
|
||
|
|
SELECT
|
||
|
|
t.id AS trip_id,
|
||
|
|
t.imei,
|
||
|
|
d.vehicle_name,
|
||
|
|
d.vehicle_number,
|
||
|
|
COALESCE(NULLIF(TRIM(d.cost_centre), ''), 'Unassigned') AS cost_centre,
|
||
|
|
t.start_time,
|
||
|
|
t.end_time,
|
||
|
|
t.distance_km,
|
||
|
|
t.avg_speed_kmh,
|
||
|
|
t.max_speed_kmh,
|
||
|
|
-- ── Enrichment columns (upstream migration 09) ─────────────────────────
|
||
|
|
COALESCE(t.vehicle_plate, d.vehicle_number) AS vehicle_plate,
|
||
|
|
t.start_address,
|
||
|
|
t.end_address,
|
||
|
|
t.waypoints_count,
|
||
|
|
-- ── Webhook-supplied trip metrics (upstream migration 03) ──────────────
|
||
|
|
t.driving_time_s,
|
||
|
|
t.idle_time_s,
|
||
|
|
t.fuel_consumed_l,
|
||
|
|
-- ── Stable per-day trip ordinal (upstream migration 09) ────────────────
|
||
|
|
t.trip_date_eat,
|
||
|
|
t.daily_seq
|
||
|
|
FROM tracksolid.v_trips_enriched t
|
||
|
|
JOIN tracksolid.devices d ON d.imei = t.imei
|
||
|
|
WHERE t.start_time IS NOT NULL
|
||
|
|
AND t.end_time IS NOT NULL;
|
||
|
|
|
||
|
|
GRANT SELECT ON public.trips_viz_v1 TO viz_anon;
|