Представления в базах данных

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

Lis Sulmont

Curriculum Manager

Представления в базах данных

В базе данных представление — это результирующий набор хранимого запроса, который пользователи могут запрашивать так же, как и обычный объект базы данных (Wikipedia)

Виртуальная таблица, не входящая в физическую схему

В памяти хранится запрос, а не данные
Данные агрегируются из таблиц
Можно запрашивать как обычную таблицу
Не нужно повторять одни и те же запросы или менять схему

¹ https://en.wikipedia.org/wiki/View_(SQL)

Создание представления (синтаксис)

CREATE VIEW view_name AS

SELECT col1, col2 
FROM table_name 
WHERE condition;

Создание представления (пример)

Измерение книги в схеме «снежинка»

Задача: Вернуть названия и авторов книг жанра science fiction

Создание представления (пример)

CREATE VIEW scifi_books AS

SELECT title,  author, genre
FROM dim_book_sf
JOIN dim_genre_sf ON dim_genre_sf.genre_id = dim_book_sf.genre_id
JOIN dim_author_sf ON dim_author_sf.author_id = dim_book_sf.author_id
WHERE dim_genre_sf.genre = 'science fiction';

Запрос к представлению (пример)

SELECT * FROM scifi_books

| title                         | author            | genre           |
|-------------------------------|-------------------|-----------------|
| The Naked Sun                 | Isaac Asimov      | science fiction |
| The Robots of Dawn            | Isaac Asimov      | science fiction |
| The Time Machine              | H.G. Wells        | science fiction |
| The Invisible Man             | H.G. Wells        | science fiction |
| The War of the Worlds         | H.G. Wells        | science fiction |
| Wild Seed (Patternmaster, #1) | Octavia E. Butler | science fiction |
| ...                           | ...               | ...             |

Что происходит «за кулисами»

SELECT * FROM scifi_books

SELECT * FROM 
(SELECT title,  author, genre
FROM dim_book_sf
JOIN dim_genre_sf ON dim_genre_sf.genre_id = dim_book_sf.genre_id
JOIN dim_author_sf ON dim_author_sf.author_id = dim_book_sf.author_id
WHERE dim_genre_sf.genre = 'science fiction');

Просмотр представлений

(в PostgreSQL)

SELECT * FROM INFORMATION_SCHEMA.views;

Включая системные представления

SELECT * FROM information_schema.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

Без системных представлений

Преимущества представлений

Не занимает место в хранилище
Форма управления доступом
- Скрывает конфиденциальные столбцы и ограничивает видимость данных
Скрывает сложность запросов
- Удобно для сильно нормализованных схем

$$ Схема базы данных Pitchfork Reviews

¹ https://www.kaggle.com/nolanbconaway/pitchfork-data

Давайте потренируемся!

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