73 lines
3.0 KiB
SQL
73 lines
3.0 KiB
SQL
-- Crear CAGG daily corregido con cálculos matemáticamente correctos
|
|
CREATE MATERIALIZED VIEW cagg_scanning_report_daily
|
|
WITH (timescaledb.continuous) AS (
|
|
SELECT
|
|
-- Bucket diario
|
|
time_bucket('1 day', "CreationTime") AS "CreationDate",
|
|
|
|
-- Pre-calcular fecha local para consultas más rápidas
|
|
"LocalDate",
|
|
|
|
-- Agrupación
|
|
"ClientMac",
|
|
"NetworkId",
|
|
"NearestApMac",
|
|
|
|
-- Datos del dispositivo (tomar el primer valor del día)
|
|
FIRST("Manufacturer", "CreationTime") as "Manufacturer",
|
|
FIRST("OS", "CreationTime") as "OS",
|
|
FIRST("SSID", "CreationTime") as "SSID",
|
|
|
|
-- CORRECCIÓN: Métricas agregadas correctamente
|
|
MIN("MinimumRssi") AS "MinimumRssi",
|
|
MAX("MaximumRssi") AS "MaximumRssi",
|
|
|
|
-- CRÍTICO: Promedio ponderado correcto en lugar de promedio de promedios
|
|
SUM("RssiSum") / SUM("DetectionsCount") AS "AverageRssi",
|
|
|
|
-- CRÍTICO: Duración total del día, no suma de duraciones parciales
|
|
EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 AS "DurationInMinutes",
|
|
|
|
MIN("FirstDetection") AS "FirstDetection",
|
|
MAX("LastDetection") AS "LastDetection",
|
|
MIN("LocalFirstDetection") AS "LocalFirstDetection",
|
|
MAX("LocalLastDetection") AS "LocalLastDetection",
|
|
SUM("DetectionsCount") AS "DetectionsCount",
|
|
|
|
-- CRÍTICO: Clasificación basada en métricas correctas
|
|
CASE
|
|
WHEN SUM("RssiSum") / SUM("DetectionsCount") > -60
|
|
AND EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 >= 5 THEN 'Visitor'
|
|
WHEN SUM("RssiSum") / SUM("DetectionsCount") <= -60 THEN 'PasserBy'
|
|
ELSE 'Unclassified'
|
|
END AS "PersonType",
|
|
|
|
CASE
|
|
WHEN EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 < 1 THEN 'Less than 1 minute'
|
|
WHEN EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 BETWEEN 1 AND 5 THEN '1-5 minutes'
|
|
WHEN EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 BETWEEN 5 AND 15 THEN '5-15 minutes'
|
|
WHEN EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 BETWEEN 15 AND 30 THEN '15-30 minutes'
|
|
WHEN EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 BETWEEN 30 AND 60 THEN '30-60 minutes'
|
|
WHEN EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 BETWEEN 60 AND 120 THEN '1-2 hours'
|
|
WHEN EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 > 120 THEN 'More than 2 hours'
|
|
END AS "PresenceCategory",
|
|
|
|
COUNT(*) AS "HourlyPeriodsDetected"
|
|
FROM cagg_scanning_report_hourly
|
|
WHERE "CreationTime" < date_trunc('day', now())
|
|
GROUP BY
|
|
1,
|
|
"LocalDate",
|
|
"ClientMac",
|
|
"NetworkId",
|
|
"NearestApMac"
|
|
ORDER BY 1
|
|
);
|
|
|
|
-- Aplicar política de refresh
|
|
SELECT add_continuous_aggregate_policy('cagg_scanning_report_daily',
|
|
start_offset => INTERVAL '3 day',
|
|
end_offset => INTERVAL '1 day',
|
|
schedule_interval => INTERVAL '15 minutes'
|
|
);
|