Шаблоны примитивов обработки данных

15 минутное чтение

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

Произвольная выборка

Шаблон «Произвольная выборка» — эффективный инструмент для выборки, очистки, трансформации данных и вспомогательных расчётов. Шаг настраивается в окне «Мастер настройки выборки данных». По умолчанию активна вкладка «Настройка полей запроса».

Как в большинстве шаблонов группы, вкладка имеет два секции: левую (источники данных) и правую (поля новой таблицы, которую создаёт шаг сценария).

Изначально задается один обязательный источник (его можно увидеть на первой вкладке «Выбор источника»). Если была задана связь с предыдущим шагом сценария, то это как раз таблица предыдущего шага (к примеру, созданного на основе шаблона «Источник» или других примитивов обработки данных). Однако, для новой таблицы можно выбирать и трансформировать поля из множества разных таблиц. Достаточно нажать кнопку со знаком «+» и указать дополнительную таблицу-источник в диалоговом окне «Выбор таблиц для сценариев». Новая таблица появится в левой секции. Контекстное меню (вызывается правой кнопкой мыши) позволяет удалять таблицы-источники.

Нужные для выборки поля перемещаются из левой секции в правую с помощью мыши (методом drag-and-drop), или кнопок «>» и «<». На предыдущем рисунке использованы поля «Товар» (из таблицы «test_xlsx_ad») и «Сумма» (из таблицы «demo_tbl»), а на основе поля «Дата» (из таблицы «demo_tbl») сгенерировано новое поле «Дата_Месяц». Точка напротив использованных полей в исходной таблице меняет цвет на синий.

В правой секции вкладки «Настройка полей запроса» можно:

добавлять, удалять поля новой таблицы. Предусмотрены кнопки «Добавить» и «Удалить всё»;
менять порядок полей сверху вниз (кнопками со стрелками над секцией). Это влияет на порядок полей слева направо (соответственно) формируемой таблицы;
задавать псевдоним нового поля ручным вводом в колонке «Псевдоним» (по умолчанию имена полей совпадают с исходными, либо генерируются автоматически при создании части даты);
трансформировать выражения, производить дополнительные расчёты с помощью вставок кода на SQL. В колонке «Поле/Выражение» двойной щелчок мыши выводит кнопку с символом троеточия «…». Нажатие на кнопку открывает подокно «Произвольное выражение» (рисунок ниже), где вручную вводится код;

менять тип полей в новой таблице с помощью колонки «Тип». Интерфейс аналогичный колонке «Поле/Выражение» — двойной щелчок мыши и кнопка «…»;
задавать сортировку по полю. Двойной щелчок мыши в колонке «Сорт.» задает сортировку по убыванию, возрастанию (поле «Товар»), либо удаляет сортировку. Сортировка работает, если в поле «Ограничить размер выборки» введено число строк. При включенной сортировке появляется символ треугольника.

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

В примере из поле «Дата» извлечено название месяца на русском языке. После закрытия окна в столбце «Поле/Выражение» генерируется код на SQL:

to_char(Дата, 'TMMonth')

Если в шаге данные собираются из нескольких таблиц, необходимо связать исходные таблицы между собой (вкладка «Связи таблиц»). Двойной щелчок мыши по названию нижестоящей таблицы «test_xlsx_ad» вызывает диалоговое окно «Соединения». В окне выбираются тип соединения («Левое») и связанные поля («Товар»).

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

Каждый отбор создаётся кнопкой «Добавить новый элемент» (правее есть кнопки группировки условий и удаления). Потом надо указать поле для отбора, вид сравнения и в колонке «Значение» ввести константу.

В итоге, в нижней части окна появится SQL-код условий отбора:

"ad_test_demo_tbl_t"."Товар" = 'Товар 1'

Можно использовать группу условий, которые должны одновременно выполняться (логическое «И»), либо не выполняться (логическое «НЕ»), либо достаточно выполнения одного любого условия из группы (логическое «ИЛИ»). Для этого надо выделить несколько условий и нажать кнопку «Сгруппировать условия«, а затем выбрать в заголовке группы нужный логический вариант. Код отбора SQL можно ввести вручную, если активировать режим «Текст отбора задается произвольным кодом».

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

Группировка

Шаблон «Группировка» позволяет агрегировать данные из таблицы-источника или таблицы предыдущего шага. Соответствует SQL-операции GROUP BY.

На следующем рисунке рассчитана сумма по полю «Сумма» для каждого значения поля «Товар».

Группируемые поля («Товар») перемещаются из левой секции «Поля источника» в нижнюю правую («Группировки») с помощью мыши (методом drag-and-drop). Точки напротив имён использованных полей поменяют цвет на синий.

Кнопки со стрелками над секцией «Группировки» позволяют менять порядок группируемых полей сверху вниз. Это влияет на их порядок в новой таблице слева направо (соответственно).

Поля для агрегаций (например, суммы, среднего, максимума, минимума, количества строк) также перемещаются с помощью мыши из левой секции «Поля источника» в верхнюю правую («Показатели»). Здесь изменение порядка строк сверху вниз производится с помощью контекстного меню (вызывается правой кнопкой мыши: там есть варианты для перемещения вверх и вниз).

Двойной щелчок мыши по полю в «Показатели» и нажатие кнопки «…» открывает диалоговое окно «Произвольное выражение». В окне можно вручную прописать код SQL для агрегации («Выражение»).

В каждой строке секций «Группировки» и «Показатели» есть столбец «Псевдоним». В столбце можно вручную ввести новое название колонки.

По умолчанию, агрегируемому выражению в секции «Показатели» присваивается тот же числовой тип, что и в исходном поле. На вкладке есть селектор «Показывать типы полей». Желательно его активировать. Тогда в агрегации можно менять тип чисел. Может понадобиться тип с большей разрядностью, если суммы в исходной таблице большие и итоговый расчёт выйдет за диапазон данных исходного поля. Иначе результатом группировки будет ошибка.

Результат шага отражается на одноименной вкладке: можно увидеть полученную таблицу и код SQL для её генерации.

Соединение

Шаблон «Соединение» нужен для объединения данных из нескольких таблиц. Для шага с таким шаблоном нужны связи из предыдущих шагов (например, таблиц-источников или вычисляемых таблиц с помощью примитивов). Должно быть минимум две таблицы для объединения. Рассмотрим пример соединения слева (аналог функции «ВПР» в MS Excel / операции LEFT JOIN в SQL). К таблице продаж по адресу надо добавить почтовый индекс.

В нижней секции «Условия связи таблиц» выбирается тип соединения («Левое»), с помощью кнопки «Добавить» добавляется строка условия равенства двух адресов. Две колонки называются по имени исходных таблиц («Продажи (основной)» и «Справочник»), а в выпадающих списках надо лишь выбрать два раза поле «Адрес». Между двумя полями есть столбец для вида условия, также с помощью выпадающего списка (в примере это равенство «=»).

Если ключ для связи составной (основан на двух и более полях), добавляются дополнительные строки условий (есть кнопка «Добавить»).

Шаг позволяет объединять за один раз более двух таблиц. В этом случае, в выпадающем списке «Присоединяемые таблицы» надо выбрать третью (четвертую и т.д.) таблицу и аналогичным образом задать связи между полями в секции «Условия связи таблиц».

Далее, следует выбрать поля, которые попадут в итоговую таблицу шага. На вкладке «Настройка полей запроса» в левой секции отображаются исходные таблицы и их поля. С помощью мыши методом drag-and-drop (или кнопками «>» и «<») их можно перетащить в правую секцию, где собираются поля и выражения итоговой таблицы. Точки напротив имён использованных полей поменяют цвет на синий.

Кнопки со стрелками над правой секцией позволяют менять порядок группируемых полей сверху вниз. Это влияет на их порядок в новой таблице слева направо (соответственно).

Кнопка «Добавить» добавляет новую строку, в которой можно создать выражение. Двойной щелчок мыши по столбцу «Поле/Выражение» и нажатие кнопки «…» открывает диалоговое окно «Произвольное выражение». В окне можно вручную прописать код SQL для расчёта нового показателя («Выражение»). Из рисунка выше можно понять, что адрес продаж переводится в верхний регистр.

В каждой строке правой секции есть столбец «Псевдоним». В столбце можно вручную ввести новое название колонки.

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

Таблица, генерируемая шаблоном, отражается на вкладке «Результат». Здесь можно увидеть также код SQL для генерации данных.

Отбор

Шаблон «Отбор» используется для фильтрации таблицы по одному или группе полей. В логике SQL он соответствует оператору WHERE. Надо выбрать поля из таблицы предыдущего шага и указать условия фильтрации (одно или более).

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

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

На рисунке выше исходная таблица предыдущего шага фильтруется одновременно по трём полям:

наименование товара должно быть «Товар 1»;
дата продаж до 01.01.2024;
сумма продаж в каждой строке больше 50 000 руб.

Вкладка «Настройка отбора данных» имеет два секции: левую (источник данных) и правую (условия отбора для новой таблицы, которую создаёт шаг сценария).

Нужные для фильтрации поля перемещаются из левой секции в правую с помощью мыши (методом drag-and-drop), или кнопок «>» и «<». Точка напротив использованных полей в исходной таблице меняет цвет на синий.

В правой секции можно:

добавлять, удалять поля с условиями фильтрации. Предусмотрены кнопки «Добавить новый элемент» и «Удалить», а также «Сгруппировать условия»;
менять порядок условий сверху вниз (кнопками со стрелками над секцией);
задавать тип сравнения двойным щелчком мыши и выбором из выпадающего списка. Для дат кроме произвольной даты есть множество вариантов («Начало прошлого года», «Начало этого дня» и т.д. для дней, недель, месяцев и лет). Далее, в колонке «Значение» следует ввести константу, а перед именем поле активировать галочку.

В итоге, в нижней части окна появится SQL-код условий отбора.

Код отбора SQL можно ввести вручную, если активировать режим «Текст отбора задается произвольным кодом».

Top N

Шаблон «Top N» извлекает из таблицы исходных данных первые N записей. Соответствует оператору SQL LIMIT. При этом, в шаблоне есть возможность фильтрации и сортировки.

Такой шаблон полезен, когда вместе с сортировкой по какому-то полю можно выбрать N «лучших» или «худших» позиций в контексте отсортированного поля.

Рассмотрим пример, когда на предыдущем шаге есть таблица следующего вида.

Выберем три самых продаваемых товара, но с суммой продаж более 1 300 000 руб. Отсортируем таблицу по полю «Сумма» и три записи с фильтром по полю «Сумма» от 1 300 000.

На первом шаге, следует указать поле для сортировки. Вкладка «Настройка сортировки» имеет два секции: левую (источник данных) и правую (условия отбора для новой таблицы, которую создаёт шаг сценария).

В правой секции можно:

менять порядок условий сверху вниз (кнопками со стрелками над секцией). Это важно, потому что можно одновременно сортировать более одного поля. Порядок полей сверху вниз влияет на очередность сортировки: сортировка полей выше приоритетнее, чем тех, что ниже;
добавлять, удалять поля с условиями фильтрации. Для этого предусмотрено контекстное меню, вызываемое правой кнопкой мыши;
задавать тип сортировки двойным щелчком мыши по колонке «Сортировка». Сортировать можно по убыванию или возрастанию (в данном случае логично по убыванию). В колонке »Сортировка» появится знак треугольника соответствующей формы.

В поле «Размер выборки» введём цифру «3».

На следующем шаге зададим фильтрацию (вкладка «Настройка отбора данных«).

Здесь также есть две секции: левая (список полей исходной таблицы с предыдущего шага) и правая (условия отбора для полей).

Нужные для фильтрации поля перемещаются из левой секции в правую с помощью мыши, или кнопок «>» и «<». Точка напротив использованных полей в исходной таблице меняет цвет на синий (в данном случае поле «Сумма»).

В правой секции можно:

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

Для дат есть множество вариантов («Произвольная дата» (с выбором из календаря), «Начало прошлого года», «Начало этого дня» и т.д. для дней, недель, месяцев и лет).

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

Объединение

Шаблон «Объединение» позволяет объединить разные таблицы (из предыдущих шагов) построчно. Соответствует логике SQL-оператора UNION.

На первом шаге (вкладка «Настройки объединения») надо указать, как поля разных таблиц соответствуют друг другу. На вкладке появляется таблица-конструктор. Каждая её колонка — это поля таблиц, участвующих в объединении (их может быть более двух). Если у полей разных таблиц одинаковые названия и идентичные типы данных, то процесс ускорит кнопка «Заполнить»: этот инструмент сам произведет расстановку. Если нужна корректировка, в каждой ячейке есть раскрывающийся список и можно выбрать соответствующее поле вручную.

При включенном селекторе «Только уникальные» из итоговой (объединённой) таблицы удаляются повторяющиеся строки. В коде SQL из оператора UNION исключается параметр ALL. На вкладке «Результат» можно посмотреть, какая таблица получится на выходе, и какой текст запроса на SQL соответствует всему шагу.

Unpivot

Шаблон «Unpivot» выполняет действия, обратные шаблону «Pivot», преобразуя столбцы данных в строки. Соответствует одноимённому оператору SQL.

Например, есть данные о продаже товаров помесячно следующего вида.

На следующем рисунке — пример настройки шаблона.

В левой секции окна настройки шаблона выделены все поля, поскольку они все задействованы.

На вкладке «Настройка полей запроса» в имени «Категория» вводим «Месяц», а в «Имя поля значения» вводим «Сумма продаж в руб.». В нижнюю правую секцию «Значение (категория)» следует переместить (мышью методом «drag-and-drop» или кнопками «<» и «>») поля с месяцами, и напротив каждого месяца можно вручную ввести полное наименование. С помощью контекстного меню можно удалить лишние поля из списка.

Если флаг «Выводить пустые» проставлен, то строки со значениями NULL будут выведены, в противном случае, строки не выводятся.

В «Тип поля значения» можно скорректировать тип (например, увеличить разрядность чисел, чтобы не было переполнения).

Pivot

Шаблон «Pivot» производит действия, обратные шаблону «Unpivot»: он преобразует строки данных в столбцы. Соответствует одноимённому оператору SQL. Например, есть данные о продаже товаров помесячно следующего вида. Требуется данные с названиями месяцев перевести в столбцы.

В окне настройки шаблона есть четыре секции:

«Поля источника» — перечень полей исходной таблицы;
«Строки (группировки)» — для полей, которые останутся в столбцах (в данном случае поле «Товар»);
«Столбцы» — для поля, которое из столбца трансформируется в строки («Месяц»);
«Значения» — числовое поле со значениями («Сумма продаж в руб.»).

Поля из секции «Поля источника» добавляются в другие три секции с помощью мыши методом drag-and-drop. Слева от названия полей, использованных в секции «Поля источника», появляется цветная точка.

В процессе трансформации для числового поля можно выбрать агрегацию (сумма, количество, число уникальных значений, минимум, максимум и среднее). Такая возможность может быть актуальна, если для комбинаций полей в «Столбцы» и «Строки (группировки») есть много записей. Например, если бы в исходной таблице записи повторялись для одних и тех же товаров в одном месяце, но по дням месяца. В некоторых случаях, в принципе, для исследования данных интересны аналитики типа среднего, максимум, минимума и т.д.

В рассматриваемом примере выбирается вариант суммирования, и укрупнения информации не произойдёт.

Результат работы шаблона (вкладка «Результат») — трансформированная таблица и SQL-код для шага.

Маппинг 2х таблиц

Шаблон «Маппинг 2х таблиц» позволяет копировать данные из одной таблицы в другую, используя настроенные правила соответствия (маппинга) полей. С помощью шаблона надо показать, какие поля первой таблицы соответствуют каким полям другой таблицы в рамках SQL-инструкции INSERT INTO. Также можно произвести фильтрацию исходных данных и некоторые преобразования.

Например, необходимо скопировать данные из одной таблицы (таблица-источник) в другую таблицу (таблица-приёмник) (без округа ЮФО). Данные поля «Регион» таблицы-источника соответствует полю «Область» таблицы-приёмника.

На вкладке «Источник и приёмник» надо выбрать имена двух таблиц в базе данных.

Округ ЮФО отфильтрован на вкладке «Настройки отбора данных».

Каждый отбор создаётся кнопкой «Добавить новый элемент» (правее есть кнопки группировки условий и удаления). Потом надо указать поле для отбора, вид сравнения (из выпадающего списка) и в колонке «Значение» ввести константу.

Можно задать сразу множество отборов, а также группироваить их (когда условия одновременно выполняются — логическое «И», не выполняются — логическое «НЕ», либо достаточно выполнения одного любого условия из группы — логическое «ИЛИ»). Для этого надо выделить несколько условий и нажать кнопку «Сгруппировать условия», а затем выбрать в заголовке группы нужный логический вариант.

Далее, на одноимённой вкладке устанавливается связь полей (рисунок ниже). Из левой секции «Источник» (где есть перечень полей таблицы-источника) с помощью мыши методом drag-and-drop нужные поля перемещаются в правую секцию «Приёмник». Если поля называются одинаково, можно воспользоваться кнопкой «Автосопоставление». Предусмотрена кнопка и быстрой очистки сопоставлений. Над обоими секциями есть кнопки «Показать таблицу» для предварительного просмотра данных.

Чтобы суммы продаж были в одном измерении, поле таблицы-источника «Сумма_продаж_млн_руб.» перед копированием в поле «Продажи_в_руб.» таблицы-приёмника надо умножить на 1000000. После двойного щелчка мышью по имени перемещенного поля в правой секции («Сумма_продаж_млн_руб.») появится кнопка «…», которая вызовет диалоговое окно «Произвольное выражение». В этом окне можно ввести необходимую формулу для вычислений.

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

Создание таблицы БД

Шаблон «Создание таблицы БД» позволяет создать пустую таблицу базы данных с определенным набором полей нужного типа. Для шаблона не нужны предыдущие шаги, чтобы получить входные данные. Такой шаг соответствует SQL-оператору CREATE TABLE.

На вкладке «Основная» задается «Имя таблицы», схема, и с помощью кнопки «Добавить» создать каждое поле. В колонке «Имя поля» вручную вводится имя поля. Двойной щелчок мыши в колонке «Тип поля» активизирует кнопку «…», которая позволяет задать тип (из категории «Типы данных» или «Пользовательские типы»).

Клавиши со стрелками вверх и вниз меняют порядок полей в новой таблице слева-направо соответственно.

На вкладке «Основная» также предусмотрен селектор «Создать если отсутствует» и «Удалить и создать». Выбранный режим выделяется синим цветом текста и влияет на алгоритм шаблона: в первом случае таблица просто создается с нуля, если её нет, во втором случае она всегда удаляется и заново создается.

Селектор «Скрыть служебные параметры» скрывает настройки: подтип данных для поля, и допускаются ли пустые значения (NULL) в конкретном поле. Результат работы шаблона (вкладка «Результат») — код на PostgreSQL.

Кнопка «Готово» в верхнем левом углу шаблона завершает настройку шага.

Разделение строки

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

Шаблон соответствует оператору UNNEST с параметром STRING_TO_ARRAY, которые специфичны для PostgreSQL.

Такой шаг может быть полезен для анализа данных, значения которых разделены определенным символом. Например, встречаются выгрузки в формате «CSV» в которых наборы данных в тексте разделены запятой.

Рассмотрим пример, когда на метеостанциях ежедневно, 6 раз в сутки, снимаются показания температуры.

Кнопка «Показать входные данные» выгружает в нижней секции пример исходной таблицы. Очевидно, что из списка полей «Разделяемое поле» надо выбрать «Температура», и укажем «Разделитель» («,»).

Ниже — результат работы шага (вкладка «Результат»). Из каждой строки исходной таблицы получилось 6 строк. Значения полей «Дата» и «Метеостанция» дублируются для каждой новой строки, а поле «Температура» разделено на 6 значений.

Маппинг 2х таблиц с предварительным удалением

Шаблон «Маппинг 2х таблиц с предварительным удалением» работает аналогично шаблону Маппинг 2х таблиц, с одним отличием. На вкладке «Связь полей» задаются поля (ключ) для поиска (в колонке «Поиск»). В таблицу-приёмник попадут все записи из таблицы-источника. Но если какие-то записи совпадут по ключу поиска, то они в таблице-приёмнике будут заменены соответствующими записями из таблицы-источника.