Что такое пользовательские вычисления?

Пользовательские вычисления позволяют определять динамические метрики и преобразования без изменения запросов набора данных. На этой странице объясняется, как использовать пользовательские вычисления на панелях мониторинга ИИ/BI.

Зачем использовать пользовательские вычисления?

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

Кастомные вычисления — один из следующих типов:

• Вычисляемые меры: агрегированные значения, такие как общая стоимость продаж или средняя стоимость. Вычисляемые меры могут использовать команду AGGREGATE OVER для расчета значений за временные интервалы.
• Вычисляемые измерения: нерегрегированные значения или преобразования, такие как классификация диапазонов возрастов или строк форматирования.

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

Создание динамических метрик с вычисляемыми мерами

Предположим, что у вас есть следующий набор данных:

Вы хотите визуализировать прибыль по регионам. Без пользовательских вычислений вам необходимо создать новый набор данных, содержащий столбец margin:

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

С помощью пользовательских вычислений можно выразить прибыль в виде агрегирования с помощью следующей формулы:

(SUM(Price) - SUM(Cost)) / SUM(Price)

Эта мера является динамической. При использовании в визуализации он автоматически обновляется для отражения групп визуализации. Например, ту же меру, что и выше, можно использовать для визуализации прибыли по Region или Item в зависимости от выбора в визуализации.

Определите неагрегированные значения с вычисляемыми измерениями

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

Например, для анализа тенденций по возрастным группам вместо отдельных возрастных категорий можно определить пользовательское age_group измерение с помощью следующего выражения:

CASE
   WHEN age < 18 THEN '<18'
   WHEN age >= 18 AND age < 25 THEN '18–24'
   WHEN age >= 25 AND age < 35 THEN '25–34'
   WHEN age >= 35 AND age < 45 THEN '35–44'
   WHEN age >= 45 AND age < 55 THEN '45–54'
   WHEN age >= 55 AND age < 65 THEN '55–64'
   WHEN age >= 65 THEN '65+'
END

Определение вычислений в окне

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

Панели мониторинга AI/BI поддерживают два типа функций окна:

• Скалярные функции окна, которые объединяются по фиксированным группировкам и ведут себя как скалярные функции. При использовании отдельно они формируют вычисляемые измерения.
• Агрегатные функции окна, которые агрегируются по динамическим группировкам и ведут себя как агрегатные функции. При использовании они формируют вычисляемые меры.

Функции окна также являются основой для выражений уровня детализации, которые позволяют управлять уровнем детализации агрегирования независимо от группирования визуализации.

Скалярные функции окна

Скалярные оконные функции используют оператор OVER с необязательными PARTITION BY и ORDER BY предложениями для вычисления агрегации по связанным строкам до выполнения группировки для визуализации. Они агрегируются по статическому набору секций, определенных в самой функции окна, прежде чем присоединяться к нетрансформированной базовой таблице в качестве измерения.

Пример вычисления общего объема продаж по регионам:

SUM(sales) OVER (PARTITION BY Region)

Пример вычисления совокупных продаж в каждом регионе:

SUM(sales) OVER (PARTITION BY Region ORDER BY Date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Синтаксис OVER

<AGGREGATE_FUNCTION>(<column>) OVER (
  [PARTITION BY <dimensions>]
  [ORDER BY <column>]
  [ROWS|RANGE frame_specification]
)

Для более подробной информации см. Оконные функции в справочнике по языку SQL.

Функции агрегатного окна

Функции агрегатного окна используют оператор AGGREGATE OVER для вычисления оконных агрегаций после того, как была применена группировка визуализаций. Группы для агрегирования автоматически наследуются от визуализации, в которой используется выражение. При необходимости можно использовать предложение PARTITION BY вместе с * для представления всех унаследованных разделов и для исключения определенных измерений с помощью предложения EXCEPT. Предложение ORDER BY позволяет частично агрегировать результирующие разделы в смежных строках, предоставляя функциональность окон.

Используя тот же набор данных, что и в предыдущем примере, следующее выражение вычисляет конечный семидневный средний показатель прибыли с помощью AGGREGATE OVER оператора.

(
  (SUM(Price) - SUM(Cost)) / SUM(Price)
) AGGREGATE OVER (
  ORDER BY Date TRAILING 7 DAY
)

После создания эта мера может применяться в любой визуализации.

Синтаксис AGGREGATE OVER

<AGGREGATE_EXPRESSION> AGGREGATE OVER (
  [PARTITION BY * [EXCEPT (<field> [, ...])]]
  [ORDER BY <field> <frame_specification>]
)

В этом синтаксисе:

• PARTITION BY * представляет все разделы, унаследованные из группировки визуализаций
• EXCEPT (<field> [, ...]) указывает измерения, которые следует исключить из набора разделов.
• PARTITION BY Оба ORDER BY предложения являются необязательными, но пустое AGGREGATE OVER () недопустимо

Спецификация кадра может быть одной из следующих:

• CURRENT
• CUMULATIVE
• ALL
• (TRAILING|LEADING) <number> <unit> [INCLUSIVE|EXCLUSIVE]
  • <number> является положительным целым числом
  • <unit> is DAY, MONTHили YEAR
  • Необязательное INCLUSIVE ключевое слово включает текущую строку в диапазоне. EXCLUSIVE исключает его. По умолчанию — EXCLUSIVE.
  • Пример: TRAILING 7 DAY INCLUSIVE или LEADING 1 MONTH.

При необходимости можно добавить выражение OFFSET, чтобы сдвинуть весь диапазон на указанный интервал:

• OFFSET <number> <unit>
  • Принимает те же спецификации интервала, что TRAILING и LEADING
  • Пример: TRAILING 7 DAY OFFSET -1 YEAR

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

ORDER BY Если поле не сгруппировано в визуализации, AGGREGATE OVER принимает агрегированное значение последней строки в качестве значения, отображаемого для каждой группы. Это эквивалентно «последнему» полуаддитивному поведению.

OVER Против AGGREGATE OVER

Основное различие между OVER и AGGREGATE OVER заключается в том, что OVER скалярная функция и AGGREGATE OVER является агрегатной функцией. OVER PARTITION BY требует условия для определения групп, в то время как AGGREGATE OVER наследует свои группы от окружающих визуализаций и может использовать данные за пределами текущей группы.

Используйте OVER синтаксис для:

• Вычисления окон, которые необходимо использовать в неагрегированных контекстах, таких как таблицы.
• Вычисления окон, которые должны игнорировать все группировки визуализаций и фильтры.
• Агрегирование на фиксированном уровне детализации: вычисление агрегатов с определенной степенью детализации с помощью PARTITION BY.
• Использование таких функций ранжирования и аналитики, как ROW_NUMBER, RANKLAG.

Используйте AGGREGATE OVER синтаксис для:

• Оконные вычисления, которые могут использоваться в различных контекстах группировки или должны учитывать данные за пределами текущей группы.
• Вычисления окон, которые учитывают фильтры визуализации.
• Агрегирование на более грубом уровне детализации, чем визуализация: исключение измерений при помощи .
• Диапазоны на основе времени, которые являются надежными для отсутствующих строк: перемещение окон с TRAILING или LEADING.

Преимущества производительности

Пользовательские вычисления оптимизированы для повышения производительности. Для небольших наборов данных (≤100 000 строк и ≤100 МБ) вычисления выполняются в браузере для ускорения реагирования. Большие наборы данных обрабатываются хранилищем SQL. Дополнительные сведения см. в статье об оптимизации набора данных и кэшировании .

Создайте пользовательское вычисление

В этом примере создается вычисляемая мера на основе набора данных samples.nyctaxi.trips. Это подразумевает общие знания о работе с дашбордами AI/BI. Если вы не знакомы с созданием панелей ИИ/BI, ознакомьтесь с разделом Создание панели мониторинга для начала работы.

1. Откройте существующий набор данных или создайте новый.

2. Нажмите кнопку +Добавить настраиваемое вычисление.

   

3. Откроется панель создания вычислений справа от экрана. Введите в текстовом поле Nameстоимость за милю.

4. (Необязательно) В текстовом поле "Комментарий " введите "Использует сумму тарифа и расстояние поездки для вычисления затрат на милю".

5. В поле "Выражение" введите следующее:

   try_divide(SUM(fare_amount), SUM(trip_distance))
   

6. Нажмите кнопку Создать.

Ссылка на другие вычисления

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

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

Например, предположим, что вы создали эти вычисляемые показатели:

• total_revenue: SUM(sale_amount)
• total_cost: SUM(cost_amount)

Вы можете создать третью вычисляемую меру, которая ссылается на оба:

• profit_margin: (MEASURE(total_revenue) - MEASURE(total_cost)) / MEASURE(total_revenue)

Добавление пользовательских вычислений в представление метрик

Можно определить пользовательские вычисления поверх набора данных, созданного метрикой. При открытии набора данных отображаются только таблица результатов и схема . Щелкните "Пользовательское вычисление", чтобы определить новый пользовательский расчет. Чтобы определить дополнительные пользовательские метрики, которые могут использовать другие ресурсы данных, внесите изменения в определение представления. Смотрите представления метрик каталога Unity.

Чтобы определить новое представление метрик из редактора набора данных панели мониторинга, см. статью "Экспорт в виде представления метрик".

Просмотр схемы

Перейдите на вкладку "Схема" на панели результатов, чтобы просмотреть настраиваемые вычисления и связанные с ним комментарии.

Вычисляемые меры перечислены в разделе Меры и помечены значком вычисляемой меры fx. Значение, связанное с вычисляемой мерой, динамически вычисляется при установке GROUP BY в визуализации. Вы не можете увидеть значение в таблице результатов. Вычисляемые измерения отображаются в разделе "Измерения ".

Использование пользовательского вычисления в визуализации

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

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

1. Щелкните Untitled page. Затем поместите на страницу новое мини-приложение визуализации.
2. Используйте панель конфигурации визуализации для изменения параметров следующим образом:
   • Набор данных: данные о такси
   • Визуализация: столбчатая диаграмма
   • Ось X:
     • Поле: dropoff_zip
     • Тип масштабирования: категориальный
     • Преобразование: Нет
   • Ось Y:
     • Стоимость за милю

На следующем рисунке показана диаграмма.

Визуализации с пользовательскими вычислениями автоматически обновляются при применении фильтров. Например, добавление фильтра pickup_zip обновит визуализацию, чтобы отобразить только данные, соответствующие выбранным значениям.

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

Чтобы изменить вычисление, выполните приведенные действия.

1. Щелкните вкладку "Данные" и выберите набор данных, связанный с вычислением, который требуется изменить.
2. Нажмите на вкладку схемы в панели результатов.
3. Меры и измерения отображаются в списке полей набора данных. Щелкните , расположенный справа от вычисления, которое вы хотите изменить. Затем нажмите кнопку "Изменить".
4. На панели редактирования настраиваемых вычислений обновите текстовые поля, которые требуется изменить. Затем щелкните Обновить.

Удалить пользовательское вычисление

Чтобы удалить вычисление, выполните приведенные действия.

1. Щелкните вкладку "Данные" и выберите набор данных, связанный с мерой, которую требуется изменить.
2. Нажмите на вкладку схемы в панели результатов.
3. Раздел измерений появляется под списком полей. Щелкните справа от вычисления, которое требуется изменить. Затем нажмите кнопку "Удалить".
4. Щелкните "Удалить" в диалоговом окне "Удалить", которое появится.

Использование параметров в пользовательских вычислениях

Вы можете ссылаться на параметры непосредственно в пользовательских вычислениях с помощью синтаксиса :keyword . Параметры позволяют динамически изменять меры и измерения.

Например, рассмотрим набор данных со столбцом возраста пользователя. Чтобы создать динамическое определение "взрослый", можно создать настраиваемое вычисление с помощью параметра с именем adult_age_threshold:

CASE
  WHEN age > :adult_age_threshold THEN "adult"
END

Где :adult_age_threshold используется параметр с числовым типом данных. На панели мониторинга пользователи могут динамически задать :adult_age_threshold параметр, который обновляет измерение.

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

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

Ограничения

Чтобы использовать пользовательские вычисления, следующее должно быть верно:

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

Поддерживаемые функции

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

Примеры

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

Условно фильтровать и агрегировать данные

Используйте инструкцию CASE для условной статистической обработки данных. В следующем примере используется samples.nyctaxi.trips набор данных и вычисляется сумма тарифов на все поездки, начинающиеся в zip-коде 10103.

SUM(CASE
  WHEN pickup_zip=10103 THEN fare_amount
  WHEN pickup_zip!=10103 THEN 0
END)

Создание строк

Используйте функцию CONCAT для создания нового строкового значения. См concat . функцию и concat_ws функцию.

CONCAT(first_name, ' ', last_name)

Формат дат

Используйте DATE_FORMAT для форматирования строк даты, отображаемых в визуализациях.

DATE_FORMAT(tpep_pickup_datetime, 'YYYY-MM-dd')