Как сочетать объединения и подзапросы

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

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

Готовим промежуточные таблицы

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

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

Первый подзапрос отберёт пять стран с наибольшим количеством заказов.

SQL
SELECT billing_country AS country,
       COUNT(total) AS total_invoice
FROM invoice
GROUP BY billing_country
ORDER BY total_invoice DESC
LIMIT 5; 

country	total_invoice
USA	91
Canada	56
Brazil	35
France	35
Germany	28

Данные в таблице сгруппированы по стране. Таблица отсортирована по убыванию количества заказов и ограничена первыми пятью записями.

Теперь нужно сформировать вторую таблицу — посчитать число зарегистрированных пользователей на страну. Получать срез или сортировать данные не нужно. Отобрать данные можно после объединения.

SQL
SELECT country AS country,
       COUNT(customer_id) AS total_clients
FROM client
GROUP BY country; 

country	total_clients
Argentina	1
Spain	1
Italy	1
Hungary	1
India	2
Belgium	1
Czech Republic	2
Chile	1
Sweden	1
Norway	1

Две таблицы сформированы. Можно их проанализировать:

У таблиц есть общее поле со страной — country. С помощью этого поля их можно объединить.
В итоговую таблицу должны войти не все страны, а только пять — с наибольшим числом заказов. Список этих стран находится в первой таблице, значит, эту таблицу можно назначить «левой» и применить LEFT OUTER JOIN.

Осталось разобраться с тем, как объединить таблицы, сформированные подзапросами.

Объединяем результат подзапросов

Напомним правила объединения таблиц:

После оператора FROM назначают первую таблицу.
Затем следует оператор присоединения для выбранного типа — LEFT OUTER JOIN.
После оператора указывают вторую таблицу и поля, по которым объединяют данные.

Теперь можно добавить подзапросы вместо таблиц.

SQL
...
FROM (......) LEFT OUTER JOIN (......) ON ...
... 

Не забудьте назначить подзапросам псевдонимы, иначе код вызовет ошибку.

SQL
SELECT *
FROM
  (SELECT billing_country AS country,
          COUNT(total) AS total_invoice
   FROM invoice
   GROUP BY billing_country
   ORDER BY total_invoice DESC
   LIMIT 5) AS i
LEFT OUTER JOIN
  (SELECT country AS country,
          COUNT(customer_id) AS total_clients
   FROM client
   GROUP BY country) AS c ON i.country = c.country; 

country	total_invoice	cоuntry	total_clients
France	35	France	5
USA	91	USA	13
Brazil	35	Brazil	5
Germany	28	Germany	4
Canada	56	Canada	8

Таблицы объединены по полю country. Тип присоединения — LEFT OUTER JOIN, поэтому в таблице остались данные только о пяти странах. Но таблицу нужно исправить: убрать лишнее поле country и добавить сортировку.

SQL
SELECT i.country,
       i.total_invoice,
       c.total_clients
FROM
  (SELECT billing_country AS country,
          COUNT(total) AS total_invoice
   FROM invoice
   GROUP BY billing_country
   ORDER BY total_invoice DESC
   LIMIT 5) AS i
LEFT OUTER JOIN
  (SELECT country AS country,
          COUNT(customer_id) AS total_clients
   FROM client
   GROUP BY country) AS c ON i.country = c.country
ORDER BY i.total_invoice DESC; 

country	total_invoice	total_clients
USA	91	13
Canada	56	8
France	35	5
Brazil	35	5
Germany	28	4

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

Проверяем данные

Иногда при записи данных возникают ошибки. Проверим данные за 2010 год на корректность и помесячно сравним:

сумму поля total из таблицы invoice,
произведение полей unit_price и quantity из таблицы invoice_line.

На первый взгляд, подзапросы не нужны и можно решить задачу с помощью присоединения. Однако, если присоединить таблицу invoice к invoice_line, значения в поле total будут дублироваться. Ключевое слово DISTINCT не поможет: совпадающая стоимость разных заказов станет одним значением.

Лучше использовать подзапросы, но сначала декомпозиция. Достаточно двух пунктов:

найти сумму поля total,
найти сумму произведения полей unit_price и quantity.

Напишем первый подзапрос:

SQL
SELECT EXTRACT(MONTH FROM CAST(invoice_date AS date)) AS month_invoice,
       SUM(total) AS sum_of_total
FROM invoice
WHERE EXTRACT(YEAR FROM CAST(invoice_date AS date)) = 2010
GROUP BY month_invoice; 

Второй подзапрос немного сложнее — понадобится присоединить таблицу с подробностями о заказах:

SQL
SELECT EXTRACT(MONTH FROM CAST(invoice_date AS date)) AS month_invoice,
       SUM(unit_price*quantity) AS sum_of_price
FROM invoice AS i
LEFT JOIN invoice_line AS il ON i.invoice_id = il.invoice_id
WHERE EXTRACT(YEAR FROM CAST(invoice_date AS date)) = 2010
GROUP BY month_invoice; 

Последний шаг — соединить таблицы по общему месяцу:

SQL
SELECT t_1.month_invoice,
       sum_of_total,
       sum_of_price
FROM
  (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS date)) AS month_invoice,
          SUM(total) AS sum_of_total
   FROM invoice
   WHERE EXTRACT(YEAR
                 FROM CAST(invoice_date AS date)) = 2010
   GROUP BY month_invoice) AS t_1
JOIN
  (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS date)) AS month_invoice,
          SUM(unit_price*quantity) AS sum_of_price
   FROM invoice AS i
   LEFT JOIN invoice_line AS il ON i.invoice_id = il.invoice_id
   WHERE EXTRACT(YEAR
                 FROM CAST(invoice_date AS date)) = 2010
   GROUP BY month_invoice) AS t_2 ON t_1.month_invoice = t_2.month_invoice; 

month_invoice	sum_of_total	sum_of_price
1	52.62	52.62
2	46.62	46.62
3	44.62	44.62
4	37.62	37.62
5	37.62	37.62
6	37.62	37.62
7	37.62	37.62
8	37.62	37.62
9	36.63	36.63
10	37.62	37.62
11	37.62	37.62
12	37.62	37.62

Задача решена: ошибок нет. Но проверить не помешает.