Расширенный функционал
Вступление
В главе Язык SQL. Общие сведения были рассмотрены основы использования SQL для хранения и обращения к данным в QHB. Теперь мы обсудим некоторые более продвинутые функции SQL, которые упрощают управление и предотвращают потерю или повреждение данных. Кроме того, мы рассмотрим некоторые расширения QHB.
В этой главе иногда приводятся ссылки на примеры из главы Язык SQL. Общие сведения, которые будут изменены или улучшены, поэтому будет полезно прочитать эту главу. Некоторые примеры также можно найти далее в разделе Расширенный SQL. Также там содержатся некоторые примеры данных и таблицы, которые повторно в этой главе не описываются.
Представления
В разделе Соединения таблиц мы рассматривали запросы из нескольких таблиц. Предположим, что комбинированный список записей о погоде и местоположении городов представляет особый интерес для вашего приложения, но вы не хотите заново вводить весь запрос каждый раз, когда он понадобится. Вы можете создать представление запроса, дающее этому запросу имя, к которому можно обращаться как к обычной таблице:
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
SELECT * FROM myview;
Активное использование представлений является ключевым аспектом хорошего проекта базы данных SQL. Представления позволяют скрыть элементы структуры таблиц, которые могут меняться по мере развития приложения, за согласованными интерфейсами.
Представления можно использовать практически везде, где можно использовать настоящую таблицу. Также можно создавать представления из других представлений.
Внешние ключи
Вспомните таблицы weather и cities из главы Язык SQL. Общие сведения. Рассмотрим следующую проблему: Вы хотите убедиться, что никто не сможет вставить в таблицу weather строки, для которых нет соответствующей записи в таблице cities. Это ограничение называется поддержанием ссылочной целостности данных. В упрощенных СУБД это будет реализовано (если вообще будет) путем просмотра таблицы cities с целью проверки, существует ли соответствующая запись, а затем будут добавлены или отклонены новые записи из weather. Этот подход имеет ряд недостатков и очень неудобен, поэтому QHB может сделать это за вас.
Новое объявление таблиц будет выглядеть так:
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
Теперь попробуйте вставить неверную запись:
INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
ERROR: insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL: Key (city)=(Berkeley) is not present in table "cities".
-- ОШИБКА: INSERT или UPDATE в таблице "weather" нарушает ограничение внешнего ключа "weather_city_fkey"
-- ПОДРОБНОСТИ: Ключ (city)=(Berkeley) отсутствует в таблице "cities".
Поведение внешних ключей можно настроить под ваше приложение. В данном руководстве мы не зайдем дальше этого простого примера, но дополнительную информацию можно найти в главе Определение данных. Правильное использование внешних ключей, несомненно, улучшит качество ваших приложений для базы данных, поэтому мы настоятельно рекомендуем изучить их.
Транзакции
Транзакции являются фундаментальной концепцией всех СУБД. Ключевая особенность транзакции состоит в том, что она объединяет несколько последовательных действий в одну операцию «все или ничего». Промежуточные состояния между действиями не видны другим, параллельно выполняющимся, транзакциям, и если происходит какой-либо сбой, препятствующий завершению конкретной транзакции, то ни одно из ее действий никак не влияет на базу данных.
Например, рассмотрим банковскую базу данных, которая содержит остатки по различным счетам клиентов, а также общие остатки по депозитам для филиалов. Предположим, что мы хотим перевести со счета Алисы 100 долларов на счет Боба. Если сильно упростить, команды SQL для этого могут выглядеть так:
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
Подробности этих команд здесь не важны; важно отметить, что для выполнения этой довольно простой операции требуется несколько отдельных обновлений. Сотрудники банка хотят быть уверены, что либо все эти обновления произойдут, либо ни одно из них не произойдет. Конечно, будет нехорошо, если из-за системного сбоя Боб получит 100 долларов, но со счета Алисы они списаны не будут. Да и Алиса не останется счастливым клиентом, если у нее со счета будет списана сумма без зачисления Бобу. Нам нужна гарантия того, что если во время операции что-то пойдет не так, ни одно из выполненных до сих пор действий не вступит в силу. Группировка всех изменений в одну транзакцию дает нам эту гарантию. Транзакция считается атомарной: с точки зрения других транзакций она либо выполняется полностью, либо вообще не выполняется.
Также нам нужна гарантия, что после завершения и подтверждения транзакции в СУБД она действительно надежно сохранится и не будет утеряна, даже если вскоре после этого произойдет сбой системы. Например, если мы записываем снятие наличных Бобом, мы хотим исключить всякую возможность того, что списание с его счета исчезнет в результате сбоя сразу после того, как он выйдет за дверь банка. Транзакционная база данных гарантирует, что все сделанные транзакцией изменения будут записаны в постоянном хранилище (то есть на диске) до сообщения о завершении транзакции.
Еще одно важное свойство транзакционных баз данных тесно связано с понятием атомарных изменений: когда одновременно выполняется несколько транзакций, каждая из них не должна видеть неполные изменения, внесенные другими. Например, если одна транзакция занята суммированием всех остатков в филиалах, не годится, чтобы она включала списание из филиала Алисы, но не включала пополнение из филиала Боба, и наоборот. Таким образом, транзакции должны выполнять «все или ничего» не только с точки зрения их необратимого воздействия на базу данных, но и с точки зрения их видимости во время исполнения. Изменения, сделанные открытой транзакцией, невидимы для других транзакций вплоть до завершения этой транзакция, после чего становятся видимыми сразу все изменения.
В QHB транзакция устанавливается путем заключения команд SQL этой
транзакции в команды BEGIN и COMMIT. Таким образом, наша банковская транзакция
будет выглядеть так:
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- и т. д.
COMMIT;
Если в ходе транзакции мы решаем, что не хотим ее фиксировать (возможно, мы
только что заметили, что баланс Алисы стал отрицательным), вместо COMMIT можно
выполнить команду ROLLBACK, и все изменения будут отменены.
На самом деле QHB обрабатывает каждый оператор SQL как выполняемый
в транзакции. Если вы не добавите команду BEGIN, то каждый отдельный оператор
будет неявно окружен командами BEGIN и (в случае успешного завершения) COMMIT.
Группу операторов, окруженную BEGIN и COMMIT, иногда называют блоком
транзакций.
Примечание
Некоторые клиентские библиотеки добавляют командыBEGINиCOMMITавтоматически, поэтому можно, не ведая того, получить эффект блоков транзакций. Проверьте документацию по используемому вами прикладному интерфейсу.
Можно управлять транзакциями более детально, используя точки сохранения. Точки
сохранения позволяют выборочно отменять отдельные части транзакции, в то же
время фиксируя остальные. После определения точки сохранения командой SAVEPOINT
при необходимости можно откатиться до этой точки с помощью ROLLBACK TO. Все
изменения базы данных, сделанные транзакцией между определением точки сохранения
и откатом к ней отменяются, но изменения, сделанные раньше этой точки, остаются.
После отката к точке сохранения она продолжает оставаться определенной, поэтому к ней можно откатиться несколько раз. И наоборот, если вы уверены, что вам не понадобится снова возвращаться к определенной точке сохранения, ее можно удалить, чтобы освободить ресурсы системы. Имейте в виду, что удаление или откат к точке сохранения автоматически сбросит все точки сохранения, которые были определены после нее.
Все это происходит внутри блока транзакции, поэтому ни одно из изменений не видно другим сеансам базы данных. Когда и если вы фиксируете блок транзакции, зафиксированные действия становятся видимыми для других сеансов как единое целое, тогда как отмененные действия вообще никогда не становятся видимыми.
Вспоминая банковскую базу данных, предположим, что мы списываем 100 долларов со счета Алисы и зачисляем их на счет Боба, а потом выясняем, что нужно было зачислить их на счет Уолли. Это можно исправить, используя точки сохранения, например:
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
-- ой ... забудем об этом и используем счет Уолли
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;
Этот пример, конечно, чрезмерно упрощен, но блоком транзакции можно активно
управлять с помощью точек сохранения. Более того, команда ROLLBACK TO — это
единственный, не считая полного отката и повторного запуска, способ восстановить
контроль над блоком транзакции, из-за ошибки переведенным системой в прерванное
состояние.
Что такое оконные функции
Оконная функция выполняет различные вычисления для набора строк таблицы, которые так или иначе связаны с текущей строкой. Это сопоставимо с вычислением, которое можно выполнить с помощью агрегатной функции. Однако оконные функции не группируют сроки в одну выходную строку, как это сделали бы обычные, не оконные агрегатные функции. Вместо этого строки остаются отдельными элементами. За кадром же оконная функция может обращаться не только к текущей строке результата запроса.
Вот пример, который показывает, как сравнить зарплату каждого сотрудника со средней зарплатой в его или ее отделе:
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
depname | empno | salary | avg
-----------+-------+--------+-----------------------
develop | 11 | 5200 | 5020.0000000000000000
develop | 7 | 4200 | 5020.0000000000000000
develop | 9 | 4500 | 5020.0000000000000000
develop | 8 | 6000 | 5020.0000000000000000
develop | 10 | 5200 | 5020.0000000000000000
personnel | 5 | 3500 | 3700.0000000000000000
personnel | 2 | 3900 | 3700.0000000000000000
sales | 3 | 4800 | 4866.6666666666666667
sales | 1 | 5000 | 4866.6666666666666667
sales | 4 | 4800 | 4866.6666666666666667
(10 rows)
Первые три выходных столбца взяты непосредственно из таблицы empsalary, и для каждой строки в этой таблице есть одна выходная строка. Четвертый столбец представляет среднее значение по всем строкам таблицы, которые имеют то же значение depname что и текущая строка. (На самом деле это та же функция, что и обычный, не оконный агрегат avg, но из-за предложения OVER она обрабатывается как оконная функция и производит вычисление в пределах рамки окна.)
Вызов оконной функции всегда содержит предложение OVER, идущее сразу после ее имени и аргумента(ов). Это синтаксически отличает ее от обычной функции или не оконной агрегатной функции. Предложение OVER точно определяет, как строки запроса разделяются для обработки оконной функцией. Предложение PARTITION BY внутри OVER делит строки на группы, или партиции, имеющие одинаковые значения выражений PARTITION BY. Для каждой строки оконная функция вычисляется по строкам, попадающим в тот же раздел, что и текущая строка.
Кроме того, используя в OVER предложение ORDER BY, можно управлять порядком, в котором строки обрабатываются оконными функциями. (Окну ORDER BY даже необязательно соответствовать порядку, в котором выводятся строки). Например:
SELECT depname, empno, salary,
rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
depname | empno | salary | rank
-----------+-------+--------+------
develop | 8 | 6000 | 1
develop | 10 | 5200 | 2
develop | 11 | 5200 | 2
develop | 9 | 4500 | 4
develop | 7 | 4200 | 5
personnel | 2 | 3900 | 1
personnel | 5 | 3500 | 2
sales | 1 | 5000 | 1
sales | 4 | 4800 | 2
sales | 3 | 4800 | 2
(10 rows)
Как видно из результата, функция rank формирует номер по ранжиру для каждого отдельного значения ORDER BY в партиции текущей строки, используя порядок, определенный в предложении ORDER BY. Для rank не требуется явный параметр, поскольку ее поведение полностью определяется предложением OVER.
Строки, рассматриваемые оконной функцией, являются строками «виртуальной таблицы», созданной из предложения FROM путем отсеивания его результата предложениями WHERE, GROUP BY и HAVING, если таковые имеются. Например, строка, удаленная из результата из-за несоответствия условию WHERE, не видна ни одной оконной функции. Запрос может содержать несколько оконных функций, которые по-разному разбивают данные на части с использованием разных предложений OVER, но все они действуют на одну и ту же коллекцию строк, определенных этой виртуальной таблицей.
Мы уже видели, что ORDER BY можно опустить, если порядок строк не важен. Также можно опустить и PARTITION BY, и в этом случае получится одна партиция, содержащая все строки.
Есть еще одно важное понятие, связанное с оконными функциями: для каждой строки в ее партиции есть набор строк, называемый рамкой окна. Некоторые оконные функции действуют только на строки рамки окна, а не на всю партицию. По умолчанию, если задано ORDER BY, то рамка состоит из всех строк от начала партиции до текущей строки, а также любых последующих строк, которые, согласно предложению ORDER BY, равны текущей строке. Если ORDER BY опущено, рамка по умолчанию состоит из всех строк в партиции.1 Вот пример использования sum:
SELECT salary, sum(salary) OVER () FROM empsalary;
salary | sum
--------+-------
5200 | 47100
5000 | 47100
3500 | 47100
4800 | 47100
3900 | 47100
4200 | 47100
4500 | 47100
4800 | 47100
6000 | 47100
5200 | 47100
(10 rows)
Мы видим, что, поскольку в предложении OVER нет ORDER BY, рамка окна совпадает с партицией, которая в отсутствие PARTITION BY представляет собой всю таблицу; другими словами, каждая сумма вычисляется по всей таблице, и поэтому мы получаем одинаковый результат для каждой выходной строки. Но если добавить предложение ORDER BY, получатся совсем другие результаты:
SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
salary | sum
--------+-------
3500 | 3500
3900 | 7400
4200 | 11600
4500 | 16100
4800 | 25700
4800 | 25700
5000 | 30700
5200 | 41100
5200 | 41100
6000 | 47100
(10 rows)
Здесь сумма вычисляется из первого (самого низкого) оклада до текущего, включая любые его дубликаты (обратите внимание на результаты для повторяющихся окладов).
Оконные функции разрешены только в списке SELECT и предложении ORDER BY
запроса. Они запрещены в других местах, например в предложениях GROUP BY,
HAVING и WHERE. Это потому, что они логически выполняются после
обработки этих предложений. Кроме того, оконные функции выполняются после не
оконных агрегатных функций. Это означает, что разрешается включать вызов агрегатной
функции в аргументы оконной, но не наоборот.
Если есть необходимость отфильтровать или сгруппировать строки после вычислений оконной функции, можно воспользоваться подзапросами на выборку. Например:
SELECT depname, empno, salary, enroll_date
FROM
(SELECT depname, empno, salary, enroll_date,
rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
FROM empsalary
) AS ss
WHERE pos < 3;
Приведенный выше запрос показывает только строки из внутреннего запроса с результатом rank (номером по ранжиру) менее 3.
Когда запрос включает несколько оконных функций, можно записать каждую из них с отдельным предложением OVER, но это будет дублированием и будет приводить к ошибкам, если потребуется одинаковое определение окна для нескольких функций. Вместо этого каждое определение окна можно указать в предложении WINDOW, а затем ссылаться на него в OVER. Например:
SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
Более подробную информацию об оконных функциях можно найти в подразделах Вызовы оконных функций и Обработка оконных функций, разделе Оконные функции и на справочной странице команды SELECT.
Что такое наследование
Наследование — это понятие, взятое из объектно-ориентированных баз данных. Оно открывает новые интересные возможности при проектировании баз данных.
Давайте создадим две таблицы: cities (города) и capitals (столицы). Естественно, столицы тоже являются городами, поэтому нужно каким-то образом неявно показать их при перечислении всех городов. При должной сноровке можно изобрести, например, такую схему:
CREATE TABLE capitals (
name text,
population real,
elevation int, -- (высота в футах)
state char(2)
);
CREATE TABLE non_capitals (
name text,
population real,
elevation int -- (высота в футах)
);
CREATE VIEW cities AS
SELECT name, population, elevation FROM capitals
UNION
SELECT name, population, elevation FROM non_capitals;
Она работает нормально, пока идет запрос данных, но если понадобится, к примеру, изменить несколько строк, все кончится плохо.
Есть и другое, лучшее решение:
CREATE TABLE cities (
name text,
population real,
elevation int -- (высота в футах)
);
CREATE TABLE capitals (
state char(2) UNIQUE NOT NULL
) INHERITS (cities);
В этом случае строка таблицы capitals наследует все столбцы (name (название), population (население) и elevation (высота над уровнем моря)) от родительской таблицы cities. Столбец name имеет тип text, собственный тип QHB для символьных строк переменной длины. В таблице capitals имеется дополнительных столбец state, показывающий аббревиатуру штата. В QHB таблица может наследоваться от ноля и более других таблиц.
Например, следующий запрос найдет названия всех городов, включая столицы штатов, расположенные выше 500 футов над уровнем моря:
SELECT name, elevation
FROM cities
WHERE elevation > 500;
и вернет:
name | elevation
-----------+-----------
Las Vegas | 2174
Mariposa | 1953
Madison | 845
(3 rows)
С другой стороны, следующий запрос найдет все города, не являющиеся столицами штатов и расположенные выше 500 футов над уровнем моря:
SELECT name, elevation
FROM ONLY cities
WHERE elevation > 500;
name | elevation
-----------+-----------
Las Vegas | 2174
Mariposa | 1953
(2 rows)
Здесь слово ONLY перед cities показывает, что запрос должен выполняться
только по таблице cities и не спускаться ниже ее в иерархии наследования. Многие
уже рассмотренные здесь команды — SELECT, UPDATE и DELETE — поддерживают
запись с указанием ONLY.
Примечание
Хотя наследование зачастую бывает полезно, оно не было интегрировано с ограничениями уникальности или внешних ключей, что ограничивает его полезность. Подробную информацию см. в разделе Наследование.
Расширенный SQL
-----------------------------
-- Наследование:
-- Таблица может наследоваться от нескольких таблиц. Запрос может получить как записи из таблицы
-- так дополнительно и записи из всех ее наследников.
-----------------------------
-- Для примера, таблица capitals (столицы) наследуется от таблицы cities (города), при этом будут унаследованы все поля.
CREATE TABLE cities (
name text,
population float8,
altitude int -- (в футах)
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
-- Теперь заполним таблицы:
INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63);
INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174);
INSERT INTO cities VALUES ('Mariposa', 1200, 1953);
INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA');
INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI');
SELECT * FROM cities;
SELECT * FROM capitals;
-- Можно найти все города, включая столицы, которые расположены на высоте 500 футов и выше.
SELECT c.name, c.altitude
FROM cities c
WHERE c.altitude > 500;
-- Для того чтобы в запросе участвовала только родительская таблица, используйте ключевое слово ONLY:
SELECT name, altitude
FROM ONLY cities
WHERE altitude > 500;
-- Очистка таблиц (сначала надо удалить дочерние таблицы):
DROP TABLE capitals;
DROP TABLE cities;
Есть возможность определить рамку окна и другими способами, но в этом руководстве они не рассматриваются. Подробную информацию об этих способах см. в подразделе Вызовы оконных функций.