LEFT JOIN и вложенные запросы — решение повседневных задач

Опубликовано Опубликовано в рубрике SQL

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

Итак, на входе мы имеем вот такую табличку под названием `sum`

DATE

ORDER_ID

USER_ID

PRICE

01.01

1

1

5

01.01

2

1

10

01.01

3

2

5

01.01

4

3

5

01.01

5

1

5

02.01

6

1

5

02.01

7

2

10

02.01

8

3

5

03.01

9

3

5

03.01

10

3

5

Из неё нам необходимо получить таблицу следующего вида

DATE

Количество

заказов

Количество

покупателей

Оборот

01.01

5

3

30

02.01

3

3

20

03.01

2

1

10

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

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

SELECT `date` FROM `sum` GROUP BY `date`

Тут все элементарно, так что добавим количество заказов в день. В этом нам поможет конструкция LEFT JOIN (или LEFT OUTER JOIN — кому что больше нравится). Внутри неё мы будем использовать не готовую таблицу, а результат запроса, который сосчитает нужные нам цифры. Итак, попробуем добавить его

SELECT s.date, t.count AS 'Кол-во заказов' FROM sum s 
LEFT JOIN (SELECT date, COUNT(order_id) AS count FROM sum GROUP BY date) t ON s.date = t.date 
GROUP BY s.date

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

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

SELECT s.date, t.count AS 'Кол-во заказов', q.user AS 'Кол-во покупателей' FROM sum s 
LEFT JOIN (SELECT date, COUNT(order_id) AS count FROM sum GROUP BY date) t ON s.date = t.date 
LEFT JOIN (SELECT date, COUNT(DISTINCT user_id) AS user FROM sum GROUP BY date) q ON s.date = q.date 
GROUP BY s.date

Здесь в запросе добавлена конструкция COUNT(DISTINCT), которая исключает неправильный результат подсчета, ведь среди 5 заказов за 01.01 мы имеем только трёх уникальных покупателей!

Для подсчета дневного оборота нам поможет SQL-функция SUM().

SELECT s.date, t.count AS 'Кол-во заказов', q.user AS 'Кол-во покупателей', m.`summary` AS 'Оборот' FROM sum s 
LEFT JOIN (SELECT date, COUNT(order_id) AS count FROM sum GROUP BY date) t ON s.date = t.date 
LEFT JOIN (SELECT date, COUNT(DISTINCT user_id) AS user FROM sum GROUP BY date) q ON s.date = q.date 
LEFT JOIN (SELECT date, SUM(price) AS summary FROM sum GROUP BY date) m ON s.date = m.date 
GROUP BY s.date

Как видите, добавился третий LEFT JOIN, который добавляет необходимые нам значения. В основной части запроса я постоянно использую оператор AS, чтобы столбец имел человеко-понятное имя. Внутри LEFT JOIN AS также используется для удобной передачи значений в основной запрос.

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

Итак, на выходе мы имеем ровно то, что и хотели увидеть!

Задача решена!

За сим все!

Безошибочного Вам кода!

LEFT JOIN и вложенные запросы — решение повседневных задач: 5 комментариев

  1. Доброго времени суток! Никак не могу понять, был запрос SELECT `date` FROM `sum` GROUP BY `date` — по нему вопросов нет, после чего откуда то берется SELECT s.date, t.count AS ‘Кол-во заказов’ FROM sum s
    LEFT JOIN (SELECT date, COUNT(order_id) AS count FROM sum GROUP BY date) t ON s.date = t.date
    GROUP BY s.date , что это за приставки? откуда взялся t.count? Будьте добры объясните пожалуйста

    1. Иван, смотрите:
      В MySQL можно задавать короткое имя для таблицы, чтобы сократить запись. За это отвечает конструкция FROM sum s Именно тут я задал для таблицы sum короткое имя s. Это необходимо для того, чтобы, во-первых, работал LEFT JOIN, во-вторых, можно было точно указывать конкретный столбец. Ведь если у Вас в двух таблицах есть столбцы с одинаковыми именами, то для MySQL будет непонятно, что откуда брать. А в данном случае прописывается конкретный адрес.
      Таким образом, t.count — это столбец count в таблице, которую мы используем в LEFT JOIN. Её не существует физически — это всего лишь результат выборки, временная таблица. Но и к ней можно обратиться по алиасу (короткому имени).

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

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