Las tablas de patrones almacenan el conocimiento extraído del análisis histórico de operaciones. Estos patrones alimentan las predicciones y recomendaciones del sistema, permitiendo anticipar demanda, identificar flujos recurrentes y optimizar el posicionamiento de la flota.
Identifica y almacena los flujos recurrentes entre zonas.
CREATE TABLE siga.flow_patterns (
id SERIAL PRIMARY KEY,
-- Zonas del flujo
origin_zone VARCHAR(50) NOT NULL REFERENCES siga.zones(code),
destination_zone VARCHAR(50) NOT NULL REFERENCES siga.zones(code),
-- Clasificación del patrón
pattern_type VARCHAR(50) NOT NULL,
CHECK (pattern_type IN (
'REGULAR_FLOW', -- Flujo constante y predecible
'WEEKLY_SPIKE', -- Picos semanales
'SEASONAL', -- Estacional
'EVENT_DRIVEN', -- Relacionado con eventos
'IMBALANCE_CREATOR' -- Crea desequilibrio de flota
)),
-- Datos del patrón
pattern_data JSONB NOT NULL,
/* Ejemplo para REGULAR_FLOW:
{
"avg_daily_loads": 8.5,
"peak_hours": [10, 11, 15, 16],
"avg_revenue_per_load": 750,
"typical_cargo_types": ["general", "palletized"],
"return_load_probability": 0.65,
"dominant_customers": ["CUST-123", "CUST-456"]
}
Ejemplo para WEEKLY_SPIKE:
{
"spike_days": [1, 5], // Lunes y Viernes
"spike_multiplier": 2.5,
"normal_daily_loads": 4,
"spike_daily_loads": 10,
"preparation_needed": true,
"pre_positioning_value": 1200
}
Ejemplo para IMBALANCE_CREATOR:
{
"imbalance_ratio": 0.7, // 70% no tienen retorno
"vehicles_trapped_weekly": 15,
"avg_empty_return_km": 380,
"mitigation_strategies": [
"pre_book_return_loads",
"incentivize_alternative_destinations"
]
}
*/
-- Temporalidad
temporal_pattern JSONB DEFAULT '{}',
/* Ejemplo:
{
"months_active": [1,2,3,4,5,6,7,8,9,10,11,12],
"days_of_week": [1,2,3,4,5],
"hours_of_day": {
"high": [8,9,10,14,15,16],
"medium": [11,12,13,17,18],
"low": [6,7,19,20,21]
}
}
*/
-- Métricas de confianza
confidence_score DECIMAL(3,2) NOT NULL DEFAULT 0.50,
sample_size INTEGER NOT NULL DEFAULT 0,
last_occurrence DATE,
-- Validez
valid_from DATE NOT NULL DEFAULT CURRENT_DATE,
valid_until DATE,
is_active BOOLEAN DEFAULT true,
-- Impacto económico
economic_impact JSONB DEFAULT '{}',
/* Ejemplo:
{
"monthly_revenue": 45000,
"monthly_empty_km": 12000,
"opportunity_value": 8000,
"criticality": "high"
}
*/
-- Metadata
discovered_at TIMESTAMP DEFAULT NOW(),
last_updated TIMESTAMP DEFAULT NOW(),
updated_by VARCHAR(100),
-- Constraints
CONSTRAINT unique_flow_pattern UNIQUE (origin_zone, destination_zone, pattern_type),
CHECK (origin_zone != destination_zone),
-- Índices
INDEX idx_flow_patterns_zones (origin_zone, destination_zone),
INDEX idx_flow_patterns_type (pattern_type),
INDEX idx_flow_patterns_confidence (confidence_score DESC),
INDEX idx_flow_patterns_active (is_active, valid_from, valid_until)
);
Información granular sobre comportamiento de flujos.
CREATE TABLE siga.flow_pattern_details (
id SERIAL PRIMARY KEY,
flow_pattern_id INTEGER NOT NULL REFERENCES siga.flow_patterns(id) ON DELETE CASCADE,
-- Segmentación temporal
time_segment VARCHAR(50) NOT NULL,
CHECK (time_segment IN (
'MORNING_PEAK', -- 06:00-10:00
'MIDDAY', -- 10:00-14:00
'AFTERNOON_PEAK', -- 14:00-18:00
'EVENING', -- 18:00-22:00
'NIGHT', -- 22:00-06:00
'WEEKEND', -- Sábado-Domingo
'HOLIDAY' -- Festivos
)),
-- Métricas del segmento
segment_metrics JSONB NOT NULL,
/* Ejemplo:
{
"avg_loads": 3.2,
"load_distribution": {
"06:00": 0.5,
"07:00": 1.2,
"08:00": 1.5
},
"avg_wait_time_origin": 2.5,
"avg_transit_time": 4.2,
"typical_vehicle_types": ["TRAILER", "RIGID"],
"price_sensitivity": "low"
}
*/
-- Factores influyentes
influencing_factors JSONB DEFAULT '{}',
/* Ejemplo:
{
"weather_impact": {
"rain": -0.2,
"snow": -0.5,
"clear": 0
},
"traffic_impact": {
"peak_hours": -0.3,
"normal": 0
},
"seasonal_impact": {
"summer": 0.2,
"christmas": -0.4
}
}
*/
-- Estadísticas
observation_count INTEGER DEFAULT 0,
last_observed DATE,
reliability_score DECIMAL(3,2) DEFAULT 0.50,
-- Metadata
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
-- Índices
INDEX idx_flow_pattern_details_pattern (flow_pattern_id),
INDEX idx_flow_pattern_details_segment (time_segment)
);
Patrones de demanda predictiva por zona geográfica.
CREATE TABLE siga.demand_patterns (
id SERIAL PRIMARY KEY,
-- Identificación
zone VARCHAR(50) NOT NULL REFERENCES siga.zones(code),
pattern_date DATE NOT NULL,
day_of_week INTEGER NOT NULL CHECK (day_of_week BETWEEN 0 AND 6),
week_of_month INTEGER NOT NULL CHECK (week_of_month BETWEEN 1 AND 5),
-- Patrón horario de demanda
hourly_demand JSONB NOT NULL,
/* Ejemplo:
{
"00": {"loads": 0.1, "revenue": 100},
"01": {"loads": 0.0, "revenue": 0},
...
"08": {"loads": 4.5, "revenue": 3375},
"09": {"loads": 5.2, "revenue": 3900},
...
"23": {"loads": 0.3, "revenue": 225}
}
*/
-- Agregados diarios
total_daily_loads DECIMAL(6,2) GENERATED ALWAYS AS (
(SELECT SUM((value->>'loads')::DECIMAL)
FROM jsonb_each(hourly_demand))
) STORED,
total_daily_revenue DECIMAL(10,2) GENERATED ALWAYS AS (
(SELECT SUM((value->>'revenue')::DECIMAL)
FROM jsonb_each(hourly_demand))
) STORED,
-- Factores contextuales
contextual_factors JSONB DEFAULT '{}',
/* Ejemplo:
{
"is_holiday": false,
"special_events": [],
"weather_conditions": "normal",
"economic_indicators": {
"fuel_price_index": 102.5,
"demand_index": 98.0
}
}
*/
-- Destinos típicos desde esta zona
typical_destinations JSONB DEFAULT '{}',
/* Ejemplo:
{
"MAD": {"probability": 0.35, "avg_revenue": 850},
"BCN": {"probability": 0.25, "avg_revenue": 750},
"VAL": {"probability": 0.20, "avg_revenue": 450},
"otros": {"probability": 0.20, "avg_revenue": 600}
}
*/
-- Métricas de calidad
prediction_accuracy DECIMAL(3,2),
confidence_level DECIMAL(3,2) DEFAULT 0.50,
sample_size INTEGER DEFAULT 0,
-- Metadata
created_at TIMESTAMP DEFAULT NOW(),
last_updated TIMESTAMP DEFAULT NOW(),
-- Constraints
CONSTRAINT unique_demand_pattern UNIQUE (zone, pattern_date),
-- Índices
INDEX idx_demand_patterns_zone_date (zone, pattern_date DESC),
INDEX idx_demand_patterns_dow (day_of_week),
INDEX idx_demand_patterns_accuracy (prediction_accuracy DESC)
);
-- Índice para búsquedas de patrones similares
CREATE INDEX idx_demand_patterns_similarity
ON siga.demand_patterns (zone, day_of_week, week_of_month);
Captura variaciones estacionales en flujos y demanda.
CREATE TABLE siga.seasonal_patterns (
id SERIAL PRIMARY KEY,
-- Tipo y alcance del patrón
pattern_scope VARCHAR(50) NOT NULL,
CHECK (pattern_scope IN ('GLOBAL', 'ZONE', 'ROUTE', 'CUSTOMER')),
scope_identifier VARCHAR(100), -- zone_code, route_id, o customer_id
-- Temporada
season_name VARCHAR(50) NOT NULL,
season_start_month INTEGER NOT NULL CHECK (season_start_month BETWEEN 1 AND 12),
season_end_month INTEGER NOT NULL CHECK (season_end_month BETWEEN 1 AND 12),
-- Impacto del patrón
impact_metrics JSONB NOT NULL,
/* Ejemplo:
{
"demand_multiplier": 1.35,
"price_elasticity": -0.2,
"capacity_constraints": true,
"typical_changes": {
"fruit_transport": "+80%",
"general_cargo": "-20%",
"refrigerated": "+150%"
},
"zone_impacts": {
"MURCIA": "+120%",
"VALENCIA": "+90%",
"BARCELONA": "+20%"
}
}
*/
-- Estrategias recomendadas
recommended_strategies JSONB DEFAULT '{}',
/* Ejemplo:
{
"pre_positioning": {
"zones": ["MURCIA", "VALENCIA"],
"vehicles_needed": 8,
"timing": "2 weeks before"
},
"partnerships": [
"increase_owner_operators",
"seasonal_contracts"
],
"pricing": {
"adjustment": "+15%",
"lock_in_contracts": true
}
}
*/
-- Histórico de precisión
historical_accuracy JSONB DEFAULT '[]',
/* Ejemplo:
[
{"year": 2023, "predicted": 135, "actual": 142, "accuracy": 0.95},
{"year": 2022, "predicted": 128, "actual": 130, "accuracy": 0.98}
]
*/
-- Estado
is_active BOOLEAN DEFAULT true,
confidence_score DECIMAL(3,2) DEFAULT 0.70,
-- Metadata
discovered_at TIMESTAMP DEFAULT NOW(),
last_validated TIMESTAMP DEFAULT NOW(),
validated_by VARCHAR(100),
-- Índices
INDEX idx_seasonal_patterns_scope (pattern_scope, scope_identifier),
INDEX idx_seasonal_patterns_season (season_start_month, season_end_month),
INDEX idx_seasonal_patterns_active (is_active)
);
Comportamientos recurrentes específicos de vehículos.
CREATE TABLE siga.vehicle_patterns (
id SERIAL PRIMARY KEY,
vehicle_id INTEGER NOT NULL REFERENCES siga.vehicles(id),
-- Identificación del patrón
pattern_name VARCHAR(100) NOT NULL,
pattern_category VARCHAR(50) NOT NULL,
CHECK (pattern_category IN (
'PERFORMANCE', -- Patrones de rendimiento
'MAINTENANCE', -- Necesidades de mantenimiento
'ROUTE_PREFERENCE', -- Preferencias de ruta
'EFFICIENCY', -- Eficiencia de combustible
'RELIABILITY' -- Confiabilidad
)),
-- Métricas del patrón
pattern_metrics JSONB NOT NULL,
/* Ejemplos por categoría:
PERFORMANCE:
{
"avg_daily_km": 385,
"avg_deliveries": 3.2,
"peak_performance_hours": [8, 9, 10],
"performance_decline_after_km": 400
}
MAINTENANCE:
{
"service_interval_km": 15000,
"avg_days_between_issues": 45,
"common_issues": ["tyre_wear", "brake_adjustment"],
"preventive_maintenance_roi": 3.5
}
EFFICIENCY:
{
"fuel_consumption_loaded": 32.5,
"fuel_consumption_empty": 28.0,
"optimal_speed_range": [80, 90],
"efficiency_by_route_type": {
"highway": 0.95,
"urban": 0.75,
"mixed": 0.85
}
}
*/
-- Frecuencia y confiabilidad
occurrence_count INTEGER DEFAULT 1,
last_occurrence DATE,
confidence_score DECIMAL(3,2) DEFAULT 0.50,
-- Impacto operacional
impact_score DECIMAL(3,2),
impact_description TEXT,
-- Recomendaciones
recommendations JSONB DEFAULT '{}',
/* Ejemplo:
{
"immediate": ["schedule_preventive_maintenance"],
"planning": ["prefer_highway_routes"],
"restrictions": ["avoid_urban_deliveries_peak_hours"]
}
*/
-- Estado
is_active BOOLEAN DEFAULT true,
requires_attention BOOLEAN DEFAULT false,
-- Metadata
discovered_at TIMESTAMP DEFAULT NOW(),
last_updated TIMESTAMP DEFAULT NOW(),
updated_by VARCHAR(100),
-- Constraints
CONSTRAINT unique_vehicle_pattern UNIQUE (vehicle_id, pattern_name),
-- Índices
INDEX idx_vehicle_patterns_vehicle (vehicle_id),
INDEX idx_vehicle_patterns_category (pattern_category),
INDEX idx_vehicle_patterns_impact (impact_score DESC),
INDEX idx_vehicle_patterns_attention (requires_attention)
);
Comportamientos y preferencias de conductores.
CREATE TABLE siga.driver_patterns (
id SERIAL PRIMARY KEY,
driver_id INTEGER NOT NULL REFERENCES siga.drivers(id),
-- Tipo de patrón
pattern_type VARCHAR(50) NOT NULL,
CHECK (pattern_type IN (
'SCHEDULE_PREFERENCE', -- Preferencias horarias
'ROUTE_PREFERENCE', -- Rutas preferidas
'PERFORMANCE_PROFILE', -- Perfil de rendimiento
'REST_PATTERN', -- Patrones de descanso
'EFFICIENCY_STYLE' -- Estilo de conducción
)),
-- Datos del patrón
pattern_data JSONB NOT NULL,
/* Ejemplos:
SCHEDULE_PREFERENCE:
{
"preferred_start_time": "06:00",
"avoid_nights": true,
"max_continuous_hours": 8,
"preferred_break_times": ["12:00", "18:00"],
"weekend_availability": 0.3
}
ROUTE_PREFERENCE:
{
"preferred_zones": ["BCN", "TAR", "GIR"],
"avoid_zones": ["downtown_MAD"],
"preferred_distance": "medium", // short, medium, long
"international_willing": false
}
PERFORMANCE_PROFILE:
{
"avg_speed_highway": 85,
"avg_speed_urban": 45,
"on_time_delivery": 0.96,
"incident_rate": 0.02,
"customer_rating": 4.7
}
*/
-- Compatibilidad con vehículos
vehicle_compatibility JSONB DEFAULT '{}',
/* Ejemplo:
{
"preferred_types": ["RIGID", "VAN"],
"certified_for": ["ADR", "refrigerated"],
"size_limit": "12m"
}
*/
-- Métricas de calidad
reliability_score DECIMAL(3,2) DEFAULT 0.80,
consistency_score DECIMAL(3,2) DEFAULT 0.75,
-- Impacto en operaciones
operational_impact JSONB DEFAULT '{}',
/* Ejemplo:
{
"productivity_index": 1.05,
"cost_efficiency": 0.98,
"customer_satisfaction": 1.10
}
*/
-- Estado
is_current BOOLEAN DEFAULT true,
last_validated DATE,
-- Metadata
identified_at TIMESTAMP DEFAULT NOW(),
last_updated TIMESTAMP DEFAULT NOW(),
-- Índices
INDEX idx_driver_patterns_driver (driver_id),
INDEX idx_driver_patterns_type (pattern_type),
INDEX idx_driver_patterns_current (is_current)
);
CREATE OR REPLACE VIEW siga.v_active_patterns_summary AS
WITH flow_summary AS (
SELECT
'FLOW' as pattern_type,
fp.origin_zone || ' → ' || fp.destination_zone as pattern_description,
fp.confidence_score,
fp.economic_impact->>'monthly_revenue' as economic_value,
fp.last_updated
FROM siga.flow_patterns fp
WHERE fp.is_active = true
AND (fp.valid_until IS NULL OR fp.valid_until > CURRENT_DATE)
),
demand_summary AS (
SELECT
'DEMAND' as pattern_type,
dp.zone || ' (' ||
CASE dp.day_of_week
WHEN 0 THEN 'SUN' WHEN 1 THEN 'MON' WHEN 2 THEN 'TUE'
WHEN 3 THEN 'WED' WHEN 4 THEN 'THU' WHEN 5 THEN 'FRI'
WHEN 6 THEN 'SAT'
END || ')' as pattern_description,
dp.confidence_level as confidence_score,
dp.total_daily_revenue::TEXT as economic_value,
dp.last_updated
FROM siga.demand_patterns dp
WHERE dp.pattern_date >= CURRENT_DATE - INTERVAL '7 days'
),
seasonal_summary AS (
SELECT
'SEASONAL' as pattern_type,
sp.season_name || ' - ' || sp.scope_identifier as pattern_description,
sp.confidence_score,
(sp.impact_metrics->>'demand_multiplier')::TEXT as economic_value,
sp.last_validated as last_updated
FROM siga.seasonal_patterns sp
WHERE sp.is_active = true
)
SELECT * FROM flow_summary
UNION ALL SELECT * FROM demand_summary
UNION ALL SELECT * FROM seasonal_summary
ORDER BY confidence_score DESC, pattern_type;
CREATE OR REPLACE VIEW siga.v_problematic_zones AS
SELECT
z.code as zone_code,
z.name as zone_name,
-- Métricas de desequilibrio
COALESCE(imb.imbalance_count, 0) as imbalance_patterns,
COALESCE(imb.avg_vehicles_trapped, 0) as avg_vehicles_trapped,
COALESCE(imb.monthly_cost, 0) as imbalance_cost,
-- Métricas de demanda
COALESCE(dem.avg_daily_loads, 0) as avg_daily_demand,
COALESCE(dem.demand_variability, 0) as demand_volatility,
-- Score problemático (0-100)
CASE
WHEN imb.imbalance_count > 3 THEN 50
WHEN imb.imbalance_count > 1 THEN 30
ELSE 0
END +
CASE
WHEN dem.demand_variability > 0.5 THEN 30
WHEN dem.demand_variability > 0.3 THEN 20
ELSE 0
END +
CASE
WHEN imb.avg_vehicles_trapped > 5 THEN 20
WHEN imb.avg_vehicles_trapped > 2 THEN 10
ELSE 0
END as problem_score
FROM siga.zones z
LEFT JOIN (
-- Patrones de desequilibrio por zona
SELECT
destination_zone,
COUNT(*) as imbalance_count,
AVG((pattern_data->>'vehicles_trapped_weekly')::INTEGER) as avg_vehicles_trapped,
SUM((economic_impact->>'monthly_empty_km')::INTEGER * 0.70) as monthly_cost
FROM siga.flow_patterns
WHERE pattern_type = 'IMBALANCE_CREATOR'
AND is_active = true
GROUP BY destination_zone
) imb ON z.code = imb.destination_zone
LEFT JOIN (
-- Variabilidad de demanda por zona
SELECT
zone,
AVG(total_daily_loads) as avg_daily_loads,
STDDEV(total_daily_loads) / NULLIF(AVG(total_daily_loads), 0) as demand_variability
FROM siga.demand_patterns
WHERE pattern_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY zone
) dem ON z.code = dem.zone
WHERE z.type != 'RURAL'
ORDER BY problem_score DESC;
CREATE OR REPLACE FUNCTION siga.update_flow_patterns()
RETURNS void AS $$
DECLARE
v_pattern RECORD;
BEGIN
-- Actualizar confianza basada en ocurrencias recientes
FOR v_pattern IN
SELECT fp.id, fp.origin_zone, fp.destination_zone,
COUNT(o.id) as recent_occurrences
FROM siga.flow_patterns fp
LEFT JOIN siga.orders o
ON o.origin_zone = fp.origin_zone
AND o.destination_zone = fp.destination_zone
AND o.created_at > CURRENT_DATE - INTERVAL '30 days'
WHERE fp.is_active = true
GROUP BY fp.id, fp.origin_zone, fp.destination_zone
LOOP
UPDATE siga.flow_patterns
SET confidence_score = LEAST(
0.95,
confidence_score + (v_pattern.recent_occurrences * 0.01)
),
sample_size = sample_size + v_pattern.recent_occurrences,
last_occurrence = CURRENT_DATE,
last_updated = NOW()
WHERE id = v_pattern.id;
END LOOP;
-- Degradar patrones sin ocurrencias recientes
UPDATE siga.flow_patterns
SET confidence_score = GREATEST(
0.30,
confidence_score - 0.05
)
WHERE is_active = true
AND last_occurrence < CURRENT_DATE - INTERVAL '60 days';
-- Desactivar patrones obsoletos
UPDATE siga.flow_patterns
SET is_active = false,
valid_until = CURRENT_DATE
WHERE confidence_score < 0.30;
END;
$$ LANGUAGE plpgsql;
-- Programar ejecución semanal
CREATE EXTENSION IF NOT EXISTS pg_cron;
SELECT cron.schedule('update-flow-patterns', '0 2 * * 0', 'SELECT siga.update_flow_patterns()');
CREATE OR REPLACE FUNCTION siga.detect_new_patterns()
RETURNS TABLE(
pattern_type VARCHAR,
origin VARCHAR,
destination VARCHAR,
significance DECIMAL
) AS $$
BEGIN
RETURN QUERY
WITH recent_flows AS (
-- Analizar últimos 90 días
SELECT
origin_zone,
destination_zone,
COUNT(*) as flow_count,
AVG(agreed_revenue) as avg_revenue,
STDDEV(EXTRACT(EPOCH FROM (delivery_window ->> pickup_window))) as time_variability
FROM siga.orders
WHERE created_at > CURRENT_DATE - INTERVAL '90 days'
AND status = 'DELIVERED'
GROUP BY origin_zone, destination_zone
HAVING COUNT(*) > 10
),
existing_patterns AS (
SELECT DISTINCT origin_zone, destination_zone
FROM siga.flow_patterns
WHERE is_active = true
)
-- Identificar nuevos patrones potenciales
SELECT
'NEW_FLOW' as pattern_type,
rf.origin_zone as origin,
rf.destination_zone as destination,
(rf.flow_count * rf.avg_revenue / 1000)::DECIMAL as significance
FROM recent_flows rf
LEFT JOIN existing_patterns ep
ON rf.origin_zone = ep.origin_zone
AND rf.destination_zone = ep.destination_zone
WHERE ep.origin_zone IS NULL
AND rf.flow_count > 20
ORDER BY significance DESC;
END;
$$ LANGUAGE plpgsql;
-- Identificar las rutas más rentables por día
SELECT
fp.origin_zone,
fp.destination_zone,
unnest(fp.temporal_pattern->'days_of_week')::INTEGER as day_of_week,
fp.pattern_data->>'avg_revenue_per_load' as avg_revenue,
fp.pattern_data->>'return_load_probability' as return_probability,
fp.confidence_score
FROM siga.flow_patterns fp
WHERE fp.pattern_type = 'REGULAR_FLOW'
AND fp.is_active = true
AND fp.confidence_score > 0.70
ORDER BY
day_of_week,
(fp.pattern_data->>'avg_revenue_per_load')::DECIMAL DESC;
-- Predecir demanda para mañana basada en patrones
WITH tomorrow_context AS (
SELECT
EXTRACT(DOW FROM CURRENT_DATE + INTERVAL '1 day')::INTEGER as dow,
CEIL(EXTRACT(DAY FROM CURRENT_DATE + INTERVAL '1 day') / 7.0)::INTEGER as wom
)
SELECT
dp.zone,
z.name as zone_name,
dp.total_daily_loads as expected_loads,
dp.total_daily_revenue as expected_revenue,
dp.confidence_level,
dp.typical_destinations
FROM siga.demand_patterns dp
JOIN siga.zones z ON dp.zone = z.code
JOIN tomorrow_context tc ON dp.day_of_week = tc.dow AND dp.week_of_month = tc.wom
WHERE dp.pattern_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY dp.total_daily_revenue DESC;
⬅️ Volver a Tablas de Decisiones | ➡️ Siguiente: Vistas y KPIs