Обучение
Модуль
Использование встроенных функций и GROUP BY в Transact-SQL - Training
Использование встроенных функций и GROUP BY в Transact-SQL
Этот браузер больше не поддерживается.
Выполните обновление до Microsoft Edge, чтобы воспользоваться новейшими функциями, обновлениями для системы безопасности и технической поддержкой.
В Power Query можно группировать значения в разных строках в одно значение, группируя строки в соответствии со значениями в одном или нескольких столбцах. Вы можете выбрать один из двух типов операций группирования:
Группировки столбцов.
Группировки строк.
В этом руководстве вы используете следующую примерную таблицу.
Снимок экрана: таблица со столбцами, показывающими Год (2020), Страна (США, Панама или Канада), Продукт (рубашка или шорты), Канал продаж (онлайн или торговый посредник) и Единицы (различные значения от 55 до 7500)
Группу можно найти в трех местах:
На вкладке "Главная " в группе "Преобразование ".
На вкладке "Преобразование" в группе таблиц.
В контекстном меню при щелчке правой кнопкой мыши выберите столбцы.
В этом примере цель состоит в том, чтобы суммировать общее количество проданных единиц на уровне страны и канала продаж. Столбцы Country и Sales Channel используются для выполнения группы по операции.
Эта операция предоставляет следующую таблицу.
С помощью группы по функциям доступные операции можно классифицировать двумя способами:
В следующей таблице описывается каждая из этих операций.
Имя операции | Категория | Description |
---|---|---|
Sum | Операция столбца | Суммирует все значения из столбца |
Average | Операция столбца | Вычисляет среднее значение из столбца |
Median | Операция столбца | Вычисляет медиану из столбца |
Min | Операция столбца | Вычисляет минимальное значение из столбца |
Max | Операция столбца | Вычисляет максимальное значение из столбца |
Процентиль | Операция столбца | Вычисляет процентиль, используя входное значение от 0 до 100 из столбца. |
Подсчет уникальных значений | Операция столбца | Вычисляет количество отдельных значений из столбца |
Count : подсчет строк | Операция строки | Вычисляет общее количество строк из заданной группы |
Подсчет отдельных строк | Операция строки | Вычисляет количество отдельных строк из заданной группы. |
Все строки | Операция строки | Выводит все сгруппированные строки в табличном значении без агрегирования |
Примечание
В Power Query Online доступны разные значения и операции процентиля .
Начиная с исходного примера, в этом примере создается столбец с общими единицами и двумя другими столбцами, которые дают вам имя и единицы, проданные для наиболее производительного продукта, суммируются на уровне страны и канала продаж.
Используйте следующие столбцы в качестве группы по столбцам :
Создайте два новых столбца, выполнив следующие действия.
После завершения операции обратите внимание, что столбец Products имеет значения [Table] внутри каждой ячейки. Каждое значение [Таблица] содержит все строки, сгруппированные по столбцам Country и Sales Channel из исходной таблицы. В ячейке можно выбрать пробел, чтобы просмотреть содержимое таблицы в нижней части диалогового окна.
Примечание
Панель предварительного просмотра сведений может не отображать все строки, которые использовались для операции с группировкой. Вы можете выбрать значение [Таблица], чтобы просмотреть все строки, относящиеся к соответствующей операции по группе.
Затем необходимо извлечь строку, которая имеет наибольшее значение в столбце "Единицы" таблиц в новом столбце Products, и вызвать новый продукт верхнего исполнителя столбца.
Используя новый столбец Products со значениями [Таблица], создайте новый настраиваемый столбец, перейдя на вкладку "Добавить столбец " на ленте и выбрав настраиваемый столбец из группы "Общие ".
Присвойте новому продукту верхнего исполнителя столбца. Введите формулу Table.Max([Products], "Units" )
в формуле настраиваемого столбца.
Результат этой формулы создает новый столбец со значениями [Record]. Эти значения записей по сути являются таблицей с одной строкой. Эти записи содержат строку с максимальным значением столбца "Единицы " каждого значения [Таблица] в столбце Products .
С помощью этого нового столбца продукта верхнего исполнителя, содержащего значения [Запись], можно выбрать значок развертывания, выбрать поля "Продукт и единицы", а затем ОК.
После удаления столбца Products и настройки типа данных для обоих только что развернутых столбцов результат будет выглядеть следующим образом.
Примечание
Следующая функция доступна только в Power Query Online.
Чтобы продемонстрировать, как выполнить "нечеткое группирование", рассмотрим пример таблицы, показанной на следующем рисунке.
Цель нечеткой группировки — выполнить операцию по группе, которая использует приблизительный алгоритм сопоставления для текстовых строк. Power Query использует алгоритм сходства Jaccard для измерения сходства между парами экземпляров. Затем она применяет агломеративную иерархическую кластеризацию к экземплярам группирования. На следующем рисунке показаны ожидаемые выходные данные, в которых таблица сгруппирована по столбцу Person .
Чтобы выполнить нечеткое группирование, выполните те же действия, которые ранее описаны в этой статье. Единственное различие заключается в том, что на этот раз в диалоговом окне "Группа по " установите флажок "Использовать нечеткое группирование ".
Для каждой группы строк Power Query выбирает наиболее частый экземпляр в качестве канонического экземпляра. Если несколько экземпляров происходят с одной частотой, Power Query выбирает первый. После нажатия кнопки "ОК" в диалоговом окне "Группа по", вы получите ожидаемый результат.
Однако у вас больше контроля над нечеткой операцией группировки путем расширения параметров нечетких групп.
Для нечетких группирования доступны следующие параметры:
В этом примере таблица преобразования используется для демонстрации того, как можно сопоставить значения. Таблица преобразования содержит два столбца:
На следующем рисунке показана таблица преобразования, используемая в этом примере.
Важно!
Важно, чтобы в таблице преобразования были одинаковые столбцы и имена столбцов, как показано на предыдущем изображении (они должны быть помечены как "From" и "To"). В противном случае Power Query не распознает таблицу как таблицу преобразования.
Вернитесь в группу по диалоговому окне, разверните параметры нечеткой группы, измените операцию с счетчиков на все строки, включите параметр "Показать оценки сходства", а затем выберите раскрывающееся меню таблицы преобразования.
После выбора таблицы преобразования нажмите кнопку "ОК". Результат этой операции дает следующие сведения:
В этом примере параметр "Игнорировать регистр" включен, поэтому значения в столбце From таблицы преобразования используются для поиска текстовой строки, не учитывая регистр строки. Эта операция преобразования выполняется сначала, а затем выполняется нечеткое группирование.
Оценка сходства также отображается в табличном значении рядом с столбцом person, который отражает точное группирование значений и их соответствующие оценки сходства. Этот столбец можно развернуть при необходимости или использовать значения из новых столбцов частоты для других типов преобразований.
Примечание
При группировке по нескольким столбцам таблица преобразования выполняет операцию замены во всех столбцах, если при замене значения увеличивается оценка сходства.
Дополнительные сведения о работе таблиц преобразования см. в руководстве по таблицам преобразования.
Обучение
Модуль
Использование встроенных функций и GROUP BY в Transact-SQL - Training
Использование встроенных функций и GROUP BY в Transact-SQL