Агрегирование данных при выборке

Функции:

COUNT – подсчитывает количество строк, удовлетворяющих условиям запроса

SUM – подсчитывает сумму значений указанного выражения по строкам, удовлетворяющим условиям запроса

AVG – подсчитывает среднее значение указанного выражения по строкам, удовлетворяющим условиям запроса

MAX, MIN – подсчитывают соответственно максимальное и минимальное значения указанного выражения по строкам, удовлетворяющим условиям запроса

select COUNT (*) FROM TREADER

select COUNT (DISTINCT READER)

FROM TBOOK_READER

select SUM (PRICE) FROM NAKLS

where NAKLS.NUM > 36

Пример:

Выборка списка читателей и взятых ими книг

SELECT A.RDNUMB, A.RDNAME, B.BOOKNM

FROM TREADER A, TBOOK B, TBOOK_READER AB

where AB.READER = A.UNIKEY

AND AB.BOOKKEY = B.UNIKEY

B таблице TBOOK и TREADER ключи имеют одинаковое название UNIKEY.

Представления

Представление (View) – это виртуальная таблица, созданная на основе запроса к обычным таблицам. Представление реализуется как запрос, хранящийся на сервере и выполняющийся всякий раз, когда происходит обращение к представлению.

Представления дают возможность создавать уровни организации данных, позволяющие отделить реализацию хранения данных от их вида. Например, можно создать представление, которое выбирает данные из нескольких таблиц. Если клиенты используют это представление, а не напрямую обращаются к лежащим в его основе таблицам, то у разработчика БД появляется возможность менять запрос (например, с целью оптимизации), что не будет замечать клиент – для него это будет все то же представление.

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

Для создания и удаления представлений существуют команды DDL.

Чтобы создать представление необходимо использовать предложение:

CREATE VIEW viewname [(view_column [, view_column...])]

AS, которое выбирает данные, включаемые в представление. Необязательный параметр WITH CHECK_OPTION позволяет проверять каждую строку данных модифицируемого представления на условие «попадания» в представление.

Пример:

CREATE VIEW MyView

AS

SELECT NAME, PRICE

FROM Table_example;

Число записей в представлении MyView будет равно числу записей в таблице Table_example.

Рассмотрим пример:

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

Н а рисунке 39 представлена реляционная диаграмма для вышеприведенного примера

WISEMEN

ID_WISEMEN WISEMEN_NAME

0,N 1,1

WISEBOOK

ID_BOOK ID_WISEMEN BOOK

Рис. 39 Реляционная диаграмма

Тогда соответствующее данной реляционной диаграмме представление имеет вид:

CREATE VIEW WiseBookCount

(WISEMEN, HOW_WISEBOOKS)

AS

SELECT M.WISEMEN_NAME, COUNT(B.BOOK)

FROM WISEMEN M, WISEBOOK B

WHERE (M.ID_WISEMEN=B.ID_WISEMEN)

GROUP BY M.WISEMEN_NAME

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

Для извлечения данных из представления используются предложения SELECT.

SELECT * FROM WiseBookCount

ORDER BY HOW_WISEBOOKS

Установление того, какое поле, возвращаемое запросом (в представлении) соответствует какому полю представления, осуществляется по порядковому номеру – первое поле запроса отобразится в первое поле представления, второе – во второе и т. д.

Для вывода списка мудрецов, чье имя начинается с буквы «К» можно использовать следующее представление:

CREATE VIEW WiseMen (WISEMEN)

AS

SELECT M.WISEMEN_NAME

FROM WISEMEN M

WHERE M.WISEMEN_NAME LIKE ‘K%’;

В представлениях необходимо использовать явное именование полей (с указанием таблицы).

Новые встроенные функции

В СУБД Firebird добавлены новые встроенные функции из стандарта SQL-99 — CASE, COALESCE и NULLIF.

CASE является базовой для этого ряда функций и реализует условную выборку значений из таблиц, например:

SELECT CASE O.TYPE WHEN 0 THEN ‘Доход’ ELSE ‘Расход’ END FROM OPERATIONS O

или так

SELECT CASE WHEN (O.TYPE = 0) THEN ‘Доход’ ELSE ‘Расход’ END FROM OPERATIONS O

Следует отметить, что у функции CASE может быть любое количество аргументов, т.е. Она не ограничивается только простейшей проверкой типа ‘ЕСЛИ-ИНАЧЕ’, например:

SELECT CASE O.TYPE WHEN 0 THEN ‘Доход’ WHEN 1 THEN ‘Расход’ ELSE ‘—-’ END FROM OPERATIONS O

Функция COALESCE является упрощением CASE для проверки на NULL

SELECT COALESCE(O.STATUS, ‘—-’) FROM OPERATIONS O

Результатом COALESCE будет являться первый аргумент, если он не NULL, или второй аргумент в противном случае.

Функция NULLIF также является упрощением CASE, но для иного случая

SELECT NULLIF(O.STATUS1, O.STATUS2) FROM OPERATIONS O

Результатом NULLIF будет являться NULL, если оба аргумента равны, или первый аргумент в противном случае.

Диалект базы данных

Выбор диалекта базы данных для СУБД Firebird очень важен.

Диалект 1 и Диалект 3 отличаются друг от друга следующими принципиальными особенностями:

Диалект 3 позволяет использовать расширенный набор типов данных, таких как типы для работы с большими целыми числами (ВigInt – 64 разряда), типы для работы с датой и временем DATE и TIME.

Диалект 3 различает регистр идентификаторов, если идентификатор заключён в двойные кавычки. Table1 и TABLE1 в обоих диалектах будут равнозначны, а вот “Table1” и “TABLE1” (TABLE1) сервер будет интерпретировать как разные идентификаторы.

Диалект 3 не поддерживает неявное приведение типов данных (как в диалекте 1). Это означает, что в Диалекте 1 выражение ‘25’+5 будет корректным, и в результате мы получим 30. В Диалекте 3 это выражение вызовет ошибку несоответствия типов.

В Диалекте 3 двойные кавычки применяются только для обозначения идентификаторов (названия таблиц), а одинарные – для строковых констант. В Диалекте 1 строковые константы можно заключать как в одиночные, так и в двойные кавычки.

В Диалекте 3 используются новые ключевые слова (зарезервированные слова): COLUMN, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, DAY, EXTRACT, HOUR, MINUTE, MONTH, SECOND, TIME, TIMESTAMP, WEEKDAY, YEAR, YEARDAY.

Арифметические операции в диалекте 3 взяты из стандарта SQL-92. Например, деление целого на целое в диалекте 3 возвращает усечённое целое, в то время как в диалекте 1 оно вернёт число с плавающей точкой двойной точности.

Генераторы в диалекте 3 могут иметь тип bigint (64-битовые целые числа), а в диалекте 1 генераторы – 32-битовые целые.

Сам сервер не имеет диалекта – диалект базы данных является атрибутом базы данных. Он является интерфейсом клиента, который определяет, какой набор возможностей запрашивается у базы данных.

Выбор диалекта, в котором будет создаваться база данных, важен по той причине, что переход между разными диалектами – занятие достаточно нетривиальное и трудоёмкое. Иными словами, если есть возможность, то лучше сразу выбрать верный диалект.

В общем случае при выборе диалекта следует руководствоваться следующими правилами:

выбираем Диалект 3, если проектируется база данных для приложения, которое будет использовать только современные библиотеки прямого доступа к InterBase/FireBird, которые полностью поддерживают Диалект 3.

выбираем Диалект 1, если важна совместимость с более ранними библиотеками доступа к InterBase/FireBird, такими как BDE.

Может возникнуть вопрос, почему диалекты бывают 1 и 3, а где же 2? Диалект 2 действительно существует, но используется в качестве промежуточного этапа при миграции с Диалекта 1 на Диалект 3.

Типы с фиксированной точкой

Firebird предоставляет два типа данных чисел с фиксированной точкой NUMERIC и DECIMAL. Каждый тип объявляется как TYPE (P, S), где Р определяет точность (количество значащих цифр), а S – масштаб (количество цифр справа от символа десятичной точки).

Типы NUMERIC и DECIMAL, как они реализованы в FIREBIRD, являются идентичными, за исключением случая, когда точность меньше пяти. NUMERIC не соответствует стандарту SQL-92.

FIREBIRD хранит масштабированное число как тип SMALLINT (16 бит), INTEGER (32 бита) или BIGINT (64 бита) в соответствии с объявленным масштабом (размером точности). Его объявленная точность сохраняется вместе с объявленным масштабом в виде отрицательного множителя масштаба, представляющего степень числа 10. Когда к числу происходит обращение для вывода или для расчётов, оно получается произведением хранимого целого на 10 множитель масштаба.

Например, для столбца, объявленного как NUMERI (4, 3), FIREBIRD сохраняет число в виде SMALLINT. Если вы вводите число 7.2345, FIREBIRD без сообщения округляет самую правую цифру (4) и сохраняет 16-битовое целое 7235 и множитель масштаба -3. Это число будет найдено как 7.235 (7235*10^-3).

Числа в формате NUMERIC с точностью меньше 5 хранятся как SMALLINT (16 разрядов). Числа в формате DECIMAL с точностью меньше 5 хранятся как INTEGER (32 разряда).

Кроме того, числа в Диалекте 1 с точностью 10 – 18 хранятся как DOUBLE PRECISION, а в Диалекте 3 – как BIGINT.

При выполнении деления типов с фиксированной точкой диалекты 1 и 3 ведут себя по-разному.

В Диалекте 3, когда оба операнда при операции деления являются типами с фиксированной точкой, FIREBIRD суммирует масштабы обоих операндов для определения масштаба результата (частного).

В Диалекте 1 деление всегда создаёт частное типа DOUBLE PRECISION.

Примеры. В диалекте 3 частное от деления DECIMAL (12.3) на DECIMAL (9,2) будет DECIMAL (18,5). (Масштабы суммируются.)

11223344.556 / 1234567.89 = 9.09090

В диалекте 1 первое число трактуется как число DOUBLE PRECISION (точность 12). Частное тоже имеет тип DOUBLE PRECISION. Результат

9.09090917308727

(по причине ошибок, присущих типам с плавающей точкой).

Следующие операции деления в Диалекте 3 дают различные результаты:

1 / 3.00 = 0.33 типа NUMERIC (18, 2)

потому что сумма масштабов равна 0+2 = 2

типа типа

Integer Numeric (18,2)

1.00 / 3.00 = 0, 3333 типа NUMERIC (18, 4)

потому что сумма масштабов равна 2+2 = 4

NUMERIC(18,2)

При выполнении умножения операндов с фиксированной точкой масштаб результата равен сумме масштабов операндов.

123.123 результат имеет масштаб 5 (2+3)´12.12

NUMERIC(9,2) NUMERIC(9,3)

= 1492.25076

В Диалекте 3 точность умножения чисел с фиксированной точкой будет равна 18. В Диалекте 4 если точность результата равна > 9, то результатом будет DOUBLE PRECISION.

При сложении и вычитании масштаб результата равен максимальному масштабу операндов.

12.12 + 123.123 = 135.243 будет иметь масштаб 3,

а

12.12 – 123.123 = – 111.003

В Диалекте 3 результат любого сложения или вычитания имеет тип NUMERIC(18, n). В Диалекте 1 он имеет тип (9, n), где n – максимальный масштаб операнда.