Средний доход на клиента/сотрудника онлайн урок.


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

SELECT * , avg( amount )
FROM payments
GROUP BY customer_id


+-----+-------------+--------------+--------------+-----------+--------------------+
| id | customer_id | check_number | payment_date | amount | avg( amount ) |
+-----+-------------+--------------+--------------+-----------+--------------------+
| 1 | 1 | HQ336336 | 2004-10-19 | 6066.78 | 7438.12 |
| 4 | 2 | BO864823 | 2004-12-17 | 14191.12 | 26726.993333333332 |
| 7 | 3 | GG31455 | 2003-05-20 | 45864.03 | 45146.267499999994 |
| 11 | 4 | DB933704 | 2004-11-14 | 19501.82 | 38983.22666666667 |
| 14 | 5 | DB889831 | 2003-02-16 | 50218.95 | 26056.1975 |
... ... ... ... ... ...


Для получения среднего дохода на сотрудника мы вначале просто напишем запрос для получения списков заказчиков (которые внесли оплату) и платежей.

SELECT c.id AS customer_id, p.id AS payment_id, p.amount
FROM customers c
JOIN payments p ON p.customer_id = c.id

Далее необходимо присоединить поле с идентификатором сотрудника, причем нам нужен полный список - даже тех, кто не привязан к клиентам. Это осуществляется при помощи RIGHT JOIN. При этом выборка природно не будет содержать клиентов, которые привязаны к сотруднику, но не внесли оплаты.

SELECT c.id AS customer_id, p.id AS payment_id, avg( p.amount ) , w.id AS worker_id
FROM customers c
JOIN payments p ON p.customer_id = c.id
RIGHT JOIN workers w ON w.id = c.worker_id
GROUP BY w.id


+-------------+------------+--------------------+-----------+
| customer_id | payment_id | avg( p.amount ) | worker_id |
+-------------+------------+--------------------+-----------+
| NULL | NULL | NULL | 1 |
| NULL | NULL | NULL | 2 |
| NULL | NULL | NULL | 3 |
| NULL | NULL | NULL | 4 |
| NULL | NULL | NULL | 5 |
| NULL | NULL | NULL | 6 |
| 6 | 18 | 47138.40714285715 | 7 |
| 2 | 4 | 26733.31 | 8 |
| 25 | 83 | 27618.799999999996 | 9 |
| 17 | 67 | 29947.942000000003 | 10 |
| 16 | 63 | 28718.392352941177 | 11 |
| 10 | 34 | 29220.339999999997 | 12 |
| 14 | 56 | 29972.934210526317 | 13 |
| 1 | 1 | 38344.95896551724 | 14 |
| 13 | 52 | 27785.254444444443 | 15 |
| 29 | 93 | 32697.77380952381 | 16 |
| 5 | 14 | 30365.364285714288 | 17 |
| 3 | 7 | 31836.613749999997 | 18 |
| 19 | 73 | 35564.79714285715 | 19 |
| NULL | NULL | NULL | 20 |
| 15 | 59 | 32650.719285714284 | 21 |
| NULL | NULL | NULL | 22 |
| 40 | 117 | 32289.789166666666 | 23 |
+-------------+------------+--------------------+-----------+


Тут мы сразу сгруппировали записи по идентификатору сотрудника, и в этих группах вычислили средние значения. Осталось сделать так, чтобы после вычислений не было NULL значений. Воспользуемся функцией IFNULL(e1, e2) - она возвращает e1, если e1 не NULL, иначе - она возвращает e2

SELECT c.id AS customer_id, p.id AS payment_id, ifnull( avg( p.amount ) , 0 ) , w.id AS worker_id
FROM customers c
JOIN payments p ON p.customer_id = c.id
RIGHT JOIN workers w ON w.id = c.worker_id
GROUP BY w.id


+-------------+------------+-------------------------------+-----------+
| customer_id | payment_id | ifnull( avg( p.amount ) , 0 ) | worker_id |
+-------------+------------+-------------------------------+-----------+
| NULL | NULL | 0 | 1 |
| NULL | NULL | 0 | 2 |
| NULL | NULL | 0 | 3 |
| NULL | NULL | 0 | 4 |
| NULL | NULL | 0 | 5 |
| NULL | NULL | 0 | 6 |
| 6 | 18 | 47138.40714285715 | 7 |
| 2 | 4 | 26733.31 | 8 |
| 25 | 83 | 27618.799999999996 | 9 |
| 17 | 67 | 29947.942000000003 | 10 |
| 16 | 63 | 28718.392352941177 | 11 |
| 10 | 34 | 29220.339999999997 | 12 |
| 14 | 56 | 29972.934210526317 | 13 |
| 1 | 1 | 38344.95896551724 | 14 |
| 13 | 52 | 27785.254444444443 | 15 |
| 29 | 93 | 32697.77380952381 | 16 |
| 5 | 14 | 30365.364285714288 | 17 |
| 3 | 7 | 31836.613749999997 | 18 |
| 19 | 73 | 35564.79714285715 | 19 |
| NULL | NULL | 0 | 20 |
| 15 | 59 | 32650.719285714284 | 21 |
| NULL | NULL | 0 | 22 |
| 40 | 117 | 32289.789166666666 | 23 |
+-------------+------------+-------------------------------+-----------+