Привет Хабр! Меня зовут Татьяна Ошуркова, я разработчик, аналитик и автор телеграм-канала IT Talks. Я начинала свой путь в качестве разработчика баз данных и за время своего опыта мной было решено большое количество интересных задач, которые научили меня определенным подходам к работе с данными. Полученными навыками я пользуюсь и сегодня в роли системного аналитика.

На мой взгляд, навык работы с базами данных (БД) в системном анализе, даже если вы не пишете требования к самим БД, очень полезен. Он помогает эффективно решать задачи и находить новые подходы. Умение работать с базами данных может помочь в понимании бизнес-процессов, в непосредственном анализе работы системы, поиске и проработке различных кейсов.

12 декабря я проведу бесплатный вебинар: «Базы данных для системного аналитика на практике», где я подробно разберу необходимые навыки для системного аналитика в практических задачах. Запись на вебинар доступна по ссылке.

В этой статье я разберу ключевые навыки работы с базами данных для системного аналитика, исходя из своего опыта. Я не буду останавливаться на теории, но каждый пункт будет рассмотрен на практическом примере. Статья будет полезна не аналитикам баз данных, а специалистам, работающим с БД параллельно с другим функционалом. Если вы аналитик БД, то вам необходима данная информация, если только вы начинающий специалист.

Проектирование баз данных

Проектирование базы данных – это процесс создания логической и физической структуры для хранения и управления данными.

Определение сущностей, атрибутов и типов связей

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

• Сущности. Данное понятие представляет собой объекты, о которых нужно хранить информацию. После проработки бизнес-процесса важно корректно выделить все необходимые сущности.

• Атрибуты. Это свойства сущности. На этапе их определения можно разбить сущности на более мелкие. Важно корректно определить тип данных для каждого атрибута.

• Ключи и индексы. От корректности первичных, внешних ключей и индексов зависит множество аспектов. Это относится к безопасности данных, количеству ошибок и скорости отработки запросов.

• Типы связей. Связи показывают, как сущности взаимодействуют друг с другом. Это также важно и зависит от проработки первичных и внешних ключей.

Нормализация

Нормализация – это процесс организации данных в базе данных для минимизации избыточности и устранения аномалий при добавлении, обновлении или удалении записей. Цель нормализации – повысить согласованность данных и упростить их управление. Необходим знать, что такое 3 формы нормализации, как применять их на практике и для чего они необходимы. Основными принципами нормализации являются:

• Минимизация избыточности данных. Данные хранятся только один раз в одной таблице. Это предотвращает дублирование, которое может привести к ошибкам и увеличению объема хранения.

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

• Обеспечение гибкости. Хорошо нормализованная база данных легче адаптируется к изменениям в требованиях.

Далее рассмотрим несколько примеров в части проектирования. У нас есть задача спроектировать модель БД для хранения данных о клиентах и их заказах.

В данном примере мы не разделили процесс на сущности, а создали одну таблицу для работы с данными. Данные дублируются, отсутствуют внешние ключи, а нормализация не применяется. Это может привести к избыточности данных, аномалиям при обновлениях и сложностям с масштабированием.

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

• Информация о клиентах и продуктах вынесена в отдельные таблицы. Это устраняет дублирование.

• Изменение данных в одной таблице автоматически отражается на всех связанных записях через внешние ключи.

• Легче масштабировать и анализировать данные.

Работа с SQL-запросами

Как и в предыдущем разделе речь идет не только о знании синтаксиса и типов запросов. Качественная выборка – это отличный инструмент для анализа данных. С помощью выборок можно найти необходимые кейсы для воспроизведения проблематики, проанализировать работу системы, выявить возможные ошибки и многое другое.

Типы SQL JOINS и оконные функции

В данной статье я не буду подробно останавливать на простейших командах. Хотелось бы обратить внимание на 2 операции в запросах. Это типы соединений и оконные функции. В моем опыте именно они решили нестандартные задачи, делали выборки более эффективными и помогали в работе со сложными данными.

JOIN – это операция в SQL, которая позволяет объединять данные из нескольких таблиц на основе их логической связи. Есть различные типы соединений: LEFT JOIN, RIGHT JOIN и так далее. Их использование отличается от WHERE не только синтаксисом, но также назначением и задачами.

Оконные функции – это функция, которая использует значения из одной или нескольких строк для возврата значения. Они работают в «окне» – наборе строк, который определяется с помощью OVER().

План запроса

План запроса – это пошаговое описание того, как СУБД (система управления базами данных) выполняет SQL-запрос. Он показывает, какие операции выполняются и в каком порядке, чтобы получить данные, указанные в запросе. План включает:

• Типы операций (например, сканирование таблицы, соединение, фильтрация).

• Используемые индексы.

• Оценки объема данных (число строк, байтов).

• Стоимость выполнения (время и ресурсы, требуемые для выполнения операций).

План запроса формируется оптимизатором базы данных, который анализирует запрос и выбирает наиболее эффективный способ его выполнения.

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

Далее рассмотрим пример. Нужно отметить, что использование оконных функций или различных JOIN не всегда может сделать запрос более оптимальным. Пример иллюстрирует применение на практике того, о чем я писала выше.

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

Рассмотрим неоптимизированный пример с подзапросами:

• Множественные подзапросы. Каждый подзапрос пересчитывает данные, что вызывает множество сканирований таблиц Orders и Products.

• Сложность анализа. Код громоздкий и трудно читаемый.

SELECT 
    c.ClientID,
    (SELECT SUM(o.TotalAmount) FROM Orders o WHERE o.ClientID = c.ClientID) AS TotalAmount,
    (SELECT MAX(o.TotalAmount) FROM Orders o WHERE o.ClientID = c.ClientID) AS MaxOrderAmount,
    (SELECT MAX(o.OrderDate) FROM Orders o WHERE o.ClientID = c.ClientID) AS LastOrderDate
FROM Clients c;

Оптимизируем запрос, а также рассмотрим пример использования JOIN и оконных функций.

• JOIN. Таблицы Clients, Orders и Products связаны через ClientID и OrderID, что исключает множественные подзапросы.

• Группировка (GROUP BY). Данные агрегируются по клиенту.

• Оконная функция (RANK). Позволяет ранжировать клиентов по общей сумме заказов, добавляя аналитику в результат.

• Производительность. Все расчеты выполняются за один проход по данным.

SELECT 
    c.ClientID,
    SUM(o.TotalAmount) AS TotalAmount,
    MAX(o.TotalAmount) AS MaxOrderAmount,
    MAX(o.OrderDate) AS LastOrderDate,
    RANK() OVER (ORDER BY SUM(o.TotalAmount) DESC) AS ClientRank
FROM 
    Clients c
JOIN 
    Orders o ON c.ClientID = o.ClientID
LEFT JOIN 
    Products p ON o.OrderID = p.OrderID
GROUP BY 
    c.ClientID, c.ClientName;

Создание хранимых процедур

Хранимые процедуры – это наборы SQL-запросов и логики, которые инкапсулированы в объект базы данных. Они позволяют эффективно использовать ресурсы, упрощать повторное использование кода и централизовать бизнес-логику. Хранимые процедуры широко используются во всех СУБД (системах управления базами данных), как Oracle, SQL Server, MySQL, PostgreSQL, и других.

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

Рассмотрим пример процедуры на PL/SQL, которая автоматизирует поиск самых популярных товаров (с учетом количества заказов и средней оценки) для каждого клиента. Этот запрос может быть сложным, если пытаться написать его одним SQL-запросом

CREATE OR REPLACE PROCEDURE GetMostPopularProducts IS
    v_client_id Clients.ClientID%TYPE;
    v_product_id Orders.ProductID%TYPE;
    v_max_quantity NUMBER := 0;
    v_max_product_id Orders.ProductID%TYPE;
    v_avg_rating Products.AverageRating%TYPE;
BEGIN
    FOR client_record IN (SELECT ClientID FROM Clients) LOOP
        -- Находим товар с максимальным количеством заказов для клиента
        FOR order_record IN (SELECT ProductID, SUM(Quantity) AS TotalQuantity 
                              FROM Orders WHERE ClientID = client_record.ClientID 
                              GROUP BY ProductID) LOOP
            IF order_record.TotalQuantity > v_max_quantity THEN
                v_max_quantity := order_record.TotalQuantity;
                v_max_product_id := order_record.ProductID;
            END IF;
        END LOOP;
        
        -- Находим средний рейтинг для самого популярного товара
        SELECT AVG(Rating) INTO v_avg_rating FROM Reviews WHERE ProductID = v_max_product_id;
        
        -- Выводим результаты
        DBMS_OUTPUT.PUT_LINE('Клиент: ' || client_record.ClientID || ', Товар: ' || v_max_product_id || 
                             ', Кол-во заказов: ' || v_max_quantity || ', Средний рейтинг: ' || v_avg_rating);

        -- Сброс переменных для следующего клиента
        v_max_quantity := 0;
    END LOOP;
END GetMostPopularProducts;

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

• Подсчет количества заказов для каждого клиента. Для каждого клиента вычисляется общее количество заказанных товаров и выбирается товар с максимальным количеством.

• Вычисление среднего рейтинга. Для товара с максимальным количеством заказов вычисляется средний рейтинг из таблицы Reviews.

• Вывод результата. Для каждого клиента выводится его ID, товар с максимальным количеством заказов, количество этих заказов и средний рейтинг.

Работа с транзакциями

Транзакции в SQL – это последовательность операций, которые выполняются как единое целое. Транзакция начинается с первой операции и заканчивается, когда все операции внутри неё либо подтверждаются (commit), либо откатываются (rollback). Основная цель транзакции – обеспечить целостность данных, чтобы избежать ошибок, которые могут возникнуть из-за сбоев системы или некорректных операций.

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

Рассмотрим небольшой пример с проверкой баланса и обновлением данных в базе данных, который выполняет перевод средств между двумя счетами.

CREATE OR REPLACE PROCEDURE Transfer_Funds (p_from_account NUMBER, p_to_account NUMBER, p_amount NUMBER) AS
BEGIN
    IF (SELECT Balance FROM Accounts WHERE AccountID = p_from_account) < p_amount THEN
        RAISE_APPLICATION_ERROR(-20001, 'Недостаточно средств');
    END IF;
    UPDATE Accounts SET Balance = Balance - p_amount WHERE AccountID = p_from_account;
    UPDATE Accounts SET Balance = Balance + p_amount WHERE AccountID = p_to_account;
    INSERT INTO Transfers (FromAccountID, ToAccountID, Amount) VALUES (p_from_account, p_to_account, p_amount);
    COMMIT;
END;

• Проверка баланса. Если на счете отправителя недостаточно средств, генерируется ошибка.

• Перевод средств. Выполняются два UPDATE: один для списания средств, второй — для зачисления.

• Коммит транзакции. Все изменения фиксируются с помощью COMMIT .

Подведем итоги

Навыки работы с базами данных для системного аналитика играют очень важную роль. Сложной найти систему, которая бы не использовала БД, а значит данные навыки будут всегда актуальны.

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

В завершение делюсь подборкой литературы по базам данных с использованием различных СУБД:

• Изучаем SQL. Генерация, выборка и обработка данных. Болье Алан

• Путеводитель по базам данных. Комаров Владимир

• SQL: быстрое погружение. Шилдс Уолтер

• Базы данных на примерах. Практика, практика и только практика. Финкова Мария А.