Объединение таблиц по схеме Left-By в Envision

Объединение таблиц по схеме By-At












Главная » Ресурсы » Здесь

Система Envision обладает скрытым механизмом объединения таблиц, в которых есть столбец Id (индекс наименований) — к таким таблицам часто относятся данные о продажах и закупках. Тем не менее, данный скрытый механизм (он называется естественное соединение) не подходит для более сложных ситуаций. Поэтому Envision предлагает более универсальный механизм объединения, который называется By-At. Схема By-At — это особая разновидность объединения данных, которая позволяет объединять таблицы нужным образом.

Образец сценария

В данном разделе мы используем образец набора данных, который можно найти в папке /sample в вашей учетной записи Lokad. Прежде чем продолжать работу, прочитайте статью о чтении файлов с помощью Envision и уделите особое внимание используемым типам файлов, так как этот раздел крайне важен для понимания следующего сценария.
read "/sample/Lokad_Items.tsv"
read "/sample/Lokad_Orders.tsv" as O
read "/sample/Lokad_Suppliers.tsv" as S[*]

Moq = same(S.Moq) by S.Supplier at Supplier
show table "Item list" a1d4 tomato with
  Id
  Name
  Supplier
  Moq
В первых трех строках приводятся обычные операторы для считывания файлов. В третьей строке ожидание типа S[*] показывает, что для таблицы поставщиков не ожидается никаких типов — это независимая таблица без первичных или внешних ключей. В файле Lokad_Suppliers.tsv, в отличие от всех остальных файлов в .tsv в папке, нет столбца Id. Из-за того что этой колонки в нем нет, система Envision не может автоматически объединить эту таблицу с таблицей Items. Таблица поставщиков объединяется по поставщикам, причем каждый поставщик занимает одну строку, при этом все остальные таблицы индексируются по наименованиям.

В строке 5 мы применяем объединение по схеме By-At. Оно называется так, потому что в нем используются ключевые слова by и at для объединения таблицы Items и таблицыS. Мы могли бы также написать следующий сценарий:
Items.Moq = same(S.Moq) by S.Supplier at Items.Supplier
Согласно требованиям Envision, название таблицы Items обычно опускается.

В данном примере мы заполнили вектор Items.Moq данными, полученными из таблицы S. Два поля, Items.Supplier и S.Supplier, были объединены.

Если данные о поставщике из таблицы Items не соответствуют данным из таблицы S, значение ячейки устанавливается на ноль. Однако с помощью ключевого слова or, которое стоит после обычных агрегаторов Envision, можно прописать другие значения по умолчанию. Мы могли бы написать следующий сценарий:
Items.Moq = same(S.Moq) by S.Supplier at Items.Supplier or 0
В строках 6–10 прописано отображение итоговой таблицы Items, которая отражает расчеты, выполненные в строке с конструкцией By-At, описанной выше. Как и ожидалось, всем наименованиям было присвоено значение MOQ, установленное соответствующим поставщиком.

Базовый синтаксис для By-At

Общий синтаксис для By-At выглядит следующим образом:
T1.A = agg(T2.B) by [T2.X, T2.Y, T2.Z] if E at [T1.X, T1.Y, T1.Z] or T1.C
Как и другие агрегаторы Envision, By-At позволяет производить кратный подбор — то есть сопоставлять n полей одновременно. Можно использовать все привычные агрегаторы Envision: sum, а также min, median, same и т. д. Блок or можно опустить, как и при обычном объединении.

Семантику By-At можно описать следующим образом:

  • Построить все группы кортежей для by [T2.X, T2.Y, T2.Z], фильтр с условием E, если есть. Рассчитать обобщенное значение agg(T2.B) для всех этих групп, если им соответствует хотя бы одна группа слева.
  • Построить все группы кортежей для at [T1.X, T1.Y, T1.Z]. Назначить соответствующее обобщенное значение справа для всех этих групп.
  • Для всех групп с параметром at, у которых нет соответствия справа, установить значение T1.C по умолчанию.

Как и обычные агрегаторы, оператор or не является строго обязательным, и он возвращает значение, являющееся результатом объединения, если группа пуста.

Советы по использованию By-At

By-At — это мощная конструкция, которую можно использовать во многих ситуациях, а не только для объединения таблиц для SQL.

Таблицу можно соединить саму с собой по следующей схеме: Например:
O.DaySum = sum(O.Quantity) by O.Date at O.Date
С помощью данного сценария можно рассчитывать ежедневные показатели по таблице O, не обращаясь к таблице Day.

Календарные таблицы Day и Week можно объединять по следующей схеме: Например:
Day.Shift = sum(O.Quantity) by [O.Id, O.Date - 1] at [Day.Id, Day.Date]
С помощью данного сценария можно переносить заказы с одного дня на другой по схеме By-At.

Перевод By-At в SQL

Тем, кто знаком с SQL, может быть интересно, что выражение Envision:
Moq = same(S.Moq) by S.Supplier at Supplier or defaultMoq
имеет следующий эквивалент в SQL, в котором используется внешнее объединение слева: UPDATE Items LEFT OUTER JOIN S ON Items.Supplier = S.Supplier SET Moq = COALESCE(S.Moq, defaultMoq) Синтаксис Envision делает упор на вычисления, подобные тем, которые выполняются в Excel, а не на алгебру отношений как таковую.

Перекрестные соединения

Перекрестное соединение — еще одна классическая реляционная операция, которая поддерживается Envision. Перекрестное соединение работает по принципу прямого произведения соединяемых строк. Перекрестное соединение позволяет легко создать большое число обрабатываемых строк, поэтому рекомендуется строго следить за объемами соединенных таблиц, если вы не хотите, чтобы время их обработки вышло за пределы разумного. Синтаксис перекрестного соединения:
T1.R = sum(T1.A * T2.B) cross (T1, T2) if (T1.C & T2.D) or T1.Default
Данный оператор создает временную таблицу без названия, которая представляет собой объединение таблиц T1 и T2. Аргумент агрегатора, как и фильтр «IF», оцениваются в этой таблице (и принудительно импортируются из таблиц T1 и T2). Результат объединения загружается в таблицу T1 за счет объединения отфильтрованных строк перекрестного соединения, которые соответствуют строкам T1.

Если между T1 и T2 уже есть связь, например, по столбцам Items и Orders, где вторую таблицу можно загрузить в первую, или если обе таблицы идентичны, то можно использовать псевдоним:
T1.R = sum(T1.A * TAlias.B) cross (T1, T2 as TAlias) if (T1.C & TAlias.B) or T1.Default
Таблица-псевдоним TAlias содержит те же переменные, что и T2, но никак не соотносится с остальными таблицами (кроме таблицы, полученной в результате перекрестного соединения). Таблица-псевдоним недоступна за пределами аргумента агрегатора и фильтра «IF». Имя псевдонима может использоваться больше одного раза в сценарии (в отдельных агрегаторах), однако оно не должно конфликтовать с исходными и встроенными таблицами, а также таблицами, созданными с помощью оператора table T = ... (как показано выше).

Размер таблицы, получаемой в результате перекрестного соединения, на данный момент ограничен строкой 4e9 (всего четыре миллиарда строк). При перекрестном соединении таблицы с ней же самой в ней может быть не более 60 000 строк. На практике работа Envision значительно замедлится задолго до того, как вы достигнете ограничения в 4 миллиарда строк. Чтобы сложность выполняемых операций не выходила за пределы разумного, рекомендуется надлежащим образом фильтровать объединенные таблицы до перекрестного соединения.