#sql #PostgreSQL #Oracle #база данных #анализ данных
Представление (VIEW) – это объект базы данных (БД), который хранит в себе запрос SELECT. При обращении к данному объекту будет возвращен результирующий набор данных (результат выполнения запроса).
Представления бывают двух видов.
Ссылка на статью с разбором системных представлений
Преставление также называют псевдонимами запросов или виртуальными таблицами. Это связано с тем, что представление не хранит в себе информацию, но при этом вы можете обращаться к нему как к обычной таблице, указав после оператора FROM название представления. Каждый раз при обращении к представлению, выполняется запрос заключённый в представлении и возвращается результат выполнения этого запроса.
Для создания представлений могут использоваться как таблицы, так и представления. То есть представления могут быть вложенными.
Преимущества представлений
- Гибкая настройка прав доступа
Представление может включать в себя отдельные строки, столбцы таблиц или результат действия с данными. Тем самым появляется возможность ограничить доступ пользователей к базовым таблицам.
- Предотвращение дублирования запросов
Как функции в программировании помогают избежать дублирования кода, так и представления в SQL помогают избежать дублирования запросов. Вы можете обращаться к одному и тому же представлению несколько раз, в различных запросах. И если нужно что-то поменять в запросе, который используется неоднократно, то нужно изменить только лишь само определение представления.
- Сокрытие реализации
Пользователь имеет доступ к представлениям, но не имеет доступа к базовым таблицам, из которых извлекаются данные. А значит администратор/ разработчик БД может менять схему хранения данных так как ему необходимо при этом обновляя определения представлений.
Создание представлений
Синтаксис создания представления следующий:
CREATE VIEW view_name AS
SELECT columns
FROM tables;
Для создания представления используется оператор CREATE VIEW. view_name – это название представления. После оператора AS следует сам запрос.
Обновление определения представления
Синтаксис данной операции следующий:
CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM tables;
Обновление применимо, например, в том случае, если вам необходимо что-то изменить в запросе.
Если представления не существует, то оно будет создано.
Удаление представления
Синтаксис удаления представления следующий:
DROP VIEW view_name;
Материализованные представления
Как уже говорилось ранее, представления не хранят данные. При запросе к представлениям данные извлекаются из таблиц. Но здесь существует исключение – это материализованные представления.
Синтаксис создания такого представления представлен ниже:
CREATE MATERIALIZED VIEW view_name AS
SELECT columns
FROM tables;
Данный тип представлений один раз выполняет запрос и хранит данные. Тем самым повышается быстродействие, но данные в представлении необходимо обновлять вручную.
Для этого применяется следующая команда:
REFRESH MATERIALIZED VIEW view_name;
Материализованные представления не являются частью стандарта ANSI SQL и поддерживаются не всеми системами управления базами данных (СУБД). PostgreSQL и Oracle Database поддерживают данный тип представлений.
Практическая часть
Для примера создадим небольшую базу данных, в которой будет хранится информация о договорах страхования.
Схема БД представлена ниже.
Весь код далее был написан в pgAdmin. Соответственно в качестве СУБД использовалась PostgreSQL.
Код создание таблиц приведён ниже.
-- Таблица "Страховой агент"
CREATE TABLE insurance_agents
(
id SERIAL PRIMARY KEY,
last_name CHARACTER VARYING(30) NOT NULL CHECK(last_name != ''),
first_name CHARACTER VARYING(30) NOT NULL CHECK(first_name != ''),
middle_name CHARACTER VARYING(30) NOT NULL CHECK(middle_name != ''),
phone CHARACTER VARYING(18) NOT NULL UNIQUE CHECK(phone != ''),
email CHARACTER VARYING(30) NOT NULL CHECK(email != '') UNIQUE,
insurance_percent REAL CHECK((insurance_percent >= 0.2) AND (insurance_percent <= 1)) NOT NULL
);
-- Таблица "Страхователь"
CREATE TABLE insurants
(
id SERIAL PRIMARY KEY,
last_name CHARACTER VARYING(30) NOT NULL CHECK(last_name != ''),
first_name CHARACTER VARYING(30) NOT NULL CHECK(first_name != ''),
middle_name CHARACTER VARYING(30) NOT NULL CHECK(middle_name != ''),
phone CHARACTER VARYING(18) NOT NULL UNIQUE CHECK(phone != ''),
email CHARACTER VARYING(30) NOT NULL CHECK(email != '') UNIQUE
);
-- Таблица "Выгодоприобретатель"
CREATE TABLE beneficiaries
(
id SERIAL PRIMARY KEY,
last_name CHARACTER VARYING(30) NOT NULL CHECK(last_name != ''),
first_name CHARACTER VARYING(30) NOT NULL CHECK(first_name != ''),
middle_name CHARACTER VARYING(30) NOT NULL CHECK(middle_name != ''),
phone CHARACTER VARYING(18) NOT NULL UNIQUE CHECK(phone != ''),
email CHARACTER VARYING(30) NOT NULL CHECK(email != '') UNIQUE
);
-- Таблица "Страховой полис"
CREATE TABLE insuranсe_policies
(
id SERIAL PRIMARY KEY, series_and_number CHARACTER VARYING(10) NOT NULL UNIQUE CHECK(series_and_number != ''),
object_insurance CHARACTER VARYING(30) NOT NULL CHECK(object_insurance != ''),
insurance_amount NUMERIC(8,2) NOT NULL,
insurance_premium NUMERIC(8,2) NOT NULL,
date_in DATE NOT NULL,
date_out DATE NOT NULL,
id_agent INTEGER,
id_insurant INTEGER,
id_beneficiary INTEGER,
FOREIGN KEY (id_agent) REFERENCES insurance_agents (id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (id_insurant) REFERENCES insurants (id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (id_beneficiary) REFERENCES beneficiaries (id) ON DELETE CASCADE ON UPDATE CASCADE
);
Далее заполняем таблицы данными на своё усмотрение. Данные из таблиц, приведённые в статье, являются случайными и не имеют никакой связи с реальностью.
Сделаем тестовый запрос к БД.
-- Страховая премия по объектам страхования
SELECT object_insurance AS "Объект страхования",
Sum(insurance_premium) AS "Общая страховая премия",
Round(Avg(insurance_premium), 2),
Min(insurance_premium),
Max(insurance_premium)
FROM insuranсe_policies
GROUP BY object_insurance
ORDER BY Sum(insurance_premium) DESC
Результат выполнения запроса:
Всё в порядке, всё работает. Можем приступать к работе с представлениями.
Создадим представление.
-- Продажи по сотрудникам
CREATE VIEW sales_agents AS
SELECT last_name AS "Фамилия",
first_name AS "Имя",
middle_name AS "Отчество",
Count(*) AS "Кол-во сделок",
Sum(insurance_amount + insurance_premium) AS "Сумма сделок"
FROM insurance_agents
INNER JOIN insuranсe_policies
ON insurance_agents.id = insuranсe_policies.id_agent
GROUP BY last_name,
first_name,
middle_name
ORDER BY last_name
Попробуем обратится к представлению как к обычной таблице.
SELECT *
FROM sales_agents
Результат:
Таким образом, пользователь сможет обращаться к представлению как к обычной таблице. Тем самым можно ограничить доступ к некоторым полям в таблице страховых агентов. Например, не показывать номера телефонов агентов.
Также не нужно переписывать данной запрос несколько раз, достаточно обратиться к представлению.
Теперь, изменим представление – добавим почты агентов. Удалять столбцы из представления нельзя.
-- Продажи по сотрудникам
CREATE OR REPLACE VIEW sales_agents AS
SELECT last_name AS "Фамилия",
first_name AS "Имя",
middle_name AS "Отчество",
Count(*) AS "Кол-во сделок",
Sum(insurance_amount + insurance_premium) AS "Сумма сделок",
email AS "Почта"
FROM insurance_agents
INNER JOIN insuranсe_policies
ON insurance_agents.id = insuranсe_policies.id_agent
GROUP BY last_name,
first_name,
middle_name,
ORDER BY last_name
Результат:
Удалим представление и создадим материализованное представление.
Удаление:
Создание материализованного представления:
-- Страховая сумма и премия по годам
CREATE MATERIALIZED VIEW by_year AS
SELECT CAST(Extract(year FROM date_in) AS INT) AS "Год",
Sum(insurance_amount) AS "Страховая сумма",
Sum(insurance_premium) AS "Страховая премия"
FROM insuranсe_policies
GROUP BY Extract(year FROM date_in)
ORDER BY Extract(year FROM date_in)
Обратимся к представлению как к таблице:
SELECT *
FROM by_year
Результат:
Попробуем изменить данные и вновь вызовем представление. Удалим запись о договоре страхования с серией и номером 0567412354.
Информация о договоре:
Удаление:
Вызов обращения:
Выполним отдельно запрос из представления, чтобы сверить результаты.
По скриншотам видно, что данные в материализованном представлении не обновились (данные за 2015 год).
Однако скорость выполнения операций отличается.
Представление:
Запрос:
Обработка операции с обращением к материализованному представлению происходит быстрее.
В этом посте я рассмотрел тему представлений в SQL. Что они из себя представляют, какие бывают, для чего и как используются. Если остались вопросы, задавайте их в комментариях.
Надеюсь материал был полезен. Удачи в использовании представлений!
Читайте также:
