Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ
Шрифт:
. ! .
В Firebird существует группа агрегатных (обобщающих) функций, которые обычно используются вместе с условиями группирования для вычисления итогов и статистики на уровне группы.
Агрегатными функциями являются SUMO, которая вычисляет итоги, MINO и MAXO, отыскивающие наименьшее и наибольшее значение соответственно, и AVGO, вычисляющая среднее значение. Функция COUNT также ведет себя как агрегатная функция в группирующих запросах, возвращая счетчик строк для всех строк ниже контекста (уровня)группы.
В отличие от других группируемых элементов агрегатное выражение в списке SELECT не может
Таблица PROJ_DEPT_BUDGET содержит строки пересечения проектов и отделов. Мы заинтересованы в отыскании итога по бюджетам, выделенным каждому проекту, независимо от отдела. Следующий список элементов, который рассматривался ранее в этом разделе, задает список полей из двух нужных нам элементов:
SELECT
PROJ_ID,
SUM(PROJECTED_BUDGET) AS TOTAL_BUDGET
FROM PROJ_DEPT_BUDGET
WHERE FISCAL_YEAR = 1994
GROUP BY PROJ_ID;
Эти две спецификации поля хороши в качестве группируемых элементов. Идентификатор отдела (DEPT_NO) не присутствует в списке, потому что список задает нужные нам по проекту итоги. Для получения этих итогов мы используем аргумент PROJ_ID В предложении GROUP BY.
С другой стороны, если мы хотим получить список бюджетов отделов, независимых от проектов, список полей должен включать DEPT_NO, чтобы он был аргументом в предложении GROUP BY:
SELECT
DEPT_NO,
SUM(PROJECTED_BUDGET) AS TOTAL_BUDGET
FROM PROJ_DEPT_BUDGET
WHERE FISCAL_YEAR = 1994
GROUP BY DEPT_NO;
В агрегатных выражениях типа SUMO, AVG и COUNT(<имя-столбца>) строки, содержащие NULL в соответствующем столбце, игнорируются. Функция AVG создает числитель, суммируя все непустые значения, и знаменатель, подсчитывая строки, содержащие непустые значения.
! ! !
ПРИМЕЧАНИЕ. Если у вас есть столбцы, по которым вы собираетесь вычислять среднее значение, важно решить в процессе проектирования, как при вычислении среднего значения вы будете трактовать "пустые" экземпляры - как NULL (они будут исключены из вычисления) или как ноль. Вы можете реализовать нужное правило, используя значение по умолчанию или (лучше) триггер BEFORE INSERT [86] .
. ! .
86
В этом случае также желателен и триггер BEFORE UPDATE.
– Прим. перев.
Группирующий элемент
Предложение GROUP BY получает список группирующих элементов:
* в Firebird 1.0.x группирующим элементом может быть только имя столбца или соответствующее выражение внешней функции (UDF);
* Firebird 1.5 расширил диапазон группирующих элементов, добавив также номер, порядковое числительное, представляющее номер позиции (слева направо) соответствующего элемента из списка полей SELECT. Этот номер может быть использован вместе с существующей возможностью для аргументов ORDER BY;
* версии Firebird 1.5 и более поздние также имеют возможность группировать по большинству функциональных выражений, таких как CAST. EXTRACT,
SUBSTRING, UPPER, CASE и COALESCE.
Следующий оператор завершает создание запроса, начатого в предыдущем примере:
SELECT PROJ_ID,
SUM(PROJECTED_BUDGET) AS TOTAL_BUDGET
FROM PROJ_DEPT_BUDGET
WHERE FISCAL_YEAR = 1994
GROUP BY PROJ_ID;
PROJ_ID TOTAL_BUDGET
GUIDE 650000.00
HWRII 520000.00
MAPDB 111000.00
MKTPR 1480000.00
VBASE 2600000.00
Группирующим
элементом не может быть выражение, включающее агрегатную функцию, такую как AVG, SUM, MAX, MIN или COUNT - они выполняют обобщение в том же группирующем контексте (на том же уровне), что и группирующий элемент. Это ограничение включает любые агрегатные выражения, которые встроены внутрь другого выражения. Например, синтаксический анализатор DSQL будет "ругаться", если вы попытаетесь сделать следующее:SELECT
PROJ_ID,
SUM(PROJECTED_BUDGET) AS TOTAL_BUDGET
FROM PROJ_DEPT_BUDGET
WHERE FISCAL_YEAR = 1994
GROUP BY 2;
ISC ERROR CODE:335544569
Cannot use an aggregate function in a GROUP BY clause
(Нельзя использовать агрегатную функцию в предложении GROUP BY)
Очень "вредная" функция COUNT используется разумно в группирующем запросе для подсчета количества для групп. Рассмотрим следующую модификацию нашего примера. Столбец DEPT_NO в наших требованиях не является подходящим кандидатом ни для группирующего, ни для группируемого элементов, однако он может дать информацию о таких элементах в контексте группирования по PROJ_ID:
SELECT PR0J_ID,
SUM (PROJECTED_BUDGET) AS T0TAL_BUDGET,
COUNT (DEPT_N0) AS NUM_DEPARTMENTS
FROM PROJ_DEPT_BUDGET
WHERE FISCAL_YEAR = 1994
GROUP BY PROJ_ID;
PROJ_ID | TOTAL_BUDGET | NUM_DEPARTMENTS |
====== | ====== | ====== |
GUIDE | 650000.00 | 2 |
HWRII | 520000.00 | 3 |
MAPDB | 111000.00 | 3 |
MKTPR | 1480000.00 | 5 |
VBASE | 2600000.00 | 3 |
Возможность использования для группирования выражений внутренних и внешних функций открывает широкий диапазон возможностей преобразования хранимых атрибутов для генерации выходных наборов, которые нельзя получить другим путем.
Например, внутренняя функция EXTRACT оперирует с типами данных даты и времени и возвращает части даты - числа, которые выделяют год, месяц, день, час и т.д. из подобных типов данных. Следующий пример для версии 1.5 запрашивает таблицу MEMBERSHIP и выводит статистику, показывающую количество участников, объединенных в каждом месяце, независимо от года или для даты их объединения: