Как связать таблицы в датасете DataLens через JOIN

Свяжите справочник каналов с учебными расходами по channel_id: выберите тип связи, задайте ключ и проверьте, что итоговая сумма 3500 сохранилась.

Как связать таблицы в датасете DataLens и сохранить все расходы: присоедините справочник каналов через LEFT JOIN по полю channel_id, чтобы в учебном примере после связи остались 3 строки и сумма 3500 — эти два числа помогут проверить результат.

Из каких таблиц можно объединять данные

В датасет добавляются данные из таблиц, которые доступны в источнике. Для источников-БД также можно использовать произвольный SQL-запрос (Работа с датасетом[2]). Ограничение здесь жёсткое: нельзя объединить данные из разных источников на уровне одного датасета (Объединение данных[1]). Связь на уровне датасета строится внутри одного подключения.

Данные из разных датасетов можно показать в мультидатасетном чарте: запросы к каждому датасету выполняются независимо. QL-чарт строится на основе подключения к БД и сам по себе не снимает ограничение на объединение разных источников (Объединение данных[1]).

Практический вывод для задачи со справочником и фактами: держите обе таблицы в одном подключении. Тогда связь настраивается в датасете, а не собирается заново в каждом чарте.

Добавление таблиц на рабочую область

Порядок:

  1. В датасете перейдите на вкладку Источники.
  2. Выберите подключение к источнику данных.
  3. Перетащите нужные таблицы с панели таблиц на рабочую область.

Между таблицами автоматически установится связь по первому совпадению имени и типа данных полей (Работа с датасетом[2]). Автосвязь — удобная отправная точка: если имена и типы совпали, DataLens уже предложит связь, которую остаётся проверить и при необходимости заменить.

Если новая таблица должна связаться не с первой таблицей на области, наведите её на нужную таблицу так, чтобы вокруг появилась серая рамка, и отпустите. Связь образуется с указанной таблицей, а не с первой по умолчанию (Работа с датасетом[2]).

Тип связи и ключевое поле

Чтобы открыть настройку связи, в датасете на вкладке Источники нажмите на изображение связи между таблицами. Затем выберите тип: inner, left, right или full (Работа с датасетом[2]). Доступны операторы INNER, LEFT, RIGHT и FULL (Настройки датасета[3]).

Тип Что попадает в результат Когда выбирать под задачу
inner
только строки, у которых ключ есть в обеих таблицах
нужны факты с подтверждённым справочным значением
left
все строки левой таблицы плюс совпадения из правой
нужны все факты, даже без метки в справочнике
right
все строки правой таблицы плюс совпадения из левой
нужен полный справочник независимо от фактов
full
все строки из обеих таблиц
нужно увидеть и несовпадения с двух сторон

Поля для связи выбираются из списка; при наведении показывается полное имя поля, работает поиск по имени. Установить связь можно только по полям с одним и тем же типом данных, и DataLens получает тип из подключения. Если типы не совпадают, есть два варианта: привести поля к одному типу на уровне источника либо описать датасет через SQL-запрос с функциями CAST или CONVERT (Работа с датасетом[2]).

Важный нюанс: при объединении таблиц в датасете появляются дубликаты полей, по которым установлена связь. Их можно удалить вручную из списка полей либо исключить повторы SQL-запросом (Работа с датасетом[2]).

Опция «Оптимизировать связь»

Если опция Оптимизировать связь включена и в чарте используются поля только из одной связанной таблицы, DataLens может пропустить оператор JOIN: запрос возвращает данные только из этой таблицы без фильтрации по другим. Так уменьшается объём запрашиваемых данных и время выполнения, но результат может отличаться от ожидаемого (Объединение данных[1]).

Оптимизация срабатывает при всех условиях сразу: опция включена, в чарте поля только одной из связанных таблиц, поля других таблиц не находятся ни в одной секции чарта и не применяются в вычисляемых полях (Объединение данных[1]).

Если нужно, чтобы объединение выполнялось всегда, даже когда выбираются поля только из одной таблицы, опцию отключают: тогда связь становится обязательной и оператор JOIN применяется безусловно (Работа с датасетом[2]).

Проверка связи на учебных данных

Учебный пример. Две таблицы в одном подключении. costs — факты расходов по каналам, channels — справочник каналов.

Таблица costs:

channel_id cost
1
1000
2
2000
3
500

Справочник channels:

channel_id label
1
search
2
social

Схема: costs — левая таблица, channels — правая. Ключ связи — поле channel_id с одинаковым типом данных в обеих таблицах. Тип связи — left.

Результат после LEFT JOIN:

channel_id cost label
1
1000
search
2
2000
social
3
500
NULL

Проверка результата по двум контрольным числам:

  • строк — 3, как и в исходной costs;
  • сумма cost — 3500, как и в исходной costs.

Условие, при котором обе метрики сохранны: ключ channel_id в правой таблице уникален. Если в справочнике окажется два значения channel_id = 1, LEFT JOIN даст две строки для этого ключа, и сумма расходов вырастет — это уже размножение строк, а не корректная связь.

Что означают числа: третья строка получила label = NULL, потому что ключ channel_id = 3 отсутствует в channels. LEFT JOIN сохранил эту строку расходов, а метка канала для неё не нашлась. С типами связи это ожидаемое поведение: LEFT JOIN берёт все строки левой таблицы и добавляет совпадения из правой. INNER JOIN отбросил бы третью строку, и сумма стала бы 3000. RIGHT JOIN, наоборот, показал бы только каналы из справочника. FULL JOIN вывел бы и строки расходов без метки, и каналы без расходов. Выбор здесь диктует вопрос: нужно сохранить все расходы — left, нужны только расходы с известным каналом — inner.

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

Порядок проверки, который переносится на рабочие данные:

  1. До связи зафиксируйте две величины: число строк в таблице фактов и сумму показателя. В примере — 3 строки и 3500.
  2. Настройте связь по ключу channel_id с уникальным значением в справочнике.
  3. Откройте предпросмотр датасета и сравните число строк и сумму с зафиксированными.
  4. Просканируйте результат на строки с пустой меткой — это факты, для которых ключа нет в справочнике. Решите, допустимы ли они в отчёте.
  5. Если строка размножилась, проверьте уникальность ключа в справочнике: дубли ключа дают дубли строк.

Другие задачи аналитики: когортный анализ и разбор падения позиций в GSC.

Что делать, если типы поля не совпадают

Связь ставится только по полям одного типа. Если channel_id в costs — число, а в справочнике пришёл строкой, есть два рабочих варианта: привести типы на уровне источника либо описать датасет SQL-запросом и преобразовать тип функцией CAST или CONVERT (Работа с датасетом[2]). Правка типа поля в самом датасете не меняет то, как DataLens видит тип в подключении, поэтому надёжнее решать вопрос в источнике или в запросе.

Источники

  1. Yandex DataLens | Объединение данных Yandex DataLensyandex.cloud
  2. Yandex DataLens | Работа с датасетом в Yandex DataLensyandex.cloud
  3. Yandex DataLens | Настройки датасета в Yandex DataLensyandex.cloud

Валерий Курземнек

Автор метода «Машиночитаемый маркетинг»

Что ещё почитать