задание

Есть база данных для хранения информации о клиентах, товарах и заказах со следующей структурой: clients (id, name) - ID клиента и его имя merchandise (id, name) - ID товара и его наименование orders (id, item_id, customer_id, comment, status, order_date) - ID заказа, ID товара, ID клиента, комментарий клиента, статус заказа (‘new’, ‘complete’), дата заказа (то есть структура предполагает, что один заказ - это один товар)

Необходимо:

Написать скрипт, который получает на вход текстовый файл с данными о заказах (разделитель “;”) вида: ID товара;ID клиента;Комментарий к заказу и загружает содержимое в описанную выше структуру БД, при этом все невалидные строки должны записываться в отдельный файл. Использование сторонних решений / библиотек нежелательно.
Написать SQL запросы, возвращающие набор данных, соответствующий следующим условиям: a. Выбрать имена (name) всех клиентов, которые не делали заказы в последние 7 дней. b. Выбрать имена (name) 5 клиентов, которые сделали больше всего заказов в магазине. c. Выбрать имена (name) 10 клиентов, которые сделали заказы на наибольшую сумму. d. Выбрать имена (name) всех товаров, по которым не было доставленных заказов (со статусом “complete”).
Описать, какие бы вы создали индексы для оптимизации скорости работы запросов из п.2 и почему

В качестве решения тестового задания принимается архив, содержащий:

скрипт импорта заказов из п.1, набор тестовых данных для скрипта, соответствующий описанному формату, файл с SQL запросами и перечнем необходимых индексов (с обоснованием).

1. Скрипт импорта заказов (PHP)

<?php
// config.php (можно вынести параметры подключения)
$host = 'localhost';
$db = 'shop';
$user = 'root';
$pass = '1';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8", $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    ]);
} catch (PDOException $e) {
    die("DB error: " . $e->getMessage());
}

$inputFile = 'orders.txt';
$errorFile = 'invalid_orders.txt';

$handle = fopen($inputFile, 'r');
$errors = fopen($errorFile, 'w');

while (($line = fgets($handle)) !== false) {
    $line = trim($line);
    if (empty($line)) continue;

    $parts = explode(';', $line);
    if (count($parts) !== 3) {
        fwrite($errors, $line . PHP_EOL);
        continue;
    }

    [$itemId, $customerId, $comment] = $parts;

    // Проверка существования
    $stmt = $pdo->prepare("SELECT 1 FROM merchandise WHERE id = ? LIMIT 1");
    $stmt->execute([$itemId]);
    $merch = $stmt->fetchColumn();

    $stmt = $pdo->prepare("SELECT 1 FROM clients WHERE id = ? LIMIT 1");
    $stmt->execute([$customerId]);
    $client = $stmt->fetchColumn();

    if ($merch && $client) {
        $stmt = $pdo->prepare("INSERT INTO orders (item_id, customer_id, comment, status, order_date) VALUES (?, ?, ?, 'new', NOW())");
        $stmt->execute([$itemId, $customerId, $comment]);
    } else {
        fwrite($errors, $line . PHP_EOL);
    }
}

fclose($handle);
fclose($errors);
echo "Import done.";

Тестовые данные (orders.txt):

1;1;Быстрая доставка
2;1;Хороший товар
3;2;Нужен гарантийный талон
4;3;Подходит по размеру
5;2;Цвет немного другой
6;99;Невалидный клиент
7;4;;Пустой комментарий

2. SQL запросы

a. Клиенты, не делавшие заказы в последние 7 дней:

SELECT c.name
FROM clients c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
      AND o.order_date >= DATE_SUB(NOW(), INTERVAL 7 DAY)
);

b. 5 клиентов с наибольшим количеством заказов:

SELECT c.name
FROM clients c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id
ORDER BY COUNT(o.id) DESC
LIMIT 5;

c. 10 клиентов с наибольшей суммой заказов (предполагается поле price в merchandise):

SELECT c.name
FROM clients c
JOIN orders o ON c.id = o.customer_id
JOIN merchandise m ON o.item_id = m.id
GROUP BY c.id
ORDER BY SUM(m.price) DESC
LIMIT 10;

d. Товары без доставленных заказов (статус complete):

SELECT m.name
FROM merchandise m
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.item_id = m.id
      AND o.status = 'complete'
);

3. Индексы для оптимизации

orders(order_date) – ускоряет фильтрацию по дате в запросе a.
orders(customer_id) – ускоряет JOIN и группировку в запросах b и c.
orders(item_id, status) – ускоряет фильтрацию по статусу и товару в запросе d.
Уникальный индекс merchandise(id) и clients(id) уже существуют как PK.

Обоснование: Индексы покрывают все условия WHERE, JOIN и ORDER BY/GROUP BY в запросах, что значительно сокращает время выполнения за счет быстрого поиска и сортировки.

v0ff4k/distant_sellers-task.md

Select an option