Ограничение максимального размера выборки

В PostgreSQL есть специфические конструкции LIMITи OFFSET, с помощью которых можно управлять количеством и порядком вывода записей. LIMIT ограничивает максимальное количества возвращаемых записей, а OFFSET задает номер записи в курсоре (0,1,2, …), начиная с которой будут выводиться записи. Они имеют следующий синтаксис:

LIMIT { число | ALL } [OFFSET начало]

где число - количество записей, ALL - все записи, начало - номер первой записи. Например, если число = 10 и начало = 3, то запрос вернет 10 записей, начиная с четвертой (номер 3) по порядку.

Использование данных типа timestamp

Данные типа timestamp используются в предложении WHERE для предотвращения одновременного обновления одной и той же строки двумя пользователями. Если при изменении значений данной строки поле типа timestamp также изменяется, то при одновременном редактировании строки двумя пользователями при сохранении одним из них обновленной информации поле timestamp изменится и строка не будет соответствовать прежней копии. Когда другой пользователь попытаетесь сохранить свои изменения, предложение WHERE не даст ему этого сделать, поскольку команда не найдет выбранную ранее строку. Данные типа timestamp - это хорошее средство для поддержания сервером уникальности каждой строки.

2.3. Выборка с упорядочением

Простейший вариант использования модификатора ORDER - упорядочение строк результата по значению одного из столбцов с указанием порядка сортировки или без такого указания (по умолчанию строки будут сортироваться в порядке возрастания значений в указанном столбце.). Например, выдать перечень книг из таблицы Книги в алфавитном порядке по фамилиям авторов:

SELECT “Код_книги”, “Автор”, “Название”FROM “Книги”ORDER BY “Автор”

или в порядке убывания цены:

SELECT “Код_книги”, “Цена”FROM “Книги”ORDER BY “Цена” DESC

При включении в список ORDER BY нескольких столбцов СУБД сортирует строки результата по значениям первого столбца списка пока не появится несколько строк с одинаковыми значениями данных в этом столбце. Такие строки сортируются по значениям следующего столбца из списка ORDER BY и т.д. Например, выдать содержимое таблицы Заказы, отсортировав ее строки по столбцам Код_заказчика и Дата:

SELECT *FROM “Заказы”ORDER BY “Код_заказчика”, “Дата”

Кроме того, в список ORDER BY можно включать не только имя столбца, но и его порядковую позицию в перечне SELECT. Номер столбца в списке элементов SELECT отсчитывается слева направо. Благодаря этому возможно упорядочение результатов на основе вычисляемых столбцов, не имеющих имен, например:

SELECT “Код_книги”, ”Цена” * 0.2FROM “Книги”ORDER BY 2 DESC

В списке выбора инструкции SELECT столбцы можно задавать различными способами, не учитывая порядок их следования в базовой таблице. Можно даже один и тот же столбец указывать несколько раз, например:

SELECT “Код_книги”, ”Автор”, “ Название”, “Издательство”, “Цена”, “Код_книги”FROM “Книги”ORDER BY 2, 3

Выдаваемые по этому запросу строки не помещаются полностью на экране, и поэтому при горизонтальной прокрутке первые столбцы выходят за пределы активного окна. Выводимый повторно в конце списка Код_книги облегчит работу с таким списком.

2.4. Выборка и агрегирование данных.

Для выполнения объединения данных по некоторому критерию используется модификатор GROUP BY, который делит таблицу на группы строк, каждая из которых имеет одинаковые значения в столбце, указанном в GROUP BY.

Пусть необходимо узнать, какие поставщики осуществляли поставки. Для этого можно, очевидно, ввести запрос

SELECT DISTINCT "Код_поставщика"

FROM "Поставки"

То же самое получим с помощью запроса

SELECT "Код_поставщика"

FROM "Поставки"

GROUP BY "Код_поставщика"

Однако, если теперь в список SELECT добавить еще одно поле, например, Код_книги, то получим сообщение об ошибке

ERROR: column "Поставки.Код_книги" must appear in the GROUP BY clause or be used in an aggregate function

т.е. поле Код_книги должно быть указано в строке GROUP BY или использоваться в агрегирующей функции. Это связано с тем, что SQL-функция создает единственное значение из множества значений столбца-аргумента, а для «свободного» столбца должно быть выдано все множество его значений. Поэтому подобный запрос отвергается системой.

Если добавить Код_книги в строку GROUP BY, то в группах с одинаковым значением Код_поставщика данные дополнительно будут сгруппированы по Код_книги.

Сама по себе группировка не имеет смысла - группировка (или агрегирование) обычно предшествует неким операциям, выполняемым над данными в группах. Такие операции вводятся с помощью агрегирующих SQL-функций: SUM - сумма, AVG - среднее, MIN - минимальное значение, MAX - максимальное значение, COUNT - количество.

Возвращаясь к последнему примеру, предположим, что необходимо получить данные о количестве книг, поставленных каждым поставщиком в отдельности. Это можно сделать с помощью запроса:

SELECT "Код_поставщика"', “Код_книги”, SUM(“Количество”) AS “Количество”

FROM "Поставки"

GROUP BY "Код_поставщика ", “Код_книги”

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

В столбце-аргументе перед применением любой функции, кроме COUNT(*), исключаются все неопределенные значения. Если оказывается, что аргумент — пустое множество, функция COUNT принимает значение 0, а остальные — NULL.

Предположим, что теперь требуется узнать, сколько книг каждого издательства представлено в базе данных BOOKSHOP. Для этого введем запрос:

SELECT “Издательство”, COUNT(*)FROM “Книги”GROUP BY “Издательство”

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

Т.о., GROUP BY инициирует перекомпоновку указанной таблицы по группам, далее к каждой группе применяется инструкция SELECT. Каждое выражение в списке SELECT должно принимать единственное значение для группы, т.е. оно может быть либо значением столбца, указанного в GROUP BY, либо арифметическим выражением, включающим это значение, либо константой, либо одной из скалярных SQL-функций, которая оперирует всеми значениями столбца в группе и сводит эти значения к единственному значению (например, к сумме, среднему значению и т.д.).

Модификатор GROUP BY не предполагает упорядочивание результата. Чтобы упорядочить результаты запроса, в конце инструкции надо добавить модификатор ORDER BY:

SELECT "Код_поставщика"', “Код_книги”, SUM(“Количество”) AS “Количество”

FROM "Поставки"

GROUP BY "Код_поставщика ", “Код_книги”

ORDER BY 1, 3

Строки таблицы можно группировать по любой комбинации ее столбцов. Так, в процессе выполнения запроса:

SELECT “Код_заказчика”, “Оплачен”, COUNT(*) AS “Количество заказов”FROM “Заказы”GROUP BY “Код_заказчика”, “Оплачен”ORDER BY 1

сначала выполняется группировка по значениям столбца Код_заказчика, затем в каждой из полученных подгрупп выполняется группировка по значениям столбца Оплачен и применяется агрегирующая функция COUNT, т.е. вычисляет для каждого заказчика количество оплаченных и неоплаченных заказов.

Если в запросе используются WHERE и GROUP BY, то строки, не удовлетворяющие условию WHERE, исключаются до выполнения группирования. Например, предыдущий запрос можно модифицировать так, чтобы он выдавал список заказчиков, оплативших заказы, и количество оплаченных ими заказов:

SELECT “Код_заказчика”, “Оплачен”, COUNT(*) “Количество заказов”FROM “Заказы”WHERE “Оплачен” = ‘ДА’GROUP BY “Код_заказчика”, “Оплачен”ORDER BY 1

Предложение HAVING играет такую же роль для групп, что и WHERE для строк: она используется для исключения групп, точно так же, как WHERE используется для исключения строк. Эта фраза включается в предложение лишь при наличии фразы GROUP BY, а выражение в HAVING должно принимать единственное значение для группы. Например, выдать список поставщиков, поставивших более двух книг:

SELECT “Код_поставщика”, COUNT(*) “Количество поставленных книг”FROM “Книги”GROUP BY “Код_поставщика”HAVING COUNT(*) > 2

2.5. Преобразование типов данных в инструкции SELECT.

В PostgreSQL существует четыре фундаментальные SQL-конструкции, требующие четких правил преобразования типов: вызовы функций, применение операторов, присваивание значений при вставке и модифицировании данных, применение конструкций UNION (объединение результатов запросов) и CASE (аналог оператора if..then..else).

В PostgreSQL поддерживаются три варианта синтаксиса явного преобразования (приведения) типов:

· для строковых констант

тип 'значение'

'значение'::тип

CAST ('значение' AS тип)

· для числових констант

значение:: тип

CAST (значение AS тип)

· для полей набора данных, возвращаемых запросом SQL

идентификатор:: тип

CAST (идентификатор AS тип)

Пример:

SELECT ("Остаток"+10)::float * 1.1,

"Цена"::char(8) || char(4) ' UAH', -- конкатенация двух строк

CAST("Цена" AS text)

from "Книги"

Следующий пример демонстрирует использование функций преобразования типов для построения строкового выражения:

SELECT 'От ' || '01/01/2011' || ' до ' || CAST(now() AS varchar(64)) || ' прошло ' || (CAST (now() AS varchar(64)):: date - date '01/01/2011'):: text || ' дней'

Возможно также использование преобразования типов в предложении WHERE. Следующий запрос возвратит данные об январских поставках в базе данных BookShop:

SELECT *

FROM "Поставки"

WHERE CAST("Дата" AS varchar) LIKE '%2011-01-%'

Тот же результат получается с помощью стандартной функции extract:

SELECT *

FROM "Поставки"

WHERE extract(month from "Дата") = 1 AND extract(year from "Дата") = 2011

Кроме синтаксических форм преобразования типов существуют некоторые функции, позволяющие добиться практически того же результата. Имена этих функций часто совпадают с именами итоговых типов, например, text() или timeofday().