Вычисляемые столбцы и команда GROUP BY

Вычисляемые столбцы - это выражения, выполняющие те или иные действия над наборами данных. Для этих целей используются функции COUNT, SUM, AVG, MAX и MIN, которые также называют агрегатными. Для примера рассмотрим использование функций COUNT, SUM и AVG.

Функция COUNT выполняет подсчет строк. В приведенном ниже запросе с помощью функции COUNT определяется максимальный объем продаж офисов

SELECT MAX(sales)

FROM zakazy.offisy

Далее приведен более сложный пример, определяющий количество клиентов, обслуживаемых служащими

SELECT s.family, count(c.id_cln)

FROM zakazy.clienty c

LEFT JOIN zakazy.sluzhaschie s on c.id_slzh = s.id_slzh

GROUP BY s.family

Результат выполнения этого запроса приведен на Рис. 22.

Рис. 22. Результат выполнения запроса с вычисляемым столбцом

В предыдущем выше примере мы применили операцию группировки записей – GROUP BY. Она позволяет создать итоговый запрос. Обычный запрос включает в результаты запроса по одной записи для каждой строки таблицы. Итоговый запрос вначале группирует строки по определенному признаку, а затем включает в результаты запроса одну итоговую строку для каждой группы. Столбцы, указанные в предложении GROUP BY, называются столбцами группировки, так как они определяют признак, по которому строки делятся на группы.

Сортировка записей

Операция ORDER BY используется для сортировки возвращаемого набора данных.

Следующий пример сортирует список служащих по фамилии

SELECT *

FROM zakazy.sluzhaschie

ORDER BY family

Результат выполнения запроса:

Рис. 23. Результат выполнения запроса с сортировкой записей

Конструкция CASE

Чтобы программа SQL могла принимать простейшие решения, не прибегая к про­цедурным языкам, в PostgreSQL поддерживаются конструкции CASE, предусмот­ренные стандартом SQL Ключевые слова SQL CASE, WHEN, THEN и END позволяют вы­полнять простые условные преобразования записей.

Вся конструкция CASE включается в целевой список команды SELECT. По умол­чанию итоговому полю конструкции CASE присваивается имя case, но ему можно назначить синоним, как любому обычному полю. Общий синтаксис конструкции CASE в списке целей команды SELECT выглядит следующим образом:

CASE WHEN условие1 THEN результат1

WHEN условие2 THEN результат2

[ ... ]

[ ELSE результат_по^умолчанию END [ AS синоним ]

Конструкция CASE-WHEN-THEN-ELSE отчасти напоминает условные команды if-then-else в традиционных языках программирования. Условия секций WHEN должны возвращать логический результат.

Если условие в секции WHEN выполняется, результат соответствующей секции THEN возвращается в поле итогового набора. Если ни одно условие не выполнено, можно задать значение по умолчанию в секции ELSE. Если при отсутствии секции ELSE результат остается неопределенным, возвращается NULL.

Пример.

SELECT description,

CASE WHEN count_tvr < 25 THEN 'на складе меньше 25 единиц'

WHEN count_tvr = 25 THEN 'на складе ровно 25 единиц'

ELSE 'на складе больше 25 единиц'

END

FROM zakazy.tovary

Рис. 24. Результат выполнения запроса с конструкцией CASE

Подзапросы

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

Подзапросы могут находиться практически в любой части команды SQL — в списке целей, в секции WHERE и т. д. В следующем примере (Рис. 25) приведен простой пример использования подзапроса для выборки критерия поиска из другой таблицы.

SELECT *

FROM zakazy.clienty

WHERE id_slzh = (SELECT id_slzh FROM zakazy.sluzhaschie

WHERE family = ‘Пронин’ AND name = ‘Игорь’)

Рис. 25. Результат выполнения запроса с подзапросом

Оператор = сравнивает id_slzh подзапроса к таблице sluzhashie с полем id_slzh таблицы clienty. Подзапрос находит в таблице sluzhashie запись о служащим с фамилией Пронин и именем Игорь; сравнивая поле id_slzh этой записи с полем id_slzh таблицы clienty, мы отбираем все компании, связанные с данным служащим.

Построение подзапросов требует осторожности: чтобы результат запроса про­верялся простым оператором, подзапрос должен возвращать только одну запись. Например, если использовать для выборки идентификатор служащего более общий запрос, воз­вращающий несколько записей, PostgreSQL выдаст сообщение об ошибке.

Рис. 26. Результат выполнения запроса с подзапросом

Если требуется проверить присутствие отдельной величины в заданном набо­ре, замените оператор = ключевым словом IN. Следующий подзапрос (Рис. 26), выбирает несколько значений, сравниваемых с полем id_slzh (для служащих, должность которых Ст.Брокер). Сравнение осуществляется при помощи ключевого слова IN.

SELECT *

FROM zakazy.clienty

WHERE id_slzh = (SELECT id_slzh FROM zakazy.sluzhaschie

WHERE dlzh = ‘Гл.Брокер’)

Работа с представлениями

Представление таблицы SQL создается с помощью оператора SELECT, после чего с полученным набором данных можно обращаться как с таблицей и применять к нему операторы SELECT. В самом представлении не хранится никаких данных - это всего лишь маленькая SQL-программа.

Представления создаются с помощью SQL-оператора CREATE VIEW следующего вида

CREATE VIEW имя_представления (список_столбцов) AS

SELECT ...

Рассмотрим пример создания представления, созданного на базе двух таблиц.

CREATE VIEW zakazy.v_officy AS

SELECT o.id_ofc, o.city, o.target, o.sales, s.id_slzh,

s.family, s.name, s.dlzh, s.quota, s.sales AS sales_slzh

FROM zakazy.offisy o

LEFT JOIN zakazy.sluzhaschie s ON o.id_ofc = s.id_ofc;

Созданное таким образом представление вызывается как обычная таблица. На Рис. 27 приведен пример запроса, выполняющего выборку данных представления v_officy

Рис. 27. Запрос, выполняющий выборку данных представления v_officy

Хранимые процедуры

Хранимая процедура − это отдельная программа, написанная на процедурном языке используемого сервера БД. Существует две разновидности хранимых процедур:

– процедуры выбора (аналог SELECT-запросов), используются в операторе SELECT вместо таблицы и, следовательно, возвращающие наборы данных (в заголовке процедуры обязательно должен быть определен набор выходных параметров);

– исполняемые процедуры, предназначенные для исполнения команд, например, UPDATE.

Синтаксис, применяемый при создании хранимых процедур, варьируется в зависимости от SQL-сервера. В данном учебном пособии мы рассмотрим правила создания хранимых процедур при помощи PL/pgSQL.

PL/pgSQL (Procedural Language/PostGres Structured Query Language - процедурное расширение языка SQL, используемое в СУБД PostgreSQL. Этот язык предназначен для написания функций, триггеров и правил и обладает следующими особенностями:

– добавляет управляющие конструкции к стандарту SQL;

– допускает сложные вычисления;

– может использовать все объекты БД, определенные пользователем;

– прост в использовании.

Язык PL/pgSQL имеет относительно простую структуру, что объясняется в ос­новном тем, что каждый логически обособленный фрагмент кода существует в виде функции. Хотя на первый взгляд PL/pgSQL мало похож на другие языки програм­мирования (такие, как язык С), сходство все же существует: логические фрагмен­ты создаются и выполняются в виде функций, все переменные обязательно объяв­ляются перед использованием, функции получают аргументы при вызове и возвращают некоторое значение в конце своей работы.

Регистр символов в именах функций PL/pgSQL не учитывается. В ключевых словах и идентификаторах допускается использование произвольных комбинаций символов верхнего и нижнего регистров. Также обратите внимание на частое уд­воение апострофов во многих местах этой главы - всюду, где обычно используют­ся одиночные апострофы. Удвоение экранирует апострофы в определениях функ­ций, поскольку определение функции в действительности представляет собой большую строковую константу в команде CREATE FUNCTION

Не нашли, что искали? Воспользуйтесь поиском по сайту: