Агрегации в DataLens

В этом разделе описано, как работают агрегация и группировка данных в DataLens, как правильно формулировать выражения с агрегациями, а также приведены примеры SQL-запросов с использованием агрегации для ClickHouse.

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

Основные понятия из SQL

Что такое агрегация

В качестве исходных данных рассмотрим таблицу Selling, содержащую данные о продажах в городах:

#CityCategoryDateSalesProfitDay's discount
1DetroitOffice Supplies2014-01-021070,05
2PortlandOffice Supplies2014-04-0514100,00
3PortlandOffice Supplies2014-01-2120120,20
4San FranciscoOffice Supplies2014-03-11830,10
5DetroitFurniture2014-01-011230,00
6PortlandFurniture2014-01-21720,05
7San FranciscoTechnology2014-01-02730,10
8San FranciscoTechnology2014-01-171350,20

Агрегация применяется для расчета результирующих значений. Суть процесса агрегации — преобразовать большой набор строк в единственное значение. В SQL для этого применяются специальные агрегатные функции. К наиболее часто применяемым можно отнести функции SUM, MIN, MAX, AVG и COUNT. Каждая из этих функций оперирует значениями столбца некоторой таблицы и в результате создает единственное значение. Например, для функции SUM — это сумма всех значений в столбце, для функции AVG — среднее значение, для функции MAX — максимальное значение.

Различают два способа применения агрегатных функций, когда:

  • агрегатные функции возвращают результирующее значение для одной группы;
  • агрегатные функции возвращают результирующее значение для нескольких групп.

Агрегация для одной группы

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

Например, чтобы получить сумму значений в столбце Sales из таблицы Selling, нужно выполнить запрос:

SELECT    sum("Sales")FROM "Selling"

Результат:

Sales
91

Агрегация для нескольких групп

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

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

Примеры

Вычисление суммы продаж по каждому городу:

SELECT    "City",    sum("Sales")FROM "Selling"GROUP BY "City"

Результат:

CitySales
Detroit22
Portland41
San Francisco28

Вычисление суммы продаж по каждому городу и категории товаров:

SELECT    "City",    "Category",    sum("Sales")FROM "Selling"GROUP BY "City","Category"

Результат:

CityCategorySales
DetroitOffice Supplies10
PortlandOffice Supplies34
San FranciscoOffice Supplies8
DetroitFurniture12
PortlandFurniture7
San FranciscoTechnology20

Ограничения

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

  • Столбцы, по которым делается выборка, должны быть указаны в секции GROUP BY:

    SELECT    "City",    sum("Sales")FROM "Selling"GROUP BY "City"
    SELECT    "City",    sum("Sales")FROM "Selling"GROUP BY "Category"
  • Агрегированные и неагрегированные выражения нельзя использовать на одном уровне запроса:

    SELECT    "City",    sum("Sales") as "Detroit Sales"FROM "Selling"WHERE "City" = 'Detroit'GROUP BY "City"
    SELECT    if("City" = 'Detroit', sum("Sales"), 0) as "Detroit Sales"FROM "Selling"GROUP BY "Category"

Фильтрация

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

Условия для фильтрации исходного набора строк указываются в секции WHERE:

SELECT    "City",    sum("Sales")FROM "Selling"WHERE "Category" = 'Furniture'GROUP BY "City"

Результат:

CitySales
Detroit12
Portland7

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

SELECT    "City",    "Category",    sum("Sales")FROM "Selling"GROUP BY "City","Category"HAVING sum("Sales") >= 10

Результат:

CityCategorySales
DetroitOffice Supplies10
PortlandOffice Supplies34
DetroitFurniture12
San FranciscoTechnology20

Агрегирование данных в DataLens

Измерения и показатели

В DataLens агрегация выполняется с помощью измерений и показателей.

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

Измерение — это поле датасета без указанной агрегации. Например, регион, продукт или категория. В датасете и в визарде измерения отображаются зеленым цветом. С помощью измерений выполняется группировка запроса в чарте (секция GROUP BY в SQL). Чтобы сгруппировать данные в чарте, необходимо перенести измерение в нужную секцию.

Способы создания показателей

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

Создание показателей на уровне датасета

Добавить показатель на уровне датасета можно следующими способами:

  • В интерфейсе создания датасета на вкладке Поля выбрать тип агрегации для поля в столбце Агрегация.

    image

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

    image

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

Создание показателей на уровне чарта

Добавить показатель на уровне чарта можно следующими способами:

Показатели могут состоять не только из одной агрегатной функции и иметь более сложные выражения. Например, в этом чарте для расчета средней суммы продаж за день используется показатель Sales per day, вычисляемый по формуле SUM([Sales])/COUNTD([Date]).

image

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

При построении любого чарта в DataLens выполняется группировка и агрегация данных.
Рассмотрим таблицу Selling, для которой необходимо посчитать сумму продаж (Sales) за все даты (Date) отдельно по каждому городу (City). Для этого нужно сгруппировать данные по полю City. При группировке строки объединяются таким образом, что в результате для каждого значения City будет ровно одна строка. Все исходные строки, где значения City совпадают и равны, образуют группу строк. В итоге получится три группы, по которым будет происходить суммирование значения Sales:

  • в группу Detroit попадут строки 1 и 5;
  • в группу Portland — строки 2, 3 и 6;
  • в группу San Francisco — строки 4, 7 и 8.

На примере чарта Столбчатая диаграмма результат будет выглядеть так:

image

Можно сделать группировку не по одному полю, а по нескольким. Тогда каждая строка будет определяться набором значений всех полей, по которым производится группировка. Строк в конечном результате будет столько, сколько найдется уникальных наборов таких значений.
Например, при добавлении поля Category в секцию Цвета оно будет участвовать в группировке. Чарт будет выглядеть так:

image

Примечание

Измерения в секции Цвета также участвуют в группировке данных.

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

image

Ограничения в выражениях

Как и в SQL, в DataLens вы не можете использовать в одном выражении агрегированные и неагрегированные значения.

Например, в чарт с группировкой по измерениям City и Category нельзя добавить показатель SUM([Sales]) * (1 - [Day's discount]) для расчета суммы продаж с учетом скидки. В этом случае измерения City и Category задают разбиение на группы, и поэтому имеют фиксированные значения в каждой группе. Для каждой группы можно рассчитать значение SUM([Sales]). Но поле Day's discount не является ни агрегацией, ни измерением в рамках группы. Оно не имеет фиксированного значения — в каждой строке группы оно может быть разным. Поэтому невозможно определить, какое конкретно значение поля Day's discount должно быть выбрано при вычислении показателя SUM([Sales]) * (1 - [Day's discount]) для каждой группы. Получается, что выражение SUM([Sales]) * (1 - [Day's discount]) вычислить невозможно. В DataLens в подобных случаях возникает ошибка Inconsistent aggregation among operands.

image

Этой ошибки можно избежать несколькими способами:

  • Добавить поле Day's discount в секцию для измерений. В этом случае группировка будет происходить по измерениям City, Category и Day's discount, поэтому в каждой группе для расчета значения показателя SUM([Sales]) * (1 - [Day's discount]) будет использоваться фиксированное значение поля Day's discount.

    image

  • Указать тип агрегации для поля Day's discount. Тогда это поле станет показателем и исходная формула будет корректной.

    image

Фильтрация измерений и показателей

В чартах можно фильтровать значения измерений и показателей. Для этого перенесите измерение или показатель в секцию Фильтры и задайте условия фильтрации:

image

Примеры

Продажи по городам в категории Furniture:

image

Продажи по городам и категориям, где показатель SUM([Sales]) больше или равен 10:

image

Подстановка полей

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

Пример 1

Поле [TotalSales] задано с помощью агрегатной функции SUM([Sales]). Тогда вычисляемое поле [TotalSales]/10 будет иметь тип агрегации Авто.

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

Пример 2

Для поля [Sales] в интерфейсе создания датасета указан тип агрегации Сумма. Тогда вычисляемое поле AVG([Sales]) будет иметь тип агрегации Авто и рассчитываться как среднее. Агрегация Сумма при этом будет проигнорирована.