Базы данных для системного аналитика. Краткий обзор на практике / Хабр

Привет Хабр! Меня зовут Татьяна Ошуркова, я разработчик, аналитик и автор телеграм-канала 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: быстрое погружение. Шилдс Уолтер
Базы данных на примерах. Практика, практика и только практика. Финкова Мария А.

IT Talks | Ошуркова Татьяна

t.me

Базы данных для системного аналитика. Краткий обзор на практике