mirror of
https://github.com/MeshCore-Beacon/beacon-server.git
synced 2026-09-02 09:03:45 +00:00
MrAlders0n
bc3a375816
The payload-breakdown and top-observers matviews stored a single 7-day total per group, so the 24h/7d/30d selector did nothing: payload ignored the window entirely (the store discarded `since`) and top-observers only varied which rows cleared the last_heard cutoff, never the counts. Bucket both views by hour over a 30-day horizon and sum the buckets in range, mirroring mv_hourly_iata_stats. The stats endpoints now honour the requested window while still reading a small precomputed table.
20 lines
737 B
SQL
20 lines
737 B
SQL
-- Precomputed observer activity, bucketed by hour so top-observers can be
|
|
-- served for any window (24h/7d/30d) by summing the buckets in range. Was a
|
|
-- ~2s per-request scan of a week of observations.
|
|
|
|
CREATE MATERIALIZED VIEW mv_top_observers_by_iata AS
|
|
SELECT
|
|
po.iata,
|
|
po.observer_id,
|
|
o.display_name,
|
|
o.observer_type,
|
|
date_trunc('hour', po.heard_at)::timestamptz AS bucket,
|
|
COUNT(*) AS observation_count
|
|
FROM packet_observations po
|
|
JOIN observers o ON o.id = po.observer_id
|
|
WHERE po.heard_at > NOW() - INTERVAL '30 days'
|
|
GROUP BY po.iata, po.observer_id, o.display_name, o.observer_type, date_trunc('hour', po.heard_at);
|
|
|
|
CREATE UNIQUE INDEX idx_mv_top_observers
|
|
ON mv_top_observers_by_iata(iata, observer_id, bucket);
|