Агрегация относится к таким операциям, когда больший набор строк свёртывается в меньший. Типичные агрегатные функции - COUNT, MIN, MAX, SUM и AVG. SQL Server поддерживает также и другие агрегаты, типа STDEV и VAR.
Я собираюсь посвятить этой теме несколько статей. В этой статье, я сосредоточусь на "Скалярных Агрегатах". Скалярные агрегаты - запросы с агрегатными функциями в списке оператора SELECT и без предложения GROUP BY. Скалярные агрегаты всегда возвращают одну строку.
Скалярные агрегаты
Существует только один оператор скалярной агрегации: Stream Aggregate - статистическое выражение потока. Например:
create table t (a int, b int, c int)
select count(*) from t|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0))) |--Stream Aggregate(DEFINE:([Expr1005]=Count(*))) |--Table Scan(OBJECT:([t])) |
Это своеобразный "Hello World!" в агрегации, статистическое выражение потока только и делает, что считает число строк на входе и возвращает результат подсчёта. Фактически, статистическое выражение потока вычисляет count ([Expr1005]) как bigint. Вычисляемый скаляр тут необходим для того, чтобы конвертировать результат в ожидаемый на выходе тип int. Обратите внимание, что скалярное статистическое выражение потока - это практически единственный пример (я не могу придумать ещё один такой прямо сейчас) нелистового оператора, который может выдать строку на выход даже для пустого входного набора.
Легко показать, как работают и другие простые скалярные агрегатные функции, такие как: MIN, MAX и SUM. Также мы можем вычислять сразу несколько скалярных агрегатов, используя всего одно статистическое выражение потока:
select min(a), max(b) from t|--Stream Aggregate(DEFINE:([Expr1004]=MIN([t].[a]), [Expr1005]=MAX([t].[b]))) |--Table Scan(OBJECT:([t])) |
В этом плане считывается каждая строка таблицы "t", и отслеживается минимальное и максимальное значение столбцов "a" и "b" соответственно. Обратите внимание, что для агрегатов MIN и MAX нет необходимости приводить результат к нужному типу, так как типы этих агрегатов вычислены на основе типов столбцов "a" и "b".
Такие агрегаты, как AVG, фактически рассчитываются на основе двух других агрегатов: SUM и COUNT:
select avg(a) from t|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1005]=(0) THEN NULL
ELSE [Expr1006]/CONVERT_IMPLICIT(int,[Expr1005],0) END))
|--Stream Aggregate(DEFINE:([Expr1005]=COUNT_BIG([t].[a]), [Expr1006]=SUM([t].[a])))
|--Table Scan(OBJECT:([t]))
На этот раз во время вычисления скаляра также вычисляется среднее, для которого вычисляется сумма и общее число. Выражение CASE необходимо для исключения деления на ноль.
Хотя для SUM необходимость этого и не очевидна, но дополнительно выполняется подсчёт строк:
select sum(a) from t|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END)) |--Stream Aggregate(DEFINE:([Expr1005]=COUNT_BIG([t].[a]), [Expr1006]=SUM([t].[a]))) |--Table Scan(OBJECT:([t])) |
Выражение CASE использует общее число строк для гарантии того, что если на входе будет пустое множество, SUM возвратит вместо нуля - NULL.
Скалярный DISTINCT
Теперь давайСкалярный DISTINCT посмотрим, что случается, если ли мы добавим к агрегату ключевое слово DISTINCT:
select count(distinct a) from t|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0))) |--Stream Aggregate(DEFINE:([Expr1007]=COUNT([t].[a]))) |--Sort(DISTINCT ORDER BY:([t].[a] ASC)) |--Table Scan(OBJECT:([t])) |
Этот запрос должен сосчитать только те строки, которые имеют уникальное для столбца "a" значение. Для устранения дубликатов строк с одинаковыми значениями в столбце "a" используется оператор сортировки. Дубли строк будут просто удалены из вывода, найти их не трудно, поскольку после сортировки полученного на входе набора дубликаты будут смежными записями.
Не все DISTINCT агрегаты требуют устранения дубликатов. Например, MIN и MAX ведут себя точно так же и без ключевого слова DISTINCT. Минимальные и максимальные значения в наборе остаются теми же самыми, не смотря на то, есть ли в наборе дубликаты или нет.
select min(distinct a), max(distinct b) from t|--Stream Aggregate(DEFINE:([Expr1004]=MIN([t].[a]), [Expr1005]=MAX([t].[b]))) |--Table Scan(OBJECT:([t])) |
Если имеется уникальный индекс, устранение дубликатов тоже можно пропустить, так как индекс гарантирует, что дубликатов нет:
create unique index ta on t(a)
select count(distinct a) from t
drop index t.ta|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0))) |--Stream Aggregate(DEFINE:([Expr1007]=COUNT([t].[a]))) |--Index Scan(OBJECT:([t].[ta])) |
Несколько DISTINCT
Рассмотрим такой запрос:
select count(distinct a), count(distinct b) from tКак было показано выше, мы можем вычислить "count(distinct a)", устраняя дубликаты строк для столбца "a". Точно так же мы можем вычислить "count(distinct b)", устраняя дубликаты строк для столбца "b". Но это при условии, что эти оба набора строк различны. Как можно вычислить оба дистинкта в одно и то же время? Ответ - мы не можем. Мы должны сначала вычислить одну составляющую результата, а потом другую, и затем нужно объединить оба результата в одну строку для вывода:
|--Nested Loops(Inner Join) |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1010],0))) | |--Stream Aggregate(DEFINE:([Expr1010]=COUNT([t].[a]))) | |--Sort(DISTINCT ORDER BY:([t].[a] ASC)) | |--Table Scan(OBJECT:([t])) |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0))) |--Stream Aggregate(DEFINE:([Expr1011]=COUNT([t].[b]))) |--Sort(DISTINCT ORDER BY:([t].[b] ASC)) |--Table Scan(OBJECT:([t])) |
Для двух входных наборов выполняется подсчёт как в оригинальном запросе, и они соединяются вложенным циклом. Один входной набор удаляет дубликаты для столбца "a", другой для столбца "b".
Соединение вложенных циклов не имеет предиката соединения; это перекрестное соединение. Используя оба вводных набора, соединение вложенных циклов выдаёт одну строку с двумя скалярными агрегатами (результат перекрестного соединения - одна строка). Перекрестное соединение нужно только для того, чтобы "склеить" два столбца результата в одну строку.
Если мы имеем больше двух разных агрегатов по разным столбцам, будет использоваться более одного перекрестного соединения. План может быть похож на тот, который мы только что рассмотрели, если в запросе соединены DISTINCT агрегаты и не-DISTINCT агрегаты. В этом случае, один из входных потоков перекрестного соединения будет агрегироваться без сортировки.
В следующей статье, я напишу об агрегатах с предложением GROUP BY.
Комментариев нет:
Отправить комментарий