БЛОГ

Только качественные посты

Смотрите видео к статье:

Или операторы объединения таблиц SQL JOIN в Excel Power Query (начиная с Excel 2016)

Как известно самым популярным и эффективным инструментом работы с табличными данными (или просто таблицами) является программа Microsoft Excel.

При этом также известно, что самым мощным и распространённым языком программирования для работы с табличными данными является язык Structured Query Language = SQL = Язык структурированных запросов

Исходя из этого факта, логично предположить, что Microsoft Excel должен поддерживать язык SQL по умолчанию. Но, как правило, SQL поддерживается только базами данных (СУБД).

Несмотря на это, для Excel (начиная с 2010 версии) появилась бесплатная надстройка Power Query, которая позволяет имитировать часть полезного функционала языка SQL, а именно:

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

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

Различают следующие виды оператора «JOIN»:

  • INNER JOIN — Оператор внутреннего соединения двух таблиц
  • LEFT OUTER JOIN — Оператор левого внешнего соединения двух таблиц
  • RIGHT OUTER JOIN — Оператор правого внешнего соединения двух таблиц
  • FULL OUTER JOIN — Оператор полного внешнего соединения двух таблиц
  • CROSS JOIN — Оператор перекрёстного соединения (декартово произведение) двух таблиц

С выходом надстройки Power Query для Excel (это один из инструментов уровня Self-Service BI) в Excel появилась поддержка функционала всех видов операторов «JOIN» языка SQL:
SQL_JOIN_POWER_QUERY_BIWEB

Рассмотрим операторов «JOIN» в Excel на примерах:

Представим, что у нас в Excel есть две Таблицы: A «Люди» и B «Города»
SQL_JOIN_POWER_QUERY_BIWEB

Теперь давайте объединим данные таблицы с помощью различных операторов «JOIN»:
(объединяем таблицы через столбцы: A.Cityid = B.id)

Оператор INNER JOIN вернет следующий результат:
SQL_JOIN_POWER_QUERY_BIWEB

Оператор LEFT JOIN вернет следующий результат:
SQL_JOIN_POWER_QUERY_BIWEB

Оператор RIGHT JOIN вернет следующий результат:
SQL_JOIN_POWER_QUERY_BIWEB

Оператор FULL OUTER JOIN вернет следующий результат:
SQL_JOIN_POWER_QUERY_BIWEB

Оператор CROSS JOIN вернет следующий результат:
SQL_JOIN_POWER_QUERY_BIWEB

В надстройке Power Query для Excel данная функция «JOIN» называется «Слияние» — слияние запросов, где:

Оператору слияния INNER JOIN соответствует тип соединения: Внутреннее (только совпадающие строки)
SQL_JOIN_POWER_QUERY_BIWEB

Оператору слияния LEFT JOIN соответствует тип соединения: Внешнее соединение слева (все из первой таблицы, совпадающие из второй)
SQL_JOIN_POWER_QUERY_BIWEB

Оператору слияния RIGHT JOIN соответствует тип соединения: Внешнее соединение справа (все из второй таблицы, совпадающие из первой)
SQL_JOIN_POWER_QUERY_BIWEB

Оператору слияния FULL OUTER JOIN соответствует тип соединения: Полное внешнее (все строки из обеих таблиц)
SQL_JOIN_POWER_QUERY_BIWEB

Оператора слияния CROSS JOIN в интерфейсе Power Query нет, но его можно создать из оператора слияния FULL OUTER JOIN, убрав связи таблиц
SQL_JOIN_POWER_QUERY_BIWEB

Скачать Excel файл с примерами объединения SQL JOIN (функция «Слияние» в Power Query) можно скачать здесь

Пошаговая инструкция использования функции «Слияние»/«Объединения» в Power Query находится в видеоуроке к данной статье

Комментариев нет