Найти города с наибольшей логистической нагрузкой, включая текущие маршруты и успешно завершенные доставки

Предмет: Информатика
Раздел: Работа с базами данных, SQL-запросы, аналитика логистики

Разберем каждый из запросов подробно.

2. Города с высокой загруженностью доставок

Задача: Найти города с наибольшей логистической нагрузкой, включая текущие маршруты и успешно завершенные доставки.

SQL-Запрос (пример):

SELECT 
    city, 
    COUNT(*) AS total_deliveries,
    SUM(CASE WHEN status = 'в пути' THEN 1 ELSE 0 END) AS active_routes,
    SUM(CASE WHEN status = 'доставлено' THEN 1 ELSE 0 END) AS completed_deliveries
FROM 
    deliveries
GROUP BY 
    city
ORDER BY 
    total_deliveries DESC;

Пояснение:

• Считаем общее количество доставок по каждому городу.
• Отдельно считаем активные маршруты (в пути) и завершенные доставки.
• Сортируем по убыванию общего количества доставок.

3. Определение точек срыва доставки

Задача: Найти доставки, которые длились более 3 дней, и связать их с погодными условиями. Отсортировать по длительности маршрута.

SQL-Запрос (пример):

SELECT 
    d.delivery_id, 
    d.city, 
    DATEDIFF(d.delivery_date, d.shipment_date) AS duration_days,
    w.weather_condition
FROM 
    deliveries d
JOIN 
    weather w ON d.city = w.city AND DATE(d.delivery_date) = w.date
WHERE 
    DATEDIFF(d.delivery_date, d.shipment_date) > 3
ORDER BY 
    duration_days DESC;

Пояснение:

• Вычисляем длительность доставки как разницу между датой доставки и отправки.
• Присоединяем таблицу с погодой по городу и дате.
• Отбираем только те доставки, что длились более 3 дней.
• Сортируем по убыванию длительности.

4. Хватит ли подарков на складе для всех доставок?

Задача: Найти разницу между доступным количеством подарков и требуемым для доставок в статусе "сборка". Отсортировать по возрастанию разницы.

SQL-Запрос (пример):

SELECT 
    p.product_id,
    p.product_name,
    p.available_quantity,
    COALESCE(SUM(r.required_quantity), 0) AS total_required,
    (p.available_quantity - COALESCE(SUM(r.required_quantity), 0)) AS difference
FROM 
    products p
LEFT JOIN 
    required_items r ON p.product_id = r.product_id
LEFT JOIN 
    deliveries d ON r.delivery_id = d.delivery_id
WHERE 
    d.status = 'сборка'
GROUP BY 
    p.product_id, p.product_name, p.available_quantity
ORDER BY 
    difference ASC;

Пояснение:

• Считаем доступное количество товаров и требуемое для сборки.
• Вычисляем разницу.
• Сортируем по возрастанию, чтобы увидеть, каких товаров может не хватить.

Если у тебя есть структура таблиц или пример данных, могу адаптировать запросы более точно.