Хелен Борри - Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ
Чтобы улучшить производительность, используйте условия WHERE для предварительной фильтрации групп, a HAVING используйте для фильтрации результатов, полученных от агрегатных выражений. Например, группа, полученная при использовании выражения SUM(X), может быть отфильтрована с помощью HAVING SUM(X) > <минимальное-значение>. Следовательно, предложение HAVING обычно принимает агрегатное выражение в качестве своего аргумента.
Взяв предыдущий запрос, мы можем использовать предложение WHERE для фильтрации групп проектов, которые появляются в выходном наборе, и использовать предложение HAVING для установки начального диапазона сумм, которые мы хотим просматривать:
SELECT
PROJ_ID
SUM(PROJECTED_BUDGET) AS TOTAL_BUDGET
FROM PROJ_DEPT_BUDGET
WHERE FISCAL_YEAR = 1994
AND PROJ_ID STARTING WITH 'M'
GROUP BY PROJ_ID
HAVING SUM(PROJECTED BUDGET) >= 100000;
PROJ_ID TOTAL_BUDGET
MAPDB 111000.00
MKTPR 1480000.00
Предложение HAVING может принимать сложные аргументы, содержащие логические операции AND и OR, которые используют ту же логику приоритетов, что и предложение WHERE.
Подвыражение COLLATE
Если вам нужно сгруппировать текстовый столбец с использованием последовательности сортировки, отличной от той, которая была определена по умолчанию для этого столбца, вы можете включить предложение COLLATE. Подробную информацию о COLLATE см. в разд. "Последовательность сортировки" главы 11.
Использование ORDER BY в группирующем запросе
Начиная с Firebird версии 1.5, элементы в списке ORDER BY В группирующем запросе должны быть либо агрегатными функциями, допустимыми в контексте группирования, либо элементами, которые представлены в списке GROUP BY.
Firebird 1.0.x является менее ограничивающим- он допускает упорядочение по элементам или выражениям, находящимся вне контекста группирования.
Улучшенные условия группирования
Firebird 1.5 и более поздние версии поддерживают некоторые дополнительные условия группирования, недоступные в версии 1.0.x.
Подзапросы во встроенных агрегатах
Группируемое поле, которое связано с выражением подзапроса, может содержать агрегатное выражение, ссылающееся на элемент агрегатного выражения в списке
GROUP BY.
В следующем примере реентерабельный подзапрос к системной таблице RDB$RELATION_FIELDS содержит агрегатное выражение (MAX(I:.RDB$FIELD_POSITION)), результат которого используется для локализации имени (RDB$FIELD_NAME) столбца, имеющего наибольший номер позиции для каждой таблицы (RDB$RELATION_NAME) в базе данных:
SELECT
r.RDB$RELATION_NAME,
MAX(r.RDB$FIELD_POSITION) AS MAXFIELDPOS,
(SELECT
r2,RDB$FIELD_NAME FROM RDB$RELATION_FIELDS r2
WHERE
r2.RDB$RELATION _NAME = r.RDB$RELATION_NAME
and r2.RDB$FIELD_POSITION = MAX(r.RDB$FIELD_POSITION)) AS FIELDNAME
FROM RDB$RELATI ON_FIELDS r
/* мы используем предложение WHERE для фильтрации системных таблиц */
WHERE r,RDB$RELATION NAME NOT STARTING WITH 'RDB$'
GROUP BY 1;
RDB$RELATION_NAME
MAXFIELDPOS
FIELDNAME
===========
============
=========
COUNTRY
1
CURRENCY
CROSS_RATE
3
UPDATE_DATE
CUSTOMER
11
ON_HOLD
DEPARTMENT
6
PHONE_NO
EMPLOYEE
10
FOLL_NAME
EMPLOYEE_PROJECT
1
PROJ_ID
JOB
7
LANGUAGE_REQ
PHONE_LIST
5
PHONE_NO
PROJECT
4
PRODUCT
PROJ_DEPT_BUDGET
4
PROJECTED_BUDGET
SALARY_HISTORY
5
NEW_SALARY
SALES
12
AGED
Теперь в тех же самых текстах мы используем COUNT() для подсчета количества столбцов, хранимых в каждой таблице:
SELECT
rf.RDB$RELATION_NAME AS "Table Name", (
SELECT
r.RDB$RELATION_ID
FROM RDB$RELATIONS r
WHERE r.RDB$RELATION_NAME = rf.RDB$RELATION_NAME) AS ID,
COUNT(*) AS "Field Count"
FROM RDB$RELATION_FIELDS rf
WHERE rf,RDB$RELATION_NAME NOT STARTING WITH 'RDB$'
GROUP BY
rf.RDB$RELATION NAME;
Table Name
ID
Field Count
COUNTRY
128
2
CROSS_RATE
139
4
CUSTOMER
137
12
DEPARTMENT
130
7
... и т.д.
Агрегаты во встроенных подзапросах
Выражение агрегатной функции- COUNT(), AVG() и т.д.- может принимать аргумент, который является выражением подзапроса, возвращающим скалярное значение. Например, далее результат запроса SELECT COUNT(*) передается на более высокий уровень в выражение SUMO, которое для каждой таблицы (RDB$RELATION_NAME) выводит произведение счетчика полей на количество индексов в таблице:
SELECT
r.RDB$RELATION_NAME,
SUM((SELECT COUNT(*) FROM RDB$RELATION_FIELDS rf
WHERE rf,RDB$RELATION_NAME = r.RDB$RELATION_NAME))
AS "Fields * Indexes"
FROM RDB$RELATIONS r
JOIN RDB$INDICES i
ON (i.RDB$RELATION_NAME = r.RDBSRELATION_NAME)
WHERE r.RDB$RELATION_NAME NOT STARTING WITH 'RDB$'
GROUP BY r.RDB$RELATION_NAME;
RDB$RELATION NAME Fields * Indexes
COUNTRY 2
CROSS_RATE 4
CUSTOMER 48
DEPARTMENT 35
... и т.д.
Агрегаты на смешанных уровнях группирования
Агрегатные функции для различных уровней группирования могут быть перемешаны в одном и том же группирующем запросе.
В следующем примере результат выражения, полученный из подзапроса, который выполняет COUNT() для столбца на самом низком уровне группы (RDB$INDICES), передается на уровень группирования. Предложение HAVING выполняет фильтрацию, определяемую двумя другими агрегатными функциями на нижнем уровне группы.
SELECT
r.RDB$RELATION_NAME,
MAX(i.RDB$STATISTICS) AS "Maxl",
/* одно агрегатное выражение, вложенное в другое */
(SELECT COUNT(*) || ' - ' || MAX(i.RDB$STATISTICS)
FROM RDB$RELATION_FIELDS rf
WHERE rf.RDB$RELATION_NAME = r.RDB$RELATION_NAME) AS "Max2"
FROM
RDB$RELAT IONS r
JOIN RDB$INDICES i
ON (i.RDB$RELATION NAME = r.RDB$RELATION_NAME)
WHERE r.RDB$RELATION_NAME NOT STARTING WITH 'RDB$'
GROOP BY
r.RDB$RELATION_NAME
HAVING
MIN(i.RDB$STATISTICS) MAX(i.RDB$STATISTICS);
RDB$RELATION NAME Maxl Max2
MTRANSACTION 000000000000001 18 - 1.000000000000000
MEMBER 0.0135135138407 12 - 0.01351351384073496
! ! !
ВНИМАНИЕ! Вы можете получить результат при выполнении этого запроса в Firebird 1.0.x, однако он будет некорректным.
. ! .
Вложенные агрегатные функции
Агрегатное выражение может быть вложено внутрь другого агрегатного выражения, если внутренняя агрегатная функция находится на более низком уровне (контексте), что иллюстрируется предыдущим запросом.
Пора дальше
В этой главе по языку манипулирования данными мы рассмотрели возможности SQL по трансформации абстрактных данных, хранимых в таблицах, в информацию, которую конечный пользователь читает в осмысленных контекстах. При некоторых условиях есть смысл сохранять постоянные определения полезных выходных наборов (виртуальных таблиц), чтобы не изобретать колесо каждый раз, когда требуется похожий набор. В следующей главе рассматриваются способы осуществления этого, в том числе и возможности просмотров.
ГЛАВА 24. Просмотры.
В терминологии SQL-89 и SQL-92 просмотр является стандартным типом таблицы, он также называется просматриваемой или виртуальной таблицей. Он характеризуется как виртуальный, потому что вместо того, чтобы хранить табличный объект и выделять страницы для хранения данных, сервер Firebird сохраняет только описание метаданных объекта. Оно содержит уникальный идентификатор, список спецификаций столбцов и компилированный оператор SELECT для поиска описанных в этих столбцах данных во время выполнения.
Что такое просмотр?
По своей природе просмотр является спецификацией таблицы, которая не хранит данные. Он действует как фильтр для столбцов и строк таблиц, на которые ссылается просмотр, - "окно", через которое просматриваются фактические данные. Запрос, определяющий просмотр, может обращаться к одной или более таблицам или к другим просмотрам базы данных. Во многих случаях он ведет себя как постоянная таблица и инкапсулирует некоторые специальные расширения для связи с лежащими в его основе таблицами.
Вы вызываете просмотр, как если бы он был обычной таблицей, выполняете соединения, упорядочиваете и группируете выход, задаете условия поиска, используете в качестве подзапроса, наследуете столбцы времени выполнения из его виртуальных данных, используете выбранный из него именованный или неименованный курсор и т.д.
Многие запросы могут быть "изменяемыми", позволяя изменять состояние лежащих в их основе постоянных таблиц, или они могут быть сделаны изменяемыми через триггеры. Когда подтверждаются изменения данных в таблицах, содержимое данных просмотра изменяется вместе с ними. При подтверждении изменения данных просмотра данные лежащих в его основе таблиц изменяются соответствующим образом.
Ключи и индексы
Просмотры не могут иметь ключей или индексов. Лежащие в их основе таблицы, называемые базовыми таблицами, будут использованы как источник индексов при конструировании оптимизатором планов запроса. Тема плана запроса для запросов, включающих просмотры, весьма запутана. Она обсуждается позже в этой главе в разд. "Использование планов запроса для просмотров".
