SQL-инструменты, использованные в проекте

Описание

В итоговом проекте по базе данных stroy использовались разные механизмы PostgreSQL — от базовой агрегации до оконной аналитики, рекурсивных запросов и материализованных представлений.

Этот документ систематизирует ключевые SQL-приёмы, применённые при решении задач.

1. Агрегатные функции

Агрегатные функции использовались для получения итоговых количественных показателей.

В проекте применялись:

– count(*) — подсчёт количества проектов;

– sum(...) — расчёт сумм платежей, окладов, стоимости проектов и бонусов;

– avg(...) — расчёт среднего возраста сотрудников;

– max(...) — определение максимального размера бонуса.

Примеры задач:

– количество проектов, подписанных в 2023 году;

– сумма полученных платежей от контрагентов из заданного города;

– средний возраст уволенных сотрудников;

– максимальный бонус руководителя проекта.

2. Обработка возможных NULL

Для ситуаций, когда агрегатная функция может вернуть null, использовалась функция:

coalesce(...)

Примеры:

coalesce(avg(...), 0)

coalesce(sum(pp.amount), 0)

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

Применение:

– средний возраст уволенных сотрудников;

– сумма платежей;

– сумма окладов сотрудников.

3. Работа с датами и интервалами

В проекте использовались функции и операции для анализа временных показателей.

extract

Используется для получения года из даты или timestamp:

extract(year from sign_date)

extract(year from fact_transaction_timestamp)

Применение:

– фильтрация проектов по году подписания;

– разбиение платежей по годам;

– расчёт полных лет возраста.

age

Используется для расчёта возраста человека:

age(current_date, birthdate)

Применение:

– суммарный возраст сотрудников;

– средний возраст в полных годах.

Интервальная фильтрация по датам

В проекте используется фильтрация через границы периода:

hire_date >= date '2022-01-01'

and hire_date < date '2023-01-01'

Такой подход корректно выделяет записи за конкретный календарный год и избегает неоднозначностей.

4. Строковая фильтрация

Для поиска сотрудника по условиям на фамилию использовались строковые операции.

like

last_name like 'М%'

Позволяет выбрать фамилии, начинающиеся на нужную букву.

length

length(last_name) = 8

Позволяет ограничить длину фамилии.

Конкатенация строк

first_name || ' ' || last_name

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

5. Соединение таблиц

Проект активно использует join для объединения данных из разных сущностей.

Пример многоступенчатой цепочки

project_payment

→ project

→ customer

→ address

→ city

→ country

Она использовалась для расчёта суммы платежей от контрагентов из конкретного города и страны.

Пример проектной цепочки

project

→ employee

→ person

Она применялась для получения ФИО руководителя проекта.

Проект демонстрирует умение:

– выбирать корректный тип соединения;

– строить запросы через несколько уровней связей;

– использовать left join, когда связанные данные могут отсутствовать.

6. Исключающая логика через NOT EXISTS

Для поиска уволенных сотрудников, которые не задействованы на проектах, использовался подзапрос:

not exists (

   select 1

  from project pr

  where e.employee_id = any(pr.employees_id)

    or e.employee_id = pr.project_manager_id

)

Этот подход позволяет исключить сотрудников, которые:

– указаны в составе участников проекта;

– являются руководителями проектов.

Что демонстрирует:

– проверку отсутствия связанных данных;

– аккуратную обработку нескольких условий участия;

– применение подзапроса в логике фильтрации.

7. Работа с массивами через ANY

В таблице project сотрудники, задействованные на проекте, хранятся в массиве employees_id.

Для проверки наличия сотрудника в массиве используется оператор:

e.employee_id = any(pr.employees_id)

Это позволяет определить, входит ли сотрудник в команду проекта.

Применение:

– исключение сотрудников, которые уже участвовали в проектах.

8. Общие табличные выражения — CTE

Для повышения читаемости и декомпозиции сложной логики применялись CTE:

with manager_bonus as (...)

select ...

CTE позволяли:

– разделять сложный запрос на этапы;

– упростить чтение решения;

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

Примеры задач:

– расчёт бонусов руководителей;

– накопительные авансовые платежи;

– комплексный запрос со скользящим средним;

– формирование материализованного представления.

9. Оконная функция ROW_NUMBER

Функция:

row_number() over (...)

использовалась в двух задачах.

Нумерация платежей по годам

row_number() over (

       partition by extract(year from fact_transaction_timestamp)

       order by fact_transaction_timestamp, project_payment_id

)

Позволяет сделать сквозную нумерацию платежей отдельно внутри каждого года.

Выбор последнего платежа по проекту

row_number() over (

   partition by project_id

   order by fact_transaction_timestamp desc,

           project_payment_id desc

)

Позволяет определить самый поздний фактический платёж по каждому проекту.

10. Накопительный итог через SUM() OVER

Для расчёта планируемых авансовых платежей использовалась оконная сумма:

sum(amount) over (

   partition by date_trunc('month', plan_payment_date)

   order by plan_payment_date, project_payment_id

)

Что это даёт:

– суммы считаются последовательно по датам;

– накопление начинается заново в каждом месяце;

– можно определить момент первого превышения порога.

Применение:

– поиск даты, когда накопительная сумма превысила 30 000 000.

11. Функция LAG

Для сравнения текущего накопительного значения с предыдущим применялась функция:

lag(accumulation) over (...)

Она позволила определить момент, когда сумма впервые превысила контрольное значение.

Логика отбора:

accumulation > 30000000

and coalesce(previous_accumulation, 0) <= 30000000

12. Скользящее среднее через AVG() OVER

В сложной аналитической задаче использовался расчёт скользящего среднего платежей:

avg(amount) over (

  order by fact_transaction_timestamp, project_payment_id

 rows between 2 preceding and 2 following

)

Окно включает:

– две строки перед текущей;

– текущую строку;

– две строки после текущей.

Это позволяет рассчитать усреднённое значение платежей в локальном окружении каждой строки.

13. Рекурсивный CTE

Для обхода иерархии структурных подразделений использовалась рекурсия:

with recursive units as (

       select unit_id

       from company_structure

       where unit_id = 17



       union all



       select cs.unit_id

       from company_structure cs

       join units u on u.unit_id = cs.parent_id

)

Этот подход позволяет:

– начать с выбранного подразделения;

– последовательно найти всех потомков;

– использовать полученный список в последующем расчёте окладов.

14. Агрегация строк через STRING_AGG

В итоговом отчёте нужно было вывести названия типов работ контрагента в одной строке.

Использовалось:

string_agg(

       tw.type_of_work_name,

       ', '

       order by tw.type_of_work_name

)

Результат:

Проектирование, Строительство, Согласование документации

Применение:

– материализованное представление по проектам и контрагентам.

15. Материализованное представление

Финальная задача проекта включает создание объекта:

create materialized view project_payment_report as

...

Материализованное представление используется для хранения готового отчёта, который объединяет:

– проект;

– последний фактический платёж;

– руководителя;

– контрагента;

– типы работ.

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

16. LEFT JOIN для сохранения проектов без платежей

При создании итогового отчёта используется:

left join last_project_payment lpp

       on lpp.project_id = pr.project_id

Это позволяет сохранить в отчёте проекты даже в том случае, если по ним ещё не было фактических оплат.

Такой выбор типа соединения делает отчёт полнее и предотвращает случайную потерю строк.

17. Декомпозиция сложных запросов

Особенно сложные задачи проекта решались не монолитно, а через последовательные этапы.

Например, задача 9 логически разделена на:

1. нумерацию платежей;

2. отбор каждого пятого платежа;

3. расчёт скользящих средних;

4. суммирование скользящих средних;

5. расчёт годовой стоимости проектов;

6. сравнение агрегатов.

Такой подход:

– упрощает чтение запроса;

– облегчает проверку;

– снижает риск логических ошибок;

– делает решение объяснимым на защите.

Итоговый перечень применённых SQL-инструментов