Las vistas y KPIs proporcionan una capa de abstracción sobre las tablas base, ofreciendo métricas calculadas en tiempo real y agregaciones optimizadas para dashboards, reportes y toma de decisiones. Estas vistas materializadas y funciones garantizan performance consistente incluso con millones de registros.
CREATE MATERIALIZED VIEW siga.v_fleet_utilization_realtime AS
WITH vehicle_status AS (
SELECT
v.id,
v.plate,
v.current_zone,
v.status,
v.next_available,
CASE
WHEN v.status = 'IN_TRANSIT' THEN 'PRODUCTIVE'
WHEN v.status = 'LOADING' THEN 'PRODUCTIVE'
WHEN v.status = 'UNLOADING' THEN 'PRODUCTIVE'
WHEN v.status = 'WAITING' AND v.current_zone IN (
SELECT code FROM siga.zones WHERE type = 'PORT'
) THEN 'STRATEGIC_WAIT'
WHEN v.status = 'WAITING' THEN 'IDLE'
WHEN v.status = 'MAINTENANCE' THEN 'UNAVAILABLE'
ELSE 'UNKNOWN'
END as utilization_status,
-- Tiempo en estado actual
EXTRACT(EPOCH FROM (NOW() - v.updated_at)) / 3600 as hours_in_status
FROM siga.vehicles v
WHERE v.active = true
),
utilization_summary AS (
SELECT
COUNT(*) FILTER (WHERE utilization_status = 'PRODUCTIVE') as productive_count,
COUNT(*) FILTER (WHERE utilization_status = 'STRATEGIC_WAIT') as strategic_wait_count,
COUNT(*) FILTER (WHERE utilization_status = 'IDLE') as idle_count,
COUNT(*) FILTER (WHERE utilization_status = 'UNAVAILABLE') as unavailable_count,
COUNT(*) as total_fleet
FROM vehicle_status
)
SELECT
NOW() as calculated_at,
total_fleet,
productive_count,
strategic_wait_count,
idle_count,
unavailable_count,
-- Porcentajes
ROUND(productive_count::NUMERIC / NULLIF(total_fleet, 0) * 100, 2) as utilization_percentage,
ROUND((productive_count + strategic_wait_count)::NUMERIC / NULLIF(total_fleet, 0) * 100, 2) as effective_utilization,
ROUND(idle_count::NUMERIC / NULLIF(total_fleet, 0) * 100, 2) as idle_percentage,
-- KPIs adicionales
(SELECT AVG(hours_in_status) FROM vehicle_status WHERE utilization_status = 'IDLE') as avg_idle_hours,
(SELECT COUNT(DISTINCT current_zone) FROM vehicle_status WHERE utilization_status = 'PRODUCTIVE') as active_zones
FROM utilization_summary;
-- Índice para refrescos rápidos
CREATE UNIQUE INDEX idx_fleet_utilization_realtime ON siga.v_fleet_utilization_realtime (calculated_at);
-- Refresco cada 5 minutos
CREATE OR REPLACE FUNCTION refresh_fleet_utilization()
RETURNS void AS $$
BEGIN
REFRESH MATERIALIZED VIEW CONCURRENTLY siga.v_fleet_utilization_realtime;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE VIEW siga.v_empty_kilometers_analysis AS
WITH trip_segments AS (
-- Extraer segmentos de viaje de las posiciones
SELECT
vp1.vehicle_id,
vp1.time as start_time,
vp2.time as end_time,
ST_Distance(vp1.position::geometry, vp2.position::geometry) / 1000 as distance_km,
-- Determinar si el segmento es vacío basado en órdenes
CASE
WHEN EXISTS (
SELECT 1 FROM siga.orders o
WHERE o.assigned_vehicle_id = vp1.vehicle_id
AND o.status IN ('IN_TRANSIT', 'LOADING')
AND vp1.time BETWEEN o.pickup_window AND o.delivery_window
) THEN false
ELSE true
END as is_empty
FROM vehicle_positions vp1
JOIN vehicle_positions vp2
ON vp1.vehicle_id = vp2.vehicle_id
AND vp2.time = (
SELECT MIN(time)
FROM vehicle_positions
WHERE vehicle_id = vp1.vehicle_id
AND time > vp1.time
)
WHERE vp1.time > NOW() - INTERVAL '24 hours'
),
daily_summary AS (
SELECT
DATE_TRUNC('day', start_time) as day,
vehicle_id,
SUM(distance_km) as total_km,
SUM(CASE WHEN is_empty THEN distance_km ELSE 0 END) as empty_km,
COUNT(*) FILTER (WHERE is_empty) as empty_segments
FROM trip_segments
GROUP BY DATE_TRUNC('day', start_time), vehicle_id
)
SELECT
day,
COUNT(DISTINCT vehicle_id) as vehicles_active,
-- Totales
ROUND(SUM(total_km), 2) as total_km,
ROUND(SUM(empty_km), 2) as empty_km,
-- Ratios
ROUND(SUM(empty_km) / NULLIF(SUM(total_km), 0) * 100, 2) as empty_km_percentage,
-- Promedios por vehículo
ROUND(AVG(total_km), 2) as avg_km_per_vehicle,
ROUND(AVG(empty_km), 2) as avg_empty_km_per_vehicle,
-- Distribución
COUNT(*) FILTER (WHERE empty_km / NULLIF(total_km, 0) > 0.4) as vehicles_high_empty,
COUNT(*) FILTER (WHERE empty_km / NULLIF(total_km, 0) < 0.2) as vehicles_low_empty,
-- Costo estimado
ROUND(SUM(empty_km) * 0.70, 2) as empty_km_cost_eur
FROM daily_summary
GROUP BY day
ORDER BY day DESC;
CREATE MATERIALIZED VIEW siga.v_delivery_performance AS
WITH delivery_metrics AS (
SELECT
o.id as order_id,
o.customer_id,
o.origin_zone,
o.destination_zone,
o.assigned_vehicle_id,
o.assigned_driver_id,
-- Tiempos planificados vs reales
o.pickup_window,
o.delivery_window,
-- Obtener tiempos reales de los eventos
(SELECT changed_at FROM siga.order_status_history
WHERE order_id = o.id AND status = 'LOADING'
ORDER BY changed_at DESC LIMIT 1) as actual_pickup_time,
(SELECT changed_at FROM siga.order_status_history
WHERE order_id = o.id AND status = 'DELIVERED'
ORDER BY changed_at DESC LIMIT 1) as actual_delivery_time,
-- Métricas económicas
o.agreed_revenue,
o.actual_cost,
o.agreed_revenue - COALESCE(o.actual_cost, o.estimated_cost) as margin
FROM siga.orders o
WHERE o.status = 'DELIVERED'
AND o.created_at > NOW() - INTERVAL '30 days'
),
performance_calc AS (
SELECT
*,
-- On-time pickup
CASE
WHEN actual_pickup_time BETWEEN pickup_window THEN true
ELSE false
END as on_time_pickup,
-- On-time delivery
CASE
WHEN actual_delivery_time BETWEEN delivery_window THEN true
ELSE false
END as on_time_delivery,
-- Delays en minutos
CASE
WHEN actual_delivery_time > upper(delivery_window) THEN
EXTRACT(EPOCH FROM (actual_delivery_time - upper(delivery_window))) / 60
ELSE 0
END as delivery_delay_minutes
FROM delivery_metrics
)
SELECT
DATE_TRUNC('day', actual_delivery_time) as delivery_date,
-- Volumen
COUNT(*) as total_deliveries,
COUNT(DISTINCT customer_id) as unique_customers,
COUNT(DISTINCT assigned_vehicle_id) as vehicles_used,
-- Performance temporal
COUNT(*) FILTER (WHERE on_time_pickup) as on_time_pickups,
COUNT(*) FILTER (WHERE on_time_delivery) as on_time_deliveries,
ROUND(COUNT(*) FILTER (WHERE on_time_delivery)::NUMERIC / COUNT(*) * 100, 2) as otd_percentage,
-- Delays
AVG(delivery_delay_minutes) FILTER (WHERE delivery_delay_minutes > 0) as avg_delay_minutes,
MAX(delivery_delay_minutes) as max_delay_minutes,
-- Performance por zonas
MODE() WITHIN GROUP (ORDER BY origin_zone) as most_common_origin,
MODE() WITHIN GROUP (ORDER BY destination_zone) as most_common_destination,
-- Métricas financieras
SUM(agreed_revenue) as total_revenue,
SUM(actual_cost) as total_cost,
SUM(margin) as total_margin,
ROUND(SUM(margin) / NULLIF(SUM(agreed_revenue), 0) * 100, 2) as margin_percentage
FROM performance_calc
GROUP BY DATE_TRUNC('day', actual_delivery_time);
-- Índice para queries rápidas
CREATE INDEX idx_delivery_performance_date ON siga.v_delivery_performance (delivery_date DESC);
CREATE OR REPLACE VIEW siga.v_route_profitability AS
WITH route_metrics AS (
SELECT
o.origin_zone,
o.destination_zone,
COUNT(*) as trip_count,
-- Ingresos
AVG(o.agreed_revenue) as avg_revenue,
SUM(o.agreed_revenue) as total_revenue,
-- Costos
AVG(o.actual_cost) as avg_cost,
SUM(o.actual_cost) as total_cost,
-- Distancia y tiempo
AVG(ST_Distance(o.origin_point::geometry, o.destination_point::geometry) / 1000) as avg_distance_km,
AVG(EXTRACT(EPOCH FROM (upper(o.delivery_window) - lower(o.pickup_window))) / 3600) as avg_duration_hours,
-- Retornos vacíos
COUNT(*) FILTER (WHERE NOT EXISTS (
SELECT 1 FROM siga.orders o2
WHERE o2.origin_zone = o.destination_zone
AND o2.assigned_vehicle_id = o.assigned_vehicle_id
AND o2.pickup_window >= o.delivery_window
AND o2.pickup_window <= o.delivery_window + INTERVAL '24 hours'
)) as empty_returns
FROM siga.orders o
WHERE o.status = 'DELIVERED'
AND o.created_at > NOW() - INTERVAL '90 days'
GROUP BY o.origin_zone, o.destination_zone
HAVING COUNT(*) >= 5 -- Solo rutas con volumen significativo
)
SELECT
rm.origin_zone,
rm.destination_zone,
rm.trip_count,
-- Métricas básicas
ROUND(rm.avg_revenue, 2) as avg_revenue,
ROUND(rm.avg_cost, 2) as avg_cost,
ROUND(rm.avg_revenue - rm.avg_cost, 2) as avg_margin,
ROUND((rm.avg_revenue - rm.avg_cost) / NULLIF(rm.avg_revenue, 0) * 100, 2) as margin_percentage,
-- Eficiencia
ROUND(rm.avg_distance_km, 2) as avg_distance_km,
ROUND(rm.avg_revenue / NULLIF(rm.avg_distance_km, 0), 2) as revenue_per_km,
ROUND((rm.avg_revenue - rm.avg_cost) / NULLIF(rm.avg_distance_km, 0), 2) as margin_per_km,
-- Problema de retornos vacíos
rm.empty_returns,
ROUND(rm.empty_returns::NUMERIC / rm.trip_count * 100, 2) as empty_return_percentage,
ROUND(rm.empty_returns * rm.avg_distance_km * 0.70, 2) as empty_return_cost,
-- Score de rentabilidad (0-100)
CASE
WHEN (rm.avg_revenue - rm.avg_cost) / NULLIF(rm.avg_revenue, 0) > 0.25 THEN 100
WHEN (rm.avg_revenue - rm.avg_cost) / NULLIF(rm.avg_revenue, 0) > 0.20 THEN 80
WHEN (rm.avg_revenue - rm.avg_cost) / NULLIF(rm.avg_revenue, 0) > 0.15 THEN 60
WHEN (rm.avg_revenue - rm.avg_cost) / NULLIF(rm.avg_revenue, 0) > 0.10 THEN 40
ELSE 20
END as profitability_score
FROM route_metrics rm
ORDER BY avg_margin DESC;
CREATE MATERIALIZED VIEW siga.v_vehicle_financial_performance AS
WITH vehicle_period AS (
SELECT generate_series(
DATE_TRUNC('month', NOW() - INTERVAL '6 months'),
DATE_TRUNC('month', NOW()),
INTERVAL '1 month'
) as month
),
vehicle_metrics AS (
SELECT
vp.month,
v.id as vehicle_id,
v.plate,
v.type as vehicle_type,
-- Ingresos del mes
COALESCE(SUM(o.agreed_revenue), 0) as monthly_revenue,
-- Costos operativos
COALESCE(SUM(o.actual_cost), 0) as operational_cost,
-- Kilómetros
COALESCE(
(SELECT SUM(ST_Distance(p1.position::geometry, p2.position::geometry) / 1000)
FROM vehicle_positions p1
JOIN vehicle_positions p2
ON p1.vehicle_id = p2.vehicle_id
AND p2.time = (SELECT MIN(time) FROM vehicle_positions WHERE vehicle_id = p1.vehicle_id AND time > p1.time)
WHERE p1.vehicle_id = v.id
AND DATE_TRUNC('month', p1.time) = vp.month
), 0
) as total_km,
-- Entregas
COUNT(o.id) as deliveries_count,
-- Días activos
COUNT(DISTINCT DATE(o.actual_delivery_time)) as active_days
FROM vehicle_period vp
CROSS JOIN siga.vehicles v
LEFT JOIN siga.orders o
ON o.assigned_vehicle_id = v.id
AND DATE_TRUNC('month', o.actual_delivery_time) = vp.month
AND o.status = 'DELIVERED'
WHERE v.active = true
GROUP BY vp.month, v.id, v.plate, v.type
),
performance_calc AS (
SELECT
*,
monthly_revenue - operational_cost as gross_margin,
-- Costos fijos estimados
CASE vehicle_type
WHEN 'TRAILER' THEN 3500
WHEN 'RIGID' THEN 2500
WHEN 'VAN' THEN 1500
END as fixed_monthly_cost,
-- Métricas de eficiencia
CASE WHEN total_km > 0 THEN monthly_revenue / total_km ELSE 0 END as revenue_per_km,
CASE WHEN active_days > 0 THEN monthly_revenue / active_days ELSE 0 END as revenue_per_day
FROM vehicle_metrics
)
SELECT
month,
vehicle_id,
plate,
vehicle_type,
-- Métricas financieras
ROUND(monthly_revenue, 2) as monthly_revenue,
ROUND(operational_cost, 2) as operational_cost,
ROUND(gross_margin, 2) as gross_margin,
ROUND(gross_margin - fixed_monthly_cost, 2) as net_margin,
-- Ratios
ROUND(gross_margin / NULLIF(monthly_revenue, 0) * 100, 2) as gross_margin_percentage,
ROUND((gross_margin - fixed_monthly_cost) / NULLIF(monthly_revenue, 0) * 100, 2) as net_margin_percentage,
-- Eficiencia
ROUND(revenue_per_km, 2) as revenue_per_km,
ROUND(revenue_per_day, 2) as revenue_per_day,
deliveries_count,
active_days,
ROUND(total_km, 2) as total_km,
-- Score de performance (0-100)
CASE
WHEN (gross_margin - fixed_monthly_cost) > fixed_monthly_cost * 0.5 THEN 100
WHEN (gross_margin - fixed_monthly_cost) > fixed_monthly_cost * 0.3 THEN 80
WHEN (gross_margin - fixed_monthly_cost) > fixed_monthly_cost * 0.1 THEN 60
WHEN (gross_margin - fixed_monthly_cost) > 0 THEN 40
ELSE 20
END as performance_score
FROM performance_calc
ORDER BY month DESC, net_margin DESC;
-- Índices para queries eficientes
CREATE INDEX idx_vehicle_financial_month ON siga.v_vehicle_financial_performance (month DESC);
CREATE INDEX idx_vehicle_financial_vehicle ON siga.v_vehicle_financial_performance (vehicle_id);
CREATE OR REPLACE VIEW siga.v_prediction_accuracy AS
WITH prediction_results AS (
SELECT
d.id as decision_id,
d.created_at as prediction_time,
d.decision_type,
d.zone,
d.expected_value as predicted_value,
dr.actual_value,
dr.variance,
dr.variance_percentage,
-- Clasificación de accuracy
CASE
WHEN ABS(dr.variance_percentage) <= 10 THEN 'EXCELLENT'
WHEN ABS(dr.variance_percentage) <= 20 THEN 'GOOD'
WHEN ABS(dr.variance_percentage) <= 30 THEN 'ACCEPTABLE'
ELSE 'POOR'
END as accuracy_level,
-- Tiempo entre predicción y resultado
EXTRACT(EPOCH FROM (dr.completed_at - d.created_at)) / 3600 as prediction_horizon_hours
FROM siga.decisions d
JOIN siga.decision_results dr ON d.id = dr.decision_id
WHERE d.created_at > NOW() - INTERVAL '30 days'
AND dr.actual_value IS NOT NULL
),
accuracy_by_type AS (
SELECT
decision_type,
COUNT(*) as total_predictions,
-- Distribución de accuracy
COUNT(*) FILTER (WHERE accuracy_level = 'EXCELLENT') as excellent_count,
COUNT(*) FILTER (WHERE accuracy_level = 'GOOD') as good_count,
COUNT(*) FILTER (WHERE accuracy_level = 'ACCEPTABLE') as acceptable_count,
COUNT(*) FILTER (WHERE accuracy_level = 'POOR') as poor_count,
-- Métricas de error
AVG(ABS(variance_percentage)) as mean_absolute_percentage_error,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ABS(variance_percentage)) as median_error,
STDDEV(variance_percentage) as error_std_dev,
-- Por horizonte temporal
AVG(ABS(variance_percentage)) FILTER (WHERE prediction_horizon_hours <= 24) as mape_24h,
AVG(ABS(variance_percentage)) FILTER (WHERE prediction_horizon_hours > 24 AND prediction_horizon_hours <= 48) as mape_48h,
AVG(ABS(variance_percentage)) FILTER (WHERE prediction_horizon_hours > 48) as mape_72h_plus
FROM prediction_results
GROUP BY decision_type
)
SELECT
decision_type,
total_predictions,
-- Accuracy score (0-100)
ROUND(
(excellent_count * 100.0 + good_count * 80.0 + acceptable_count * 50.0 + poor_count * 20.0)
/ NULLIF(total_predictions, 0),
2
) as accuracy_score,
-- Distribución porcentual
ROUND(excellent_count::NUMERIC / total_predictions * 100, 2) as excellent_percentage,
ROUND(good_count::NUMERIC / total_predictions * 100, 2) as good_percentage,
ROUND(acceptable_count::NUMERIC / total_predictions * 100, 2) as acceptable_percentage,
ROUND(poor_count::NUMERIC / total_predictions * 100, 2) as poor_percentage,
-- Métricas de error
ROUND(mean_absolute_percentage_error, 2) as mape,
ROUND(median_error, 2) as median_error,
ROUND(error_std_dev, 2) as error_volatility,
-- Degradación por horizonte
ROUND(mape_24h, 2) as error_24h,
ROUND(mape_48h, 2) as error_48h,
ROUND(mape_72h_plus, 2) as error_72h_plus,
-- Tendencia (mejorando o empeorando)
CASE
WHEN (SELECT AVG(ABS(variance_percentage))
FROM prediction_results pr2
WHERE pr2.decision_type = accuracy_by_type.decision_type
AND pr2.prediction_time > NOW() - INTERVAL '7 days')
<
(SELECT AVG(ABS(variance_percentage))
FROM prediction_results pr3
WHERE pr3.decision_type = accuracy_by_type.decision_type
AND pr3.prediction_time BETWEEN NOW() - INTERVAL '30 days' AND NOW() - INTERVAL '7 days')
THEN 'IMPROVING'
ELSE 'DEGRADING'
END as trend
FROM accuracy_by_type
ORDER BY accuracy_score DESC;
CREATE MATERIALIZED VIEW siga.v_position_values_realtime AS
WITH zone_demand AS (
-- Demanda actual y predicha por zona
SELECT
z.code as zone_code,
z.name as zone_name,
-- Cargas disponibles ahora
COUNT(DISTINCT o.id) FILTER (
WHERE o.origin_zone = z.code
AND o.status = 'PENDING'
AND lower(o.pickup_window) <= NOW() + INTERVAL '4 hours'
) as current_loads,
-- Valor de cargas disponibles
COALESCE(SUM(o.agreed_revenue) FILTER (
WHERE o.origin_zone = z.code
AND o.status = 'PENDING'
AND lower(o.pickup_window) <= NOW() + INTERVAL '4 hours'
), 0) as current_value,
-- Predicción próximas 24h
COALESCE(
(SELECT SUM(dp.total_daily_revenue)
FROM siga.demand_patterns dp
WHERE dp.zone = z.code
AND dp.day_of_week = EXTRACT(DOW FROM NOW() + INTERVAL '1 day')
AND dp.confidence_level > 0.7
ORDER BY dp.pattern_date DESC
LIMIT 1
), 0
) as predicted_24h_value,
-- Vehículos en zona
COUNT(DISTINCT v.id) FILTER (WHERE v.current_zone = z.code AND v.status = 'AVAILABLE') as available_vehicles,
COUNT(DISTINCT v.id) FILTER (WHERE v.current_zone = z.code) as total_vehicles
FROM siga.zones z
LEFT JOIN siga.orders o ON o.origin_zone = z.code
LEFT JOIN siga.vehicles v ON v.current_zone = z.code
GROUP BY z.code, z.name
),
zone_connections AS (
-- Valor de conexiones desde cada zona
SELECT
origin_zone,
AVG(pattern_data->>'avg_revenue_per_load')::NUMERIC as avg_outbound_revenue,
AVG(pattern_data->>'return_load_probability')::NUMERIC as avg_return_probability,
COUNT(DISTINCT destination_zone) as connected_zones
FROM siga.flow_patterns
WHERE is_active = true
AND confidence_score > 0.6
GROUP BY origin_zone
),
position_calculation AS (
SELECT
zd.*,
COALESCE(zc.avg_outbound_revenue, 0) as avg_outbound_revenue,
COALESCE(zc.avg_return_probability, 0) as avg_return_probability,
COALESCE(zc.connected_zones, 0) as connected_zones,
-- Cálculo del valor de posición
(
-- Valor inmediato
CASE
WHEN zd.available_vehicles > 0
THEN zd.current_value / zd.available_vehicles
ELSE zd.current_value
END * 0.4 +
-- Valor futuro
CASE
WHEN zd.total_vehicles > 0
THEN zd.predicted_24h_value / (zd.total_vehicles + 1)
ELSE zd.predicted_24h_value
END * 0.4 +
-- Valor de conectividad
COALESCE(zc.avg_outbound_revenue, 0) * COALESCE(zc.avg_return_probability, 0) * 0.2
) as position_value
FROM zone_demand zd
LEFT JOIN zone_connections zc ON zd.zone_code = zc.origin_zone
)
SELECT
NOW() as calculated_at,
zone_code,
zone_name,
-- Métricas actuales
current_loads,
ROUND(current_value, 2) as current_value,
available_vehicles,
total_vehicles,
-- Predicciones
ROUND(predicted_24h_value, 2) as predicted_24h_value,
-- Conectividad
connected_zones,
ROUND(avg_outbound_revenue, 2) as avg_outbound_revenue,
ROUND(avg_return_probability * 100, 2) as return_probability_pct,
-- Valor de posición calculado
ROUND(position_value, 2) as position_value,
-- Ranking
RANK() OVER (ORDER BY position_value DESC) as value_rank,
-- Categorización
CASE
WHEN position_value > 1000 THEN 'HOT_ZONE'
WHEN position_value > 500 THEN 'GOOD_ZONE'
WHEN position_value > 200 THEN 'NEUTRAL_ZONE'
ELSE 'COLD_ZONE'
END as zone_category,
-- Recomendación
CASE
WHEN total_vehicles > available_vehicles * 2 AND position_value < 300 THEN 'EVACUATE'
WHEN available_vehicles = 0 AND position_value > 800 THEN 'REPOSITION_HERE'
WHEN total_vehicles > 5 AND current_loads < 2 THEN 'REDISTRIBUTE'
ELSE 'MAINTAIN'
END as recommendation
FROM position_calculation
ORDER BY position_value DESC;
-- Índice para búsquedas rápidas
CREATE INDEX idx_position_values_zone ON siga.v_position_values_realtime (zone_code);
CREATE INDEX idx_position_values_category ON siga.v_position_values_realtime (zone_category);
-- Refresco cada 15 minutos
SELECT cron.schedule('refresh-position-values', '*/15 * * * *',
'REFRESH MATERIALIZED VIEW CONCURRENTLY siga.v_position_values_realtime');
CREATE OR REPLACE VIEW siga.v_fleet_balance_analysis AS
WITH zone_statistics AS (
-- Estadísticas por zona
SELECT
z.code as zone_code,
z.name as zone_name,
z.type as zone_type,
-- Vehículos actuales
COUNT(DISTINCT v.id) as current_vehicles,
-- Demanda promedio
COALESCE(
(SELECT AVG(total_daily_loads)
FROM siga.demand_patterns dp
WHERE dp.zone = z.code
AND dp.pattern_date > CURRENT_DATE - INTERVAL '30 days'
), 0
) as avg_daily_demand,
-- Capacidad teórica (vehículos * entregas promedio por día)
COUNT(DISTINCT v.id) * 3.5 as theoretical_capacity
FROM siga.zones z
LEFT JOIN siga.vehicles v ON v.current_zone = z.code AND v.active = true
GROUP BY z.code, z.name, z.type
),
flow_balance AS (
-- Balance de flujos entrada/salida
SELECT
zone_code,
-- Flujos de salida
COUNT(DISTINCT fp_out.destination_zone) as outbound_connections,
COALESCE(SUM((fp_out.pattern_data->>'avg_daily_loads')::NUMERIC), 0) as daily_outbound_loads,
-- Flujos de entrada
COUNT(DISTINCT fp_in.origin_zone) as inbound_connections,
COALESCE(SUM((fp_in.pattern_data->>'avg_daily_loads')::NUMERIC), 0) as daily_inbound_loads
FROM siga.zones z
LEFT JOIN siga.flow_patterns fp_out
ON fp_out.origin_zone = z.code
AND fp_out.is_active = true
LEFT JOIN siga.flow_patterns fp_in
ON fp_in.destination_zone = z.code
AND fp_in.is_active = true
GROUP BY z.code
),
balance_calculation AS (
SELECT
zs.*,
fb.outbound_connections,
fb.inbound_connections,
fb.daily_outbound_loads,
fb.daily_inbound_loads,
-- Métricas de balance
fb.daily_inbound_loads - fb.daily_outbound_loads as net_flow,
-- Ratio oferta/demanda
CASE
WHEN zs.avg_daily_demand > 0
THEN zs.current_vehicles / (zs.avg_daily_demand / 3.5)
ELSE NULL
END as supply_demand_ratio,
-- Score de desbalance (0-100, donde 100 es muy desbalanceado)
CASE
WHEN zs.avg_daily_demand = 0 AND zs.current_vehicles > 2 THEN 80
WHEN zs.avg_daily_demand > 0 AND zs.current_vehicles = 0 THEN 100
WHEN zs.avg_daily_demand > 0 THEN
LEAST(100, ABS(1 - (zs.current_vehicles / (zs.avg_daily_demand / 3.5))) * 50)
ELSE 0
END as imbalance_score
FROM zone_statistics zs
LEFT JOIN flow_balance fb ON zs.zone_code = fb.zone_code
)
SELECT
zone_code,
zone_name,
zone_type,
-- Estado actual
current_vehicles,
ROUND(avg_daily_demand, 2) as avg_daily_demand,
ROUND(theoretical_capacity, 2) as theoretical_capacity,
-- Flujos
outbound_connections,
inbound_connections,
ROUND(daily_outbound_loads, 2) as daily_outbound_loads,
ROUND(daily_inbound_loads, 2) as daily_inbound_loads,
ROUND(net_flow, 2) as net_flow,
-- Ratios y scores
ROUND(supply_demand_ratio, 2) as supply_demand_ratio,
ROUND(imbalance_score, 2) as imbalance_score,
-- Categorización
CASE
WHEN imbalance_score >= 70 THEN 'CRITICAL_IMBALANCE'
WHEN imbalance_score >= 50 THEN 'HIGH_IMBALANCE'
WHEN imbalance_score >= 30 THEN 'MODERATE_IMBALANCE'
ELSE 'BALANCED'
END as balance_status,
-- Recomendación de acción
CASE
WHEN current_vehicles > avg_daily_demand / 3.5 * 1.5 THEN
'REDUCE: ' || ROUND(current_vehicles - avg_daily_demand / 3.5) || ' vehicles'
WHEN current_vehicles < avg_daily_demand / 3.5 * 0.7 THEN
'INCREASE: ' || ROUND(avg_daily_demand / 3.5 - current_vehicles) || ' vehicles'
ELSE 'MAINTAIN'
END as action_recommendation
FROM balance_calculation
WHERE zone_type != 'RURAL' -- Excluir zonas rurales del análisis
ORDER BY imbalance_score DESC;
CREATE OR REPLACE VIEW siga.v_system_learning_metrics AS
WITH monthly_performance AS (
-- Performance mensual del sistema
SELECT
DATE_TRUNC('month', d.created_at) as month,
COUNT(DISTINCT d.id) as total_decisions,
COUNT(DISTINCT dr.id) as decisions_with_results,
-- Accuracy de predicciones
AVG(ABS(dr.variance_percentage)) as avg_prediction_error,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ABS(dr.variance_percentage)) as median_prediction_error,
-- Adopción de recomendaciones
COUNT(*) FILTER (WHERE d.decision_source = 'SUGGESTED') as suggested_decisions,
COUNT(*) FILTER (WHERE d.decision_source = 'AUTOMATIC') as automatic_decisions,
-- Resultados económicos
SUM(dr.actual_value) as total_actual_value,
SUM(d.expected_value) as total_expected_value,
-- Feedback
AVG(df.rating) as avg_user_rating,
COUNT(DISTINCT df.id) as feedback_count
FROM siga.decisions d
LEFT JOIN siga.decision_results dr ON d.id = dr.decision_id
LEFT JOIN siga.decision_feedback df ON d.id = df.decision_id
WHERE d.created_at > NOW() - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', d.created_at)
),
pattern_evolution AS (
-- Evolución de patrones descubiertos
SELECT
DATE_TRUNC('month', discovered_at) as month,
COUNT(*) as new_patterns_discovered,
AVG(confidence_score) as avg_pattern_confidence
FROM siga.flow_patterns
WHERE discovered_at > NOW() - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', discovered_at)
),
learning_metrics AS (
SELECT
mp.month,
mp.total_decisions,
mp.decisions_with_results,
-- Tasa de aprendizaje
ROUND(mp.decisions_with_results::NUMERIC / NULLIF(mp.total_decisions, 0) * 100, 2) as learning_rate,
-- Mejora en accuracy
ROUND(mp.avg_prediction_error, 2) as avg_error_pct,
ROUND(mp.median_prediction_error, 2) as median_error_pct,
LAG(mp.avg_prediction_error) OVER (ORDER BY mp.month) as prev_month_error,
-- Adopción del sistema
ROUND((mp.suggested_decisions + mp.automatic_decisions)::NUMERIC / NULLIF(mp.total_decisions, 0) * 100, 2) as system_adoption_rate,
ROUND(mp.automatic_decisions::NUMERIC / NULLIF(mp.total_decisions, 0) * 100, 2) as automation_rate,
-- Performance económica
ROUND((mp.total_actual_value - mp.total_expected_value) / NULLIF(mp.total_expected_value, 0) * 100, 2) as value_variance_pct,
-- Satisfacción
ROUND(mp.avg_user_rating, 2) as avg_satisfaction,
mp.feedback_count,
-- Patrones
COALESCE(pe.new_patterns_discovered, 0) as new_patterns,
ROUND(COALESCE(pe.avg_pattern_confidence, 0), 2) as pattern_confidence
FROM monthly_performance mp
LEFT JOIN pattern_evolution pe ON mp.month = pe.month
)
SELECT
month,
total_decisions,
learning_rate,
-- Tendencia de accuracy
avg_error_pct,
median_error_pct,
CASE
WHEN prev_month_error IS NULL THEN NULL
WHEN avg_error_pct < prev_month_error THEN 'IMPROVING'
WHEN avg_error_pct = prev_month_error THEN 'STABLE'
ELSE 'DEGRADING'
END as accuracy_trend,
ROUND(COALESCE(prev_month_error - avg_error_pct, 0), 2) as error_improvement,
-- Métricas de adopción
system_adoption_rate,
automation_rate,
-- Performance
value_variance_pct,
avg_satisfaction,
feedback_count,
-- Descubrimiento de conocimiento
new_patterns,
pattern_confidence,
-- Score de madurez del sistema (0-100)
ROUND(
LEAST(100,
(100 - avg_error_pct) * 0.3 + -- Accuracy (30%)
system_adoption_rate * 0.25 + -- Adoption (25%)
automation_rate * 0.15 + -- Automation (15%)
COALESCE(avg_satisfaction * 20, 60) * 0.2 + -- Satisfaction (20%)
LEAST(new_patterns * 5, 50) * 0.1 -- Discovery (10%)
), 2
) as system_maturity_score
FROM learning_metrics
ORDER BY month DESC;
CREATE MATERIALIZED VIEW siga.v_executive_dashboard AS
WITH current_metrics AS (
SELECT
-- Operacionales
(SELECT utilization_percentage FROM siga.v_fleet_utilization_realtime LIMIT 1) as fleet_utilization,
(SELECT empty_km_percentage FROM siga.v_empty_kilometers_analysis WHERE day = CURRENT_DATE LIMIT 1) as empty_km_today,
(SELECT otd_percentage FROM siga.v_delivery_performance WHERE delivery_date = CURRENT_DATE LIMIT 1) as otd_today,
-- Financieros
(SELECT SUM(total_revenue) FROM siga.v_delivery_performance WHERE delivery_date >= DATE_TRUNC('month', CURRENT_DATE)) as mtd_revenue,
(SELECT AVG(margin_percentage) FROM siga.v_route_profitability WHERE trip_count >= 5) as avg_route_margin,
-- Predictivos
(SELECT AVG(accuracy_score) FROM siga.v_prediction_accuracy) as avg_prediction_accuracy,
(SELECT COUNT(*) FROM siga.v_position_values_realtime WHERE zone_category = 'HOT_ZONE') as hot_zones_count,
-- Estratégicos
(SELECT AVG(imbalance_score) FROM siga.v_fleet_balance_analysis) as avg_fleet_imbalance,
(SELECT system_maturity_score FROM siga.v_system_learning_metrics ORDER BY month DESC LIMIT 1) as system_maturity
),
comparisons AS (
SELECT
-- Comparación con mes anterior
(SELECT utilization_percentage FROM siga.v_fleet_utilization_realtime WHERE calculated_at < DATE_TRUNC('month', CURRENT_DATE) ORDER BY calculated_at DESC LIMIT 1) as prev_month_utilization,
(SELECT AVG(empty_km_percentage) FROM siga.v_empty_kilometers_analysis WHERE day >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') AND day < DATE_TRUNC('month', CURRENT_DATE)) as prev_month_empty_km,
-- Targets
75.0 as target_utilization,
20.0 as target_empty_km,
95.0 as target_otd,
85.0 as target_accuracy
)
SELECT
NOW() as dashboard_updated_at,
-- KPIs actuales con comparaciones
JSON_BUILD_OBJECT(
'fleet_utilization', JSON_BUILD_OBJECT(
'current', ROUND(cm.fleet_utilization, 2),
'target', c.target_utilization,
'vs_target', ROUND(cm.fleet_utilization - c.target_utilization, 2),
'vs_prev_month', ROUND(cm.fleet_utilization - c.prev_month_utilization, 2),
'status', CASE
WHEN cm.fleet_utilization >= c.target_utilization THEN 'ON_TRACK'
WHEN cm.fleet_utilization >= c.target_utilization * 0.9 THEN 'WARNING'
ELSE 'CRITICAL'
END
),
'empty_kilometers', JSON_BUILD_OBJECT(
'current', ROUND(cm.empty_km_today, 2),
'target', c.target_empty_km,
'vs_target', ROUND(cm.empty_km_today - c.target_empty_km, 2),
'vs_prev_month', ROUND(cm.empty_km_today - c.prev_month_empty_km, 2),
'status', CASE
WHEN cm.empty_km_today <= c.target_empty_km THEN 'ON_TRACK'
WHEN cm.empty_km_today <= c.target_empty_km * 1.1 THEN 'WARNING'
ELSE 'CRITICAL'
END
),
'on_time_delivery', JSON_BUILD_OBJECT(
'current', ROUND(cm.otd_today, 2),
'target', c.target_otd,
'vs_target', ROUND(cm.otd_today - c.target_otd, 2)
),
'revenue_mtd', JSON_BUILD_OBJECT(
'amount', ROUND(cm.mtd_revenue, 2),
'avg_margin', ROUND(cm.avg_route_margin, 2)
),
'system_intelligence', JSON_BUILD_OBJECT(
'prediction_accuracy', ROUND(cm.avg_prediction_accuracy, 2),
'hot_zones', cm.hot_zones_count,
'fleet_balance', ROUND(100 - cm.avg_fleet_imbalance, 2),
'maturity_score', ROUND(cm.system_maturity, 2)
)
) as kpi_summary,
-- Alertas activas
(
SELECT JSON_AGG(alerts)
FROM (
SELECT
'HIGH_EMPTY_KM' as alert_type,
'Empty kilometers above target' as message,
'CRITICAL' as severity
WHERE cm.empty_km_today > c.target_empty_km * 1.2
UNION ALL
SELECT
'LOW_UTILIZATION' as alert_type,
'Fleet utilization below target' as message,
'WARNING' as severity
WHERE cm.fleet_utilization < c.target_utilization * 0.9
UNION ALL
SELECT
'FLEET_IMBALANCE' as alert_type,
'Significant fleet imbalance detected' as message,
'WARNING' as severity
WHERE cm.avg_fleet_imbalance > 50
) alerts
) as active_alerts
FROM current_metrics cm, comparisons c;
-- Refresco cada hora
SELECT cron.schedule('refresh-executive-dashboard', '0 * * * *',
'REFRESH MATERIALIZED VIEW CONCURRENTLY siga.v_executive_dashboard');
CREATE OR REPLACE FUNCTION siga.calculate_custom_kpi(
p_kpi_name VARCHAR,
p_date_from DATE DEFAULT CURRENT_DATE - INTERVAL '30 days',
p_date_to DATE DEFAULT CURRENT_DATE,
p_zone VARCHAR DEFAULT NULL,
p_vehicle_id INTEGER DEFAULT NULL
) RETURNS TABLE (
metric_name VARCHAR,
metric_value NUMERIC,
metric_unit VARCHAR,
calculation_details JSONB
) AS $$
BEGIN
CASE p_kpi_name
WHEN 'cost_per_km' THEN
RETURN QUERY
SELECT
'Cost per Kilometer'::VARCHAR,
ROUND(SUM(actual_cost) / NULLIF(SUM(
ST_Distance(origin_point::geometry, destination_point::geometry) / 1000
), 0), 2),
'€/km'::VARCHAR,
JSON_BUILD_OBJECT(
'total_cost', SUM(actual_cost),
'total_km', SUM(ST_Distance(origin_point::geometry, destination_point::geometry) / 1000),
'orders_analyzed', COUNT(*)
)::JSONB
FROM siga.orders
WHERE status = 'DELIVERED'
AND DATE(created_at) BETWEEN p_date_from AND p_date_to
AND (p_zone IS NULL OR origin_zone = p_zone OR destination_zone = p_zone)
AND (p_vehicle_id IS NULL OR assigned_vehicle_id = p_vehicle_id);
WHEN 'decision_roi' THEN
RETURN QUERY
SELECT
'Decision ROI'::VARCHAR,
ROUND(
(SUM(dr.actual_value) - SUM(d.expected_value)) /
NULLIF(ABS(SUM(d.expected_value)), 0) * 100,
2
),
'%'::VARCHAR,
JSON_BUILD_OBJECT(
'total_decisions', COUNT(*),
'total_expected', SUM(d.expected_value),
'total_actual', SUM(dr.actual_value),
'positive_variance_count', COUNT(*) FILTER (WHERE dr.variance > 0)
)::JSONB
FROM siga.decisions d
JOIN siga.decision_results dr ON d.id = dr.decision_id
WHERE d.created_at BETWEEN p_date_from AND p_date_to
AND (p_zone IS NULL OR d.zone = p_zone);
ELSE
RAISE EXCEPTION 'Unknown KPI: %', p_kpi_name;
END CASE;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION siga.export_kpi_report(
p_report_type VARCHAR DEFAULT 'MONTHLY',
p_format VARCHAR DEFAULT 'JSON'
) RETURNS TEXT AS $$
DECLARE
v_result TEXT;
BEGIN
IF p_format = 'JSON' THEN
SELECT JSON_BUILD_OBJECT(
'report_type', p_report_type,
'generated_at', NOW(),
'operational_kpis', (
SELECT JSON_BUILD_OBJECT(
'fleet_utilization', utilization_percentage,
'empty_km_ratio', (SELECT AVG(empty_km_percentage) FROM siga.v_empty_kilometers_analysis WHERE day >= DATE_TRUNC('month', CURRENT_DATE)),
'on_time_delivery', (SELECT AVG(otd_percentage) FROM siga.v_delivery_performance WHERE delivery_date >= DATE_TRUNC('month', CURRENT_DATE))
)
FROM siga.v_fleet_utilization_realtime
LIMIT 1
),
'financial_kpis', (
SELECT JSON_BUILD_OBJECT(
'total_revenue', SUM(total_revenue),
'total_margin', SUM(total_margin),
'avg_margin_pct', AVG(margin_percentage)
)
FROM siga.v_delivery_performance
WHERE delivery_date >= DATE_TRUNC('month', CURRENT_DATE)
),
'predictive_kpis', (
SELECT JSON_BUILD_OBJECT(
'avg_accuracy', AVG(accuracy_score),
'improving_models', COUNT(*) FILTER (WHERE trend = 'IMPROVING'),
'degrading_models', COUNT(*) FILTER (WHERE trend = 'DEGRADING')
)
FROM siga.v_prediction_accuracy
)
)::TEXT INTO v_result;
ELSIF p_format = 'CSV' THEN
-- Implementar exportación CSV
RAISE EXCEPTION 'CSV format not yet implemented';
END IF;
RETURN v_result;
END;
$$ LANGUAGE plpgsql;
-- Crear tabla de control de refrescos
CREATE TABLE IF NOT EXISTS siga.materialized_view_refresh_log (
id SERIAL PRIMARY KEY,
view_name VARCHAR(255) NOT NULL,
refresh_started_at TIMESTAMP NOT NULL DEFAULT NOW(),
refresh_completed_at TIMESTAMP,
refresh_duration INTERVAL GENERATED ALWAYS AS (refresh_completed_at - refresh_started_at) STORED,
status VARCHAR(50) DEFAULT 'IN_PROGRESS',
error_message TEXT,
rows_affected INTEGER
);
-- Función para refrescar todas las vistas materializadas
CREATE OR REPLACE FUNCTION siga.refresh_all_materialized_views()
RETURNS TABLE (
view_name VARCHAR,
refresh_status VARCHAR,
duration INTERVAL
) AS $$
DECLARE
v_view RECORD;
v_start_time TIMESTAMP;
v_log_id INTEGER;
BEGIN
FOR v_view IN
SELECT schemaname, matviewname
FROM pg_matviews
WHERE schemaname = 'siga'
ORDER BY matviewname
LOOP
v_start_time := clock_timestamp();
-- Log inicio
INSERT INTO siga.materialized_view_refresh_log (view_name)
VALUES (v_view.schemaname || '.' || v_view.matviewname)
RETURNING id INTO v_log_id;
BEGIN
-- Ejecutar refresco
EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY %I.%I',
v_view.schemaname, v_view.matviewname);
-- Log éxito
UPDATE siga.materialized_view_refresh_log
SET refresh_completed_at = clock_timestamp(),
status = 'SUCCESS'
WHERE id = v_log_id;
RETURN QUERY
SELECT
v_view.schemaname || '.' || v_view.matviewname,
'SUCCESS'::VARCHAR,
clock_timestamp() - v_start_time;
EXCEPTION WHEN OTHERS THEN
-- Log error
UPDATE siga.materialized_view_refresh_log
SET refresh_completed_at = clock_timestamp(),
status = 'ERROR',
error_message = SQLERRM
WHERE id = v_log_id;
RETURN QUERY
SELECT
v_view.schemaname || '.' || v_view.matviewname,
'ERROR'::VARCHAR,
clock_timestamp() - v_start_time;
END;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- Programar refrescos automáticos
SELECT cron.schedule('refresh-all-views-daily', '0 2 * * *',
'SELECT * FROM siga.refresh_all_materialized_views()');
⬅️ Volver a Tablas de Patrones | ➡️ Volver a Modelo de Datos