Разное

Cross join: Оператор SQL CROSS JOIN: примеры, синтаксис

Содержание

Оператор SQL CROSS JOIN: примеры, синтаксис

Оператор SQL CROSS JOIN формирует таблицу перекрестным соединением (декартовым произведением) двух таблиц. При использовании оператора SQL CROSS JOIN каждая строка левой таблицы сцепляется с каждой строкой правой таблицы. В результате получается таблица со всеми возможными сочетаниями строк обеих таблиц.

Оператор SQL CROSS JOIN имеет следующий синтаксис:

SELECT
    column_names [,... n]
FROM
    Table_1 CROSS JOIN Table_2

Обратите внимание, что в операторе за ненадобностью отсутствует условие (ON).


Примеры оператора SQL CROSS JOIN. Имеются две таблицы:

Authors — содержит в себе информацию об авторах книг:

AuthorID AuthorName
1 Bruce Eckel
2 Robert Lafore
3 Andrew Tanenbaum

Books — содержит в себе информацию о названии книг:

BookID BookName
3 Modern Operating System
1 Thinking in Java
3 Computer Architecture
4 Programming in Scala

В таблице Books поле BookID являются внешним ключом и ссылаются на таблицу Authors.

Пример 1. Произвести декартово произведение обеих таблиц с помощью оператора SQL CROSS JOIN:

SELECT *
FROM Authors CROSS JOIN Books

Результирующая таблица будет выглядеть следующим образом:

Authors.AuthorID Authors. AuthorName Books.BookID Books.BookName
1 Bruce Eckel 3 Modern Operating System
1 Bruce Eckel 1 Thinking in Java
1 Bruce Eckel 3 Computer Architecture
1 Bruce Eckel 4 Programming in Scala
2 Robert Lafore 3 Modern Operating System
2 Robert Lafore 1 Thinking in Java
2 Robert Lafore 3 Computer Architecture
2 Robert Lafore 4 Programming in Scala
3 Andrew Tanenbaum 3 Modern Operating System
3 Andrew Tanenbaum 1 Thinking in Java
3 Andrew Tanenbaum 3 Computer Architecture
3 Andrew Tanenbaum 4 Programming in Scala

Функция CROSSJOIN (DAX) — DAX



  • Чтение занимает 2 мин

В этой статье

Возвращает таблицу, содержащую декартово произведение всех строк из всех таблиц в аргументах.Returns a table that contains the Cartesian product of all rows from all tables in the arguments. Столбцы в новой таблице являются всеми столбцами во всех таблицах аргументов.The columns in the new table are all the columns in all the argument tables.

СинтаксисSyntax

CROSSJOIN(<table>, <table>[, <table>]…)  

ПараметрыParameters

ТерминTerm ОпределениеDefinition
таблицаtable Любое выражение DAX, возвращающее таблицу данныхAny DAX expression that returns a table of data

Возвращаемое значениеReturn value

Таблица, содержащая декартово произведение всех строк из всех таблиц в аргументах. A table that contains the Cartesian product of all rows from all tables in the arguments.

RemarksRemarks

  • Имена столбцов из аргументов table должны быть разными во всех таблицах, иначе возвращается ошибка.Column names from table arguments must all be different in all tables or an error is returned.

  • Общее число строк, возвращаемых функцией CROSSJOIN(), равно произведению числа строк из всех таблиц в аргументах; кроме того, общее число столбцов в таблице результатов является суммой количества столбцов во всех таблицах.The total number of rows returned by CROSSJOIN() is equal to the product of the number of rows from all tables in the arguments; also, the total number of columns in the result table is the sum of the number of columns in all tables. Например, если TableA содержит rA строк и cA столбцов, TableB содержит rB строк и cB столбцов, а TableC содержит rC строк и cC столбцов, то результирующая таблица содержит rA × rB × rC строк и cA + cB + cC столбцов.For example, if TableA has rA rows and cA columns, and TableB has rB rows and cB columns, and TableC has rC rows and cC column; then, the resulting table has rA × rb × rC rows and cA + cB + cC columns.

  • Эта функция не поддерживается для использования в режиме DirectQuery при использовании в вычисляемых столбцах или правилах безопасности на уровне строк (RLS).This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

ПримерExample

В следующем примере показаны результаты применения функции CROSSJOIN() к двум таблицам: Цвета и Бланк. The following example shows the results of applying CROSSJOIN() to two tables: Colors and Stationery.

Таблица Цвета содержит цвета и узоры:The table Colors contains colors and patterns:

ЦветColor МодельPattern
КрасныйRed Горизонтальные полосыHorizontal Stripe
ЗеленыйGreen Вертикальные полосыVertical Stripe
СинийBlue ШтриховкаCrosshatch

В таблице Бланк содержатся шрифты и их стиль отображения:The table Stationery contains fonts and presentation:

ШрифтFont Уровень представленияPresentation
Serifserif Приподнятыйembossed
Sans-Serifsans-serif Утопленныйengraved

Ниже представлено выражение для создания перекрестного объединения.The expression to generate the cross join is presented below:

CROSSJOIN( Colors, Stationery)  

Если выражение выше используется везде, где ожидается табличное выражение, результаты выражения будут выглядеть следующим образом:When the above expression is used wherever a table expression is expected, the results of the expression would be as follows:

ЦветColor МодельPattern ШрифтFont Уровень представленияPresentation
КрасныйRed Горизонтальные полосыHorizontal Stripe Serifserif Приподнятыйembossed
ЗеленыйGreen Вертикальные полосыVertical Stripe Serifserif Приподнятыйembossed
СинийBlue ШтриховкаCrosshatch Serifserif Приподнятыйembossed
КрасныйRed Горизонтальные полосыHorizontal Stripe Sans-Serifsans-serif Утопленныйengraved
ЗеленыйGreen Вертикальные полосыVertical Stripe Sans-Serifsans-serif Утопленныйengraved
СинийBlue ШтриховкаCrosshatch Sans-Serifsans-serif Утопленныйengraved

Использование 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 может упростить их решение.

DAX функции GENERATE, GENERATEALL, CROSSJOIN в Power BI и Power Pivot

 Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):


Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы обсудим функции GENERATE, GENERATEALL и CROSSJOIN в Power BI и Power Pivot, которые способны в коде языка DAX создавать вычисляемые таблицы на основе пересечений строк других таблиц.

Давайте рассмотрим каждую из этих функций отдельно, подробно разбирая то, как они работают на примерах формул в Power BI. Так как в этой программе можно создавать таблицы в модели данных, что нам пригодится для демонстрации примеров.

В Power Pivot (Excel) функции GENERATE, GENERATEALL и CROSSJOIN также работают, только таблицы они там создают виртуально, во время вычисления самой формулы.

Итак, переходим к разбору функций.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

Да, и еще один момент, до 26 марта 2021 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» с огромной скидкой 60%.

В этом видеокурсе язык DAX преподнесен как простой конструктор, состоящий из нескольких блоков, которые имеют свое определенное, конкретное предназначение. Сочетая различными способами эти блоки, Вы, при помощи конструктора формул DAX, с легкостью сможете решать любые (простые или сложные) аналитические задачи.

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 60% (до 26 марта 2021 г.): узнать подробнее

 

DAX функции GENERATE и GENERATEALL в Power BI и Power Pivot

GENERATE () и GENERATEALL () — создают таблицу из всех пересечений строк двух таблиц, входящих в параметры функций. То есть, для каждой строки первой таблицы возвращаются (по очереди вычисляются) все строки из второй.

Иногда, на просторах Интернета я встречаю, что некоторые пользователи несколько неверно прописывают имя этих функций, добавляя второе слово: generate table. Слово table здесь не нужно.

Синтаксис:

GENERATE ('Таблица 1'; 'Таблица 2')
GENERATEALL ('Таблица 1'; 'Таблица 2')

Где: Таблица — исходная таблица или табличное выражение

! — Названия столбцов в обеих таблицах должны быть разными

Обе функции GENERATE и GENERATEALL работают практически одинаково, за исключением того, как они обрабатывают пустую вторую таблицу.

Давайте сначала разберемся как они обе функционируют, а затем рассмотрим различия в их работе.

Примеры формул на основе DAX функций GENERATE и GENERATEALL.

В Power BI Desktop имеются 2 исходные таблицы «Год» и «Месяц»:

 

Создадим при помощи функции GENERATE формулу, возвращающую календарь из пересечений строк всех годов со всеми имеющимися месяцами:

Календарь = GENERATE (‘Год’;’Месяц’)

Результатом выполнения этой формулы будет перечисление всех 3 месяцев по каждому году:

 

Итог будет абсолютно тем же самым, если вместо GENERATE мы пропишем DAX функцию GENERATEALL:

Календарь = GENERATEALL ('Год';'Месяц')

 

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

Рассмотрим пример, когда вторая таблица «Месяц» полностью пустая:

 

При тех же формулах:

Календарь = GENERATE ('Год';'Месяц')
Календарь = GENERATEALL ('Год';'Месяц')

В этот раз результат будет разным.

GENERATE — возвратит вообще пустую таблицу:

 

А DAX функция GENERATEALL возвратит таблицу, состоящую из двух столбцов, где будут прописаны года, а месяца останутся пустыми:

 

Рассмотрим еще одну DAX функцию, похожую на GENERATE и GENERATEALL, которая также возвращает пересечение всех строк, только уже не двух, а многих таблиц.

 

DAX функция CROSSJOIN в Power BI и Power Pivot

CROSSJOIN () — создает таблицу из всех пересечений строк всех таблиц, входящих в параметры функции.

Синтаксис:

CROSSJOIN ('Таблица 1'; 'Таблица 2'; 'Таблица N')

Где: Таблица — исходная таблица или табличное выражение

! — Названия столбцов во всех таблицах должны быть разными

Рассмотрим пример формулы на основе DAX функции CROSSJOIN.

В Power BI Desktop имеются 3 исходные таблицы «Год», «Месяц», «День»:

 

Напишем формулу календаря из пересечения всех строк года, месяца и дня на основе функции CROSSJOIN:

Календарь = CROSSJOIN ('Год'; 'Месяц'; 'День')

Результатом выполнения этой формулы будет следующее пересечение всех строк:

 

Так как все эти 3 функции GENERATE, GENERATEALL и CROSSJOIN в итоге своей работы возвращают таблицы, то всех их можно легко использовать в сочетании со многими другими функциями в DAX.

Например, результаты таблицы, созданной на основе CROSSJOIN можно отфильтровать функцией FILTER. Таким образом, убрать какие-то лишние строки из того количества пересечений, которое было создано.

На этом, с функциями создания таблиц в Power BI и Power Pivot из пересечений строк, в этой статье, все.

Также, напоминаю Вам, что до 26 марта 2021 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» с огромной скидкой 60%.

В этом видеокурсе язык DAX преподнесен как простой конструктор, состоящий из нескольких блоков, которые имеют свое определенное, конкретное предназначение. Сочетая различными способами эти блоки, Вы, при помощи конструктора формул DAX, с легкостью сможете решать любые (простые или сложные) аналитические задачи.

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 60% (до 26 марта 2021 г.): узнать подробнее

Пожалуйста, оцените статью:

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1

(13 голосов, в среднем: 5 из 5 баллов)
 
 
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
 
 
 
 

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

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

SQL — CARTESIAN или CROSS JOINS

CARTESIAN JOIN или CROSS JOIN возвращают декартово произведение множеств записей из двух или более соединенных таблиц. Таким образом, он приравнивается к внутреннему соединению, где условие WHERE всегда принимает значение ИСТИНЫ или где условие WHERE отсутствует в запросе.

Синтаксис

Базовый синтаксис CARTESIAN JOIN или CROSS JOIN следующий:

SELECT table1.column1, table2.column2...
FROM  table1, table2 [, table3 ]

Пример

Рассмотрим следующие две таблицы.

Таблица 1 – таблица CUSTOMERS выглядит следующим образом:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Maxim    |  35 | Moscow    | 21000.00 |
|  2 | AndreyEx |  38 | Krasnodar | 55500.00 |
|  3 | Oleg     |  33 | Rostov    | 34000.00 |
|  4 | Masha    |  35 | Moscow    | 34000.00 |
+----+----------+-----+-----------+----------+

 

Таблица 2: Таблица ORDERS выглядит следующим образом:

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2017-01-11 00:00:00 |           3 |  34000 |
| 100 | 2017-01-11 00:00:00 |           3 |  34000 |
| 101 | 2017-02-02 00:00:00 |           2 |  12500 |
| 103 | 2017-03-05 00:00:00 |           4 |  45000 |
+-----+---------------------+-------------+--------+

 

Теперь, давайте объединим эти две таблицы, используя INNER JOIN следующим образом:

SQL> SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS, ORDERS;

 

Это произведет следующий результат:

+----+----------+-----+--------+
| ID | NAME     | AGE | AMOUNT |
+----+----------+-----+--------+
|  1 | Maxim    |  35 |  34000 |
|  1 | Maxim    |  35 |  34000 |
|  1 | Maxim    |  35 |  12500 |
|  1 | Maxim    |  35 |  45000 |
|  2 | AndreyEx |  38 |  34000 |
|  2 | AndreyEx |  38 |  34000 |
|  2 | AndreyEx |  38 |  12500 |
|  2 | AndreyEx |  38 |  45000 |
|  3 | Oleg     |  33 |  34000 |
|  3 | Oleg     |  33 |  34000 |
|  3 | Oleg     |  33 |  12500 |
|  3 | Oleg     |  33 |  45000 |
|  4 | Masha    |  35 |  34000 |
|  4 | Masha    |  35 |  34000 |
|  4 | Masha    |  35 |  12500 |
|  4 | Masha    |  35 |  45000 |
+----+----------+-----+--------+

 

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Полное соединение · Loginom Help