Руководство. Использование агрегатных функций
Функции агрегирования позволяют группировать и объединять данные из нескольких строк в суммарное значение. Суммарное значение зависит от выбранной функции, например от количества, максимального или среднего значения.
Из этого руководства вы узнаете, как выполнять следующие задачи:
В примерах этого руководства используется StormEvents таблица, которая является общедоступной в кластере справки. Чтобы изучить собственные данные, создайте собственный бесплатный кластер.
Это руководство основывается на основе основы из первого учебника Learn common операторов.
Предварительные требования
- Учетная запись Майкрософт или удостоверение пользователя Microsoft Entra для входа в кластер справки.
Использование оператора Summarize
Оператор summarize имеет важное значение для выполнения агрегирования данных. Оператор summarize группирует строки на by основе предложения , а затем использует предоставленную функцию агрегирования для объединения каждой группы в одну строку.
Найдите количество событий по состоянию с помощью summarize функции агрегирования count .
StormEvents
| summarize TotalStorms = count() by State
Выходные данные
| Состояние | TotalStorms |
|---|---|
| Техас | 4701 |
| Канзас | 3166 |
| Айова | 2337 |
| Иллинойс | 2022 |
| Миссури | 2016 |
| ... | ... |
Визуализация результатов запроса
Визуализация результатов запроса на диаграмме или диаграмме может помочь определить закономерности, тенденции и выбросы в данных. Это можно сделать с помощью оператора render .
В этом руководстве вы увидите примеры использования render для отображения результатов. Пока давайте рассмотрим render результаты предыдущего запроса на линейчатой диаграмме.
StormEvents
| summarize TotalStorms = count() by State
| render barchart
Условное подсчет строк
При анализе данных используйте функцию countif() для подсчета строк на основе определенного условия, чтобы понять, сколько строк соответствует заданным критериям.
Следующий запрос использует для countif() подсчета штормов, вызвавших ущерб. Затем в запросе top используется оператор для фильтрации результатов и отображения состояний с наибольшим объемом ущерба, причиненного урожаем в результате штормов.
StormEvents
| summarize StormsWithCropDamage = countif(DamageCrops > 0) by State
| top 5 by StormsWithCropDamage
Выходные данные
| Состояние | StormsWithCropDamage |
|---|---|
| Айова | 359 |
| НЕБРАСКА | 201 |
| Миссисипи | 105 |
| СЕВЕРНАЯ КАРОЛИНА | 82 |
| Миссури | 78 |
Группирование данных в ячейки
Для агрегирования по числовым значениям или значениям времени сначала необходимо сгруппировать данные в ячейки с помощью функции bin( ). Использование bin() поможет вам понять, как значения распределяются в определенном диапазоне, и выполнить сравнения между различными периодами.
Следующий запрос подсчитывает количество штормов, которые нанесли ущерб урожаю за каждую неделю в 2007 году. Аргумент 7d представляет неделю, так как для функции требуется допустимое значение интервала времени .
StormEvents
| where StartTime between (datetime(2007-01-01) .. datetime(2007-12-31))
and DamageCrops > 0
| summarize EventCount = count() by bin(StartTime, 7d)
Выходные данные
| StartTime | EventCount |
|---|---|
| 2007-01-01T00:00:00Z | 16 |
| 2007-01-08T00:00:00Z | 20 |
| 2007-01-29T00:00:00Z | 8 |
| 2007-02-05T00:00:00Z | 1 |
| 2007-02-12T00:00:00Z | 3 |
| ... | ... |
Добавьте | render timechart в конец запроса, чтобы визуализировать результаты.
Примечание
bin() похожа на функцию в floor() других языках программирования. Он уменьшает каждое значение до ближайшего значения, кратного заданному модулю, и позволяет summarize назначать строки группам.
Вычисление значения min, max, avg и sum
Чтобы узнать больше о типах штормов, вызывающих урожай, рассчитайте урожай min(), max() и avg() для каждого типа события, а затем отсортируйте результат по среднему ущербу.
Обратите внимание, что для создания нескольких вычисляемых столбцов можно использовать несколько агрегатных функций в одном summarize операторе.
StormEvents
| where DamageCrops > 0
| summarize
MaxCropDamage=max(DamageCrops),
MinCropDamage=min(DamageCrops),
AvgCropDamage=avg(DamageCrops)
by EventType
| sort by AvgCropDamage
Выходные данные
| EventType | MaxCropDamage | MinCropDamage | AvgCropDamage |
|---|---|---|---|
| Заморозка/заморозка | 568600000 | 3000 | 9106087.5954198465 |
| Wildfire | 21000000 | 10000 | 7268333.333333333 |
| Засухи | 700000000 | 2000 | 6763977.8761061952 |
| Наводнение | 500000000 | 1000 | 4844925.23364486 |
| Шквалистый ветер | 22000000 | 100 | 920328.36538461538 |
| ... | ... | ... | ... |
Результаты предыдущего запроса показывают, что события Frost/Freeze привели к наибольшему урону урожая в среднем. Однако запрос bin() показал, что события с повреждением урожая в основном происходили в летние месяцы.
Используйте sum(), чтобы проверка общее количество поврежденных культур, а не количество событий, нанесших определенный ущерб, как показано count() в предыдущем запросе bin().
StormEvents
| where StartTime between (datetime(2007-01-01) .. datetime(2007-12-31))
and DamageCrops > 0
| summarize CropDamage = sum(DamageCrops) by bin(StartTime, 7d)
| render timechart
Теперь вы можете увидеть пик урона урожая в январе, который, вероятно, был из-за мороза / заморозки.
Совет
Используйте minif(), maxif(), avgif() и sumif() для выполнения условных агрегирования, как это было в разделе условного подсчета строк .
Вычисление процентных значений
Вычисление процентных значений поможет вам понять распределение и пропорции различных значений в данных. В этом разделе рассматриваются два распространенных метода вычисления процентных значений с помощью язык запросов Kusto (KQL).
Вычисление процента на основе двух столбцов
Используйте count() и countif , чтобы найти процент штормовых событий, вызвавших урожай в каждом штате. Сначала подсчитайте общее количество штормов в каждом штате. Затем подсчитайте количество штормов, вызвавших урожай в каждом штате.
Затем используйте команду extend , чтобы вычислить процент между двумя столбцами, разделив количество штормов с урожаем на общее количество штормов и умножив на 100.
Чтобы получить десятичный результат, используйте функцию todouble() для преобразования по крайней мере одного из целочисленных значений счетчика в значение double перед выполнением деления.
StormEvents
| summarize
TotalStormsInState = count(),
StormsWithCropDamage = countif(DamageCrops > 0)
by State
| extend PercentWithCropDamage =
round((todouble(StormsWithCropDamage) / TotalStormsInState * 100), 2)
| sort by StormsWithCropDamage
Выходные данные
| Состояние | TotalStormsInState | StormsWithCropDamage | PercentWithCropDamage |
|---|---|---|---|
| Айова | 2337 | 359 | 15.36 |
| НЕБРАСКА | 1766 | 201 | 11.38 |
| Миссисипи | 1218 | 105 | 8.62 |
| СЕВЕРНАЯ КАРОЛИНА | 1721 | 82 | 4,76 |
| Миссури | 2016 | 78 | 3,87 |
| ... | ... | ... | ... |
Примечание
При вычислении процентных значений преобразуйте по крайней мере одно из целочисленных значений в делении с todouble() или toreal(). Это гарантирует, что вы не получите усеченные результаты из-за целочисленного деления. Дополнительные сведения см. в разделе Правила типов для арифметических операций.
Вычисление процента на основе размера таблицы
Чтобы сравнить количество штормов по типу события с общим числом штормов в базе данных, сначала сохраните общее число штормов в базе данных в качестве переменной. Операторы Let используются для определения переменных в запросе.
Так как операторы табличных выражений возвращают табличные результаты, используйте функцию toscalar() для преобразования табличного count() результата функции в скалярное значение. Затем числовое значение можно использовать при вычислении процента.
let TotalStorms = toscalar(StormEvents | summarize count());
StormEvents
| summarize EventCount = count() by EventType
| project EventType, EventCount, Percentage = todouble(EventCount) / TotalStorms * 100.0
Выходные данные
| EventType | EventCount | Процент |
|---|---|---|
| Шквалистый ветер | 13015 | 22.034673077574237 |
| Град | 12711 | 21.519994582331627 |
| Внезапное наводнение | 3688 | 6.2438627975485055 |
| Засухи | 3616 | 6.1219652592015716 |
| Зимняя погода | 3349 | 5.669928554498358 |
| ... | ... | ... |
Извлечение уникальных значений
Используйте make_set(), чтобы преобразовать выбранные строки в таблице в массив уникальных значений.
Следующий запрос использует make_set() для создания массива типов событий, вызывающих смерть в каждом состоянии. Затем результирующая таблица сортируется по количеству типов storm в каждом массиве.
StormEvents
| where DeathsDirect > 0 or DeathsIndirect > 0
| summarize StormTypesWithDeaths = make_set(EventType) by State
| project State, StormTypesWithDeaths
| sort by array_length(StormTypesWithDeaths)
Выходные данные
| Состояние | StormTypesWithDeaths |
|---|---|
| Калифорния | ["Грозовой ветер","Высокий прибой","Холодный/ветер","Сильный ветер","Rip Current","Тепло","Чрезмерная жара","Лесной пожар","Пыльная буря","Астрономический прилив","Плотный туман","Зимняя погода"] |
| Техас | ["Flash Flood","Грозовый ветер","Торнадо","Молния","Наводнение","Ледяной шторм","Зимняя погода","Rip Current","Чрезмерное тепло","Плотный туман","Ураган (тайфун)","Холодный/ветровой холод"] |
| ОКЛАХОМА | ["Мгновенное наводнение","Торнадо","Холодный/ветровой холод","Зимний шторм","Тяжелый снег","Чрезмерная жара","Жара","Ледяной шторм","Зимняя погода","Плотный туман"] |
| НЬЮ-ЙОРК | ["Наводнение","Молния","Грозовый ветер","Флэш-наводнение","Зимняя погода","Ледяной шторм","Экстремальный холод/ветер холод","Зимний шторм","Тяжелый снег"] |
| Канзас | ["Грозовой ветер","Сильный дождь","Торнадо","Наводнение","Вспышка наводнения","Молния","Тяжелый снег","Зимняя погода","Метель"] |
| ... | ... |
Сегмент данных по условию
Функция case() группирует данные в контейнеры на основе указанных условий. Функция возвращает соответствующее результирующее выражение для первого удовлетворенного предиката или окончательное выражение else, если ни один из предикатов не удовлетворен.
В этом примере государства группируются на основе числа травм, связанных со штормами, которые получили их граждане.
StormEvents
| summarize InjuriesCount = sum(InjuriesDirect) by State
| extend InjuriesBucket = case (
InjuriesCount > 50,
"Large",
InjuriesCount > 10,
"Medium",
InjuriesCount > 0,
"Small",
"No injuries"
)
| sort by State asc
Выходные данные
| Состояние | InjuriesCount | InjuriesBucket |
|---|---|---|
| АЛАБАМА | 494 | большой |
| АЛЯСКА | 0 | Травмы отсутствуют |
| АМЕРИКАНСКОЕ САМОА | 0 | Травмы отсутствуют |
| АРИЗОНА | 6 | Small |
| АРКАНЗАС | 54 | большой |
| АТЛАНТИКА СЕВЕРНАЯ | 15 | Средний |
| ... | ... | ... |
Создайте круговую диаграмму, чтобы визуализировать долю состояний, которые пережили бури, приводящие к большому, среднему или небольшому количеству травм.
StormEvents
| summarize InjuriesCount = sum(InjuriesDirect) by State
| extend InjuriesBucket = case (
InjuriesCount > 50,
"Large",
InjuriesCount > 10,
"Medium",
InjuriesCount > 0,
"Small",
"No injuries"
)
| summarize InjuryBucketByState=count() by InjuriesBucket
| render piechart
Вычисление агрегатов для скользящего окна
В следующем примере показано, как суммировать столбцы с помощью скользящего окна.
Запрос вычисляет минимальный, максимальный и средний ущерб от торнадо, наводнений и лесных пожаров с помощью скользящего окна в семь дней. Каждая запись в результирующем наборе агрегирует данные за предыдущие семь дней, а результаты содержат запись за день в период анализа.
Ниже приведено пошаговое описание запроса.
- Записывайте каждую запись в один день относительно
windowStart. - Добавьте семь дней к значению ячейки, чтобы задать конец диапазона для каждой записи. Если значение выходит за пределы диапазона
windowStartиwindowEnd, измените значение соответствующим образом. - Создайте массив из семи дней для каждой записи, начиная с текущего дня записи.
- Разверните массив с шага 3 с помощью mv-expand , чтобы дублировать каждую запись до семи записей с интервалами в один день между ними.
- Выполняйте агрегаты для каждого дня. Из-за шага 4 этот шаг фактически суммирует предыдущие семь дней.
- Исключите первые семь дней из конечного результата, так как для них нет семидневного периода обратного просмотра.
let windowStart = datetime(2007-07-01);
let windowEnd = windowStart + 13d;
StormEvents
| where EventType in ("Tornado", "Flood", "Wildfire")
| extend bin = bin_at(startofday(StartTime), 1d, windowStart) // 1
| extend endRange = iff(bin + 7d > windowEnd, windowEnd,
iff(bin + 7d - 1d < windowStart, windowStart,
iff(bin + 7d - 1d < bin, bin, bin + 7d - 1d))) // 2
| extend range = range(bin, endRange, 1d) // 3
| mv-expand range to typeof(datetime) // 4
| summarize min(DamageProperty), max(DamageProperty), round(avg(DamageProperty)) by Timestamp=bin_at(range, 1d, windowStart), EventType // 5
| where Timestamp >= windowStart + 7d; // 6
Выходные данные
Следующая таблица результатов усечена. Чтобы просмотреть полные выходные данные, выполните запрос.
| Отметка времени | EventType | min_DamageProperty | max_DamageProperty | avg_DamageProperty |
|---|---|---|---|---|
| 2007-07-08T00:00:00Z | Ураган | 0 | 30 000 | 6905 |
| 2007-07-08T00:00:00Z | Наводнение | 0 | 200 000 | 9261 |
| 2007-07-08T00:00:00Z | Wildfire | 0 | 200 000 | 14033 |
| 2007-07-09T00:00:00Z | Ураган | 0 | 100 000 | 14783 |
| 2007-07-09T00:00:00Z | Наводнение | 0 | 200 000 | 12529 |
| 2007-07-09T00:00:00Z | Wildfire | 0 | 200 000 | 14033 |
| 2007-07-10T00:00:00Z | Ураган | 0 | 100 000 | 31400 |
| 2007-07-10T00:00:00Z | Наводнение | 0 | 200 000 | 12263 |
| 2007-07-10T00:00:00Z | Wildfire | 0 | 200 000 | 11694 |
| ... | ... | ... |
Следующий шаг
Теперь, когда вы знакомы с распространенными операторами запросов и агрегатными функциями, перейдите к следующему руководству, чтобы узнать, как объединить данные из нескольких таблиц.
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по