mirror of
https://github.com/MeshCore-Beacon/beacon-server.git
synced 2026-09-19 18:24:33 +00:00
MrAlders0n
7650d9b742
GET /observers/{id}/activity returns bucketed observation counts, LoRa
airtime, SNR/RSSI aggregates and a payload-type breakdown for one
observer. Airtime is computed at ingest from the raw frame length and
stored on the observation; intervals of 1h and up are served from an
hourly matview so no request path joins back to packets.
25 lines
1.4 KiB
SQL
25 lines
1.4 KiB
SQL
-- Hourly per-observer rollup so 7d/30d activity sums a few thousand rows instead of scanning
|
|
-- a month of observations. payload_type is a key so the breakdown comes from the same rows.
|
|
-- sqlc types the cast aggregates as non-null even though airtime_ms/snr_*/rssi_sum are NULL for
|
|
-- empty groups, so readers must gate on the matching *_n counts.
|
|
CREATE MATERIALIZED VIEW mv_observer_activity_hourly AS
|
|
SELECT
|
|
observer_id,
|
|
payload_type,
|
|
date_trunc('hour', heard_at)::timestamptz AS bucket,
|
|
COUNT(*)::bigint AS observations,
|
|
SUM(airtime_ms)::real AS airtime_ms,
|
|
COUNT(airtime_ms)::bigint AS airtime_n,
|
|
SUM(snr) FILTER (WHERE NOT (COALESCE(rssi, 0) = 0 AND COALESCE(snr, 0) = 0))::real AS snr_sum,
|
|
COUNT(snr) FILTER (WHERE NOT (COALESCE(rssi, 0) = 0 AND COALESCE(snr, 0) = 0))::bigint AS snr_n,
|
|
MIN(snr) FILTER (WHERE NOT (COALESCE(rssi, 0) = 0 AND COALESCE(snr, 0) = 0))::real AS snr_min,
|
|
SUM(rssi) FILTER (WHERE NOT (COALESCE(rssi, 0) = 0 AND COALESCE(snr, 0) = 0))::bigint AS rssi_sum,
|
|
COUNT(rssi) FILTER (WHERE NOT (COALESCE(rssi, 0) = 0 AND COALESCE(snr, 0) = 0))::bigint AS rssi_n
|
|
FROM packet_observations
|
|
WHERE heard_at > NOW() - INTERVAL '30 days'
|
|
AND payload_type IS NOT NULL
|
|
GROUP BY observer_id, payload_type, date_trunc('hour', heard_at);
|
|
|
|
CREATE UNIQUE INDEX idx_mv_observer_activity_hourly
|
|
ON mv_observer_activity_hourly(observer_id, payload_type, bucket);
|