Период заказов клиентов онлайн урок.


Выберем клиентов, у которых период между двумя заказами более трех месяцев. Для этого нам, прежде всего, необходимы комбинации дат в таблице заказов. Сами комбинации дат нам нужны в рамках каждого заказчика, т.е. чтоб мы сравнивали даты одного и того же заказчика а не разных.

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

SELECT * FROM orders o1, orders o2

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

SELECT * FROM orders o1, orders o2 
WHERE o1.customer_id = o2.customer_id

Теперь мы получили комбинации строк для каждого заказчика. Но даты у нас дублируются, т.е. в результате у нас есть как комбинация (date-1, date-2), так и комбинация (date-2, date-1). Также в комбинациях присутствуют строки с одинаковой датой. Для того, чтобы убрать симметричные комбинации, допишем условие, чтобы одна из дат всегда была больше другой

SELECT * FROM orders o1, orders o2 WHERE o1.customer_id = o2.customer_id AND o1.date > o2.date

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

SELECT
o1.id, o1.date, o1.customer_id,o2.id, o2.date, o2.customer_id,
period_diff(date_format(o1.date, '%y%m'), date_format(o2.date,'%y%m' )) as diff
FROM orders o1, orders o2
WHERE o1.customer_id = o2.customer_id and o1.date > o2.date


+----+------------+-------------+----+------------+-------------+------+
| id | date | customer_id | id | date | customer_id | diff |
+----+------------+-------------+----+------------+-------------+------+
| 26 | 2003-05-21 | 3 | 21 | 2003-04-29 | 3 | 1 |
| 29 | 2003-06-06 | 11 | 5 | 2003-01-31 | 11 | 5 |
| 34 | 2003-06-27 | 11 | 5 | 2003-01-31 | 11 | 5 |
| 34 | 2003-06-27 | 11 | 29 | 2003-06-06 | 11 | 0 |
| 36 | 2003-07-02 | 6 | 14 | 2003-03-26 | 6 | 4 |
| 38 | 2003-07-10 | 81 | 22 | 2003-05-07 | 81 | 2 |
| 43 | 2003-08-08 | 6 | 14 | 2003-03-26 | 6 | 5 |
| 43 | 2003-08-08 | 6 | 36 | 2003-07-02 | 6 | 1 |
| 45 | 2003-08-13 | 90 | 17 | 2003-04-11 | 90 | 4 |
| 47 | 2003-09-03 | 101 | 32 | 2003-06-16 | 101 | 3 |
+----+------------+-------------+----+------------+-------------+------+


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

SELECT COUNT(*) FROM orders o3 WHERE o3.date < o1.date AND o3.date > o2.date AND o1.customer_id = o3.customer_id

Тут мы используем даты из наших комбинаций, учитывая что дата из o1 всегда больше даты из o2.

Добавим результат этого подзапроса в список полей выборки:

SELECT
o1.id, o1.date, o1.customer_id,o2.id, o2.date, o2.customer_id,
period_diff(date_format(o1.date, '%y%m'), date_format(o2.date,'%y%m' )) as diff,
(SELECT COUNT(*) FROM orders o3 WHERE o3.date < o1.date AND o3.date > o2.date AND o1.customer_id = o3.customer_id) AS inside_dates
FROM orders o1, orders o2
WHERE o1.customer_id = o2.customer_id and o1.date > o2.date
ORDER BY o1.customer_id ASC


+-----+------------+-------------+-----+------------+-------------+------+--------------+
| id | date | customer_id | id | date | customer_id | diff | inside_dates |
+-----+------------+-------------+-----+------------+-------------+------+--------------+
| 199 | 2004-09-27 | 1 | 24 | 2003-05-20 | 1 | 16 | 0 |
| 246 | 2004-11-25 | 1 | 24 | 2003-05-20 | 1 | 18 | 1 |
| 246 | 2004-11-25 | 1 | 199 | 2004-09-27 | 1 | 2 | 0 |
| 247 | 2004-11-29 | 2 | 25 | 2003-05-21 | 2 | 18 | 1 |
| 247 | 2004-11-29 | 2 | 179 | 2004-08-06 | 2 | 3 | 0 |
| 179 | 2004-08-06 | 2 | 25 | 2003-05-21 | 2 | 15 | 0 |
| 243 | 2004-11-24 | 3 | 21 | 2003-04-29 | 3 | 19 | 2 |
| 243 | 2004-11-24 | 3 | 26 | 2003-05-21 | 3 | 18 | 1 |
| 243 | 2004-11-24 | 3 | 124 | 2004-02-20 | 3 | 9 | 0 |
| 248 | 2004-11-29 | 3 | 21 | 2003-04-29 | 3 | 19 | 3 |
+-----+------------+-------------+-----+------------+-------------+------+--------------+


Теперь осталось убрать записи, где inside_dates не равно нулю, а разница менее или равна 3 месяцам и сгруппировать по идентификатору клиента для устранения дублей.

Перенесем вычисление разницы и подзапрос в условие:

SELECT o1.id, o1.date, o1.customer_id,o2.id, o2.date, o2.customer_id 
FROM orders o1, orders o2
WHERE o1.customer_id = o2.customer_id AND o1.date > o2.date
AND (select count(*) from orders o3 where o3.date < o1.date and o3.date > o2.date and o1.customer_id = o3.customer_id) = 0
AND period_diff(date_format(o1.date, '%y%m'), date_format(o2.date,'%y%m' )) > 3
GROUP BY o1.customer_id
ORDER BY o1.customer_id ASC


+-----+------------+-------------+-----+------------+-------------+
| id | date | customer_id | id | date | customer_id |
+-----+------------+-------------+-----+------------+-------------+
| 199 | 2004-09-27 | 1 | 24 | 2003-05-20 | 1 |
| 179 | 2004-08-06 | 2 | 25 | 2003-05-21 | 2 |
| 124 | 2004-02-20 | 3 | 26 | 2003-05-21 | 3 |
| 276 | 2005-02-03 | 4 | 216 | 2004-10-29 | 4 |
| 59 | 2003-10-10 | 5 | 4 | 2003-01-29 | 5 |
| 36 | 2003-07-02 | 6 | 14 | 2003-03-26 | 6 |
| 131 | 2004-03-15 | 8 | 201 | 2003-10-04 | 8 |
| 102 | 2003-12-01 | 9 | 12 | 2003-03-25 | 9 |
| 149 | 2004-05-07 | 10 | 8 | 2003-02-24 | 10 |
| 29 | 2003-06-06 | 11 | 5 | 2003-01-31 | 11 |
+-----+------------+-------------+-----+------------+-------------+