Штирлиц — Сеть печатных салонов в Перми

Весь комплекс печатных услуг в Перми. Общирная сеть печатных салонов в Перми. Цифровая печать, цветное и черно-белое копирование документов, сканирование документов, ризография в Перми

Cross join: CROSS JOIN (U-SQL) — U-SQL

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

Время прочтения: 5 мин.

CROSS JOIN (или полное соединение таблиц без условий) — декартова перемножение множеств. Говоря простым языком — для каждого варианта первого множества будет сопоставлены все варианты второго множества.

Данным типом соединения нужно пользоваться с осторожностью, т.к. виртуальная таблица занимает n*m объема данных в ОЗУ (для соединения 3-х таблиц — n*m*t, и т.д.). Тем не менее, при правильном использовании данный тип соединения таблиц может решать достаточно трудоемкие задачи, такие как — нахождение пересечений в исторических данных. Примером данной задачи может являться: недопущение автоматического распределения заявок клиента на андеррайтера с родственными связями.

Структура таблиц ниже представленных примеров следующая:

Допустим: клиент Сидоров Степан Павлович подал заявку на кредит. На некотором этапе рассмотрения потребовался анализ платежеспособности клиента андеррайтером.

Заявка автоматически распределилась на сотрудника Петрова Екатерина Павловна.

На первый взгляд ничего подозрительного нет — в ФИО людей совпадает только отчество, что является широко распространенной ситуацией.

Однако, при детальном анализе выясняется, что девичья фамилия андеррайтера и клиента совпадают (Сидорова / Сидоров).

SELECT fh.fio_history_id
     , fh.fio_id
     , fh.person_id
     , ln.lname, fn.fname, mn.mname
     , fh.actual
  FROM fio_history fh
 INNER JOIN fio f ON fh.fio_id = f.fio_id
 INNER JOIN lname ln ON f.lname_id = ln.lname_id
 INNER JOIN fname fn ON f.fname_id = fn.fname_id
 INNER JOIN mname mn ON f.mname_id = mn.mname_id
 WHERE person_id IN (1, 5)
 ORDER BY fh.actual DESC;

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

Для решения данной задачи можно использовать простой запрос с CROSS JOIN:

SELECT results.*
  FROM (SELECT underwriter.
  person_id as u_person_id
             , underwriter.fio_id as u_fio_id
             , underwriter.lname as u_lname
             , client.person_id as c_person_id
             , client.fio_id as c_fio_id
             , client.lname as c_lname
             , CASE 
                WHEN underwriter.lname_id = client.lname_id 
                     OR underwriter.lname_stem = client.lname_stem
                THEN 1
                ELSE 0
               END as is_equal_lnames
          FROM (-- Андеррайтер "Петрова Екатерина Павловна"
                SELECT fh.person_id, fh.fio_id, ln.lname_id, ln.lname
                     , regexp_replace(upper(ln.lname), '(А|ИЙ|АЯ)$', '')) as lname_stem
                  FROM fio_history fh
                 INNER JOIN fio f ON fh.fio_id = f.fio_id
                 INNER JOIN lname ln ON f.lname_id = ln.lname_id
                 WHERE fh.person_id = 1) underwriter
         CROSS JOIN (-- Клиент "Сидоров Степан Павлович"
                     SELECT fh.person_id, fh.
 
 fio_id, ln.lname_id, ln.lname
                          , regexp_replace(upper(ln.lname), '(А|ИЙ|АЯ)$', '')) as lname_stem
                       FROM fio_history fh
                      INNER JOIN fio f ON fh.fio_id = f.fio_id
                      INNER JOIN lname ln ON f.lname_id = ln.lname_id
                      WHERE fh.person_id = 5) client) results
 WHERE results.is_equal_lnames = 1;

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

Данный пример является достаточно простым в проверке, т.к. родственные связи находятся в пределах одного поколения.

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

В данной ситуации подход остается тот же, увеличивается только лишь количество проверяемых субъектов.

Пусть будет ситуация аналогичная предыдущей: клиент Иванов Алексей Николаевич оформляет заявку на кредит, которая впоследствии распределяется на андеррайтера Петров Юрий Александрович.

На первый взгляд, очевидно, что люди не являются родственниками.

Нам необходимо проверить всех ближайших родственников клиента со всеми ближайшими родственниками андеррайтера (предполагается, что родственные связи записаны в других справочниках; для простоты представления примера идентификаторы person_id указаны явным образом).

SELECT fh.fio_history_id
     , fh.fio_id
     , fh.person_id
     , ln.lname, fn.fname, mn.mname
     , fh.actual
  FROM fio_history fh
 INNER JOIN fio f ON fh.fio_id = f.fio_id
 INNER JOIN lname ln ON f.lname_id = ln.lname_id
 INNER JOIN fname fn ON f.fname_id = fn.fname_id
 INNER JOIN mname mn ON f.mname_id = mn.mname_id
 WHERE fh.person_id IN (1, 2, 3, 4)
 ORDER BY actual DESC;

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

WITH people_info AS (
  SELECT person_id
       , fio_id
       , NULL as relative_person_id
       , NULL as relative_fio_id
    FROM fio_history
   UNION ALL
  SELECT pinf. person_id
       , pinf.fio_id
       , fh.person_id as other_person_id
       , fh.fio_id as other_fio_id
    FROM fio_history fh
   CROSS JOIN people_info pinf
   WHERE pinf.relative_person_id IS NULL
)
SELECT results.*
  FROM (SELECT underwriter.person_id as u_person_id
             , underwriter.fio_id as u_fio_id
             , underwriter.relative_person_id as u_relative_person_id
             , underwriter.relative_fio_id as u_relative_fio_id
             , underwriter.lname as u_lname
             , client.person_id as c_person_id
             , client.fio_id as c_fio_id
             , client.relative_person_id as c_relative_person_id
             , client.relative_fio_id as c_relative_fio_id
             , client.lname as c_lname
             , CASE 
                WHEN underwriter.lname_id = client.lname_id 
                     OR underwriter.lname_stem = client.lname_stem
                THEN 1
                ELSE 0
               END as is_equal_lnames
          FROM (-- Андеррайтер "Петров Юрий Александрович"
                SELECT pinf.
  person_id, pinf.fio_id
                     , pinf.relative_person_id, pinf.relative_fio_id, ln.lname_id, ln.lname
                     , regexp_replace(upper(ln.lname), '(А|ИЙ|АЯ)$', '')) as lname_stem
                  FROM people_info pinf
                 INNER JOIN fio f ON pinf.relative_fio_id = f.fio_id
                 INNER JOIN lname ln ON f.lname_id = ln.lname_id
                 WHERE     pinf.relative_person_id IS NOT NULL
                       AND pinf.person_id IN (4)
                       AND pinf.relative_person_id IN (1, 4)) underwriter
         CROSS JOIN (-- Клиент "Иванов Алексей Николаевич"
                     SELECT pinf.person_id, pinf.fio_id
                          , pinf.relative_person_id, pinf.relative_fio_id, ln.lname_id, ln.lname
                          , regexp_replace(upper(ln.lname), '(А|ИЙ|АЯ)$', '')) as lname_stem
                       FROM people_info pinf
                      INNER JOIN fio f ON pinf.relative_fio_id = f.fio_id
                      INNER JOIN lname ln ON f.
 
 
  lname_id = ln.lname_id
                      WHERE     pinf.relative_person_id IS NOT NULL
                            AND pinf.person_id IN (3)
                            AND pinf.relative_person_id IN (2, 3)) client) results
 WHERE results.is_equal_lnames = 1;

Таким образом, родственные связи клиента и андеррайтера были обнаружены между их женами, которые являются родными сестрами.

Задачи, где может потребоваться проверка исторических данных, могут быть самыми разнообразными, и во многих случаях CROSS JOIN может упростить их решение.

CROSS JOIN в SQL — CodeChick

Если при объединении двух таблиц вы не укажете условие объединения, система базы данных объединит каждую строку из первой таблицы с каждой строкой из второй таблицы. Такой тип объединения называется CROSS JOIN, а также перекрестным или декартовым произведением. Следующая диаграмма Венна иллюстрирует работу CROSS JOIN.

Чтобы понять, как работает CROSS JOIN, давайте рассмотрим следующие таблицы

employees и departments.

 

Таблица employeesТаблица departments
+--------+--------------+------------+---------+
| emp_id | emp_name     | hire_date  | dept_id |
+--------+--------------+------------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |       4 |
|      2 | Tony Montana | 2002-07-15 |       1 |
|      3 | Sarah Connor | 2005-10-18 |       5 |
|      4 | Rick Deckard | 2007-01-03 |       3 |
|      5 | Martin Blank | 2008-06-24 |    NULL |
+--------+--------------+------------+---------+

+---------+------------------+
| dept_id | dept_name        |
+---------+------------------+
|       1 | Administration   |
|       2 | Customer Service |
|       3 | Finance          |
|       4 | Human Resources  |
|       5 | Sales            |
+---------+------------------+

Количество строк в CROSS JOIN — это произведение количества строк в каждой таблице. Далее приведен простой пример операции перекрестного объединения.

SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name FROM employees AS t1 CROSS JOIN departments AS t2;

Примечание. CROSS JOIN создает декартово произведение или, иными словами, умножение всех строк одной таблицы на все строки другой. Так, например, если в одной таблице 5 строк, а в другой 10 строк, запрос с CROSS JOIN выдаст 50 строк — произведение 5 и 10.

После выполнения приведенной выше команды вы получите набор результатов примерно следующего вида:

+--------+--------------+------------+------------------+
| emp_id | emp_name     | hire_date  | dept_name        |
+--------+--------------+------------+------------------+
|      1 | Ethan Hunt   | 2001-05-01 | Administration   |
|      2 | Tony Montana | 2002-07-15 | Administration   |
|      3 | Sarah Connor | 2005-10-18 | Administration   |
|      4 | Rick Deckard | 2007-01-03 | Administration   |
|      5 | Martin Blank | 2008-06-24 | Administration   |
|      1 | Ethan Hunt   | 2001-05-01 | Customer Service |
|      2 | Tony Montana | 2002-07-15 | Customer Service |
|      3 | Sarah Connor | 2005-10-18 | Customer Service |
|      4 | Rick Deckard | 2007-01-03 | Customer Service |
|      5 | Martin Blank | 2008-06-24 | Customer Service |
|      1 | Ethan Hunt   | 2001-05-01 | Finance          |
|      2 | Tony Montana | 2002-07-15 | Finance          |
|      3 | Sarah Connor | 2005-10-18 | Finance          |
|      4 | Rick Deckard | 2007-01-03 | Finance          |
|      5 | Martin Blank | 2008-06-24 | Finance          |
|      1 | Ethan Hunt   | 2001-05-01 | Human Resources  |
|      2 | Tony Montana | 2002-07-15 | Human Resources  |
|      3 | Sarah Connor | 2005-10-18 | Human Resources  |
|      4 | Rick Deckard | 2007-01-03 | Human Resources  |
|      5 | Martin Blank | 2008-06-24 | Human Resources  |
|      1 | Ethan Hunt   | 2001-05-01 | Sales            |
|      2 | Tony Montana | 2002-07-15 | Sales            |
|      3 | Sarah Connor | 2005-10-18 | Sales            |
|      4 | Rick Deckard | 2007-01-03 | Sales            |
|      5 | Martin Blank | 2008-06-24 | Sales            |
+--------+--------------+------------+------------------+

Как вы видите, CROSS JOIN не так полезен, как другие типы объединений, которые мы рассматривали в предыдущих статьях. Поскольку в запросе не было указано условие объединения, каждая строка из таблицы employees объединилась с каждой строкой из таблицы departments. Так что лучше использовать CROSS JOIN, только если вы точно уверены, что он вам нужен.

SQL CROSS JOIN с примерами

В этой статье мы изучим концепцию SQL CROSS JOIN и подкрепим наши знания простыми примерами, которые объясняются иллюстрациями.

Введение

CROSS JOIN используется для создания парной комбинации каждой строки первой таблицы с каждой строкой второй таблицы. Этот тип соединения также известен как декартово соединение.

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

На следующем изображении показаны все комбинации меню, которые может генерировать наш мозг. SQL CROSS JOIN работает аналогично этому механизму, поскольку создает все парные комбинации строк таблиц, которые будут объединены.

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

Основная идея CROSS JOIN заключается в том, что она возвращает декартово произведение соединенных таблиц. В следующем совете мы кратко объясним декартово произведение;

Совет: Что такое декартово произведение?

Декартово произведение — это операция умножения в теории множеств, порождающая все упорядоченные пары заданных множеств. Предположим, что A — это множество, а элементами являются {a,b}, а B — это множество, а элементами являются {1,2,3}. Декартово произведение этих двух A и B обозначается AxB, и результат будет примерно следующим.

AxB = {(а, 1), (а, 2), (а, 3), (б, 1), (б, 2), (б, 3)}

Синтаксис

Синтаксис CROSS JOIN в SQL будет выглядеть следующим образом:

SELECT ColumnName_1,

       ColumnName_2,

       ColumnName_N

FROM [Table_1]

     CROSS JOIN [Table_2] 900 03

Или мы можем использовать следующий синтаксис вместо предыдущего. Этот синтаксис не включает ключевое слово CROSS JOIN; только мы будем размещать таблицы, которые будут объединены после предложения FROM и разделены запятой.

SELECT ColumnName_1,

       ColumnName_2,

       ColumnName_N

ИЗ [Table_1],[Table_2]

Набор результатов не меняется ни для одного из этих синтаксисов. Кроме того, мы должны отметить один момент, касающийся CROSS JOIN. В отличие от INNER JOIN, LEFT JOIN и FULL OUTER JOIN, CROSS JOIN не требует условия соединения.

Пример перекрестного соединения SQL:

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

С помощью следующего запроса мы выполним эти два шага:

1

2

3

4

5

6

7

8

9

10

Создание настольных блюд (еда warchar (100))

Создание настольных напитков (DrinkName varchar (100))

Вставка в напитки

значения («апельсиновый сок»), («чай»), (‘cofee’)

ВСТАВИТЬ В Блюда

ЗНАЧЕНИЯ(‘Омлет’), (‘Яичница’), (‘Колбаса’)

SELECT *

ИЗ Блюд;

ВЫБОР *

ИЗ Напитков

Следующий запрос соединит таблицу Meals and Drinks с помощью ключевого слова CROSS JOIN , и мы получим все парные комбинации названий блюд и напитков.

SELECT * FROM Еда

CROSS JOIN Напитки

Изображение ниже иллюстрирует принцип работы CROSS JOIN.

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

ВЫБЕРИТЕ * ИЗ Блюда

,Напитки

Совет: Количество строк набора результатов будет равно произведению количества строк таблиц, которые будут объединены. В примере с меню завтрака количество строк в таблице «Питание» равно 3, а количество строк в таблице «Напитки» равно 3, поэтому количество строк набора результатов можно найти с помощью следующего вычисления.

3 (количество строк в таблице блюд) x 3 (количество строк в таблице напитков) = 9 90 016 (количество строк набора результатов)

Функция CONCAT_WS поможет объединить выражения столбцов. Таким образом, мы можем создать более значимый набор результатов меню завтрака.

ВЫБЕРИТЕ CONCAT_WS(‘-‘,MealName,DrinkName) AS MenuList

ОТ Блюда CROSS JOIN

Напитки

SQL CROSS JOIN и вопросы производительности

Запросы SQL, содержащие ключевое слово CROSS JOIN, могут быть очень дорогостоящими. Мы пытаемся сказать, что эти запросы могут потреблять больше ресурсов и вызывать проблемы с производительностью. Для следующего запроса мы проанализируем план выполнения с помощью ApexSQL Plan. В сгенерированном фактическом плане выполнения мы увидим оператор вложенных циклов, и когда мы наведем указатель мыши на этот оператор, появится всплывающее окно с подробностями.

В этом окне нам бросается в глаза предупреждающее сообщение. Сообщение «No Join Predicate» указывает, что этот запрос может столкнуться с проблемами производительности. По этой причине оптимизатор запросов предупреждает нас об этой потенциальной проблеме. Вкратце, когда мы решаем использовать CROSS JOIN в каком-либо запросе, мы должны учитывать количество таблиц, которые будут объединены. Например, когда мы выполняем ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ двух таблиц, и если первая содержит 1000 строк, а вторая содержит 1000 строк, количество строк в результирующем наборе будет равно 1 000 000 строк.

Совет: ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ может быть реализовано только с вложенными циклами, поэтому следующие запросы вернут ошибку, если мы заставим оптимизатор запросов использовать другие типы соединений.

1

2

3

4

5

6

7

8

9 9 0003

ВЫБЕРИТЕ * ИЗ Блюда

ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ Напитки

ОПЦИЯ (СЛИВАНИЕ СОЕДИНЕНИЯ)

 

GO

 

SELECT * FROM Meals

CROSS JOIN Напитки

ОПЦИЯ (HASH JOIN )

Заключение

В этой статье мы подробно изучили основы SQL CROSS JOIN, а также упомянули соображения производительности CROSS JOIN. Когда CROSS JOIN используется для таблиц с большим количеством строк, это может негативно сказаться на производительности.

  • Автор
  • Последние сообщения

Esat Erkec

Esat Erkec — специалист по SQL Server, который начал свою карьеру более 8 лет назад в качестве разработчика программного обеспечения. Он является сертифицированным экспертом по решениям Microsoft для SQL Server.

Большая часть его карьеры была посвящена администрированию и разработке баз данных SQL Server. Его текущие интересы связаны с администрированием баз данных и бизнес-аналитикой. Вы можете найти его в LinkedIn.

Просмотреть все сообщения Эсата Эркеца

Последние сообщения от Esat Erkec (посмотреть все)

Cross join — Power Query

Редактировать

Твиттер LinkedIn Фейсбук Электронная почта

  • Статья

Перекрестное соединение — это тип соединения, который возвращает декартово произведение строк из таблиц в объединении. Другими словами, он объединяет каждую строку из первой таблицы с каждой строкой из второй таблицы.

В этой статье на практическом примере показано, как выполнить перекрестное соединение в Power Query.

Примеры таблиц ввода и вывода

В этом примере примерами исходных таблиц являются:

  • Продукт : Таблица со всеми общими продуктами, которые вы продаете.

  • Цвета : Таблица со всеми вариациями продуктов в виде цветов, которые вы можете иметь в своем инвентаре.

Цель состоит в том, чтобы выполнить операцию перекрестного соединения с этими двумя таблицами, чтобы создать список всех уникальных продуктов, которые вы можете иметь в своем инвентаре, как показано в следующей таблице. Эта операция необходима, потому что таблица Product содержит только общее название продукта и не дает уровня детализации, необходимого для просмотра возможных вариантов продукта (например, цвета).

Выполнение перекрестного соединения

Чтобы выполнить операцию перекрестного соединения в Power Query, сначала перейдите к Товар табл. На вкладке Добавить столбец на ленте выберите Пользовательский столбец . Дополнительные сведения: Добавление пользовательского столбца

В диалоговом окне Пользовательский столбец введите любое имя в поле Имя нового столбца и введите Цвета в поле Формула пользовательского столбца .

Важно

Если в имени запроса есть пробелы, например Цвета продукта , текст, который необходимо ввести в поле Раздел формулы пользовательского столбца должен иметь синтаксис #"Имя запроса" . Для Product Colors вам необходимо ввести #"Product Colors" .

Вы можете проверить название своих запросов на панели Параметры запроса в правой части экрана или на панели Запросы на левой стороне.

Оставить комментарий

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *