Соединения и объединения | A-TECHS

Соединения и объединения

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

В операциях над таблицами различают объединения и соединения. Постараемся в понятных терминах объяснить суть данных операций. За строгими академическими определениями можно обратиться к материалам по реляционной алгебре.

Отдельно отметим функцию VLookUp. Функция не является операцией реляционной алгебры, но аналогичная функция ВПР очень популярна в Excel и применяется при решении широкого спектра задач. При определенных условия функцией VLookUp можно заменить левое соединение таблиц. Если вам поначалу будет сложно применять соединения таблиц, попробуйте разобраться с функцией VLookUp.

Объединения таблиц

В случае объединения у нас есть две или более таблицы одинаковой структуры. И нам нужно составить из них одну результирующую таблицу ( объединить в одну таблицу ).

Например,  мы храним сведения об остатках товаров на складе в журнале Товары, а о резервах товаров в журнале Резервы. А нам необходимо получить информацию о наличии товаров в целом: сколько у нас товаров в резерве и сколько на складе.

Для решения этой задачи мы получим остатки из журнала Резервы, затем получим остатки из журнала Товары. А затем к результирующей таблице, полученной по журналу Товары, добавим все записи, полученные из журнала Резервы.

Union_Example.png

Рисунок 1. Иллюстрация объединения таблиц.


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


Соединения таблиц

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

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

left_join.png

Рисунок 2. Иллюстрация левого соединения таблиц.

Всего различают четыре вида соединения таблиц: внутреннее соединение (Inner Join), левое внешнее соединение (Left Join), правое внешнее соединение  (Right Join) и полное  внешнее соединение ( Full Join).

Правое внешнее соединение с точностью до перестановки таблиц равно левому внешнему соединению. Можно всегда использовать только левое соединение и не использовать правое.

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

inner_join.png

Рисунок 3. Иллюстрация внутреннего соединения.

Результатом полного внешнего соединения является таблица, включающая в себя строки, являющиеся результатом внутреннего соединения. Плюс строки первой таблицы, не удовлетворяющие условию соединения, в этих строка значения в колонках второй таблицы заполняются значением null. И строки второй таблицы, не удовлетворяющие условию соединения, в этих строка значения в колонках первой таблицы заполняются значениями null.


Примечание. Null — специальное значение, обозначающее отсутствие любого значения.


full_join.png

Рисунок 4. Иллюстрация полного внешнего соединения.

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


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


Использование нескольких выражений в источнике данных.

При компоновке данных из нескольких журналов/справочников необходимо иметь возможность в выражении работать сразу с несколькими таблицами. В этом случае выражения для разные таблиц просто отделяются точкой с запятой. В качестве результата будет возвращено последнее выражение, а все остальные будут сохранены во временные таблицы с именами $result_<n>, где n — номер выражения по порядку.

Использование нескольких таблиц в выражении будет проиллюстрировано далее на примерах соединений и объединений таблиц.

Left Join

LeftJoin( tempTable, fieldName1 as alias1,…,fieldNameN as aliasN, joinCondition), где

tempTable — имя присоединяемой временной таблицы, например $result_1.

fieldName1,…, fieldNameN — имена результирующих полей. Поля, включаемые из основной таблицы, указываются просто по имени. Поля присоединяемой таблицы вносятся с указанием имени временной таблицы.

alias1,…, aliasN — при необходимости можно задать псевдонимы полей.

joinCondition — условие соединения.

Функция выполняет левое внешнее соединение. Включаются все строки основной таблицы, и строки присоединяемой таблицы, удовлетворяющие условию соединения. Для иллюстрации смотрите Рисунок 2.


Пример:

Сотрудники | Last(Сотрудник, Ставка);
Выработка | GroupBy( Сотрудник, КоличествоЧасов ) | LeftJoin( $result_1, Сотрудник, КоличествоЧасов, result_1.Ставка as Ставка, Сотрудник = result_1.Сотрудник)

Выбираем из журнала Сотрудники ставки сотрудников, помещаем во временную таблицу $result_1. Выбираем из журнала Выработка выработанные часы сотрудников. Выполняем левое соединение по условию совпадения в строке сотрудника.

Левое соединение таблиц проиллюстрировано на Рисунке 2.


Full Join

FullJoin( tempTable, fieldName1 as alias1,…,fieldNameN as aliasN, joinCondition), где

tempTable — имя присоединяемой временной таблицы, например $result_1.

fieldName1,…, fieldNameN — имена результирующих полей. Поля, включаемые из основной таблицы, указываются просто по имени. Поля присоединяемой таблицы вносятся с указанием имени временной таблицы.

alias1,…, aliasN — при необходимости можно задать псевдонимы полей.

joinCondition — условие соединения.

Функция выполняет полное внешнее соединение. Для иллюстрации смотрите Рисунок 4.

UnionAll

UnionAll( tempTable1, tempTable2,…,tempTableN), где tempTable1,…,tempTableN — имена временных таблиц.

Объединение двух или большего числа таблиц. Строки объединяемых таблиц “приклеиваются” снизу. Перечень колонок определяется по главной таблице.


Пример:

Резервы | GroupBy( Product, Quantity) ;
Товары | GroupBy( Product, Quantity) | UnionAll ( $result_1 )

К таблице Товаров снизу будут добавлены все строки таблицы Резервы.


Union

Union( tempTable1, tempTable2,…,tempTableN), где tempTable1,…,tempTableN — имена временных таблиц.

Объединение двух или большего числа таблиц. Записи объединяемых таблиц “приклеиваются” снизу. Перечень колонок определяется по главной таблице. Добавляются только уникальные строки. Для корректной проверки уникальности строк необходимо, чтобы колонки в объединяемых таблицах совпадали.

VLookUp

VLookUp( dataField, searchObject, searchField, fieldName1 as alias1, fieldName2 as alias2,…,fieldNameN as aliasN), где

dataField — поле  текущих данных, по которому будет выполняться поиск

searchObject — журнал/справочник, в котором выполняется поиск

searchField — поле searchObject, по которому будет выполняться поиск

fieldName1,…, fieldNameN — имена полей, которые необходимо выбрать из журнала/справочника

alias1,…, aliasN — псевдонимы полей

Функция не является операцией реляционной алгебры. Но мы реализовали данную функцию, т.к. аналогичная функция ВПР очень популярна в Excel. Данная функция проще для понимания, чем операции соединения таблиц. Функция VLookUp может заменять левое соединение в случае, когда условием соединения является простое равенство двух полей в таблицах.

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

При необходимости может быть использовано несколько функций VLookUp в рамках одного выражения.


Пример:

Выработка | GroupBy( Сотрудник, КоличествоЧасов) | VLookUp( Сотрудник, Сотрудники, Сотрудник, Ставка)

Реализация задачи из Рисунка 2 без использования соединений при помощи функции VLookUp.