Агрегирующее преобразование

Область применения:SQL Server среда выполнения интеграции SSIS в Фабрика данных Azure

Преобразование "Агрегатная обработка" применяет агрегатные функции, такие как Average, к значениям столбцов и копирует результат на выход преобразования. Кроме агрегатных функций преобразование предоставляет возможность использования предложения GROUP BY, которое можно применять для указания обрабатываемых групп.

Операции

Преобразование «Агрегатная обработка» поддерживает следующие операции:

Операция Описание
Группировать по Разделение наборов данных на группы. Для группирования могут быть использованы столбцы любого типа данных. Дополнительные сведения см. в статье GROUP BY (Transact-SQL).
Сумма Суммирование значений в столбце. Допускается суммирование только столбцов, содержащих числовые данные. Дополнительные сведения см. в статье SUM (Transact-SQL).
Среднее Возвращает среднее значение значений в столбце. Допускается определение среднего значения только по столбцам, содержащим числовые данные. Дополнительные сведения см. в статье AVG (Transact-SQL).
Количество Возвращает количество элементов в группе. Дополнительные сведения см. в статье COUNT (Transact-SQL).
Подсчёт уникальных Определение числа уникальных ненулевых значений в группе.
Минимум Возвращает минимальное значение в группе. Дополнительные сведения см. в статье MIN (Transact-SQL). В отличие от функции MIN Transact-SQL, эта операция может быть использована только с числовыми типами данных, типами данных даты и времени.
Максимум Возвращает максимальное значение в группе. Дополнительные сведения см. в статье MAX (Transact-SQL). В отличие от функции MAX Transact-SQL, эта операция может быть использована только с числовыми типами данных, типами данных даты и времени.

Преобразование «Агрегат» обрабатывает значения NULL таким же образом, как и ядро реляционной базы данных SQL Server. Такое поведение определено в стандарте SQL-92. Применяются следующие правила:

  • В предложении GROUP BY значения NULL обрабатываются так же, как и все значения столбца. Если в столбце, по которому производится группирование, содержится несколько значений NULL, все они помещаются в одну группу.

  • В функциях COUNT (имя столбца) и COUNT (DISTINCT имя столбца) значения NULL не обрабатываются, а результат не включает строк именованного столбца, содержащих значения NULL.

  • В функции COUNT (*) подсчитываются все строки, включая строки со значениями NULL.

Большие числа в статистических выражениях

Столбец может содержать числовые значения, требующие особого внимания из-за их большого значения или требований высокой точности вычисления. Преобразование "Агрегация" включает свойство IsBig, которое можно задать для выходных столбцов, чтобы включить специальную обработку больших или высокоточных чисел. Если значение столбца превысит 4 миллиарда или требуемая точность будет находиться вне пределов данных с плавающей точкой, то значение IsBig должно быть установлено как 1.

Установка свойства IsBig в значение 1 влияет на результаты преобразования агрегирования следующим образом:

  • Вместо типа данных DT_R4 используется тип DT_R8.

  • Результаты вычислений хранятся в формате типа данных DT_UI8.

  • Результаты отдельных вычислений хранятся в типе данных DT_UI4.

Примечание.

Нельзя установить значение 1 свойству IsBig для столбцов, используемых в операторах GROUP BY, Maximum или Minimum.

Рекомендации по производительности

Преобразование «Агрегирование» имеет набор свойств, которые можно задать, чтобы повысить производительность преобразования.

  • При выполнении операции Группировать по задайте свойства Keys или KeysScale компонента, а также выходные данные компонента. С помощью свойства Key можно указать точное количество обрабатываемых преобразованием ключей. (В этом контексте свойство Keys ссылается на ожидаемое число групп, являющихся результатом операции Группировать по .) С помощью свойства KeysScale можно указать приблизительное количество ключей. При указании подходящего значения для ключей или KeyScale вы повышаете производительность, так как преобразование может выделять достаточную память для данных, кэшируемых преобразованием.

  • При выполнении операции подсчёт различных значений задайте для компонента свойства CountDistinctKeys или CountDistinctScale. С помощью CountDistinctKeys можно указать точное количество ключей, которое должно обрабатывать преобразование при выполнении операции подсчёта уникальных значений. (В этом контексте CountDistinctKeys обозначает количество различных значений, которые, как ожидается, будут получены в результате операции подсчёта количества различных значений.) С помощью CountDistinctScale можно указать примерное количество ключей для операции подсчёта количества различных значений. Если указать соответствующее значение для свойств CountDistinctKeys или CountDistinctScale, то производительность повысится, так как преобразование сможет выделить адекватный объем памяти для кэшируемых данных.

Конфигурация агрегирующего преобразования

Можно настроить преобразование «Агрегатная обработка» на уровнях преобразования, вывода, а также на уровне столбцов.

  • На уровне преобразования вы настраиваете преобразование «Агрегирование» для повышения производительности, указывая следующие значения:

    • Ожидаемое число групп, являющихся результатом операции Группировать по .

    • Ожидаемое число различающихся значений, являющихся результатом операции Подсчет различных объектов .

    • Процент, на который объём памяти может быть увеличен во время агрегации.

    Преобразование «Агрегат» также можно настроить на выдачу предупреждения вместо завершения с ошибкой, если значение делителя равно нулю.

  • На уровне вывода вы настраиваете производительность преобразования «Агрегирование», указывая количество групп, которые, как ожидается, будут получены в результате операции Группировать по. Преобразование «Агрегирование» поддерживает несколько выходов, и каждый из них можно настроить отдельно.

  • На уровне столбца указываются следующие значения.

    • Агрегация, которую выполняет столбец.

    • Параметры сравнения агрегации.

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

  • Ожидаемое число групп, являющихся результатом операции Группировать по в столбце.

  • Ожидаемое число различающихся значений, являющихся результатом операции Подсчет различных объектов в столбце.

Также можно определить столбцы как IsBig, если столбец содержит большие числовые значения или числовые значения высокой точности.

Преобразование Aggregate является асинхронным, что означает, что оно не получает и не публикует данные построчно. Вместо этого он обрабатывает весь набор строк, выполняет группировку и агрегирование, а затем выдаёт результаты.

Это преобразование не пропускает через себя никакие столбцы, а создает в потоке данных новые столбцы для данных, которые оно выдает. В выход преобразования копируются только входные столбцы, к которым обращаются агрегатные функции, а также входные столбцы, используемые преобразованием для группирования. Например, вход преобразования «Агрегирование» может содержать три столбца: CountryRegion, City и Population. Преобразование производит группирование по столбцу CountryRegion и применяет функцию Sum к столбцу Population . Поэтому вывод не включает столбец City .

Можно также добавить несколько выходов в преобразование «Агрегирование» и направить каждый агрегат на отдельный выход. Например, если преобразование «Агрегирование» использует функции Sum и Average, то каждая операция агрегирования может быть направлена на другой выход.

Можно применять несколько статистических обработок к одному входному столбцу. Например, если необходимо получить сумму и среднее значение входного столбца с именем Продажи, можно настроить преобразование для применения обеих функций Sum и Average к столбцу Продажи .

Преобразование «Агрегатная обработка» имеет один вход и один или более выводов. Вывод ошибок не поддерживается.

Свойства могут быть заданы с помощью конструктора SSIS или программным путем.

Диалоговое окно Расширенный редактор содержит свойства, которые можно установить с помощью программных средств. Дополнительные сведения о свойствах, которые вы можете задать в диалоговом окне Расширенный редактор или программными средствами, см. в следующих разделах.

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

Агрегирование значений в наборе данных с помощью преобразования «Агрегирование»

Редактор преобразования «Агрегация» (вкладка «Агрегации»)

На вкладке Агрегаты диалогового окна Редактор преобразования «Статистическая обработка» можно указать столбцы для статистической обработки и свойства статистических выражений. Можно применить несколько агрегаций. Это преобразование не генерирует сообщение об ошибке.

Примечание.

Если параметры числа ключей, масштаба ключей, числа различающихся ключей и масштаба различающихся ключей определены на вкладке Дополнительно , они действуют на уровне компонентов; если они определены в расширенном режиме просмотра вкладки Агрегаты — на уровне вывода; а если они определены в списке столбцов внизу на вкладке Агрегаты — на уровне столбцов.

В преобразовании «Агрегирование» свойства Ключи и Масштаб ключей обозначают количество групп, которое, как ожидается, будет получено в результате операции Группировать по. Количество различных ключей и Масштаб количества различных значений обозначают число различных значений, которые, как ожидается, будут получены в результате операции Подсчёт различных значений.

Параметры

Расширенные / Базовые
Показать или скрыть параметры для настройки нескольких агрегаций для нескольких выходных данных. По умолчанию дополнительные параметры скрыты.

Имя агрегата
На экране дополнительных параметров введите понятное имя агрегации.

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

Шкала ключей
В разделе «Дополнительно» при необходимости укажите примерное количество ключей, которое агрегация может записать. По умолчанию значение этого параметра Не указано. Если заданы оба свойства Порядок числа ключей и Ключи , приоритет имеет значение свойства Ключи .

значение Описание
Не определено Свойство «Масштаб ключей» не используется.
Низкий Агрегат может записывать около 500 000 ключей.
Средняя Агрегат может записывать около 5 000 000 ключей.
Высокая Агрегат может записывать более 25 000 000 ключей.

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

Доступные входные столбцы
Выберите столбцы из списка имеющихся входных столбцов, установив флажки в таблице.

Входной столбец
Выберите входной столбец из списка имеющихся входных столбцов.

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

Операция
Выберите операцию из списка доступных операций, руководствуясь следующей таблицей.

Операция Описание
GroupBy Разделение наборов данных на группы. Для группирования подходят столбцы любого типа данных. Дополнительные сведения см. в разделе GROUP BY.
Сумма Суммирование значений в столбце. Допускается суммирование только столбцов, содержащих числовые данные. Дополнительные сведения см. в разделе SUM.
Average Возвращает среднее значение значений в столбце. Допускается определение среднего значения только по столбцам, содержащим числовые данные. Дополнительные сведения см. в разделе AVG.
Численность Возвращает количество элементов в группе. Дополнительные сведения см. в разделе COUNT.
CountDistinct Определение числа уникальных ненулевых значений в группе. Дополнительные сведения см. в разделах COUNT и Distinct.
Минимум Возвращает минимальное значение в группе. Допускается поиск только среди числовых данных.
Максимум Возвращает максимальное значение в группе. Допускается поиск только среди числовых данных.

Флаги сравнения
Если выбран параметр Группировать по, для управления параметрами сравнения при преобразовании используются флажки. Дополнительные сведения о параметрах сравнения строк см. в разделе Comparing String Data.

Подсчет отдельных масштабов
Дополнительно можно указать приблизительное количество различающихся значений, которое может записывать агрегат. По умолчанию значение этого параметра Не указано. Если указаны оба свойства CountDistinctScale и CountDistinctKeys, то приоритет имеет CountDistinctKeys.

значение Описание
Не определено Свойство CountDistinctScale не используется.
Низкий Агрегация может записать примерно 500 000 различных значений.
Средняя Агрегат может записывать примерно 5 000 000 различных значений.
Высокая Агрегация может записывать более 25 000 000 различных значений.

Подсчет отдельных ключей
При желании можно указать точное количество уникальных значений, которые может записать агрегирование. Если указаны оба свойства CountDistinctScale и CountDistinctKeys, приоритет имеет свойство CountDistinctKeys.

Редактор агрегатного преобразования (вкладка «Дополнительно»)

Вкладка Дополнительно диалогового окна Редактор преобразования «Статистическая обработка» позволяет задавать свойства компонентов, указывать агрегатные функции и задавать свойства входных и выходных столбцов.

Примечание.

Если параметры числа ключей, масштаба ключей, числа различающихся ключей и масштаба различающихся ключей определены на вкладке Дополнительно , они действуют на уровне компонентов; если они определены в расширенном режиме просмотра вкладки Агрегаты — на уровне вывода; а если они определены в списке столбцов внизу на вкладке Агрегаты — на уровне столбцов.

В преобразовании «Агрегирование» свойства Ключи и Масштаб ключей обозначают количество групп, которое, как ожидается, будет получено в результате операции Группировать по. Количество различных ключей и Масштаб количества различных значений обозначают число различных значений, которые, как ожидается, будут получены в результате операции Подсчёт различных значений.

Параметры

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

значение Описание
Не определено Свойство Порядок числа ключей не используется.
Низкий Агрегат может записывать около 500 000 ключей.
Средняя Агрегат может записывать около 5 000 000 ключей.
Высокая Агрегат может записывать более 25 000 000 ключей.

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

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

значение Описание
Не определено Свойство CountDistinctScale не используется.
Низкий Агрегация может записать примерно 500 000 различных значений.
Средняя Агрегат может записывать примерно 5 000 000 различных значений.
Высокая Агрегация может записывать более 25 000 000 различных значений.

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

Коэффициент автоматического расширения
Используйте значение от 1 до 100, чтобы указать процент, на который можно увеличить объём памяти во время агрегации. По умолчанию значение этого параметра — 25%.

Связанные материалы

  • Last updated on