354 lines
13 KiB
SQL
354 lines
13 KiB
SQL
-- =============================================================================
|
|
-- Vista: splash_wifi_scanning_metrics_by_network
|
|
-- Descripción: Pre-calcula métricas de visitantes agregadas por NetworkId
|
|
-- Autor: Sistema
|
|
-- Fecha: 2025-10-24
|
|
-- Optimización: Eliminados JOINs innecesarios (datos ya en scanning_report_daily_full)
|
|
-- =============================================================================
|
|
|
|
-- Vista Simple: Agregación por Network (sin filtro de fechas)
|
|
-- OPTIMIZADA: Sin JOINs, usa solo scanning_report_daily_full que ya tiene NetworkName y OrganizationName
|
|
CREATE OR REPLACE VIEW public.splash_wifi_scanning_metrics_by_network AS
|
|
SELECT
|
|
s."NetworkId",
|
|
MAX(s."NetworkName") AS "NetworkName", -- Usa MAX para agregación (todos son iguales por NetworkId)
|
|
MAX(s."OrganizationName") AS "OrganizationName",
|
|
|
|
-- Rango de fechas
|
|
MIN(s."DetectionDate") AS "FirstDetection",
|
|
MAX(s."DetectionDate") AS "LastDetection",
|
|
|
|
-- Métricas de visitantes (exactas al método CalculateVisitorMetrics)
|
|
COUNT(*) AS "TotalPersons",
|
|
COUNT(*) FILTER (WHERE s."PersonType" = 'Visitor') AS "Visitors",
|
|
COUNT(*) FILTER (WHERE s."PersonType" != 'Visitor') AS "NonVisitors",
|
|
|
|
-- VisitorRate: Porcentaje de visitantes
|
|
CASE
|
|
WHEN COUNT(*) > 0
|
|
THEN ROUND((COUNT(*) FILTER (WHERE s."PersonType" = 'Visitor')::numeric / COUNT(*)::numeric * 100), 1)
|
|
ELSE 0
|
|
END AS "VisitorRate",
|
|
|
|
-- AverageDurationMinutes
|
|
CASE
|
|
WHEN COUNT(*) > 0
|
|
THEN ROUND(AVG(s."DurationInMinutes")::numeric, 0)::integer
|
|
ELSE 0
|
|
END AS "AverageDurationMinutes",
|
|
|
|
-- Métricas adicionales útiles
|
|
MIN(s."DurationInMinutes") AS "MinDurationMinutes",
|
|
MAX(s."DurationInMinutes") AS "MaxDurationMinutes",
|
|
COUNT(DISTINCT s."MacAddress") AS "UniqueDevices",
|
|
COUNT(DISTINCT CASE WHEN s."IsRegisteredUser" THEN s."MacAddress" END) AS "RegisteredUsers",
|
|
|
|
-- Distribución por categoría de presencia
|
|
COUNT(*) FILTER (WHERE s."PresenceCategory" IS NOT NULL) AS "CategorizedDetections",
|
|
|
|
-- Métricas de señal
|
|
ROUND(AVG(s."AverageRssi")::numeric, 0)::integer AS "AvgSignalStrength"
|
|
|
|
FROM public.scanning_report_daily_full s
|
|
GROUP BY s."NetworkId"
|
|
ORDER BY s."NetworkId";
|
|
|
|
-- =============================================================================
|
|
-- Vista Materializada: splash_wifi_scanning_metrics_daily
|
|
-- Descripción: Métricas agregadas por NetworkId y Fecha (para mejor performance)
|
|
-- Uso: Consultas con filtros de fecha frecuentes en dashboards
|
|
-- Mantenimiento: Refrescar diariamente con REFRESH MATERIALIZED VIEW
|
|
-- Optimización: Eliminados JOINs innecesarios (datos ya en scanning_report_daily_full)
|
|
-- =============================================================================
|
|
|
|
CREATE MATERIALIZED VIEW IF NOT EXISTS public.splash_wifi_scanning_metrics_daily AS
|
|
SELECT
|
|
s."NetworkId",
|
|
MAX(s."NetworkName") AS "NetworkName", -- Usa MAX para agregación
|
|
MAX(s."OrganizationName") AS "OrganizationName",
|
|
s."DetectionDate"::date AS "Date",
|
|
MAX(s."Year") AS "Year",
|
|
MAX(s."MonthNumber") AS "MonthNumber",
|
|
MAX(s."MonthName") AS "MonthName",
|
|
MAX(s."WeekNumber") AS "WeekNumber",
|
|
MAX(s."DayOfWeek") AS "DayOfWeek",
|
|
MAX(s."DayName") AS "DayName",
|
|
|
|
-- Métricas exactas de CalculateVisitorMetrics
|
|
COUNT(*) AS "TotalPersons",
|
|
COUNT(*) FILTER (WHERE s."PersonType" = 'Visitor') AS "Visitors",
|
|
COUNT(*) FILTER (WHERE s."PersonType" != 'Visitor') AS "NonVisitors",
|
|
|
|
-- VisitorRate: Porcentaje de visitantes
|
|
CASE
|
|
WHEN COUNT(*) > 0
|
|
THEN ROUND((COUNT(*) FILTER (WHERE s."PersonType" = 'Visitor')::numeric / COUNT(*)::numeric * 100), 1)
|
|
ELSE 0
|
|
END AS "VisitorRate",
|
|
|
|
-- AverageDurationMinutes
|
|
CASE
|
|
WHEN COUNT(*) > 0
|
|
THEN ROUND(AVG(s."DurationInMinutes")::numeric, 0)::integer
|
|
ELSE 0
|
|
END AS "AverageDurationMinutes",
|
|
|
|
-- Métricas adicionales
|
|
MIN(s."DurationInMinutes") AS "MinDurationMinutes",
|
|
MAX(s."DurationInMinutes") AS "MaxDurationMinutes",
|
|
COUNT(DISTINCT s."MacAddress") AS "UniqueDevices",
|
|
COUNT(DISTINCT CASE WHEN s."IsRegisteredUser" THEN s."MacAddress" END) AS "RegisteredUsers",
|
|
|
|
-- Métricas de señal
|
|
ROUND(AVG(s."AverageRssi")::numeric, 0)::integer AS "AvgSignalStrength",
|
|
MIN(s."MinimumRssi") AS "MinRssi",
|
|
MAX(s."MaximumRssi") AS "MaxRssi"
|
|
|
|
FROM public.scanning_report_daily_full s
|
|
GROUP BY
|
|
s."NetworkId",
|
|
s."DetectionDate"::date;
|
|
|
|
-- =============================================================================
|
|
-- Índices para Vista Materializada
|
|
-- =============================================================================
|
|
|
|
-- Índices para mejorar performance en consultas con filtros
|
|
CREATE INDEX IF NOT EXISTS idx_scanning_metrics_daily_date
|
|
ON public.splash_wifi_scanning_metrics_daily("Date", "NetworkId");
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_scanning_metrics_daily_network
|
|
ON public.splash_wifi_scanning_metrics_daily("NetworkId", "Date");
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_scanning_metrics_daily_year_month
|
|
ON public.splash_wifi_scanning_metrics_daily("Year", "MonthNumber", "NetworkId");
|
|
|
|
-- =============================================================================
|
|
-- CRÍTICO: Índices en la tabla/vista base scanning_report_daily_full
|
|
-- NOTA: Estos índices son ESENCIALES para mejorar performance
|
|
-- Si scanning_report_daily_full es una VISTA, crear MATERIALIZED VIEW con índices
|
|
-- Si es una TABLA, estos índices mejorarán drasticamente las consultas
|
|
-- =============================================================================
|
|
|
|
-- Índice compuesto para filtros de fecha y network (más usado)
|
|
CREATE INDEX IF NOT EXISTS idx_scanning_daily_networkid_date
|
|
ON public.scanning_report_daily_full("NetworkId", "DetectionDate");
|
|
|
|
-- Índice solo por fecha (para queries que filtran todas las networks)
|
|
CREATE INDEX IF NOT EXISTS idx_scanning_daily_date
|
|
ON public.scanning_report_daily_full("DetectionDate");
|
|
|
|
-- Índice por PersonType para filtros específicos
|
|
CREATE INDEX IF NOT EXISTS idx_scanning_daily_persontype
|
|
ON public.scanning_report_daily_full("PersonType");
|
|
|
|
-- Índice compuesto para queries complejas (NetworkId, Date, PersonType)
|
|
CREATE INDEX IF NOT EXISTS idx_scanning_daily_composite
|
|
ON public.scanning_report_daily_full("NetworkId", "DetectionDate", "PersonType");
|
|
|
|
-- =============================================================================
|
|
-- Comandos de Mantenimiento
|
|
-- =============================================================================
|
|
|
|
-- Refrescar la vista materializada (ejecutar diariamente o según necesidad)
|
|
-- REFRESH MATERIALIZED VIEW public.splash_wifi_scanning_metrics_daily;
|
|
|
|
-- Refrescar sin bloquear lecturas (requiere índice único, PostgreSQL 9.4+)
|
|
-- REFRESH MATERIALIZED VIEW CONCURRENTLY public.splash_wifi_scanning_metrics_daily;
|
|
|
|
-- Eliminar vista materializada (si necesitas recrearla)
|
|
-- DROP MATERIALIZED VIEW IF EXISTS public.splash_wifi_scanning_metrics_daily;
|
|
|
|
-- =============================================================================
|
|
-- Ejemplos de Uso
|
|
-- =============================================================================
|
|
|
|
-- =============================================================================
|
|
-- CONSULTA OPTIMIZADA: Equivalente a tu consulta original pero MUCHO MÁS RÁPIDA
|
|
-- Esta consulta debería ser más rápida que C# porque:
|
|
-- 1. No hace JOINs innecesarios
|
|
-- 2. Usa índices apropiados
|
|
-- 3. Filtra ANTES de agregar
|
|
-- =============================================================================
|
|
|
|
-- Consulta directa optimizada (sin vista pre-agregada)
|
|
-- Usa esta para consultas ad-hoc con filtros específicos
|
|
-- DEBE tardar MENOS de 7.63 segundos con índices apropiados
|
|
/*
|
|
SELECT
|
|
"NetworkId",
|
|
MAX("NetworkName") AS "NetworkName",
|
|
MAX("OrganizationName") AS "OrganizationName",
|
|
|
|
-- Métricas exactas de CalculateVisitorMetrics
|
|
COUNT(*) AS "TotalPersons",
|
|
COUNT(*) FILTER (WHERE "PersonType" = 'Visitor') AS "Visitors",
|
|
|
|
CASE
|
|
WHEN COUNT(*) > 0
|
|
THEN ROUND((COUNT(*) FILTER (WHERE "PersonType" = 'Visitor')::numeric / COUNT(*)::numeric * 100), 1)
|
|
ELSE 0
|
|
END AS "VisitorRate",
|
|
|
|
CASE
|
|
WHEN COUNT(*) > 0
|
|
THEN ROUND(AVG("DurationInMinutes")::numeric, 0)::integer
|
|
ELSE 0
|
|
END AS "AverageDurationMinutes"
|
|
|
|
FROM public.scanning_report_daily_full
|
|
WHERE "NetworkId" IN (17, 372, 82, 93, 95)
|
|
AND "DetectionDate" >= '2025-10-18'
|
|
AND "DetectionDate" <= '2025-10-25'
|
|
GROUP BY "NetworkId"
|
|
ORDER BY "NetworkId";
|
|
*/
|
|
|
|
-- =============================================================================
|
|
-- DIAGNÓSTICO DE PERFORMANCE
|
|
-- =============================================================================
|
|
|
|
-- Ver plan de ejecución de la consulta (busca "Seq Scan" - malo, "Index Scan" - bueno)
|
|
/*
|
|
EXPLAIN ANALYZE
|
|
SELECT
|
|
"NetworkId",
|
|
COUNT(*) AS "TotalPersons",
|
|
COUNT(*) FILTER (WHERE "PersonType" = 'Visitor') AS "Visitors"
|
|
FROM public.scanning_report_daily_full
|
|
WHERE "NetworkId" IN (17, 372, 82, 93, 95)
|
|
AND "DetectionDate" >= '2025-10-18'
|
|
AND "DetectionDate" <= '2025-10-25'
|
|
GROUP BY "NetworkId";
|
|
*/
|
|
|
|
-- Ver estadísticas de índices utilizados
|
|
/*
|
|
SELECT
|
|
schemaname,
|
|
tablename,
|
|
indexname,
|
|
idx_scan,
|
|
idx_tup_read,
|
|
idx_tup_fetch
|
|
FROM pg_stat_user_indexes
|
|
WHERE tablename = 'scanning_report_daily_full'
|
|
ORDER BY idx_scan DESC;
|
|
*/
|
|
|
|
-- Ver tamaño de la tabla/vista
|
|
/*
|
|
SELECT
|
|
pg_size_pretty(pg_total_relation_size('scanning_report_daily_full')) AS total_size,
|
|
pg_size_pretty(pg_relation_size('scanning_report_daily_full')) AS table_size,
|
|
pg_size_pretty(pg_indexes_size('scanning_report_daily_full')) AS indexes_size;
|
|
*/
|
|
|
|
-- =============================================================================
|
|
-- Ejemplos de Uso con Vistas Pre-agregadas
|
|
-- =============================================================================
|
|
|
|
-- Ejemplo 1: Obtener métricas de un network específico (sin filtro de fecha)
|
|
/*
|
|
SELECT
|
|
"NetworkName",
|
|
"TotalPersons",
|
|
"Visitors",
|
|
"VisitorRate",
|
|
"AverageDurationMinutes"
|
|
FROM public.splash_wifi_scanning_metrics_by_network
|
|
WHERE "NetworkId" = 123;
|
|
*/
|
|
|
|
-- Ejemplo 2: Obtener métricas por rango de fechas (USA LA VISTA MATERIALIZADA)
|
|
/*
|
|
SELECT
|
|
"NetworkName",
|
|
"Date",
|
|
"TotalPersons",
|
|
"Visitors",
|
|
"VisitorRate",
|
|
"AverageDurationMinutes"
|
|
FROM public.splash_wifi_scanning_metrics_daily
|
|
WHERE "NetworkId" = 123
|
|
AND "Date" BETWEEN '2025-01-01' AND '2025-01-31'
|
|
ORDER BY "Date";
|
|
*/
|
|
|
|
-- Ejemplo 3: Agregar métricas de múltiples días en un período (RECOMENDADO)
|
|
/*
|
|
SELECT
|
|
"NetworkId",
|
|
"NetworkName",
|
|
SUM("TotalPersons") AS "TotalPersons",
|
|
SUM("Visitors") AS "Visitors",
|
|
CASE
|
|
WHEN SUM("TotalPersons") > 0
|
|
THEN ROUND((SUM("Visitors")::numeric / SUM("TotalPersons")::numeric * 100), 1)
|
|
ELSE 0
|
|
END AS "VisitorRate",
|
|
ROUND(AVG("AverageDurationMinutes")::numeric, 0)::integer AS "AverageDurationMinutes"
|
|
FROM public.splash_wifi_scanning_metrics_daily
|
|
WHERE "Date" BETWEEN '2025-10-18' AND '2025-10-25'
|
|
AND "NetworkId" IN (17, 372, 82, 93, 95)
|
|
GROUP BY "NetworkId", "NetworkName";
|
|
*/
|
|
|
|
-- Ejemplo 4: Top 10 Networks por VisitorRate en el último mes
|
|
/*
|
|
SELECT
|
|
"NetworkName",
|
|
SUM("TotalPersons") AS "TotalPersons",
|
|
SUM("Visitors") AS "Visitors",
|
|
CASE
|
|
WHEN SUM("TotalPersons") > 0
|
|
THEN ROUND((SUM("Visitors")::numeric / SUM("TotalPersons")::numeric * 100), 1)
|
|
ELSE 0
|
|
END AS "VisitorRate"
|
|
FROM public.splash_wifi_scanning_metrics_daily
|
|
WHERE "Date" >= CURRENT_DATE - INTERVAL '30 days'
|
|
GROUP BY "NetworkId", "NetworkName"
|
|
ORDER BY "VisitorRate" DESC
|
|
LIMIT 10;
|
|
*/
|
|
|
|
-- =============================================================================
|
|
-- Permisos
|
|
-- =============================================================================
|
|
ALTER TABLE public.splash_wifi_scanning_metrics_by_network OWNER TO mysql;
|
|
ALTER TABLE public.splash_wifi_scanning_metrics_daily OWNER TO mysql;
|
|
|
|
-- =============================================================================
|
|
-- RECOMENDACIONES DE OPTIMIZACIÓN
|
|
-- =============================================================================
|
|
/*
|
|
Si la consulta sigue siendo lenta después de crear los índices:
|
|
|
|
1. VERIFICAR si scanning_report_daily_full es una VISTA o TABLA:
|
|
- Si es VISTA: Considera convertirla a MATERIALIZED VIEW
|
|
- Si es TABLA: Los índices deberían ayudar significativamente
|
|
|
|
2. ANALIZAR estadísticas de la tabla:
|
|
ANALYZE public.scanning_report_daily_full;
|
|
|
|
3. VACUUM si la tabla tiene muchos registros eliminados:
|
|
VACUUM ANALYZE public.scanning_report_daily_full;
|
|
|
|
4. CONSIDERAR particionamiento por fecha si la tabla es muy grande (>10M registros):
|
|
CREATE TABLE scanning_report_daily_full_2025_01 PARTITION OF scanning_report_daily_full
|
|
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
|
|
|
|
5. MONITOREAR queries lentas:
|
|
SELECT query, mean_exec_time, calls
|
|
FROM pg_stat_statements
|
|
WHERE query LIKE '%scanning_report_daily_full%'
|
|
ORDER BY mean_exec_time DESC
|
|
LIMIT 10;
|
|
|
|
6. AJUSTAR parámetros de PostgreSQL si es necesario:
|
|
- shared_buffers (25% de RAM)
|
|
- effective_cache_size (50-75% de RAM)
|
|
- work_mem (para sorts y agregaciones grandes)
|
|
- maintenance_work_mem (para CREATE INDEX)
|
|
*/
|