Описание проекта SQL Stroy Analytics
Краткое описание
SQL Stroy Analytics — итоговый проект по PostgreSQL, выполненный на основе учебной базы данных stroy.
Проект представляет собой набор аналитических SQL-запросов для решения задач в предметной области строительной и проектной компании. В работе использовались данные о сотрудниках, подразделениях, контрагентах, проектах, оплатах и заработных платах.
Итогом проекта стал единый .sql-файл, содержащий 10 решений — от базовой агрегации до рекурсивных запросов, оконных функций и материализованного представления.
Цель проекта
Цель проекта — продемонстрировать владение SQL-инструментами PostgreSQL на практике через решение задач разного уровня сложности.
В проекте необходимо было:
– анализировать структуру базы данных и связи между таблицами;
– выбирать только релевантные таблицы для каждой задачи;
– формировать корректные условия фильтрации;
– выполнять расчёты по датам, числовым значениям и платежам;
– использовать подзапросы и CTE;
– применять оконные функции;
– работать с иерархическими структурами через рекурсию;
– создавать отчётную структуру в виде материализованного представления.
Исходные данные
В основе проекта — схема stroy, описывающая фрагмент информационной системы строительной компании.
Данные включают:
– сотрудников и физических лиц;
– организационную структуру компании;
– должности и зарплаты;
– контрагентов и их адреса;
– проекты и руководителей проектов;
– участников проектов;
– платежи по проектам;
– типы выполняемых работ.
База данных является учебной и используется для отработки аналитических SQL-запросов.
Содержание итоговой работы
Проект состоит из 10 задач:
1. Подсчёт проектов, подписанных в 2023 году
Решается задача фильтрации по дате и агрегирования записей.
2. Расчёт общего возраста сотрудников, нанятых в 2022 году
Используются операции с датами и интервальным результатом.
3. Поиск сотрудника по фамилии и длительности работы
Реализуется комбинированная фильтрация по текстовому признаку и дате найма.
4. Средний возраст уволенных сотрудников, не задействованных на проектах
Используется исключение через not exists, проверка массива участников проекта и обработка возможного null через coalesce.
5. Сумма полученных платежей от контрагентов из конкретного города и страны
Запрос объединяет платежи, проекты, контрагентов, адреса, города и страны.
6. Определение руководителя проекта с максимальной премией
Используются агрегирование по руководителю проекта, расчёт бонуса и поиск максимального значения с поддержкой нескольких совпадений.
7. Дата превышения накопительной суммы авансовых платежей
Реализуются накопительные суммы и сравнение текущего значения с предыдущим через оконную функцию lag.
8. Сумма фактических окладов сотрудников подразделения и всех дочерних подразделений
Используется with recursive для обхода иерархии подразделений.
9. Комплексный аналитический запрос
В одном запросе реализованы:
– сквозная нумерация фактических платежей внутри каждого года;
– отбор каждого пятого платежа;
– скользящее среднее с окном две строки назад и две вперёд;
– сумма скользящих средних;
– сравнение этой суммы с годовой стоимостью проектов.
10. Материализованное представление для отчёта по проектам
Создаётся materialized view, в котором хранятся:
– проект;
– дата и сумма последнего фактического платежа;
– руководитель проекта;
– контрагент;
– строка с типами работ контрагента.
Моя роль
В рамках проекта я:
– восстановила и изучила учебную базу данных stroy;
– проанализировала структуру таблиц и связей;
– самостоятельно подобрала SQL-решения для каждой задачи;
– оформила запросы в единый SQL-файл;
– использовала различные техники PostgreSQL в зависимости от условий задания;
– подготовила запросы так, чтобы они соответствовали требованиям к результату и логике проверяющего.
Ключевая ценность проекта
Этот проект показывает не просто знание синтаксиса SQL, а умение:
– переводить текстовое условие задачи в алгоритм запроса;
– выбирать корректный способ решения;
– учитывать ограничения задания;
– работать с несколькими таблицами и сложной логикой;
– создавать воспроизводимые аналитические решения;
– объяснять применённый подход.
Итоговый результат
Результатом работы является файл:
Он содержит все 10 решений, подготовленные для итоговой проверки.