Функции агрегирования в Qlik Sense – типы функций
Функция агрегирования принимает несколько значений и в результате возвращает агрегированное значение. В зависимости от использования функции агрегирования можно разделить на две категории. Мы используем функции агрегирования Qlik Sense в скрипте загрузки данных и в выражениях диаграммы.
Функции агрегирования в Qlik Sense – типы функций
1. Функция диаграммы Qlik Sense Aggr()
Функция aggr() – это функция диаграммы, которая выполняет расширенную агрегацию, то есть агрегацию внутри функции. В результате агрегирования она возвращает массив значений.
Синтаксис:
Aggr({SetExpression}[DISTINCT][NODISTINCT]expr,StructuredParameter{, StructuredParameter})
Где expr – это выражение, в котором есть функция aggr().
StructuredParameter – имя измерения или меры, из которых будут взяты и отсортированы значения. Мы упоминаем это в выражении как, (Dimension(Sort-type, Ordering)).
Параметр SetExpression устанавливает набор записей, к которым должно применяться агрегирование. Если вы не упоминаете такое значение выражения набора, тогда функция применяет агрегирование к набору возможных записей в соответствии со сделанным выбором.
DISTINCT вернет один результат для каждого значения, к которому применяется агрегирование.
NODISTINCT вернет массив значений в качестве результата для каждого значения, к которому применяется агрегирование.
Например,
ProductData: LOAD * inline [ Customer|Product|UnitSales|UnitPrice Arman|AA|4|16 Arman|AA|10|15 Arman|BB|9|9 Himesh|BB|5|10 Himesh|CC|2|20 Himesh|DD|25|25 Parth|AA|8|15 Parth|CC||19 ] (delimiter is '|');
Выражение Avg(Aggr(Sum(UnitSales*UnitPrice), Customer)) вернет три значения каждое в виде суммы продаж для каждого клиента (Arman, Himesh, Parth), то есть 295, 715 и 120 после применения Aggr() функция от отдельных значений. После этого результата трех значений применяется функция Avg(), которая возвращает среднее из трех значений, 376,6667.
2. Типы функций агрегирования Qlik Sense
Типы функций агрегирования Qlik Sense
a. Основные функции агрегирования Qlik Sense
Основные функции агрегирования – это наиболее часто используемые функции агрегирования.
-
FirstSortedValue
Эта функция сортирует значения в поле на основе другого поля, загруженного в том же скрипте.
Синтаксис:
FirstSortedValue([ distinct ] value, sort-weight [, rank ])
где value – это поле или измерение, которое вы хотите отсортировать на основе значений, указанных в параметре sort-weight.
Sort-weight – это поле, значения которого будут отсортированы от наименьшего к наибольшему.
Ранг устанавливает n-е значение из списка отсортированных значений, которое вы хотите вернуть функцией.
Например, используя образцы данных, приведенные ниже, мы применим функцию и посмотрим, как она работает.
Temp:
LOAD * inline [ Customer|Product|UnitSales Arman|AA|10 Arman|AA|18 Arman|BB|9 Arman|CC|2 Chandrika|AA|4 Chandrika|BB|5 Chandrika|DD|25 Darsh|AA|8 Darsh|CC|19 Priya|AA|16 Priya|AA|16 Priya|DD|10 ] (delimiter is '|');
Теперь мы применим к нему функцию firstsortvalue.
FirstSortedValue: LOAD Customer,FirstSortedValue(Product,UnitSales) as CustomerRank
Resident Temp Group By Customer;
Это возвращает таблицу с именем FirstSortedValue с полем с именами CustomerRank (Рейтинг лкиента) и Customer (Клиент).
|
Customer |
CustomerRank |
|
Arman |
CC |
|
Chandrika |
AA |
|
Darsh |
AA |
|
Priya |
DD |
-
Max
Эта функция возвращает максимальное значение среди оцененных значений поля. Вы можете получить конкретное n-е значение из возвращенных значений с помощью параметра rank.
Синтаксис:
Max(expr [, rank])
-
Min
Эта функция возвращает наименьшее значение среди оцененных значений поля. Вы можете получить конкретное n-е значение из возвращенных значений с помощью параметра rank.
Синтаксис:
Max(expr [, rank])
-
Mode
Эта функция возвращает наиболее часто встречающееся значение или значение, которое встречается в поле наибольшее количество раз. Эта функция оценивает и возвращает как текстовые, так и числовые значения из поля.
Синтаксис:
Mode(expr)
-
Only
Эта функция возвращает значение, которое является единственно возможным результатом конкретной оценки. Эта функция использует как текстовые, так и числовые значения. Если нет такого уникального значения, которое существует как единственное значение после оценки значений данных, функция возвращает NULL.
Синтаксис:
Only(expr)
-
Sum
Эта функция вычисляет сумму значений, заданных в поле, и вычисленную сумму.
Синтаксис:
Sum([distinct]expr)
b. Функция агрегирования счетчика
Функции агрегирования счетчика подсчитывают количество значений, оцениваемых в поле, и возвращают это число.
-
Count
Функция возвращает общее количество значений, присутствующих в поле таблицы.
Синтаксис:
count([distinct ] expression |* )
-
MissingCount
Функция возвращает количество пропущенных значений в поле или выражении.
Синтаксис:
MissingCount([ distinct ] expression)
-
NullCount
Эта функция возвращает количество всех NULL, присутствующих в выражении или поле таблицы.
Синтаксис:
NullCount([ distinct ] expression)
-
NumericCount
Функция считает только числовые значения, присутствующие в выражении или поле, и возвращает результат подсчета.
Синтаксис:
NumericCount([ distinct ] expression)
-
TextCount
Эта функция считает только текстовые значения, присутствующие в выражении или поле, и возвращает счетчик.
Синтаксис:
TextCount([ distinct ] expression)
c. Функции финансового агрегирования
Функции агрегирования, применяемые к значениям финансовых данных и используемые в финансовых операциях, связанных с платежами и денежными потоками.
-
IRR
Функция вычисляет и возвращает значение внутренней процентной ставки (IRR) для серии или количества денежных потоков или денег, дебетованных и кредитованных. IRR – это процентная ставка, которую человек получает при инвестировании, когда производят платежи (показаны отрицательным знаком) и получают платежи (показаны положительным знаком). Такие платежи должны производиться через регулярные промежутки времени, например, ежемесячно или ежегодно.
Синтаксис:
IRR(value)
Например, поле «Payments» (Платежи) содержит некоторые значения, показывающие денежный поток, для которого необходимо рассчитать IRR. Предположим, что значения равны 1000, 3000, 4200, 6800, и тогда функция IRR (Payments) вернет значение процента как 0,1634.
-
XIRR
Эта функция вычисляет IRR для определенного периода или графика ряда денежных потоков. Это означает, что периоды времени для денежных потоков необязательно должны быть периодическими. В функции вы можете указать значения платежей или денежных потоков с помощью параметра pmt. Также график движения денежных средств или платежей можно установить по дате.
Синтаксис:
XIRR(pmt, date)
-
NPV
Эта функция возвращает чистую приведенную стоимость (NPV) для серии будущих платежей на основе ставки дисконта, примененной к значениям платежей за период.
Синтаксис:
NPV(discount_rate, value)
где Discount_rate – это ставка скидки, применяемая в течение всего периода для значений платежа.
value – это выражение или поле, содержащее значения платежа.
-
XNPV
Эта функция возвращает чистую приведенную стоимость (NPV) для серии будущих платежей на основе ставки дисконтирования, применяемой к значениям платежей по фиксированному графику времени или даты, то есть временные интервалы могут быть непериодическими.
Синтаксис:
XNPV(discount_rate,pmt,date)
где discount_rate – это ставка скидки, применяемая в течение всего периода к значениям платежа.
pmt – это выражение или поле, которое содержит значения платежа.
date – выражение дает даты, которые определяют расписание дат, и которое соответствует платежам, которые мы хотим оценить.
d. Статистические функции агрегирования
-
Avg
Эта функция возвращает среднее значение всех агрегированных значений из полей данных.
Синтаксис:
avg([distinct] expression)
-
Correl
Эта функция возвращает коэффициент корреляции для агрегированного набора значений, который существует в виде пары координат, представленных как значения x и y или значение1 и значение2 в выражении.
Синтаксис:
Correl(value1, value2)
Где value1 и value 2 – это серия парных значений, для которых мы можем вычислить коэффициент корреляции с помощью функции.
-
Fractile
Эта функция оценивает значение фрактиля для заданных значений агрегирования. Вы можете установить дробь от 0 до 1, соответствующую значению фрактиля, которое вы хотите вычислить для данного набора значений.
Синтаксис:
Fractile(expr, fraction)
-
Kurtosis
Функция оценивает и возвращает значение коэффициента эксцесса для заданного набора значений. Вы можете использовать параметр Distinct, чтобы указать, что все повторяющиеся значения не будут учитываться функцией.
Синтаксис:
Kurtosis([distinct ] expr)
-
Median
Функция оценивает и возвращает медиану агрегированного набора значений, заданных в выражении. С помощью параметра expr вы можете указать поле, которое содержит значения, для которых вы хотите вычислить медианное значение.
Синтаксис:
Median(expr)
-
Skew
Эта функция возвращает асимметрию набора значений, предоставленных для оценки. Вы можете использовать параметр Distinct, чтобы указать, что все повторяющиеся значения не будут учитываться функцией. Кроме того, с помощью параметра expr вы можете указать поле, содержащее значения, для которых вы хотите вычислить асимметрию.
Синтаксис:
Skew([distinct]expr)
-
Stdev
Эта функция оценивает и возвращает стандартное отклонение для заданного набора значений. Вы можете использовать параметр Distinct, чтобы указать, что все повторяющиеся значения не будут учитываться функцией. Также в параметре expr вы можете указать поле, которое содержит значения, для которых вы хотите рассчитать стандартное отклонение.
Синтаксис:
Stdev([distinct] expr)
-
Sterr
Эта функция оценивает и возвращает значение среднеквадратической погрешности (stdev/sqrt(n)) для заданного набора значений. Вы можете использовать параметр Distinct, чтобы указать, что все повторяющиеся значения не будут учитываться функцией. Также в параметре expr вы можете указать поле, которое содержит значения, для которых вы хотите вычислить стандартную ошибку.
Синтаксис:
Sterr([distinct] expr)
-
STEYX
Эта функция оценивает и возвращает стандартное значение ошибки предсказанного значения y, соответствующего каждому значению x в регрессии. Значения, которые мы принимаем в качестве входных данных, должны быть парами значений x и y.
Синтаксис:
STEYX(y_value, x_value)
e. Функции агрегирования строк
-
Concat
Эта функция возвращает объединенную строку, то есть строку, полученную в результате объединения нескольких отдельных строк.
Синтаксис:
Concat([distinct]string[,delimiter [,sort-weight]])
где string – это количество отдельных строк, которые вы хотите объединить.
delimiter – это знак, который вы хотите использовать для разделения отдельных значений в объединенной строке.
sort-weight устанавливает порядок сортировки для конкатенации строк, т.е. строка, соответствующая наименьшему значению, будет объединена первой и так далее до наибольшего значения.
Например,
TeamData: LOAD * inline [ SalesZone|Team|Amount East|Gamma|20000 East|Gamma|20000 West|Zeta|19000 East|Alpha|25000 East|Delta|14000 West|Epsilon|17000 West|Eta|14000 East|Beta|20000 West|Theta|23000 ] (delimiter is '|');
Резидентная группа TeamData по SalesGroup;
Вернет поле, содержащее записи с конкатенированными строками восточных и западных зон продаж.
|
SalesZone |
TeamConcat |
|
East |
Alpha-Beta-Delta-Gamma |
|
West |
Epsilon-Eta-Theta-Zeta |
-
FirstValue
Эта функция возвращает последнее значение из загрузки таблицы и ее полей.
Синтаксис:
FirstValue(expr)
Например,
TeamData: LOAD * inline [ SalesZone|Team|Amount East|Gamma|20000 East|Gamma|20000 West|Zeta|19000 East|Alpha|25000 East|Delta|14000 West|Epsilon|17000 West|Eta|14000 East|Beta|20000 West|Theta|23000 ] (delimiter is '|');
Функция FirstValue(Team) вернет Gamma для значения East и Zeta для значения West в качестве первого загруженного значения.
-
LastValue
Эта функция возвращает значение, которое было загружено последним во время загрузки таблицы и ее полей.
Синтаксис:
LastValue(expr)
Например,
TeamData: LOAD * inline [ SalesZone|Team|Amount East|Gamma|20000 East|Gamma|20000 West|Zeta|19000 East|Alpha|25000 East|Delta|14000 West|Epsilon|17000 West|Eta|14000 East|Beta|20000 West|Theta|23000 ] (delimiter is '|');
Функция LastValue(Team) вернет Beta для значения East и Theta для значения West в качестве первого загруженного значения.
-
MaxString
Эта функция находит и возвращает последнее значение, загруженное в поле таблицы.
Синтаксис:
MaxString(expr)
Например, в примере сценария, приведенном ниже, мы получим максимальную строку или строку, загруженную последней по порядку.
TeamData: LOAD * inline [ SalesZone|Team|Date East|Gamma|01/05/2018 East|Gamma|02/05/2018 West|Zeta|01/06/2018 East|Alpha|01/07/2018 East|Delta|01/08/2018 West|Epsilon|01/09/2018 West|Eta|01/10/2018 East|Beta|01/11/2018 West|Theta|01/12/2018 ] (delimiter is '|');
Функция MaxString(Date) вернет 01/11/2018 для East SalesZone и 01/12/2018 для SalesZone West.
-
MinString
Эта функция находит и возвращает первое значение, загруженное в поле таблицы, как минимальную строку.
Синтаксис:
MinString(expr)
Например, в примере сценария, приведенном ниже, мы получим минимальную строку или строку, загруженную последней по порядку.
TeamData: LOAD * inline [ SalesZone|Team|Date East|Gamma|01/05/2018 East|Gamma|02/05/2018 West|Zeta|01/06/2018 East|Alpha|01/07/2018 East|Delta|01/08/2018 West|Epsilon|01/09/2018 West|Eta|01/10/2018 East|Beta|01/11/2018 West|Theta|01/12/2018 ] (delimiter is '|');
Функция MinString(Date) вернет 01.05.2018 для East SalesZone и 01.06.2018 для SalesZone West.
f. Функции синтетического измерения
Функции синтетического измерения синтетически создают значения, которые не являются частью полей, загружаемых в скрипт. Значения, созданные синтетическими функциями измерения, находятся в синтетически созданном измерении. Мы можем использовать значения синтетического измерения в диаграммах в качестве вычисляемого измерения и хранить значения, возникающие из существующего измерения из таблиц, загруженных в скрипт. Мы называем такие измерения динамическими синтетическими функциями. На значения в таких измерениях не влияет выбор, сделанный в других полях.
Мы можем использовать их только в выражениях диаграммы, но не в выражениях скрипта.
-
ValueList
Функция создает набор меток строк или самих строк в качестве вновь сформированного синтетического измерения, которое будет содержать значения вычислений, выполненных со значениями других полей.
Синтаксис:
ValueList(v1 {,...})
где v1 показывает список имен измерений, разделенных запятыми, которые вы хотите создать.
,…. Добавлен список дополнительных измерений.
Например,
SalesRecord: LOAD * INLINE [ SaleID|Amount|Year 1|1|2018 2|1|2018 3|1|2018 4|2|2018 5|2|2018 6|2|2018 7|2|2018 8|1|2017 9|1|2017 10|2|2017 11|2|2017 12|2|2017 ] (delimiter is '|');
Мы создадим 3 новых синтетических измерения из функции ValueList(), которая будет использовать значения, указанные в таблице.
IF(ValueList(‘Number of Orders’, ‘Average Order Size’, ‘Total Amount’) = ‘Number of Orders’, count(SaleID),’Average Order Size’, avg(Amount), ‘Total Amount’, sum(Amount) ))
Это даст нам три новых метки строки в результирующей таблице.
|
Synthetic Dimensions |
Year |
Values |
|
Number of Orders |
2017 |
5.00 |
|
Number of Orders |
2018 |
7.00 |
|
Average Order Size |
2017 |
13.20 |
|
Average Order Size |
2018 |
15.43 |
|
Total Amount |
2017 |
66.00 |
|
Total Amount |
2018 |
108.00 |
-
ValueLoop
Эта функция возвращает набор значений, созданный автоматически в результате итераций, происходящих от начального до конечного значения. Эти вновь созданные значения находятся в синтетическом измерении.
Синтаксис:
ValueLoop(from [, to [, step ]])
где from – начальное значение диапазона или набора значений, которые будет генерировать эта функция.
to – конечное или последнее значение диапазона или набора значений, сгенерированных этой функцией.
step – размер приращения для вычисления каждого нового значения в диапазоне или наборе значений.
Например, ValueLoop(1,50) создаст список значений от 1 до 50.
ValueLoop(2,10,2) будет увеличивать каждое значение, начиная с 2, до двух значений и возвращать 2,4,6,8 и 10.
g. Вложенная агрегация
Вложенная агрегация выполняется тогда, когда пользователь хочет применить агрегирование к результату другой функции агрегации, известной как вложенные агрегации. В Qlik Sense вы можете вложить до 100 функций агрегирования друг в друга. Очень важное условие для вложения функций агрегирования: вы должны использовать квалификатор TOTAL во внутренних выражениях каждый раз, когда вкладываете функцию.
Например, обратите внимание на выражение, представленное ниже,
Sum(If(Year(OrderDate)=Max(TOTAL Year(OrderDate)), Sales))
Здесь функция Max() вложена в другую функцию агрегирования, то есть Sum(). Мы используем квалификатор TOTAL во внутреннем выражении для проверки вложенности в Qlik Sense, в противном случае мы не примем его.
3. Заключение
Итак, мы закончили наш урок по различным типам агрегирующих функций, используемых Qlik Sense. Все агрегирующие функции применяют определенную операцию к набору значений, агрегированных по аналогичному критерию, например, ко всем значениям, соответствующим определенному году, например, 2018, 2019 и т. д., или конкретному клиенту. Таким образом, сортировка и структурирование данных и информации становится простой и удобной для пользователя.
