Objectif global : Concevoir et implémenter une base de données SQLite3 pour suivre et…
- User
Objectif global : Concevoir et implémenter une base de données SQLite3 pour suivre et prédire les durées de dédouanement douanier de colis expédiés via des vols cargo réguliers. Les données seront collectées en temps réel via des requêtes API et injectées dans la base. L'objectif final est d'analyser et modéliser les facteurs influençant le délai entre :
Étape 220 : "Cargo flight departed" (départ du vol cargo du pays A). Étape 370 : "Customs Clearance Completed" (fin du dédouanement dans le pays B). Spécifications détaillées :
Structure de la base de données : Créer 7 tables principales avec des relations SQL cohérentes : parcels (colis) : Infos de base sur chaque colis (tracking number, pays de destination, etc.). customs_lots (lots douaniers) : Groupe de colis ayant pris le même vol cargo. tracking_steps (étapes de suivi) : Historique horodaté de chaque colis (codes étapes : 220, 370, etc.). customs_batches (nouvelle entité) : Sessions de dédouanement groupées (quand ≥20 colis marqués 370 en peu de temps). queue_status (file d'attente) : État de la file d'attente des lots à des moments précis. holidays (jours fériés) : Liste des jours non ouvrables dans le pays de destination. action_code_names : description textuelle des action code Mettre en place des clés étrangères pour lier les tables logiquement. Ajouter des index optimisés pour accélérer les requêtes fréquentes (ex: timestamp, action_code, lot_id, etc.). Notion de "Customs Batch" (Batch douanier) : Un batch est déclenché quand ≥20 colis reçoivent le code 370 ("Customs Clearance Completed") dans un intervalle serré (~quelques minutes). Caractéristiques à capturer : start_timestamp : Horodatage du 1er colis marqué 370 du groupe. end_timestamp : Horodatage du dernier colis marqué 370 du groupe. batch_size : Nombre total de colis dans ce batch (≥20). processing_duration : Durée en secondes entre start_timestamp et end_timestamp. avg_processing_speed : Vitesse moyenne (colis/heure) calculée automatiquement. Règle métier : Si l'écart entre deux marquages 370 dépasse 15 minutes, c'est un nouveau batch qui commence. Collecte des données via API : Les données arriveront par requêtes HTTP/JSON (probablement ~1 à 5 fois par heure). Chaque requête contiendra : Un ou plusieurs numéros de tracking avec leurs étapes horodatées (ex: {parcel_id, action_code, timestamp}). Éventuellement des métadonnées vol (départ, arrivée, code vol) si elles n'existent pas déjà. Chaque étape reçue doit : Être insérée dans tracking_steps. Déclencher la logique de détection de batch si c'est un code 370. Traitements automatisés : Détection des customs batches en temps réel (regrouper les 370 par paquets rapprochés). Mise à jour des customs_lots avec le batch_id correspondant. Calcul automatique des durées (processing_duration) et vitesses (avg_processing_speed). Analyses prévues : Statistiques journalières : Nombre de batches, colis traités, durée moyenne, etc. Évolution mensuelle de la performance douanière. Modélisation prédictive (Machine Learning) pour anticiper les durées de dédouanement futures en fonction : Jour de la semaine / heure de départ. Taille du lot douanier. File d'attente estimée. Présence de jours fériés proches.
**"Conçois une base de données SQLite3 optimisée pour suivre et analyser les durées de dédouanement douanier de colis. Les données arriveront en temps réel via API JSON.
Réalise les tâches suivantes :
Écris le code Python 3.10 complet pour : Initialiser la base SQLite (CREATE TABLE, INDEX, TRIGGER si besoin). Définir 7 tables : parcels, customs_lots, cargo_flights, tracking_steps, customs_batches, queue_status, holidays. Intégrer la logique de détection automatique des 'customs batches' (≥20 colis marqués 370 en <15 minutes). Mettre à jour les customs_lots avec le batch_id associé. Simule l'injection de données via un exemple d'API JSON (colis + étapes de tracking). Exemple d'entrée :
{ "tracking_number": "MA047420310R", "steps": [ {"action_code": "220", "timestamp": "2025-01-12 08:30:00"}, {"action_code": "230", "timestamp": "2025-01-13 10:15:00"}, {"action_code": "370", "timestamp": "2025-01-14 09:02:00"} ], "flight_info": {"departure_date": "2025-01-12 08:30:00", "flight_code": "AF1234"} } Fournis des exemples de requêtes SQL pour : Extraire les KPIs journaliers (nb batches, durée moyenne, etc.). Analyser l'impact du jour de la semaine sur la vitesse de dédouanement. Lister les lots encore en attente (pas de 370 reçu depuis >3 jours). Ébauche un modèle prédictif simplifié (Python sklearn) pour estimer la durée 220 → 370 en fonction des features (jour, taille lot, file d'attente, etc.). Contraintes techniques :
Utilise SQLite3 (pas de PostgreSQL/MySQL). Code Python doit être PEP8 (lisible, commenté, black formaté). Évite les SELECT * dans les requêtes finales (nomme explicitement les colonnes). Documente chaque table + chaque fonction Python (docstrings). Livrables attendus :
Un script init_db.py pour créer les tables/index. Un script insert_data.py simulant l'API + la détection de batches. Un notebook Jupyter (analysis.ipynb) avec les requêtes SQL + modèle ML de base. Bonus si :
Tu intègres une vue matérialisée pour les stats journalières. Tu proposes une alerte automatique (ex: par mail) si un batch dépasse 24h. Prêt ? Vas-y !"**
- Assistant
Voici un exemple de code Python pour initialiser une base de données SQLite3, insérer des données, et effectuer des analyses. Le code est divisé en plusieurs parties pour répondre à vos spécifications.
1. Script
init_db.pypour créer les tables et indeximport sqlite3 def init_db(): """Initialise la base de données SQLite pour le suivi des colis.""" conn = sqlite3.connect('customs_tracking.db') cursor = conn.cursor() # Création des tables cursor.execute(''' CREATE TABLE parcels ( id INTEGER PRIMARY KEY AUTOINCREMENT, tracking_number TEXT UNIQUE NOT NULL, destination_country TEXT NOT NULL ) ''') cursor.execute(''' CREATE TABLE customs_lots ( id INTEGER PRIMARY KEY AUTOINCREMENT, flight_code TEXT NOT NULL, departure_date TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ''') cursor.execute(''' CREATE TABLE cargo_flights ( id INTEGER PRIMARY KEY AUTOINCREMENT, flight_code TEXT UNIQUE NOT NULL, departure_date TEXT NOT NULL, arrival_date TEXT NOT NULL ) ''') cursor.execute(''' CREATE TABLE tracking_steps ( id INTEGER PRIMARY KEY AUTOINCREMENT, parcel_id INTEGER NOT NULL, action_code TEXT NOT NULL, timestamp TEXT NOT NULL, FOREIGN KEY (parcel_id) REFERENCES parcels (id) ) ''') cursor.execute(''' CREATE TABLE customs_batches ( id INTEGER PRIMARY KEY AUTOINCREMENT, customs_lot_id INTEGER NOT NULL, start_timestamp TEXT NOT NULL, end_timestamp TEXT NOT NULL, batch_size INTEGER NOT NULL, processing_duration INTEGER NOT NULL, avg_processing_speed REAL NOT NULL, FOREIGN KEY (customs_lot_id) REFERENCES customs_lots (id) ) ''') cursor.execute(''' CREATE TABLE queue_status ( id INTEGER PRIMARY KEY AUTOINCREMENT, customs_lot_id INTEGER NOT NULL, status TEXT NOT NULL, timestamp TEXT NOT NULL, FOREIGN KEY (customs_lot_id) REFERENCES customs_lots (id) ) ''') cursor.execute(''' CREATE TABLE holidays ( id INTEGER PRIMARY KEY AUTOINCREMENT, holiday_date TEXT NOT NULL, description TEXT ) ''') # Création des index cursor.execute('CREATE INDEX idx_tracking_steps_timestamp ON tracking_steps (timestamp)') cursor.execute('CREATE INDEX idx_customs_batches_start_timestamp ON customs_batches (start_timestamp)') conn.commit() conn.close() if __name__ == "__main__": init_db()2. Script
insert_data.pypour simuler l'injection de donnéesimport sqlite3 import json from datetime import datetime, timedelta def insert_data(data): """Insère les données de suivi dans la base de données et détecte les customs batches.""" conn = sqlite3.connect('customs_tracking.db') cursor = conn.cursor() # Insérer le colis cursor.execute(''' INSERT OR IGNORE INTO parcels (tracking_number, destination_country) VALUES (?, ?) ''', (data['tracking_number'], 'CountryB')) # Récupérer l'ID du colis cursor.execute('SELECT id FROM parcels WHERE tracking_number = ?', (data['tracking_number'],)) parcel_id = cursor.fetchone()[0] # Insérer les étapes de suivi for step in data['steps']: cursor.execute(''' INSERT INTO tracking_steps (parcel_id, action_code, timestamp) VALUES (?, ?, ?) ''', (parcel_id, step['action_code'], step['timestamp'])) # Détection des customs batches process_batches(cursor, parcel_id) conn.commit() conn.close() def process_batches(cursor, parcel_id): """Détecte et enregistre les customs batches.""" cursor.execute(''' SELECT action_code, timestamp FROM tracking_steps WHERE parcel_id = ? AND action_code = '370' ORDER BY timestamp ''', (parcel_id,)) steps = cursor.fetchall() if not steps: return batches = [] current_batch = [] last_timestamp = None for action_code, timestamp in steps: timestamp_dt = datetime.strptime(timestamp, '%Y-%m-%d %H:%M:%S') if last_timestamp and (timestamp_dt - last_timestamp > timedelta(minutes=15)): if len(current_batch) >= 20: batches.append(current_batch) current_batch = [] current_batch.append(timestamp_dt) last_timestamp = timestamp_dt if len(current_batch) >= 20: batches.append(current_batch) for batch in batches: start_timestamp = batch[0] end_timestamp = batch[-1] batch_size = len(batch) processing_duration = (end_timestamp - start_timestamp).total_seconds() avg_processing_speed = batch_size / (processing_duration / 3600) # colis/heure cursor.execute(''' INSERT INTO customs_batches (customs_lot_id, start_timestamp, end_timestamp, batch_size, processing_duration, avg_processing_speed) VALUES (?, ?, ?, ?, ?, ?) ''', (parcel_id, start_timestamp, end_timestamp, batch_size, processing_duration, avg_processing_speed)) if __name__ == "__main__": # Exemple de données d'entrée example_data = { "tracking_number": "MA047420310R", "steps": [ {"action_code": "220", "timestamp": "2025-01-12 08:30:00"}, {"action_code": "230", "timestamp": "2025-01-13 10:15:00"}, {"action_code": "370", "timestamp": "2025-01-14 09:02:00"} ], "flight_info": {"departure_date": "2025-01-12 08:30:00", "flight_code": "AF1234"} } insert_data(example_data)3. Notebook Jupyter
analysis.ipynbDans ce notebook, vous pouvez inclure des requêtes SQL pour extraire des KPIs et effectuer des analyses. Voici quelques exemples de requêtes SQL que vous pouvez exécuter dans le notebook :
-- Extraire les KPIs journaliers SELECT DATE(created_at) AS date, COUNT(*) AS number_of_batches, AVG(processing_duration) AS average_duration FROM customs_batches GROUP BY date; -- Analyser l'impact du jour de la semaine sur la vitesse de dédouanement SELECT strftime('%w', start_timestamp) AS day_of_week, AVG(avg_processing_speed) AS average_speed FROM customs_batches GROUP BY day_of_week; -- Lister les lots encore en attente (pas de 370 reçu depuis >3 jours) SELECT customs_lots.id, COUNT(tracking_steps.id) AS steps_count FROM customs_lots LEFT JOIN tracking_steps ON customs_lots.id = tracking_steps.parcel_id AND tracking_steps.action_code = '370' WHERE tracking_steps.timestamp IS NULL OR tracking_steps.timestamp < datetime('now', '-3 days') GROUP BY customs_lots.id;4. Modèle prédictif simplifié avec
sklearnVoici un exemple de code pour créer un modèle prédictif simple :
import pandas as pd from sklearn.model_selection import train_test_split from sklearn.linear_model import LinearRegression from sklearn.metrics import mean_squared_error # Charger les données def load_data(): conn = sqlite3.connect('customs_tracking.db') query = ''' SELECT strftime('%w', start_timestamp) AS day_of_week, batch_size, processing_duration FROM customs_batches ''' df = pd.read_sql_query(query, conn) conn.close() return df # Préparer les données df = load_data() df['day_of_week'] = df['day_of_week'].astype(int) X = df[['day_of_week', 'batch_size']] y = df['processing_duration'] # Diviser les données X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42) # Créer et entraîner le modèle model = LinearRegression() model.fit(X_train, y_train) # Prédire et évaluer y_pred = model.predict(X_test) mse = mean_squared_error(y_test, y_pred) print(f'Mean Squared Error: {mse}')Bonus
Pour intégrer une vue matérialisée pour les statistiques journalières, vous pouvez créer une vue SQL :
CREATE VIEW daily_stats AS SELECT DATE(created_at) AS date, COUNT(*) AS number_of_batches, AVG(processing_duration) AS average_duration FROM customs_batches GROUP BY date;Pour les alertes automatiques, vous pouvez utiliser une bibliothèque comme
smtplibpour envoyer des emails si un batch dépasse 24 heures.Conclusion
Ce code fournit une base solide pour suivre et analyser les durées de dédouanement douanier de colis. Vous pouvez l'étendre et l'adapter selon vos besoins spécifiques. Assurez-vous de tester chaque partie du code et d'ajuster les requêtes SQL en fonction de votre modèle de données.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.