Перекрестные соединения

В главе 5 была представлена концепция декартова произведения, ко_ торое, в сущности, является результатом соединения нескольких таб_ лиц без указания каких_либо условий соединения. Декартово произве_ дение довольно часто используется в результате случайности (т. е. ко_ гда разработчики просто забывают добавить в блок from условие соеди_ нения), но на самом деле не так уж широко распространено. Однако если действительно требуется получить декартово произведение двух таблиц, должно быть задано перекрестное соединение:

mysql> SELECT pt.name, p.product_cd, p.name

_> FROM product p CROSS JOIN product_type pt;

+_______________________________+____________+_________________________+ | name | product_cd | name | +_______________________________+____________+_________________________+

| Customer Accounts	| AUT	| auto loan	|
| Customer Accounts	| BUS	|	business line of credit |
| Customer Accounts	| CD	|	certificate of deposit	|

			Глава 10. И снова соединения
| Customer Accounts	| CHK	| checking account	|
| Customer Accounts	| MM	| money market account	|
| Customer Accounts	|	MRT	| home mortgage	|
| Customer Accounts	|	SAV	| savings account	|
| Customer Accounts	|	SBL	| small business loan	|
| Insurance Offerings	|	AUT	| auto loan	|
| Insurance Offerings	|	BUS	| business line of credit	|
| Insurance Offerings	|	CD	| certificate of deposit	|
| Insurance Offerings	|	CHK	| checking account	|
| Insurance Offerings	|	MM	| money market account	|
| Insurance Offerings	|	MRT	| home mortgage	|
| Insurance Offerings	|	SAV	| savings account	|
| Insurance Offerings	|	SBL	| small business loan	|
| Individual and Business Loans |	AUT	| auto loan	|
| Individual and Business Loans |	BUS	| business line of credit |
| Individual and Business Loans |	CD	| certificate of deposit	|
| Individual and Business Loans |	CHK	| checking account	|
| Individual and Business Loans |	MM	| money market account	|
| Individual and Business Loans |	MRT	| home mortgage	|
| Individual and Business Loans |	SAV	| savings account	|
| Individual and Business Loans |	SBL	| small business loan	|

+_______________________________+____________+_________________________+ 24 rows in set (0.00 sec)

Этот запрос формирует декартово произведение таблиц product и prod_ uct_type. В результате получаем 24 строки (8 строк product умножают_ ся на 3 строки product_type). Но теперь, когда известно, что такое пере_ крестное соединение и как оно задается, надо определиться с тем, за_ чем оно используется. Большинство книг по SQL описывают, что такое перекрестное соединение, и затем говорят, что используется оно ред_ ко. Но мне бы хотелось поделиться с читателем ситуациями, в кото_ рых я нахожу перекрестное соединение довольно полезным.

В главе 9 обсуждалось, как использовать подзапросы для создания таб_ лиц. Используемый пример показывал, как построить таблицу, вклю_ чающую три строки, которая могла быть соединена с другими таблица_ ми. Вот таблица из того примера:

mysql>	SELECT 'Small Fry' name, 0 low_limit, 4999.99 high_limit
_>	UNION ALL
_>	SELECT 'Average Joes' name, 5000 low_limit, 9999.99 high_limit
_>	UNION ALL
_> SELECT 'Heavy Hitters'	name, 10000 low_limit, 9999999.99 high_limit;
+_______________+___________+____________+
| name		| low_limit |	high_limit |
+_______________+___________+____________+
| Small Fry	|		|	4999.99	|
| Average Joes	|		|	9999.99	|
| Heavy Hitters |		|	9999999.99	|

+_______________+___________+____________+ 3 rows in set (0.00 sec)

Перекрестные соединения

Хотя эта таблица является именно тем, что требовалось для разделения клиентов на три группы на основании их совокупного остатка на счете, эта стратегия слияния однострочных таблиц с помощью оператора union all не очень подходит, если требуется соорудить большую таблицу.

Например, требуется создать запрос, формирующий строку для каж_ дого дня 2004 года, но в БД нет таблицы, содержащей строки для всех дней. Используя стратегию из примера главы 9, можно было бы сде_ лать что_то вроде этого:

SELECT '2004_01_01' dt UNION ALL

SELECT '2004_01_02' dt UNION ALL

SELECT '2004_01_03' dt UNION ALL

...

...

...

SELECT '2004_12_29' dt UNION ALL

SELECT '2004_12_30' dt UNION ALL

SELECT '2004_12_31' dt

Создавать запрос, соединяющий результаты 366 запросов, немного уто_ мительно, поэтому, наверное, нужна другая стратегия. Что если сгене_ рировать таблицу с 366 строками (2004 год был високосным) и одним столбцом, содержащим число от 0 до 366, и затем добавлять это число дней к 1 января 2004? Вот одна из возможных методик формирования подобной таблицы:

mysql>	SELECT ones.num + tens.num + hundreds.num
_>	FROM
_> (SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num) ones
_>	CROSS JOIN
_> (SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL

				Глава 10. И снова соединения
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num) tens
_>	CROSS JOIN
_>	(SELECT	0 num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num) hundreds;

+____________________________________+ | ones.num + tens.num + hundreds.num | +____________________________________+

|		|
|		|
|		|
|		|
|		|
|		|
|		|
|		|
|		|
|		|
|		|
|		|
|		|
...
...
...
|		|
|		|
|		|
|		|
|		|
|		|
|		|
|		|
|		|

+____________________________________+ 400 rows in set (0.00 sec)

Если найти декартово произведение трех наборов – {0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, {0, 10, 20, 30, 40, 50, 60, 70, 80, 90} и {0, 100, 200, 300} – и сло_ жить значения во всех трех столбцах, получится результат, состоя_ щий из 400 строк, содержащих все числа от 0 до 399. Хотя это больше 366 строк, необходимых для формирования набора дней 2004 года, из_ бавиться от лишних строк достаточно просто. Вскоре я покажу, как это сделать.

Следующий шаг – преобразовать числа в набор дат. Для этого восполь_ зуемся функцией date_add() и добавим каждое число в результирую_ щий набор к 1 января 2004 года. Затем введем условие фильтрации, чтобы отбросить все даты 2005 года.

Перекрестные соединения
mysql> SELECT DATE_ADD('2004_01_01',
_>	INTERVAL	(ones.num	+ tens.num + hundreds.num) DAY) dt
_> FROM
_>	(SELECT		num UNION	ALL
_>	SELECT		num UNION	ALL
_>	SELECT		num UNION	ALL
_>	SELECT		num UNION	ALL
_>	SELECT		num UNION	ALL
_>	SELECT		num UNION	ALL
_>	SELECT		num UNION	ALL
_>	SELECT		num UNION	ALL
_>	SELECT		num UNION	ALL
_>	SELECT		num) ones
_>	CROSS JOIN
_>	(SELECT		num UNION	ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num UNION ALL
_>	SELECT		num) tens
_>	CROSS JOIN
_>	(SELECT		num UNION	ALL
_>	SELECT	100 num UNION ALL
_>	SELECT	200 num UNION ALL
_>	SELECT	300 num) hundreds
_> WHERE DATE_ADD('2004_01_01',
_>	INTERVAL	(ones.num	+ tens.num + hundreds.num) DAY) < '2005_01_01';
+____________+
| dt	|
+____________+
| 2004_01_01 |

| 2004_01_02 | | 2004_01_03 | | 2004_01_04 | | 2004_01_05 | | 2004_01_06 | | 2004_01_07 | | 2004_01_08 | | 2004_01_09 | | 2004_01_10 |

...

...

...

| 2004_02_20 | | 2004_02_21 | | 2004_02_22 | | 2004_02_23 |

210 Глава 10. И снова соединения

| 2004_02_24 | | 2004_02_25 | | 2004_02_26 | | 2004_02_27 | | 2004_02_28 | | 2004_02_29 | | 2004_03_01 |

...

...

...

| 2004_12_20 | | 2004_12_21 | | 2004_12_22 | | 2004_12_23 | | 2004_12_24 | | 2004_12_25 | | 2004_12_26 | | 2004_12_27 | | 2004_12_28 | | 2004_12_29 | | 2004_12_30 | | 2004_12_31 | +____________+

366 rows in set (0.01 sec)

В этом подходе замечательно то, что результирующий набор автомати_ чески включает 29 февраля без всяких дополнительных вмеша_ тельств, поскольку сервер БД вычисляет его, когда добавляет 59 дней к 1 января 2004 года.

Теперь, когда у нас есть механизм получения всех дней 2004 года, что мы должны с ними сделать? Ну, может потребоваться сгенерировать запрос, по которому будут выводиться все дни 2004 года с номерами счетов, открытых в каждый из дней, с числом осуществленных опера_ ций и т. д. Вот пример, дающий ответ на первый вопрос:

mysql>	SELECT days.dt, COUNT(a.account_id)
_>	FROM account a RIGHT OUTER JOIN
_>	(SELECT DATE_ADD('2004_01_01',
_>	INTERVAL (ones.num	+ tens.num + hundreds.num) DAY) dt
_>	FROM
_>	(SELECT 0	num UNION ALL
_>	SELECT 1	num UNION ALL
_>	SELECT 2	num UNION ALL
_>	SELECT 3	num UNION ALL
_>	SELECT 4	num UNION ALL
_>	SELECT 5	num UNION ALL
_>	SELECT 6	num UNION ALL
_>	SELECT 7	num UNION ALL
_>	SELECT 8	num UNION ALL
_>	SELECT 9	num) ones
_>	CROSS JOIN

Перекрестные соединения
_>	(SELECT	0 num UNION ALL
_>	SELECT	10 num UNION ALL
_>	SELECT	20 num UNION ALL
_>	SELECT	30 num UNION ALL
_>	SELECT	40 num UNION ALL
_>	SELECT	50 num UNION ALL
_>	SELECT	60 num UNION ALL
_>	SELECT	70 num UNION ALL
_>	SELECT	80 num UNION ALL
_>	SELECT	90 num) tens
_>	CROSS JOIN
_>	(SELECT	0 num UNION ALL
_>	SELECT	100 num UNION ALL
_>	SELECT	200 num UNION ALL
_>	SELECT	300 num) hundreds
_>	WHERE DATE_ADD('2004_01_01',
_>	INTERVAL (ones.num + tens.num + hundreds.num) DAY) <
_>		'2005_01_01') days
_>	ON days.dt = a.open_date
_> GROUP	BY days.dt;
+____________+_____________________+
| dt		| COUNT(a.account_id) |
+____________+_____________________+
| 2004_01_01	|			|
| 2004_01_02	|			|
| 2004_01_03	|			|
| 2004_01_04	|			|
| 2004_01_05	|			|
| 2004_01_06	|			|
| 2004_01_07	|			|
| 2004_01_08	|			|
| 2004_01_09	|			|
| 2004_01_10	|			|
| 2004_01_11	|			|
| 2004_01_12	|			|
| 2004_01_13	|			|
| 2004_01_14	|			|
| 2004_01_15	|			|
...
...
...
| 2004_12_15	|			|
| 2004_12_16	|			|
| 2004_12_17	|			|
| 2004_12_18	|			|
| 2004_12_19	|			|
| 2004_12_20	|			|
| 2004_12_21	|			|
| 2004_12_22	|			|
| 2004_12_23	|			|
| 2004_12_24	|			|

			Глава 10. И снова соединения
| 2004_12_25 |		|
| 2004_12_26 |		|
| 2004_12_27 |		|
| 2004_12_28 |		|
| 2004_12_29	|		|
| 2004_12_30	|		|
| 2004_12_31	|		|

+____________+_____________________+ 366 rows in set (0.03 sec)

Это один из самых интересных запросов, встречавшихся до сих пор в данной книге. Его ценность в том, что он включает перекрестные со_ единения, внешние соединения, функцию работы с датами, группи_ ровку, операции с множествами (union all) и агрегатную функцию (count()). Это не самое элегантное решение заданной проблемы, но оно послужит примером того, как с небольшой долей творчества и твер_ дым знанием языка программирования даже такой редко используе_ мый механизм, как перекрестные соединения, можно сделать могуще_ ственным инструментом в наборе инструментов SQL.

Дата добавления: 2015-08-17