🔍 Основы COALESCE — защита от NULL в SQL

В SQL часто встречаются ситуации, когда в данных есть NULL — отсутствие значения. Это может вызвать ошибки или неожиданные результаты при обработке данных. Функция COALESCE() решает эту проблему, подставляя первое ненулевое значение из списка.

🛠 Синтаксис

COALESCE(значение1, значение2, ..., значениеN)

1. SQL проверяет аргументы слева направо и возвращает первое ненулевое значение.
2. Если все аргументы NULL, результат тоже будет NULL.

✔️ Пример 1: Подстановка значения по умолчанию
Допустим, у нас есть таблица users, где phone может быть NULL. Чтобы заменить NULL на строку 'Нет номера', используем COALESCE():

SELECT name, COALESCE(phone, 'Нет номера') AS phone_display
FROM users;

* Если в запросе вместо ненулевого последнего аргумента 'Нет номера' указать просто ' ', то в выгрузке запрос выведет NULL там, где номер телефона NULL в БД.

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

name    phone    phone_display
Иван    +10101   +10101
Анна    NULL     Нет номера

Как это работает?
1. Если phone не NULL, вернётся его значение.
2. Если phone = NULL, подставится 'Нет номера' в столбец phone_display.

✔️ Пример 2: Выбор приоритета из нескольких колонок
Допустим, у нас есть несколько номеров телефона (work_phone, mobile_phone, home_phone). Нам нужно вывести первый доступный:

SELECT name, COALESCE(work_phone, mobile_phone, home_phone, 'Нет') AS preferred_phone
FROM users;

После выполнения запроса база данных вернет результат (сократил названия столбов, чтобы таблица корректно отображалась не с десктопа):

name  work_p mobile_p home_p pref_p
Иван  NULL   NULL     555-1  555-1
Анна  777-5  999-9    NULL   777-5
Олег  NULL   NULL     NULL   Нет

Как это работает?
1. SQL сначала проверяет значение work_phone: если оно не NULL, оно и выводится.
2. Если work_phone = NULL, проверяется mobile_phone.
3. Если оба предыдущих столбца NULL, берётся home_phone.
4. Если все три NULL, подставляется последний не нулевой аргумент функции – 'Нет номера' в итоговый столбец preferred_phone.

Где полезно?
✔️ Замена NULL на значения по умолчанию.
✔️ Упрощение логики выбора из нескольких колонок.
✔️ Предотвращение ошибок при работе с NULL.

#Операторы_и_работа_с_данными #COALESCE

📱 Подписаться на канал | Курс автора по SQL DDL

📊 Нормальные формы в реляционных базах данных: что это и зачем нужно?

При проектировании и пользовательской эксплуатации реляционной базы данных важно следовать нормальным формам (NF), чтобы избежать избыточности, аномалий обновления и повысить целостность данных. Давайте разберёмся, какие бывают нормальные формы и как они работают.

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

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

Разберем на примерах первые три вида нормальных форм являющихся фундаментом нормализации БД.

1️⃣ Первая нормальная форма (1NF)
Требование:
1. Все атрибуты содержат только атомарные значения (неделимые).
2. Нет повторяющихся групп (множественных значений в одной ячейке).

❌ Плохо (не 1NF):

id  name  phones
1   Иван  +123, +456, +789

✔️ Хорошо (1NF):

id   name   phone
1    Иван   +123
1    Иван   +456
1    Иван   +789

Разделили атрибут phones на отдельные строки — теперь каждая ячейка содержит одно значение.

2️⃣ Вторая нормальная форма (2NF)
Требование:
1. Таблица уже в 1NF.
2. Все неключевые атрибуты зависят только от полного первичного ключа (если ключ составной).

❌ Плохо (не 2NF):

student_id  course_id course_name
1           SQL101    Основы SQL
2           SQL101    Основы SQL

course_name зависит только от course_id, а не от полного составного ключа (student_id, course_id).

✔️ Хорошо (2NF):
Разбиваем таблицу на две таблицы содержащие логически единую информацию.

Таблица студентов и курсов:

student_id   course_id
1            SQL101
2            SQL101

Таблица курсов:

course_id        course_name
SQL101           Основы SQL

Теперь course_name зависит только от course_id, а не от полного ключа.

3️⃣ Третья нормальная форма (3NF)
Требование:
1. Таблица уже в 2NF.
2. Нет транзитивных зависимостей (атрибуты зависят только от первичного ключа, а не друг от друга).

❌ Плохо (не 3NF):

employee_id   dep_id   dep_name
1             10       IT
2             20       HR

dep_name зависит от dep_id, а не от первичного ключа employee_id.

✔️ Хорошо (3NF):
Разделяем таблицы логически делящие информацию.

Таблица сотрудников:

employee_id   dep_id
1             10
2             20

Таблица отделов:

dep_id   dep_name
10       IT
20       HR

Теперь dep_name хранится отдельно и зависит только от dep_id.

💡Вывод
Нормализация помогает избежать дублирования данных, уменьшить избыточность и повысить целостность БД.
1NF — атомарные значения.
2NF — зависимость только от полного первичного ключа.
3NF — отсутствие транзитивных зависимостей.

#Реляционные_базы_данных #Оптимизация_SQL #Нормализация

📱 Подписаться на канал | Курс автора по SQL DDL

📊 Продвинутые нормальные формы в реляционных базах данных

Если 1NF, 2NF и 3NF (разобрали в этом посте) помогают избежать дублирования данных и аномалий обновления, то 4NF, 5NF и 6NF направлены на устранение более сложных зависимостей и оптимизацию структуры данных.

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

4️⃣ Четвёртая нормальная форма (4NF) — применяется для устранения многозначных зависимостей, где столбец с первичным ключом имеет связь «один-ко-многим» со столбцом, который не является ключом. Эта форма устраняет некорректные отношения «многие-ко-многим».
Требование:
1. Таблица уже в 3NF
2. Нет многозначных зависимостей

❌ Плохо (не 4NF):
Представим, что один преподаватель может вести несколько предметов и работать в нескольких аудиториях.

teacher_id   subject      classroom
1            SQL          101
1            Python       101
1            SQL          102
1            Python       102

Преподаватель 1 ведёт несколько предметов и работает в нескольких аудиториях. Предмет и аудитория независимы, но при этом создают дублирование данных.

✔️ Хорошо (4NF):
Разбиваем таблицу на две:

Преподаватель — Предмет:

teacher_id   subject
1            SQL
1            Python

Преподаватель — Аудитория:

teacher_id   classroom
1            101
1            102

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

5️⃣ Пятая нормальная форма (5NF) — разделяет таблицы на более малые таблицы для устранения избыточности данных. Разбиение идёт до тех пор, пока нельзя будет воссоздать оригинальную таблицу путём объединения малых таблиц.
Требование:
1. Таблица уже в 4NF.
2. Нет соединительных (join) зависимостей — данные не должны разлагаться на более мелкие части без потери информации.

❌ Плохо (не 5NF):

project_id  employee_id role
1           100         Разработчик
1           101         Тестировщик
2           100         Разработчик
2           102         Аналитик

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

✔️ Хорошо (5NF):
Разделяем на три таблицы

Проекты:

project_id
1
2

Сотрудники:

employee_id
100
101
102

Связь проектов и сотрудников через роли в новой стаблице:

project_id employee_id role
1          100         Разработчик
1          101         Тестировщик
2          100         Разработчик
2          102         Аналитик

Теперь данные связаны через таблицы без лишнего дублирования.

6️⃣ Шестая нормальная форма (6NF) — Каждое ограничение в связях между таблицами должно зависеть только от ключей и доменов, т.е допустимых значений столбцов. 6NF предотвращает недопустимые данные, устанавливая ограничения на уровне отношений, а не отдельных таблиц или столбцов.
Требование:
1. Таблица уже в 5NF.
2. Разделение на атомарные отношения (каждое изменение данных должно касаться только одной сущности).

Важно: 6NF встречается редко и полезна в хранилищах данных и системах, требующих историчности данных. По своему опыту скажу, что она скорее теоретическая и направлена на устранение всех возможных избыточностей.

Пример:
Допустим, нам нужно хранить историю изменения зарплаты сотрудников.
❌ Плохо (не 6NF):

employee_id salary dep  date
1           5000   IT   2024-01-01
1           5500   IT   2024-03-01
1           5500   HR   2024-05-01

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

✔️Хорошо (6NF):
Разделяем данные на независимые отношения:

История зарплаты

employee_id salary   date
1           5000     2024-01-01
1           5500     2024-03-01

История департаментов

employee_id dep  date
1           IT   2024-01-01
1           HR   2024-05-01

Теперь изменения хранятся отдельно и позволяют вести историю без дублирования.

💡Вывод
Продвинутые нормальные формы помогают устранить избыточные зависимости и дублирование, улучшая целостность данных.
4NF – исключает многозначные зависимости.
5NF – убирает дубли через разбиение отношений.
6NF – делает данные полностью атомарными, что полезно для историчности.

#Реляционные_базы_данных #Оптимизация_SQL #Нормализация

📱 Подписаться на канал | Курс автора по SQL DDL

🛠 Базовые SQL-команды для уровня Junior

Джуниор в SQL – это начинающий специалист, который работает с базами данных и владеет основами языка SQL. Обычно он занимает позицию Junior Database Developer, Junior Data Analyst, Junior SQL Developer или Junior DBA (Database Administrator). Также знание SQL крайне актуально для системных аналитиков.

Если ты только начинаешь свой путь в SQL, вот основные команды, которые нужно знать!

1️⃣ SELECT – выбор данных. Гайд тут
Извлекает данные из таблицы.

SELECT name, age FROM users;
-- Выбирает столбцы name и age из таблицы users

2️⃣ INSERT – добавление данных. Подробнее тут
Добавляет новые строки в таблицу.

INSERT INTO users (name, age) VALUES ('Алексей', 25);
-- Вставляет строку с именем "Алексей" и возрастом 25 в таблицу users

3️⃣ UPDATE – обновление данных. Подробнее тут
Изменяет существующие данные.

UPDATE users SET age = 26 WHERE name = 'Алексей';
-- Обновляет возраст пользователя "Алексей" на 26

4️⃣ DELETE – удаление данных. За подробностями сюда
Удаляет строки из таблицы.

DELETE FROM users WHERE age < 18;
-- Удаляет всех пользователей младше 18 лет

5️⃣ WHERE – фильтрация данных. Подробнее тут
Используется для выборки данных по условиям.

SELECT * FROM users WHERE age > 20;
-- Выбирает всех пользователей старше 20 лет

6️⃣ ORDER BY – сортировка данных
Сортирует результаты запроса.

SELECT * FROM users ORDER BY age DESC;
-- Выбирает всех пользователей и сортирует их по возрасту по убыванию

7️⃣ GROUP BY – группировка данных. Разбирали тут
Объединяет строки с одинаковыми значениями в одну группу.

SELECT age, COUNT(*) FROM users GROUP BY age;
-- Считает, сколько пользователей каждого возраста

8️⃣ JOIN – объединение таблиц. Объяснял в этом посте
Позволяет соединять данные из нескольких таблиц.

SELECT users.name, orders.total 
FROM users 
JOIN orders ON users.id = orders.user_id;
-- Выбирает имена пользователей и суммы их заказов

Освоив эти команды, ты сможешь работать с БД на базовом уровне. Таже рекомендую изучить следующие материалы размещенные на канале внешний ключ, первичный ключ, основы реляционной модели данных тут, типы данных.

#Карьера_SQL, #Основы_SQL

📱 Подписаться на канал | Курс автора по SQL DDL

📊 ORDER BY — как управлять порядком строк в результатах запроса?

Когда ты выполняешь SELECT, порядок строк не гарантирован. База данных возвращает строки в произвольном порядке, зависящем от её внутренней реализации. Если тебе нужен определённый порядок — используется ORDER BY.

Что такое ORDER BY?

ORDER BY — это инструкция, которая упорядочивает строки результата по значениям одного или нескольких столбцов.
Без него результаты могут быть хаотичными — особенно в больших таблицах.

Ключевые особенности:

1️⃣ Порядок по умолчанию — ASC (по возрастанию)
От меньшего к большему: 0 → 1 → 2, A → B → C

2️⃣ DESC — обратный порядок (по убыванию)
От большего к меньшему: Z → Y → X, 1000 → 100 → 10

3️⃣ Сортировка по нескольким столбцам
Если значения в первом столбце равны — используется второй, и так далее.

4️⃣ Можно сортировать по выражениям, функциям и псевдонимам

5️⃣ Можно использовать номер столбца в списке SELECT
Хотя это не рекомендуется для читаемости, но часто встречается в старом коде.

Синтаксис:

SELECT столбец1, столбец2, ...
FROM таблица
ORDER BY столбец1 [ASC | DESC], столбец2 [ASC | DESC], ...;

Примеры:

-- 1. По возрасту по возрастанию
SELECT name, age FROM users ORDER BY age;

-- 2. По возрасту по убыванию
SELECT name, age FROM users ORDER BY age DESC;

-- 3. По городу, затем по возрасту
SELECT name, city, age FROM users ORDER BY city ASC, age DESC;

-- 4. По длине имени
SELECT name FROM users ORDER BY LENGTH(name);

Важно:
1. ORDER BY всегда идёт в конце запроса, перед LIMIT, OFFSET, FETCH.
2. Он не изменяет порядок в самой таблице — только в результате запроса.
3. Если ты не укажешь ORDER BY, то повторные запросы могут возвращать строки в разном порядке.

Зачем нужен ORDER BY на практике?
✔️ Построение отчетов (например, по дате)
✔️ Вывод самых новых или самых старых записей
✔️ Получение топ-N результатов (вместе с LIMIT)
✔️ Для пользователей — упорядоченный, понятный вывод
✔️ Для анализа и сравнения значений

#Операторы_и_работа_с_данными #ORDER_BY

📱 Подписаться на канал | Курс автора по SQL DDL

🧭 Порядок выполнения SQL-запроса: не такой, как кажется на первый взгляд!

Многие уверены, что SQL исполняется сверху вниз, потому что мы так его пишем: SELECT, потом FROM, потом WHERE и так далее.

Но СУБД (система управления базами данных) читает и исполняет его иначе. Понимание этого является ключом к уверенной работе с SQL и написанию эффективных запросов. К слову этот вопрос является достаточно распространенным каверзным вопросом на собесах.

Реальный порядок выполнения SQL-запроса:

1️⃣ FROM – сначала выбирается таблица (или несколько, если есть JOIN).
На этом этапе данные подтягиваются из таблиц, применяются условия соединения (ON), создаются временные таблицы для дальнейших шагов.
2️⃣ WHERE – фильтруются строки на уровне «сырых» данных.
Удаляются строки, которые не соответствуют критериям. Этот шаг нельзя использовать с агрегатными функциями (вроде AVG, SUM) — они ещё не посчитаны.
3️⃣ GROUP BY – группировка строк по одному или нескольким столбцам.
На этом шаге создаются группы для последующего анализа: например, по отделам, категориям, датам.
4️⃣ HAVING – фильтрация уже сгруппированных данных.
Применяется после GROUP BY, работает с агрегатами. Например: показать только те отделы, где средняя зарплата выше определённого порога.
5️⃣ SELECT – выбираются нужные столбцы, считаются выражения и функции.
Только теперь в дело вступают агрегаты (AVG, COUNT, MAX) и алиасы (AS name). Здесь формируется окончательный «вид» результата.
6️⃣ DISTINCT (если есть) – удаляются дубликаты.
Работает после SELECT. Используется для уникальных значений, но может быть затратен по ресурсам.
7️⃣ ORDER BY – сортировка итогового набора данных.
Можно сортировать по столбцу, выражению или порядковому номеру из SELECT.
8️⃣ LIMIT / OFFSET / FETCH – ограничение количества строк.
Используется в пагинации, топ-результатах и тестировании.

Пример запроса:

SELECT department, AVG(salary)
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING AVG(salary) > 5000
ORDER BY AVG(salary) DESC
LIMIT 3;

Что делает запрос по шагам (порядок обработки СУБД):
1️⃣ FROM employees — загружает всех сотрудников
2️⃣ WHERE status = 'active' — оставляет только активных
3️⃣ GROUP BY department — группирует по департаментам
4️⃣ HAVING AVG(salary) > 5000 — отбирает департаменты с хорошей средней
5️⃣ SELECT department, AVG(salary) — выбирает поля
6️⃣ ORDER BY AVG(salary) DESC — сортирует по средней зарплате
7️⃣ LIMIT 3 — берёт только 3 топовых департамента

Совет:
Если запрос работает не так, как ты ожидал — проверь его на соответствие реальному порядку выполнения. Это поможет найти ошибки и понять логику SQL на глубоком уровне.

#Реляционные_базы_данных #СУБД #Оптимизация_SQL

📱 Подписаться на канал | Курс автора по SQL DDL

🏷 Псевдонимы в SQL: зачем нужны AS и как правильно их использовать?

В SQL псевдонимы (alias) — это временные имена, которые можно присвоит столбцам или таблицам прямо в запросе. Они не меняют структуру базы данных, но делают запросы понятнее, короче и логичнее.

🔧 Синтаксис:

SELECT выражение AS псевдоним
FROM таблица AS псевдоним;

Слово AS можно опустить — но явное лучше неявного.

Пример 1: псевдоним для столбца

SELECT name, salary * 1.15 AS adjusted_salary
FROM employees;

adjusted_salary — это новое имя для результата выражения salary * 1.15.

Пример 2: псевдонимы в соединениях таблиц

SELECT e.name, d.name AS department
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.id;

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

Что происходит в этом запросе?
1. employees AS e — Мы даём псевдоним e таблице employees. Теперь к её столбцам можно обращаться коротко: e. name, e. dept_id.
2. departments AS d — Аналогично, таблице departments присваиваем алиас d.
3. e. name и d. name — Здесь мы явно указываем, откуда брать каждый name, чтобы избежать путаницы.
4. d. name AS department — Мы даём столбцу более говорящий псевдоним, в результате запроса он будет отображаться как department, а не просто name.

Зачем нужны псевдонимы?
✔️ Читабельность: особенно полезно в сложных выражениях и подзапросах.
✔️ Разрешение конфликтов: если в нескольких таблицах одинаковые названия столбцов.
✔️ Переименование данных в отчётах: чтобы результат выглядел понятнее пользователю.

⚠️ Важно помнить:
Псевдонимы столбцов нельзя использовать в WHERE, потому что WHERE выполняется до SELECT.
Зато можно использовать в ORDER BY и HAVING. Пост про очередность выполнения тут

Пример из практики: расчёт налога и итоговой зарплаты
Задача: рассчитать 13% налог с зарплаты и итоговую сумму к выплате, вывести имя сотрудника, оклад, налог и сумму "на руки".

SELECT 
    name AS employee_name,
    salary AS base_salary,
    salary * 0.13 AS tax_amount,
    salary * 0.87 AS net_salary
FROM employees;

📎 Что происходит:
employee_name — читаемое имя вместо name
base_salary — для акцента на том, что это до вычета
tax_amount — отдельный расчёт налога
net_salary — итоговая сумма после удержания налога

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

📘 Заключение:
Псевдонимы — это не просто «красивости», а важный инструмент в арсенале каждого, кто работает с SQL. Они делают код читаемым, поддерживаемым и профессиональным.

#Операторы_и_работа_с_данными #Alias #Оптимизация_SQL

📱 Подписаться на канал | Курс автора по SQL DDL

🔁 Что такое транзакция?

Транзакция — это логически связанная группа SQL-операций, которые выполняются как единое целое. Она либо выполняется полностью, либо откатывается полностью, если возникла ошибка.

Если посмотреть с точки зрения машинной философии механизм транзакции можно выразить принципом «всё или ничего»

Ключевые команды:
1. BEGIN / START TRANSACTION – начало транзакции
2. COMMIT – подтверждение изменений
3. ROLLBACK – откат всех изменений с начала транзакции

💡 Почему это важно?
Представь, что ты переводишь деньги между счетами:

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

Если после первого запроса произойдёт сбой, то деньги «пропадут».

А если всё это завернуть в транзакцию — либо оба запроса сработают, либо ни один:

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

Если что-то пойдёт не так, можно сделать откат:

ROLLBACK;

Полезно знать:
Транзакции подчиняются принципам ACID:
1. Atomicity — атомарность
2. Consistency — согласованность
3. Isolation — изолированность
4. Durability — надёжность

Заключение
Транзакции — это не просто дополнительная опция. Это механизм, который:
1. Защищает данные от потерь и ошибок;
2. Позволяет писать надёжные и предсказуемые бизнес-логики;
3. Обеспечивает правильность даже в условиях высокой нагрузки.

Делись с командой — это основа безопасной работы с данными.

#Операторы_и_работа_с_данными #Транзакция #Оптимизация_SQL

📱 Подписаться на канал | Курс автора по SQL DDL

🔔 Триггеры в SQL: автоматические действия в базе данных

Что такое триггер?
Триггер (trigger) — это специальная программа внутри СУБД, которая автоматически выполняет определённый SQL-код при наступлении события в таблице. Иными словами, триггер — это реакция базы данных на изменение данных.

Какие события могут активировать триггер?
Триггеры можно настроить на 3 типа событий:
1. INSERT — Добавление новой строки
2. UPDATE — Изменение существующей строки
3. DELETE — Удаление строки

Когда срабатывает триггер?
Есть два режима срабатывания:
1. BEFORE — До того, как операция будет завершена
2. AFTER — После выполнения операции

Это позволяет:
С BEFORE — предотвратить нежелательные действия (например, отклонить запись с ошибкой).
С AFTER — реагировать на уже выполненные изменения (например, логировать, уведомлять, синхронизировать).

Зачем нужны триггеры?
Примеры использования:
1. Автоматический лог изменений: записывать, кто и когда внёс изменения.
2. Синхронизация таблиц: при удалении строки из одной таблицы, удалить её из другой.
3. Уведомления: запускать действия при изменении статуса заказа.
4. Бизнес-правила: блокировать операции, нарушающие правила компании.

🛠 Синтаксис (на примере MySQL):

CREATE TRIGGER имя_триггера
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON имя_таблицы
FOR EACH ROW
BEGIN
   -- SQL-действия*
END;
FOR EACH ROW означает, что триггер сработает для каждой строки, к которой применимо событие.

* - Внутри BEGIN ... END пишем обычный SQL-код.

Пример 1: логируем добавление сотрудника
Допустим, у нас есть таблица employees и таблица logs для записей о событиях.

CREATE TRIGGER log_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO logs(message)
  VALUES (CONCAT('Добавлен сотрудник: ', NEW.name));
END;

🔍 Объяснение:
AFTER INSERT — триггер сработает после добавления нового сотрудника.
NEW. name — обращение к значению поля name в новой строке (та, что только что вставлена).
Вставляется строка в таблицу logs.

Пример 2: предотвратить удаление важной записи

CREATE TRIGGER protect_admin_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
  IF OLD.role = 'admin' THEN
    SIGNAL SQLSTATE '45000' 
      SET MESSAGE_TEXT = 'Нельзя удалить администратора!';
  END IF;
END;

🔍 Объяснение:
BEFORE DELETE — триггер срабатывает до удаления.
OLD. role — значение удаляемой строки.
Если роль admin, выбрасывается ошибка, и удаление не происходит.

Разница между NEW и OLD
В INSERT только NEW — новая строка
В DELETE только OLD — удаляемая строка
В UPDATE оба: OLD — старая, NEW — новая

⚠️ Важные замечания
1. Один триггер = одно событие + одно время (BEFORE/AFTER).
2. Если нужно реагировать на разные события, создавайте несколько триггеров.
3. Не стоит делать сложную логику внутри триггера — это замедлит работу.
4. Циклические триггеры (когда один вызывает другой) могут привести к ошибкам или зависанию — избегайте этого.

📚 Резюме
CREATE TRIGGER — создание триггера
BEFORE / AFTER — время срабатывания
INSERT, UPDATE, DELETE — тип события
NEW, OLD — доступ к значениям до/после
FOR EACH ROW — выполняется для каждой строки

📌 Сохрани этот пост как шпаргалку!
🔁 Делись с коллегами — триггеры полезны, но ими часто пренебрегают.

#Операторы_и_работа_с_данными #Trigger

📱 Подписаться на канал | Курс автора по SQL DDL

🧬 INSERT через SELECT: массовый перенос данных

INSERT INTO ... SELECT — это оператор массовой вставки. Вместо ручного ввода значений через VALUES, мы берем данные из существующей таблицы (или нескольких) и вставляем их в другую таблицу.

Это — основа для:
1. Копирования данных между таблицами
2. Архивирования записей
3. Миграции и трансформации данных
5. Наполнения витрин и отчётов
6. Подготовки временных таблиц в ETL-процессах

Синтаксис:

INSERT INTO target_table (col1, col2)
SELECT col_a, col_b
FROM source_table
WHERE condition;

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

⚙️ Как работает?
1. Сначала выполняется SELECT — он формирует временный набор данных.
2. Затем INSERT INTO вставляет эти данные в указанную таблицу.
3. Всё это работает как единая транзакция.

Важно: порядок и типы колонок должны совпадать или быть совместимыми.

🔒 Строгие правила:
1. Количество колонок в INSERT INTO и SELECT должно совпадать.
2. Типы данных должны быть совместимыми: нельзя вставить TEXT в INT.
3. Если в целевой таблице есть NOT NULL, UNIQUE, FOREIGN KEY или DEFAULT, они тоже применяются.
4. Если есть автоинкремент (AUTO_INCREMENT), значение можно не указывать, оно подставится само.

⚠️ Частые ошибки:
1. Забыли WHERE — и вставили весь набор, включая ненужное.
2. Не проверили SELECT — и получили не те данные.
3. Не учли дубликаты — и получили ошибки или лишние строки.
4. Совпали ID-шники — и нарушили целостность данных.

🧠 SQL-инсайты:
1. Можно использовать JOIN в SELECT — перенос с объединением.
2. Можно использовать CASE, чтобы пересчитывать данные на лету.
3. LIMIT полезен при вставке порциями.
4.Поддерживается во всех основных СУБД (MySQL, PostgreSQL, SQL Server, Oracle).

ПРИМЕР 1: копирование всех данных

INSERT INTO employees_backup
SELECT * FROM employees;

Что здесь происходит:
1. SELECT * FROM employees — выбираются все строки и все столбцы из таблицы employees.
2. INSERT INTO employees_backup — полученные строки вставляются в таблицу employees_backup.

❗️ Таблицы employees и employees_backup должны иметь одинаковую структуру — одинаковые столбцы в том же порядке и с совместимыми типами данных.

ПРИМЕР 2: выборочная вставка (фильтрация)

INSERT INTO orders_archive
SELECT * FROM orders
WHERE order_date < CURDATE() - INTERVAL 1 YEAR;

Что происходит:
1. SELECT * FROM orders — берутся все заказы.
2. WHERE order_date < CURDATE() - INTERVAL 1 YEAR — фильтрация: только те заказы, которым больше года.
3. Результат вставляется в orders_archive.

Это пример архивации старых данных — очень частая задача в реальной работе с базами.

ПРИМЕР 3: агрегация и вставка

INSERT INTO sales_summary (category, total_sales)
SELECT category, SUM(amount)
FROM sales
GROUP BY category;

Что делает запрос:
1. SELECT category, SUM(amount) — для каждой категории считается общая сумма продаж.
2. GROUP BY category — группирует строки по категориям.
3. INSERT INTO sales_summary (...) — вставляет результат в таблицу итогов.

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

ПРИМЕР 4: выборка с JOIN

INSERT INTO newsletter_subscribers (user_id, user_name)
SELECT u.id, u.name
FROM users AS u
JOIN subnoscriptions AS s ON u.id = s.user_id
WHERE s.active = 1;

Что происходит по шагам:
JOIN объединяет таблицы users и subnoscriptions по user_id.
WHERE s.active = 1 — выбирает только активных подписчиков.
SELECT u. id, u. name — выбираем только нужные поля.
INSERT INTO newsletter_subscribers (...) — вставляем их в целевую таблицу.

Это пример массового импорта данных в отдельный список.

💡Вывод
INSERT INTO ... SELECT — это не просто способ скопировать данные, а инструмент для построения гибких потоков данных между таблицами. Это основа работы с отчетами, витринами, архивами и резервным копированием.

🔁 Делись с коллегами — особенно с теми, кто до сих пор вставляет данные вручную через VALUES

#Операторы_и_работа_с_данными

📱 Подписаться на канал | Курс автора по SQL DDL

🔗 INNER JOIN в SQL — объединяем таблицы

В реальных базах данных информация часто хранится в нескольких связанных таблицах. Чтобы собрать все нужные данные в один результат — используют соединения (JOIN). Самый базовый и часто используемый тип соединения — INNER JOIN.

Что такое INNER JOIN?
INNER JOIN возвращает только те строки, где есть совпадения в обеих таблицах по заданному условию (обычно по ключам).

То есть: если связь не найдена — строка не попадёт в результат.

Синтаксис:

SELECT t1.column, t2.column
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t1.id = t2.foreign_id;

ПРИМЕР:
Структура и данные исходных таблиц:
Таблица employees:

id   name     dept_id
1    Alice    10
2    Bob      20
3    Charlie  NULL
4    Diana    30

Таблица departments:

id    name
10    HR
20    Engineering
30    Marketing
40    Sales

Пример запроса:

SELECT e.name, d.name AS department
FROM employees AS e
INNER JOIN departments AS d
ON e.dept_id = d.id;

Что делает запрос:
1. Обходит сотрудников (employees)
2. Находит отделы (departments) по полю dept_id
3. Возвращает только те строки, где dept_id найден в departments. id

Результат:

name   department
Alice  HR
Bob    Engineering
Diana  Marketing

Charlie не попал в результат, потому что у него dept_id = NULL, а значит нет совпадения с таблицей departments.

🧠 Когда использовать INNER JOIN?
1. Когда нужно объединить связанные таблицы: сотрудники → отделы, заказы → клиенты, товары → категории и т.д.
2. Когда важны только те данные, у которых есть соответствие в обеих таблицах.
3. В отчетах, аналитике и выборках на основе бизнес-связей.

Вывод:
INNER JOIN — это основной способ объединить данные из разных таблиц, сохраняя логическую связь между ними. Если нет совпадения — строки просто игнорируются. Небольшой обзор на все виды Join тут

#Операторы_и_работа_с_данными #INNER_LOIN

📱 Подписаться на канал | Курс автора по SQL DDL

Удаление данных в РБД SQL: общая заметка про DML и DLL

Когда речь заходит об удалении данных в реляционной базе, важно понимать: в SQL для этого существуют различные типы команд, которые работают по-разному, некоторые из них обратимые и безопасные, а некоторые подобны экстреминатусу в Warhammer40к — уничтожат всё без остатка.

Уровень DML - можно удалить данные очень безопасно. DML-команды изменяют именно данные, но не трогают структуру таблиц:

DELETE FROM таблица1
WHERE признак = 'значение';

Особенности:
1. Можно удалить часть данных с фильтрацией (WHERE).
2. Структура таблицы остаётся без изменений.
3. Можно откатить операцию с помощью транзакции (ROLLBACK). Пост про транзакции и откат изменений тут
4. Может быть медленным на больших объёмах (поскольку строки удаляются одна за одной с фиксацией).

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

TRUNCATE таблица1;

1. Очищает таблицу полностью: удаляются все строки без условия, сбрасываются счётчики (AUTO_INCREMENT), освобождаются ресурсы.
2. Быстрее, чем DELETE, так как нет построчной обработки.
3. В большинстве СУБД невозможно откатить TRUNCATE через ROLLBACK.
4. В лог транзакций (transaction log) записывается не каждая удалённая строка, а сам факт очистки.

DROP таблица1;

1. Полностью удаляет таблицу из базы, вместе со всеми данными и схемой столбцов.
2. Требует осторожности: восстановление возможно только из бэкапа.

Кратко:
DELETE — DML, удаляет строки, фильтрация доступна, построчная обработка
TRUNCATE — DDL, очищает всю таблицу, нет фильтрации, откат изменений обычно недоступен
DROP — DDL, удаляет таблицу полностью, восстановление таблицы возможно только при наличии бэкапа

На что обратить внимание:
1. Если нужно удалить часть данных — выбирай DELETE.
2. Если нужно быстро очистить всю таблицу перед новой загрузкой данных TRUNCATE.
3. Если таблица должна быть предана полному забвению — юзай DROP.

#Операторы_и_работа_с_данными #DELETE #TRUNCATE #DROP

📱 Подписаться на канал | Курс автора по SQL DDL

🧩 Что такое ER-диаграммы и зачем они нужны?

В этом посте немного поработаем, с любимой темой автора — архитектурой. Когда вы проектируете базу данных, важно понять не только, какие таблицы будут, но и что они означают и как связаны. Для этого используют ER-модель (Entity-Relationship Model) — инструмент, созданный Питером Ченом в 1976 году.

Теоретическая база
ER-модель — это абстракция предметной области, которая позволяет выразить:
2. Сущности (Entity) — уникальные объекты (например, Пользователь, Товар, Заказ)
3. Атрибуты (Attributes) — свойства этих объектов (например, имя, цена, дата)
4. Связи (Relationships) — логические ассоциации между сущностями (например, "Пользователь оформляет Заказ")
Пост про реляционную модель

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

Основные понятия подробно:
1. Сущность (Entity). Это классы объектов реального мира, которые мы хотим сохранить в базе. Например, Student, Course, Invoice.
2. Слабая сущность. Не имеет собственного первичного ключа. Например, Payment может зависеть от Invoice.
3. Атрибут (Attribute). Конкретная характеристика сущности. Могут быть: Простыми (name, price), составными (full_name → first_name + last_name), многозначными (телефоны), производными (возраст можно вычислить по дате рождения)
4.Первичный ключ (Primary Key). Уникальный идентификатор сущности — обязателен. Посты про первичный и внешний ключи
5.Связь (Relationship) определяет, как сущности взаимодействуют. Может быть:
— бинарной (между двумя сущностями)
— тернарной (между тремя)
— рекурсивной (сущность связана сама с собой, например, Employee → Manager)
6, Кардинальность. Указывает, сколько экземпляров одной сущности может быть связано с другой. Форматы: 1:1, 1:N, M:N.
7, Обязательность (Optionality). Определяет, обязательно ли участие в связи. Например, заказ может иметь доставку, а может не иметь.

🔗 Типы связей в деталях
1, Один к одному (1:1) — Реализуется через внешний ключ с ограничением уникальности.
Пример: каждый человек имеет один паспорт.
2, Один ко многим (1:N) — Внешний ключ указывается на стороне "многих".
Пример: один клиент может сделать несколько заказов.
3, Многие ко многим (M:N) — Требует связующей таблицы.
Пример: студент может записаться на много курсов, курс — иметь много студентов.

Пример: интернет-магазин:
Фрагмент ER-диаграммы (в текстовом виде):

Customer ─< Order >─ Product
    |          |        |
    id     order_id     id
    name   order_date   name
    email  total        price

Сущности:
Customer (атрибуты customer_id, name, email)
Order (атрибуты order_id, order_date, total)
Product (атрибуты product_id, name, price)

Связи:

Customer ─────< Order

1. Это связь "один ко многим" (1:N).
2. Один покупатель (Customer) может сделать много заказов, но каждый заказ принадлежит только одному покупателю.
3. На практике в таблице Order будет внешний ключ customer_id. Чтобы реализовать такую связь в базе данных, в таблице заказов (Order) нужно указать, кто из клиентов сделал заказ.

Order >───── Product

1. Это связь "многие ко многим" (M:N).
2. Один заказ может включать много товаров, и один товар может быть в многих заказах.
3. Для реализации такой связи создаётся промежуточная таблица (не показана здесь, но подразумевается) — например, OrderItem:

OrderItem (
  order_id INT,    -- внешний ключ к Order
  product_id INT,  -- внешний ключ к Product
  quantity INT     -- количество единиц товара
)

Эта таблица реализует связь и дополнительные данные (например, количество).

Советы:
1. Используйте ER-диаграмму до нормализации — это первый логический шаг. Пост про нормализацию данных
2. Хорошие инструменты: dbdiagram.io, draw.io, Lucidchart
3. Храните диаграмму рядом с документацией — она пригодится через год, когда все забудут, зачем нужно некое поле.

ER-модель — это язык проектировщика баз данных.
Научитесь его читать и писать — и ваши БД будут чёткими, масштабируемыми и поддерживаемыми.

#Реляционные_базы_данных #Архитектура_РБД

📱 Подписаться на канал | Курс автора по SQL DDL

Ежемесячный дайджест — апрель 2025

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

🔁 Написание и исполнение SQL-запросов

ORDER BY — как управлять порядком строк в результатах запроса?
Объяснение сортировки данных по столбцам и выражениям: по возрастанию, убыванию и с учётом NULL.

Псевдонимы в SQL: зачем нужны AS и как правильно их использовать?
Упрощай чтение запросов и избегай конфликтов с помощью AS для столбцов и таблиц.

INSERT через SELECT: массовый перенос данных
Приёмы копирования данных между таблицами — быстро, надёжно, масштабируемо.

INNER JOIN в SQL — объединяем таблицы
Как связать таблицы по ключам и извлечь данные из нескольких источников сразу.

Удаление данных в РБД SQL: общая заметка про DML и DLL
Чем отличается DELETE, TRUNCATE и DROP? Разбираем на практике и по теории.

⚙️ Механизмы обработки и автоматизации

Что такое транзакция?
Твёрдая основа надёжных операций: свойства ACID и как СУБД следит за целостностью данных.

Триггеры в SQL: автоматические действия в базе данных
Как запускать автоматические действия при вставке, обновлении или удалении данных.

Порядок выполнения SQL-запроса: не такой, как кажется на первый взгляд!
SQL читается не сверху вниз: узнай, с чего действительно начинается выполнение запроса.

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

Что такое ER-диаграммы и зачем они нужны?
Основы моделирования: сущности, связи и атрибуты в текстовом формате и с пояснениями.


🏆 Популярное за месяц

Самый обсуждаемый пост:
Удаление данных в РБД SQL: общая заметка про DML и DLL

Самый просматриваемый пост:
Порядок выполнения SQL-запроса: не такой, как кажется на первый взгляд!

👾В мае вас ждёт:
— Бесплатный мини-курс по нормализации данных в РБД от автора канала на Stepik
— Разбор реального кейса моделирования БД
— Ещё больше практики и SQL-хаков
— Еще в последних постах я стал делать мемы, так что мемы тоже будут))

Напиши, какой пост был для тебя самым полезным — или чего не хватает в канале?
Спасибо, что читаешь!

#Дайджест

📱 Подписаться на канал | Курс автора по SQL DDL

🪟 Представления (Views) в SQL — базовая теория

Представление — это виртуальная таблица, которая формируется на основе результата SQL-запроса. Представления не содержат собственных данных, а при обращении к ним СУБД повторно исполняет сохранённый запрос, чтобы сгенерировать актуальные результаты.

Теория: как это работает?
1. Представление определяется через конструкцию CREATE VIEW, внутри которой задаётся любой корректный SELECT-запрос.
2. При использовании представления в других запросах оно обрабатывается СУБД как подзапрос, «разворачиваясь» в изначальный SQL.
3. Представления — это не таблицы, они не хранят данные, а только логику запроса (за исключением материализованных представлений, которые имеют такую возможность)
4. Представления можно использовать в SELECT, JOIN, WHERE, ORDER BY и других операторах как обычную таблицу.
5. Если данные в исходных таблицах меняются — результаты представления тоже изменятся.
6. Некоторые представления нельзя обновлять напрямую, особенно если они содержат JOIN, GROUP BY, DISTINCT, подзапросы и агрегаты.

Пример 1: Обычные (логические) представления

CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE status = 'active';

Что происходит:
1. CREATE VIEW active_customers AS — создаёт представление с именем active_customers.
2. Внутри — обычный SELECT-запрос: выбираем id, name, email из таблицы customers, но только те строки, где status = 'active'. Представление не копирует данные из customers, а сохраняет структуру запроса. Когда вы запрашиваете SELECT * FROM active_customers, СУБД как бы вставляет туда оригинальный SELECT.

Использование представления

SELECT * FROM active_customers WHERE email LIKE '%@gmail. com';

Что происходит:
1. Вы запрашиваете все поля из представления active_customers, но фильтруете их по email.
2. На самом деле СУБД превращает этот запрос во что-то вроде:

SELECT id, name, email
FROM customers
WHERE status = 'active' AND email LIKE '%@gmail.com';

Представления можно перезаписывать.
Обновление представления — означает изменение данных, которые оно отображает.

CREATE OR REPLACE VIEW active_customers AS
SELECT id, name, email, created_at
FROM customers
WHERE status = 'active';

Что происходит:
1. Ключевая фраза CREATE OR REPLACE означает: если представление уже существует — оно будет перезаписано новым определением.
2. К предыдущему представлению добавляется колонка created_at.

Пример 2: с объединением таблиц (JOIN)

CREATE VIEW employee_departments AS
SELECT e.id, e.name, d.name AS department
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.id;

Что происходит:
1.Создаётся представление employee_departments, в котором:
e — псевдоним для таблицы employees
d — псевдоним для таблицы departments
2. Мы соединяем сотрудников с отделами по dept_id.
3. Выводим:
e. id, e. name — данные сотрудника
d. name AS department — название отдела (переименовываем колонку name из таблицы departments в department, чтобы не было конфликта имён)

Пример использования:

SELECT * FROM employee_departments WHERE department = 'IT';

Получим выгрузку из объединенных таблиц.

Материализованные представления (команда создания CREATE MATERIALIZED VIEW)
Материализованные представления (materialized views) поддерживаются основными СУБД. Они отличаются от обычных тем, что хранят результат запроса физически, а не вычисляют его каждый раз при обращении.

Подходящие случаи для использования представлений:
1. Создание стандартных витрин для аналитики (упрощение запросов)
2. Подготовка отчётности с агрегацией (Повторное использование логики)
3. Маскирование конфиденциальных столбцов (например, зарплаты)
4. Предоставление пользователю ограниченного доступа к БД
5, Снижение дублирования,
представления позволяют вынести повторяющиеся фрагменты запросов в отдельную конструкцию, улучшая читаемость и поддержку кода.

Вывод:
Представления в SQL — это способ упростить работу с базой данных, сделать запросы чище, безопаснее и переиспользуемыми. Особенно актуальны в больших проектах с командной разработкой и аналитикой.

#Views

📱 Подписаться на канал | Курс автора по SQL DDL

🪟Оконные функции в SQL — как они работают и где применяются?

Когда обычный GROUP BY не даёт нужной гибкости, на помощь приходят оконные функции.

Оконные функции (Window Functions) — это инструмент SQL, который позволяет выполнять операции, не изменяя структуру набора данных, а сохраняя доступ ко всем строкам в результирующем наборе. Они часто используются для выполнения аналитических задач, таких как ранжирование, вычисление агрегатов по окну или анализ данных с учётом контекста и позволяют выполнять вычисления по частям набора без потери информации о отдельных строках.

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

Образец общего синтаксиса:

SELECT 
    column1,
    column2,
    ОКОННАЯ_ФУНКЦИЯ() OVER (
        [PARTITION BY partition_expression, ...]
        [ORDER BY sort_expression [ASC | DESC], ...]
        [ROWS | RANGE frame_specification]
    ) AS result_column
FROM 
    table_name;

Типы оконных функций:
ROW_NUMBER() — присваивает уникальный номер каждой строке в окне.
RANK() — ранжирует строки с учётом возможных одинаковых значений.
DENSE_RANK() — похож на RANK(), но без пропусков в номерах рангов.
NTILE(n) — делит данные на n равных частей.
SUM(), AVG(), MIN(), MAX() — агрегатные функции, применяемые к окну.

Пример 1: AVG()
Посчитать зарплату каждого сотрудника и среднюю зарплату по его отделу, не теряя строк.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM employees;

Что происходит:
1. PARTITION BY department делит строки на группы (по отделам),
2. AVG(salary) считает среднюю по каждой такой группе.

Пример 2: NTILE(n)
NTILE(n) делит данные на n равных частей и присваивает каждому элементу номер его "порции".
Это полезно, например, для разбивки данных на квартили или группы.

SELECT
    employee_id,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

Здесь сотрудники будут разделены на 4 группы по зарплатам.

Еще пример: ROW_NUMBER()
Функция присваивает уникальный номер каждой строке в окне. Строки нумеруются по порядку, начиная с 1, на основе сортировки в ORDER BY. Это полезно, например, для генерации уникальных идентификаторов в рамках группы.

SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

В этом примере всем сотрудникам будет присвоен уникальный номер, начиная с самого высокооплачиваемого.

Важно!
1. Оконные функции не сворачивают строки (в отличие от GROUP BY).
2. Работают после WHERE и GROUP BY, но до ORDER BY в основном запросе.
3. Поддерживаются в PostgreSQL, MySQL 8+, SQL Server, Oracle.

Преимущества оконных функций:
1. Гибкость — оконные функции позволяют решать сложные аналитические задачи, не меняя структуру данных.
2. Скорость — они могут быть быстрее, чем использование подзапросов или объединений, поскольку не нужно выполнять дополнительные агрегации.
3. Удобство — оконные функции позволяют работать с большими объёмами данных и производить расчёты, сохраняя доступ к каждой строке.

#Оконные_функции #SQL

📱 Подписаться на канал | Курс автора по SQL DDL

🧊 Материализованные представления — базовая теория

Когда вы создаёте обычное представление (разобрал ранее в посте) в SQL, вы, по сути, сохраняете тело запроса, но не его результат. Каждый раз при обращении к такому представлению СУБД выполняет запрос заново, извлекая данные из указанных таблиц. Это гарантирует актуальность, но процесс может быть медленным, особенно если запрос тяжёлый и работает с большим количеством данных.

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

🔍 Что такое материализованное представление?
Это объект базы данных, который сохраняет результат выполнения SQL-запроса — то есть не только структуру, но и сами данные. Он существует как полноценная таблица с данными, которые можно быстро читать без перерасчёта.

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

🧠 Теория: зачем нужны материализованные представления?
Материализованные представления применяются, когда:
1. Данные редко меняются, но часто читаются.
2. Запросы сложные и требуют много ресурсов.
3. Нужно сократить время ответа в BI-системах, аналитике, отчётах.
4. Вы строите ETL/ELT пайплайн и хотите сохранить промежуточные агрегаты.
5. Нужен кэш сложного запроса, особенно с объединениями и агрегациями.

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

Пример: агрегаты по продажам
Исходная таблица:

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product_id INT,
    quantity INT,
    total NUMERIC,
    sale_date DATE
);

Создаём материализованное представление (CREATE MATERIALIZED VIEW имя_представления), в котором храним сумму продаж и количество товаров по каждому продукту и месяцу:

CREATE MATERIALIZED VIEW sales_summary AS
SELECT
    product_id,
    DATE_TRUNC('month', sale_date) AS month,
    SUM(quantity) AS total_quantity,
    SUM(total) AS total_revenue
FROM sales
GROUP BY product_id, DATE_TRUNC('month', sale_date);

Теперь можно обращаться к sales_summary, как к обычной таблице — всё уже посчитано и лежит в готовом виде.

🔄 Обновление данных
Так как данные в материализованном представлении со временем устаревают, их нужно обновлять вручную:

REFRESH MATERIALIZED VIEW sales_summary;

Если вы не хотите блокировать чтение из представления во время обновления (например, в боевой системе), используйте:

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

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

⚙️ Технические особенности
1. Нельзя использовать функции, которые дают разные результаты при каждом вызове: NOW(), RANDOM(), CURRENT_USER и пр.
2. В представление нельзя передавать параметры (например, даты).
3. Представление ведёт себя как таблица: в нём есть строки и столбцы, и оно занимает место на диске.
4. Можно создавать индексы на материализованном представлении, как на обычной таблице — это даёт дополнительный прирост производительности.
5. Обновление может быть затратным по времени — особенно если исходные таблицы большие и часто меняются.

🔧 Поддержка в разных СУБД
1. В PostgreSQL материализованные представления поддерживаются нативно. Можно использовать REFRESH и CONCURRENTLY.
2. В Oracle поддержка есть с 8 версии, и она даже продвинутая — позволяет настраивать автоматическое обновление по расписанию.
3. В SQL Server их нет как отдельного объекта, но похожее поведение обеспечивают Indexed Views.
4. В MySQL и SQLite — отсутствуют, но можно эмулировать, создавая таблицу + триггеры или процедуры обновления.

#Views #Materialized_views #SQL

📱 Подписаться на канал | Курс автора по SQL DDL

🔧 Партиционирование таблиц в SQL

Когда таблица становится слишком большой, работа с ней замедляется. Представь таблицу с миллионами заказов за несколько лет — каждый SELECT, UPDATE или DELETE начинает тормозить.

Решение — партиционирование (partitioning).
Партиционирование — это способ разделить одну физическую таблицу на несколько более мелких частей, называемых секциями (partitions). Каждая секция обрабатывает данные в диапазоне или по определённому условию.

Партиционирование создаётся на уровне DDL — команд, которые описывают структуру таблиц в базе данных. Партиционирование задаётся при создании таблицы, потому что большинство СУБД (включая PostgreSQL, MySQL и др.) не позволяют ретроспективно изменить обычную таблицу на партиционированную. В случае наличия огромной таблицы без партиций, этот процесс можно выполнить путем создания ее аналога с партициями и осуществления миграции данных.

Зачем использовать партиционирование?
1. Повышение производительности — запросы работают только с нужной секцией, а не со всей таблицей.
2. Упрощение администрирования — старые секции можно удалять или архивировать.
3. Чёткая логическая организация данных — например, по годам или регионам.

Основные типы партиционирования:
1. Диапазонное (RANGE) — делит таблицу по значениям в диапазоне. Например: заказы по годам.
2. Списковое (LIST) — делит по значениям из списка. Например: по странам или типу продукта.
3. Хэш-партиционирование (HASH) — делит данные равномерно по хешу поля. Используется, когда нет логического критерия разделения.

Существует два основных варианта партиционирования в SQL — горизонтальное и вертикальное:
1. Горизонтальное — разделение таблицы на партиции, основанное на строках. Каждая партиция содержит определённое количество строк.
2. Вертикальное — разделение таблицы на партиции, основанное на столбцах. Каждая партиция содержит определённый набор столбцов.

Пример (PostgreSQL, RANGE партиционирование)
ТЗ: Создать таблицу orders горизонтально разбитую на секции по годам, чтобы упростить работу с большим объёмом данных в будущем — ускорить запросы, сделать более быстрым архивирование, повысить производительность.

Создаем таблицу orders:

CREATE TABLE orders (
  id SERIAL,
  order_date DATE NOT NULL,
  customer_id INT,
  amount NUMERIC
) PARTITION BY RANGE (order_date);

Что делаем:
1. PARTITION BY RANGE (order_date) означает, что данные будут автоматически распределяться по секциям в зависимости от значения order_date.
2. Сама таблица orders теперь — логическая оболочка, а данные будут физически храниться в её партициях (субтаблицах).

Создаём секции:

CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

1. Теперь все заказы автоматически попадут в нужную секцию в зависимости от order_date.
2. orders_2023 хранит все строки, где order_date от 2023-01-01 до 2023-12-31.
3. orders_2024 — за 2024 год.
4. Диапазоны не пересекаются и не перекрываются, иначе возникнет ошибка при вставке.

🛠 Что происходит при обращении к партиционированным таблицам?
Когда пользователь выполняет запрос:

SELECT * FROM orders WHERE order_date >= '2024-01-01';

PostgreSQL понимает, что нужны только данные из orders_2024, и вообще не трогает остальные таблицы. Это называется pruning — отсечение ненужных секций.

Что нужно помнить
1. Партиционирование — даёт прирост при правильных запросах, особенно с фильтрацией по колонке партиционирования.
2. Партиции ведут себя как обычные таблицы — можно создавать на них отдельные индексы, статистику, правила.
3. Логика запросов остаётся на уровне родительской таблицы — СУБД сама направляет данные в нужную субтаблицу.
3. Хорошее партиционирование требует продуманной модели данных и нагрузки.

#Partition #SQL

📱 Подписаться на канал | Курс автора по SQL DDL