19.2. Агрегатные функции, группировка и условия раздела having

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

19.2.1. Семантика агрегатных функций

Агрегатные функции (в стандарте SQL они называются функциями над множествами)¹⁴¹⁾определяются следующими синтаксическими правилами:

<set_function_specification> ::=

COUNT(*)

| set_function_type ([DISTINCT | ALL ] value_expression)

| GROUPING (column_reference)

<set_function_type> ::=

{ AVG | MAX | MIN | SUM | EVERY | ANY | SOME | COUNT }

Как видно из этих правил, в стандарте SQL:1999 определены пять стандартных агрегатных функций: COUNT– число строк или значений,MAX– максимальное значение,MIN– минимальное значение,SUM– суммарное значение иAVG– среднее значение, а также две «кванторные» функцииEVERYиSOME(ANY). В последних двух случаях выражение должно иметь булевский тип. Обсуждение функцииGROUPINGмы отложим до следующей лекции.

Агрегатные функции предназначены для того, чтобы вычислять некоторое значение для заданного мультимножества строк. Таким мультимножеством строк может быть группа строк, если агрегатная функция применяется к сгруппированной таблице, или (в вырожденных случаях) вся таблица. Для всех агрегатных функций, кроме COUNT(*), фактический (т. е. требуемый семантикой) порядок вычислений состоит в следующем. На основании параметров агрегатной функции из заданного мультимножества строк

141 Оба термина являются приемлемыми. Речь идет об агрегатных функциях, поскольку аргументом функции является агрегатное (составное) значение. Речь идет о функциях над множествами, поскольку аргументом функции является множество (в общем случае, мультимножество) значений. Но более правильно было бы говорить о групповых функциях, поскольку в большинстве случаев такие функции работают на значениях столбцов групп строк.

производится список значений. Затем по этому списку значений производится вычисление функции. Если список оказался пустым, то значением функции COUNTдля него является0, значением функцииSOME–false, значением функцииALL – true, а значением всех остальных функций –NULL.

Пусть Tобозначает тип значений из этого списка (вернее, «наименьший общий» тип, см. раздел17.2 «Скалярные выражения»лекции 17). Типы значений агрегатных функций определяются следующими правилами.

• Результат вычисления функции COUNT– это точное число с точностью и шкалой, которые определяются в реализации.

• Тип результата значений функций MAXиMINсовпадает сT. При вычислении функцийSUMиAVGтипTне должен быть типом символьных строк.

  • Если Tпредставляет собой тип точных чисел, то и типом результата функции является тип точных чисел с определяемыми в реализации точностью и шкалой.

  • Если Tпредставляет собой тип приблизительных чисел, то и типом результата функции является тип приблизительных чисел с определяемой в реализации точностью.

• Для функцийEVERYиSOME Tявляется булевским типом¹⁴²⁾.

  • Первая функция принимает значение trueв том и только в том случае, когда вычисление выражения-аргумента дает значениеtrueдля каждой строки из заданного набора строк, иfalse, когда значение выражения-аргумента естьfalseхотя бы для одной строки из заданного набора строк.

  • Функция SOMEпринимает значениеfalseв том и только в том случае, когда значение выражения-аргумента естьfalseдля каждой строки из заданного набора строк, иtrue, когда значение выражения-аргумента естьtrueхотя бы для одной строки из заданного набора строк.

Вычисление функцииCOUNT(*)производится путем подсчета числа строк в заданном мультимножестве. Все строки считаются различными, даже если они состоят из одного столбца со значениемnullво всех строках.¹⁴³⁾

Если «арифметическая» (AVG,MAX,MIN,SUM,COUNT) агрегатная функция специфицирована с ключевым словомDISTINCT, то множество значений, на котором она вычисляется, строится из значений указанного выражения, вычисляемого для каждой строки заданной группы строк. Затем из этого мультимножества удаляются неопределенные значения, и в нем устраняются значения-дубликаты (т. е. образуется множество). После этого вычисляется указанная функция.

142 Поскольку, как отмечалось в Лекции 15, в SQL к булевскому значению uknown принято относиться точно так же, как и к неопределенному значению, в списке значений для вычисления этих функций не останутся значения uknown.

143 Обратите внимание на то, что это еще один вид различения строк в SQL и еще одна скрытая интерпретация неопределенного значения. COUNT(*) работает так, как если бы выполнялось соотношение (NULL=NULL) false. Тем самым, в SQL применяются все три возможных интерпретации NULL. При вычислении логических выражений полагается (NULL=NULL) uknown; при определении строк-дубликатов неявно считается, что (NULL=NULL) true; наконец, при вычислении агрегатной функции COUNT(*) неявно полагается, что (NULL=NULL) false. Конечно, в такой «тройственности» нет ничего хорошего, но в контексте языка SQL приходится мириться с этими и другими негативными последствиями наличия неопределенных значений.

Если агрегатная функция специфицирована без ключевого слова DISTINCT(или с ключевым словомALL), то мультимножество значений формируется из значений выражения, вычисляемого для каждой строки заданной группы строк. Затем из этого мультимножества удаляются неопределенные значения, и производится вычисление агрегатной функции.