Объединение таблиц по схеме 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 Orders
read "/sample/Lokad_Suppliers.tsv" as Suppliers[*]

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

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

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

В случае если данные о поставщике из таблицы Items не соответствуют данным из таблицы Suppliers, значение ячейки устанавливается на нуль. Однако с помощью ключевого слова or, которое стоит после обычных агрегаторов Envision, можно прописать другие значения по умолчанию. Мы могли бы написать следующий сценарий:
Items.Moq = same(Suppliers.Moq) by Suppliers.Supplier at Items.Supplier or 0
В строке 6 прописано отображение итоговой таблицы 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.

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

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

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

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