Ежемесячный дайджест — апрель 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

🔍 BETWEEN в SQL: удобный инструмент фильтрации, но есть нюансы

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

Общий синтаксис:

SELECT ...
FROM ...
WHERE столбец BETWEEN значение_1 AND значение_2;

Это полностью эквивалентно:

WHERE столбец >= значение_1 AND столбец <= значение_2

Границы включаются! Это главный момент, который необходимо помнить.

Пример с числами:

SELECT * FROM products
WHERE price BETWEEN 100 AND 500;

Этот запрос вернёт все товары, цена которых от 100 до 500 включительно.
То же самое, что:

WHERE price >= 100 AND price <= 500

Обратите внимание: порядок имеет значение!
Если перепутать границы — результат будет пустым:

WHERE price BETWEEN 500 AND 100  
--  Вернёт 0 строк

Пример с датами:

SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

На первый взгляд запрос безопасен, но всё зависит от типа данных order_date
— Если это DATE, то всё хорошо: вернутся все заказы от 1 января до 31 декабря 2024 года включительно.
— Если это DATETIME или TIMESTAMP, то 31 декабря попадут только записи с временем 00:00:00. То есть, большая часть заказов за 31 декабря может быть пропущена!

Как правильно писать условия для DATETIME.
Используйте открытый верхний предел:

WHERE order_date >= '2024-01-01'
  AND order_date <  '2025-01-01'

Такой стиль абсолютно безопасен и широко применяется в профессиональной практике, особенно в BI и аналитике.

Что ещё важно?
1. Типы данных должны быть совместимы

WHERE product_id BETWEEN 'a' AND 'z'  -- OK для строк

BETWEEN работает и с текстами (сравнение лексикографическое), и с датами, и с числами.

2. NOT BETWEEN тоже существует!

WHERE age NOT BETWEEN 18 AND 60;

Возвращает все строки, где значение меньше 18 или больше 60.

BETWEEN и NULL:
Если значение в проверяемом столбце — NULL, то условие BETWEEN вернёт не TRUE, а UNKNOWN, что эквивалентно тому, что строка не попадёт в результат.

SELECT * FROM employees
WHERE commission_pct BETWEEN 0.1 AND 0.3;  -- NULL значения исключаются

Чтобы явно включить такие строки, добавьте отдельное условие:

WHERE (commission_pct BETWEEN 0.1 AND 0.3 OR commission_pct IS NULL)

Итого:
1. BETWEEN включает границы, то есть условие BETWEEN 10 AND 20 вернёт и 10, и 20.
2. При работе с типами DATETIME следует быть особенно осторожным: BETWEEN '2024-01-01' AND '2024-12-31' не захватит все записи за 31 декабря, если в них указано время.
3. BETWEEN чувствителен к порядку значений: если нижняя граница больше верхней — результат будет пустой.
4. Не забывайте про NULL: записи с NULL в проверяемом столбце не попадут в выборку.
5. Альтернативный и более безопасный способ — использовать условия вида >= ... AND < ..., особенно при работе с временными диапазонами.

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

#BETWEEN #SQL

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

🔗 Типы связей в реляционных БД: что, зачем и как?

Связи между таблицами — основа реляционных баз данных. Именно они позволяют создавать сложные модели данных. В теории баз данных тип связи (relationship type) — это формализованное описание ассоциации между двумя или более сущностями, указывающее, сколько экземпляров одной сущности может быть связано с экземплярами другой.

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

1. Один к одному (1:1)
Каждая запись в таблице A соответствует ровно одной записи в таблице B и наоборот.

Пример:
Таблица users — содержит пользователей
Таблица passports — содержит паспортные данные
Каждому пользователю соответствует один паспорт, и каждый паспорт — одному пользователю.

Как реализуется:
Вторичная таблица (passports) содержит внешний ключ на users. id
Иногда связи 1:1 выделяются в отдельную таблицу для повышения безопасности или для разделения редко используемых данных

users            passports
+---------+      +------------+
| id (PK)   <--- |user_id (PK,FK)
| name           |passport_number 
+---------+      +------------+

Связь: users. id (PK) — 1:1 — passports.user_id (PK, FK)

2. Один ко многим (1:N)
Каждая запись в таблице A может соответствовать нескольким записям в таблице B, но каждая запись в таблице B соответствует только одной записи в A.

Пример:
Таблица departments — отделы
Таблица employees — сотрудники
Один отдел включает многих сотрудников, но каждый сотрудник относится к одному отделу.

Как реализуется:
Во второй таблице (employees) создаётся внешний ключ department_id, указывающий на departments. id первой таблицы. Это — самый распространённый тип связи.

departments         employees
+----------+        +-----------------+
| id (PK)    <----- | id (PK)         
| name              | name            
+----------+        | department_id(FK)
                    +-----------------+

Связь: departments. id (PK) — 1:N — employees.department_id (FK)

3. Многие ко многим (N:M)
Каждая запись в таблице A может соответствовать нескольким записям в таблице B, и наоборот.

Пример:
Таблица students — студенты
Таблица courses — курсы
Один студент записан на много курсов, и каждый курс включает много студентов.

Как реализуется:
1. Создаётся связующая таблица (например, student_course)
2. Она содержит два внешних ключа: student_id и course_id
3. Иногда добавляют дополнительные атрибуты

students      student_courses     
+---------+   +--------------+   
| id (PK)     | student_id(FK)   
| name        | course_id(FK)    
+---------+   +--------------+  

courses
+----------+
| id (PK) 
| noscript 
+----------+    

Связи:
1. students. id — 1:N — student_courses.student_id
2. courses. id — 1:N — student_courses.course_id

Коротко о реализации типов связей:
1. Один к одному — внешний ключ + уникальность
2. Один ко многим — внешний ключ в "многой" таблице
3. Многие ко многим — связующая таблица с 2 FK

Почему это важно?
Правильное определение и реализация связей:
1. Обеспечивает целостность данных
2. Обязательно для нормализации данных и устранения дублирования
4. Необходимо для поддержки бизнес-логики и целевого использования данных БД
5. Улучшает производительность (меньше дублирования, легче кэшировать);
6. Делает базу пригодной к росту (easy to scale).

Таким образом, связи в реляционной БД — это основа структурированной, надёжной и масштабируемой системы хранения данных.

#Ключи_и_связи_между_таблицами #SQL

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

Сегодня столкнулся с гениальным описанием ошибки от СУБД.

Ок, понял 🗿

👩‍💻 Материализованные представления в SQL — погружаемся глубже в теорию

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

🧊 Что такое материализованное представление?
Материализованное представление (Materialized View) — это реальный объект базы данных, содержащий предрасчитанные данные.

В отличие от обычного VIEW, здесь:
➖Результат запроса сохраняется как отдельная таблица;
➖При чтении SELECT возвращает готовые, быстро читаемые данные;
➖Данные не обновляются автоматически (кроме некоторых случаев, например Oracle или с триггерами);
➖В основном требует явного ручного обновления — REFRESH.

🧊 Архитектурно материализованное представление — это:
➖ Текст SQL-запроса,
➖ Физическая таблица,
➖ Метаданные для обновления

🧊 Ключевые особенности
➖ Сохраняет данные — Да
➖ Обновляется автоматически — Нет (нужно REFRESH), за исключением случаев, которые рассмотрим ниже.
➖ Быстрое выполнение SELECT — Да
➖ Требует места на диске — Да

🧊 Общий синтаксис для создания материализованного представления:

CREATE MATERIALIZED VIEW имя_представления AS
SELECT ...
FROM ...
[WHERE ...]
[GROUP BY ...]
[ORDER BY ...];

Дополнительно можно указать:
WITH NO DATA — представление создаётся, но не заполняется (нужно REFRESH).
WITH DATA (по умолчанию) — данные вычисляются сразу при создании.

🧊 Допустим, у нас есть таблица orders, в которой хранятся заказы клиентов. Она может содержать такие поля:

-----------------------------
id            SERIAL
order_date    TIMESTAMP
customer_id   INT
amount        NUMERIC

Таблица обновляется постоянно: пользователи делают заказы, и каждая строка — это один заказ.

Часто требуется строить отчёты:
➖ Сколько заказов было каждый день
➖ Какова средняя сумма заказа за день

Запрос к такой таблице может быть тяжёлым, особенно если в orders миллионы строк. Поэтому создаём материализованное представление — заранее рассчитанный агрегат, чтобы анализировать среднюю сумму заказов по дням:

CREATE MATERIALIZED VIEW daily_avg_orders AS
SELECT
  order_date::date AS day,
  COUNT(*) AS total_orders,
  AVG(amount) AS avg_order_value
FROM orders
GROUP BY day
ORDER BY day;

Что делает это материализованное представление:
После выполнения CREATE MATERIALIZED VIEW — СУБД физически сохраняет результат запроса. Теперь мы можем быстро выполнять:

SELECT * FROM daily_avg_orders;

И получать результат без перерасчёта данных каждый раз.

🛠 Обновление (refresh)
Чтобы MView оставалось актуальным, его нужно обновлять вручную или автоматически.
При ручном обновлении, чтобы обновить данные в представлении после изменений в таблице orders, выполняем:

REFRESH MATERIALIZED VIEW daily_avg_orders;

Если при создании добавили WITH NO DATA — первый REFRESH обязателен.

Можно настроить автообновление через:
➖ Планировщик задач (например, cron);
➖ Расписание в СУБД (например, PostgreSQL pg_cron, Oracle DBMS_SCHEDULER);
➖ Использование триггеров или CDC

⚙️ Индексы и уникальность
Во многих СУБД материализованные представления поддерживают индексы, особенно если нужно обеспечить уникальность:

CREATE UNIQUE INDEX ON имя_представления(ключ);

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

🖥 Поддержка материализованных представлений в разных СУБД:
➖ PostgreSQL — Полноценная, ручное обновление
➖ Oracle — Продвинутая (есть автообновление, query rewrite)
➖ SQL Server — Аналог: Indexed Views
➖ MySQL — поддержки нет ❌
➖ ClickHouse — Аналог через MATERIALIZED VIEW с INSERT
➖ BigQuery — Поддержка с автообновлением

👾 Где это особенно нужно?
➖ Сложные отчёты (много JOIN, WHERE, GROUP BY) — хранит результат и не пересчитывает каждый раз
➖ BI и дашборды — быстрые ответы
➖ Разгрузка сервера — меньше нагрузки на исходные таблицы
➖ Разделение хранилищ — можно экспортировать MView в другие слои данных
➖ Медленные источники данных — данные приходят редко, но запросы часты.

Так что, материализуемся 🥁

#Views #Materialized_views #SQL

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