---
# System prepended metadata

title: Задание 1

---

# Задание 1

> Представьте, что данных в этих таблицах очень много, сотни миллионов
> записей. Если вам кажется, что с таблицами или данными что-то не так -
> исправьте по своему усмотрению. Опишите в комментариях, что вы сделали
> и почему.

1. Дату в поле `birthday` лучше хранить в специализированных форматах для дат, например в `Date`, эти форматы больше оптимизированы для хранения и обработки дат, к тому же не получим проблему 2038 года (если приложение доживёт). К тому же, в поле день рождения нам не нужно хранить время с точностью до секунды. Формат `INT` занимает 4 байта, а `Date` -- 3 байта.

2. Поле `sex` лучше сделать с типом `BOOLEAN`, потому что биологический пол может быть только мужской или женский. И, наверное, для избежания путаницы можно назвать поле `is_male`, либо же сделать enum. Если же мы говорим про gender, то тут возможно и стоит заложить побольше вариантов: https://young.scot/get-informed/national/gender-identity-terms 😂

3. Для поля `has_work` тип данных тоже лучше сделать `BOOLEAN`.

4. В рамках данной задачи (мы не делаем поиск по имени) хранение ФИО в формате `VARCHAR` -- хорошее решение, оно экономит дисковое пространство, но если нам нужно делать частые поиски по имени, возможно, стоит использовать тип данных `CHAR`.

5. В поле `id` я бы добавил `AUTO_INCREMENT`. В этом случае задача генерации уникальной последовательности будет на стороне СУБД. Если мы, по какой-то причине не хотим использовать `AUTO_INCREMENT`, имеет смысл посмотреть в сторону типа данных `UUID`. В этом случае со стороны кода мы получим возможность знать какой будет идентификатор записи ДО добавления в базу данных.

6. В таблице `loan` нужно добавить `FOREIGN KEY` на поле `account_id` для консистентности данных.

Таким образом, запрос на создание таблиц будет выглядеть вот так:

    CREATE TABLE `account` (
    `id`        INT(11) NOT NULL AUTO_INCREMENT,
    `fio`       VARCHAR(255),
    `is_male`   bool COMMENT '0 - не указан, 1 - мужчина, 2 - женщина.',
    `birthdate` DATE COMMENT 'Дата в unixtime.',
    `has_work` bool comment '1 - Есть работа, 0 – нет работы',
    PRIMARY KEY (`id`)
    );
    
    CREATE TABLE `loan` (  
    `id` INT(11) NOT NULL AUTO_INCREMENT,  
    `account_id` INT(11),  
    `amount` INT(11),  
    CONSTRAINT loan_account_id_fk  
        FOREIGN KEY (account_id) REFERENCES account (id)  
        ON DELETE CASCADE ,  
    PRIMARY KEY (`id`)  
    );
***
> Напишите запрос, возвращающий имя, количество кредитов и общую сумму
> кредитов для работающих мужчин, имеющих не более 2-х кредитов на общую
> сумму не более 50000 т.р. Отсортируйте по возрастанию общей суммы
> кредитов.

    SELECT fio,
           count(l.id) AS loan_quantity,
           SUM(l.amount) AS loan_sum
    FROM account
    JOIN loan l ON account.id = l.account_id
    WHERE has_work = 1
      AND is_male = 1
    GROUP BY l.account_id
    HAVING loan_quantity <= 2
    AND loan_sum <= 50000
    ORDER BY loan_sum;

***
> Если вам кажется, что для быстрого выполнения этого запроса таблицы
> нуждаются в оптимизации - проделайте её по своему усмотрению. Опишите
> в комментариях, что вы сделали и почему.


Посмотрим как выполняется запрос без оптимизации.

    mysql> explain select fio, count(l.amount) as loan_quantity, SUM(l.amount) as loan_sum from account
        -> join loan l on account.id = l.account_id
        -> where has_work = 1
        -> group by l.account_id
        -> having loan_quantity <= 2 and loan_sum <= 50000
        -> order by loan_sum;
    +----+-------------+---------+------------+------+--------------------+--------------------+---------+---------------------------+------+----------+----------------------------------------------+
    | id | select_type | table   | partitions | type | possible_keys      | key                | key_len | ref                       | rows | filtered | Extra                                        |
    +----+-------------+---------+------------+------+--------------------+--------------------+---------+---------------------------+------+----------+----------------------------------------------+
    |  1 | SIMPLE      | account | NULL       | ALL  | PRIMARY            | NULL               | NULL    | NULL                      | 1000 |    10.00 | Using where; Using temporary; Using filesort |
    |  1 | SIMPLE      | l       | NULL       | ref  | loan_account_id_fk | loan_account_id_fk | 5       | test_andromeda.account.id |    1 |   100.00 | NULL                                         |
    +----+-------------+---------+------------+------+--------------------+--------------------+---------+---------------------------+------+----------+----------------------------------------------+
    2 rows in set, 1 warning (0.00 sec)
    
    mysql> 

Тут мы видим, что в поле `type` в первой таблице стоит значение `ALL`, это значит, что таблица полностью просканирована, так же в поле `rows` мы видим, что обработано 1000 записей. Добавим индексов.

Добавим индекс на поле `has_work`:

    ALTER TABLE account ADD INDEX (has_work, is_female)
И снова проверим:

    mysql> explain select fio, count(l.id) as loan_quantity, SUM(l.amount) as loan_sum from account join loan l on account.id = l.account_id where has_work = 1 group by l.account_id having loan_quantity <= 2 and loan_sum <= 50000 order by loan_sum;
    +----+-------------+---------+------------+------+-----------------------+-----------------------+---------+---------------------------+------+----------+---------------------------------+
    | id | select_type | table   | partitions | type | possible_keys         | key                   | key_len | ref                       | rows | filtered | Extra                           |
    +----+-------------+---------+------------+------+-----------------------+-----------------------+---------+---------------------------+------+----------+---------------------------------+
    |  1 | SIMPLE      | account | NULL       | ref  | PRIMARY,has_work      | has_work              | 2       | const                     |  317 |   100.00 | Using temporary; Using filesort |
    |  1 | SIMPLE      | l       | NULL       | ref  | loan_account_id_index | loan_account_id_index | 5       | test_andromeda.account.id |    1 |   100.00 | NULL                            |
    +----+-------------+---------+------------+------+-----------------------+-----------------------+---------+---------------------------+------+----------+---------------------------------+
    2 rows in set, 1 warning (0.00 sec)
    
    mysql> 
То есть вместо полного сканирования всей таблицы мы сразу же отфильтровали примерно треть записей, при этом в колонке key появился используемый индекс.

# Задание 2

    Этот код использует таблицу account из предыдущего примера
    
    function load_account_data( $account_ids ) {
        $account_ids = explode(',', $account_ids);
        foreach ($account_ids as $user_id) {
            $db = mysqli_connect("localhost", "test_user", "123123", "test_andromeda");
            $sql = mysqli_query($db, "SELECT * FROM account WHERE id=$user_id");
            while($obj = $sql->fetch_object()){
                $data[$user_id] = $obj->fio;
            }
            mysqli_close($db);
        }
        return $data;
    }
    
    // Как правило, в $_GET['account_ids'] должна приходить строка
    // с номерами заёмщиков через запятую, например: 1,2,17,48
    
    $data = load_account_data( $_GET['account_ids'] );
    foreach ($data as $user_id=>$name) {
        echo "<a href=\"/show_user.php?id=$user_id\">$name</a>";
    }
    
    Если вам кажется, что с кодом что-то не так - проведите его рефакторинг по своему усмотрению. В комментариях опишите, что вы сделали и почему.

Отрефакторил код вот так:

    <?php  
      
    CONST HOST = '172.19.0.2';  
    CONST USER = 'root';  
    CONST PASSWORD = 'password';  
    CONST DATABASE = 'test_andromeda';  
      
    function load_account_data(string $account_ids): array {  
        if (validate($account_ids) === false) {  
            throw new Exception('Invalid data.');  
        }  
      
        $sql = sprintf('SELECT id, fio FROM account WHERE id in (%s)', $account_ids);  
      
        $data = [];  
      
        foreach (get_data($sql) as $obj) {  
            $data[$obj->id] = $obj->fio;  
        }  
      
        return $data;  
    }  
      
    function validate(string $account_ids): bool {  
        if (!preg_match('/^\d+(,\d+)*$/', $account_ids)) {  
            return false;  
        }  
      
        return true;  
    }  
      
    function get_data(string $query): Generator {  
        $db = mysqli_connect(HOST, USER, PASSWORD, DATABASE);  
        $sql = mysqli_query($db, $query);  
      
        while($obj = $sql->fetch_object()){  
            yield $obj;  
        }  
      
        mysqli_close($db);  
    }  
      
    $data = load_account_data('1,3,5,6,123,56,456,12');  
      
    foreach ($data as $user_id=>$name) {  
        echo "<a href=\"/show_user.php?id=$user_id\">$name</a>";  
    }

Так как в `GET` параметре может быть максимум 2048 символов, то оператор `IN` нам подходит. Для начала отвалидируем данные, чтобы убедиться, что данные пришли именно в том формате, который нам нужен: цифра, запятая между цифрами и отсутствите запятой в конце строки. Проинициализируем переменную `$data`, чтобы в случае, если передался пустой массив мы пустой массив и вернули. Дальше отделим логику обращения в БД в другую функцию, дабы не загромождать основной код низкоуровневыми операциями. Так как мы отвалидировали входную переменную, можно вставлять её в запрос как есть, без экранирования специальных символов. Если не использовать валидацию, то можно вместо `mysqli` использовать `PDO` с возможность создания `PREPARED-STATEMENT`. Для возврата данных в основную функцию используем генераторы. Так, в том числе рекомендуется делать в Doctrine для итерирования по большим объёмам данным без загрузки в память.

# Задание 3

> Имеется урл:
>
> https://www.mydomain.com/controller/test.php?a=45&b=38&c=0&d=12&e=309&f=38&g=zed456&h=test2
>
> Напишите на PHP функцию, принимающую в качестве аргумента урл и
> возвращающую строку в качестве результата, которая:
>
> а) Удалит параметры со значением 38 б) Отсортирует параметры по
> значению в) Перенесет относительный путь к файлу
> (/controller/test.php) из исходной ссылки в новый параметр path
>
> Проверьте себя: в указанном примере функцией должно быть возвращено
>
> https://www.mydomain.com/?c=0&d=12&a=45&e=309&h=test2&g=zed456&path=%2Fcontroller%2Ftest.php

Мы предполагаем, что URL нам передаётся валидный. Поэтому код вот такой:

    <?php  
      
    declare(strict_types=1);  
      
    function formatUrl(string $url): string {  
        $parsedUrl = parse_url($url);  
        $queryParams = explode('&', $parsedUrl['query']);  
        $arrayedQueryParams = [];  
      
        foreach ($queryParams as $theQueryParam) {  
            [$key, $value] = explode('=', $theQueryParam);  
      
            if ((int) $value === 38) {  
                continue;  
            }  
      
            if (is_numeric($value)) {  
                $value = (int) $value;  
            }  
      
            $arrayedQueryParams[$key] = $value;  
        }  
      
        uasort(
            $arrayedQueryParams,  
            'string_num_sort'  
        );  
      
        $arrayedQueryParams['path'] = $parsedUrl['path'];  
      
        return sprintf(  
            '%s://%s/?%s',  
            $parsedUrl['scheme'],  
            $parsedUrl['host'],  
            http_build_query($arrayedQueryParams)
        );
    }  
      
    function string_num_sort($a, $b): int {  
        if (!is_numeric($a) && is_numeric($b)) {  
            return 1;  
        }  
      
      if (is_numeric($a) && !is_numeric($b)) {  
          return -1;  
      }  
      
      return $a <=> $b;  
    }  
      
    print_r(formatUrl('https://www.mydomain.com/controller/test.php?a=45&b=38&c=0&d=12&e=309&f=38&g=zed456&h=test2'));
