Сегодня хочу рассказать об основах группировки данных в скрипте загрузки Qlik. QlikView и Qlik Sense хорошо справляются с большими объемами данных, и можно, например, загружать миллионы строк чеков, чтобы во время аналитики провалиться в детали. Но иногда такие детальные данные не нужны, и можно их агрегировать – сегодня рассмотрим способы такой группировки.
Группировка данных в Qlik: Введение
Группировку данных будем рассматривать на примере простых данных некой розничной компании, в которой есть данные со статистикой визитов в магазин и чеков в разных временных разрезах (день, неделя, месяц):
Допустим данные мы подгружаем в модель из qvd файла «Sainsburys_DATA.qvd» и хотим группировать данные по месяцам.
Итак, давайте сначала добавим новый лист и загрузим туда скрипт:
Перейдем во вторую вкладку:
И данные из «Sainsburys_DATA.qvd», которые и будем группировать по месяцам:
Группировка данных в Qlik по одному критерию
Итак, давайте начнем группировку:
- Пишем строку «Group by Sains.Month;» — вот мы и сгруппировали данные, но нам нужно сделать дополнительные манипуляции в скрипте, чтобы получить значимые для аналитики данные:
- Нам совершенно точно нужно загрузить «Sains.Amount», но для того, чтобы Qlik понял, какую сумму загружать, если наш покупатель за месяц делал несколько покупок, нам нужно каким-то образом агрегировать сумму покупок. Пишем выражение: «sum(Sains.Amount)» — это будут все продажи в течении месяца. Мы также можем агрегировать и другими функциями – максимальное значение или среднее, но агрегация при группировке должна быть сделана.
- Также напишем выражения «sum(Sains.Visits)», чтобы вычислить количество визитов в месяц, и «max(Sains.Amount)», чтобы получить наибольшее значение за месяц.
На заметку! Поле, по которому мы группируем (в нашем случае – месяц) загружается без какой-либо агрегации.
- Получаем такой скрипт:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
LOAD //Sains.Shopper, //Sains.Date, Sains.Month, //Sains.Week, //Sains.WWYYYY, //Sains.Year, //Sains.MMM, sum(Sains.Amount), sum(Sains.Visits), max(Sains.Amount) FROM I:\The_Path\Qlikview\Sainsburys_DATA.qvd (qvd) Group By Sains.Month; |
- Делаем перезагрузку данных и получаем все нужные данные, сгруппированные по месяцам:
- Единственное, что нам пока не нравится – названия столбцов, которые названы формулами – добавим в скрипт алиасы и все будет готово:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
LOAD //Sains.Shopper, //Sains.Date, Sains.Month, //Sains.Week, //Sains.WWYYYY, //Sains.Year, //Sains.MMM, sum(Sains.Amount) as Sains.Amount, sum(Sains.Visits) as Sains.Visits, max(Sains.Amount) as Sains.Highest FROM I:\The_Path\Qlikview\Sainsburys_DATA.qvd (qvd) Group By Sains.Month; |
Группировка данных в Qlik по двум критериям
А теперь давайте представим, что мы хотим группировать данные не только по месяцу, но и, например, по покупателю, а также добавить выборку по годам и месяцам.
- Добавляем поле «Sains.Shopper» в загрузку, без агрегации. А в Group by после запятой прописываем второе поле для группировки «Sains.Shopper».
- Кроме того, поработаем с годом и месяцем, чтобы по ним можно было делать отдельные выборки в приложении – завернем их в функцию only().
В итоге получим код:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
LOAD Sains.Shopper, //Sains.Date, Sains.Month, //Sains.Week, //Sains.WWYYYY, only (Sains.Year), only (Sains.MMM), sum(Sains.Amount) as Sains.Amount, sum(Sains.Visits) as Sains.Visits, max(Sains.Amount) as Sains.Highest FROM I:\The_Path\Qlikview\Sainsburys_DATA.qvd (qvd) Group By Sains.Month, Sains.Shopper; |
Думаю, что принцип группировки данных в скрипте загрузки теперь понятен.
Спасибо, что прочитали до конца.
Хороших вам разработок!
Василий Петренко
B3
Максим
Stepan
Павел Карасев
Отличный материал для первоначального ознакомления с механизмами группировки исходных данных в QlikView! Хочу добавить несколько вынесенных из опыта практических моментов, более продвинутого уровня, но из серии «лучше знать заранее».
Самое важное: механизм агрегации данных (Group By) на уровне выполнения скрипта при создании модели данных разительно отличается от механизма агрегации на уровне Front-End вычислений в уже «собранной» ассоциативной модели. Первый (в скрипте) — намного менее производительный и более ресурсоемкий.
Так, если поставить эксперимент и запустить кейс, подобный описанному в статье, на существенном объеме данных (от десятка миллионов строк), можно с помощью Диспетчера Задач Windows понаблюдать, что загрузка CPU будет лишь на одно ядро, потребление RAM подскочит, и процесс займет несколько минут или более. Если же загрузить в ассоциативную модель данные без Group By, и в GUI сделать чарт с аналогичными измерениями и выражениями Sum, то загрузка CPU будет полная и результат будет рассчитан в секунды!
Озвученное утверждение приводит к следующему выводу: использовать Group By на больших объемах данных нужно только в том случае, если вы четко уверены, что положительный эффект (сокращение объема данных в ассоциативной модели, потеря аналитической детализации) перевесит отрицательные эффекты (значительное увеличение времени выполнения скрипта, значительный рост ресурсоемкости на этапе перезагрузки данных).
Qlik выгодно выделяется именно тем, что позволяет загружать в модель все данные на исходном уровне детализации (#seethewholestory). Его укрупнение без необходимости — противоречит как Qlik best practice, так и общепринятой концепции Kimball’s dimensional modelling.
Так что перед тем, как начинать оптимизацию моделей методом Group By, попробуйте проанализировать ваши QVW Document Analyzer’ом — возможно, удаление лишних полей и уменьшение кардинальности и оптимизация ключей принесет бОльший эффект меньшими затратами.
Если Group By большой таблицы все же необходим, то убедитесь, что учли следующие моменты:
1.Контринтуитивный но проверяемый факт: Group By на резидентной таблице работает со всеми ее полями, а не только с перечисленными! Поэтому, перед тем как делать Group By на широкой таблице, создайте сначала временную резидентную, куда загрузите те и только те поля, которые будете использовать в LOAD … Group By. Ресурсоемкость при таком подходе значительно снизится.
Реализация в коде:
[Fact table]:
LOAD
[Field 1],
[Field 2],
…
[Field n]
From …;
[Temp table]:
Noconcatenate
LOAD
[Field 1],
[Field n]
Resident [Fact table];
[Groupped Table]:
LOAD
[Field 1],
Sum( [Field n] )
Resident [Temp table];
Group By
[Field 1]
;
DROP Table [Temp table];
Во многих случаяхGroup By с партиционированием/секционированием будет быстрее,чем без него: цикл с 12 итерациями Group By каждого месяца и конкатенация результатов будет быстрее и менее ресурсоемок, чем Group By целого года. На одном из QRUG’ов рассказывалось об этом, вот ссылка на пример: https://dl.orangedox.com/dAtyJ4NsvC2xtrz8Ck
И напоследок, еще раз: следите за ресурсоемкостью! Непродуманный Group By может забрать всю память и повесить систему не хуже, чем непродуманный Join!
Вася, спасибо за комментарий! Очень полезная информация.
А если вместо создания временной таблицы двойной load делать? Т.е сначала грузим нужные поля в первом, а во втором только группируем?
Андрей, вы говорите о методе Preceeding Load, так вот лично я стараюсь избегать его — он экономит лишь несколько строк кода, но может принести ряд совершенно нежелательных побочных эффектов (например нештатная работа DISTINCT или Inter-record functions).
Попробуйте, возможно получиться. Если получите интересный результат — запостите здесь.
P.s. быстрый гугл qlikview preceeding load дал две интересных взаимоисключающих статьи Роба Вундерлиха и Хенрика Кронстрома, статья Роба посвежее — он показывает, что Preceeding Load заметно менее производительный чем Resident Load.