Krotesk
@Krotesk
Инженер техподдержки телефонии на основе Asterisk

Как в таблицу SQL добавить столбец с аналогичной второй таблицы?

есть 2 таблицы, 3 столбца в которых идентичны, а 4 (valuation) разные
mysql> describe oper_survey;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| num       | varchar(20) | YES  |     | NULL    |       |
| operator  | varchar(20) | YES  |     | NULL    |       |
| queue     | varchar(20) | YES  |     | NULL    |       |
| valuation | varchar(20) | YES  |     | NULL    |       |
| date      | datetime    | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> describe service_survey;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| num       | varchar(20) | YES  |     | NULL    |       |
| operator  | varchar(20) | YES  |     | NULL    |       |
| queue     | varchar(20) | YES  |     | NULL    |       |
| valuation | varchar(20) | YES  |     | NULL    |       |
| date      | datetime    | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

Нужно получаемые данные:
mysql> SELECT queue,operator,AVG(valuation) FROM oper_survey WHERE queue = '500' AND date BETWEEN '2021-06-01' AND '2021-06-16' GROUP BY operator ORDER BY operator;
+-------+----------+------------------+
| queue | operator | AVG(valuation)   |
+-------+----------+------------------+
| 500   |          |              2.5 |
| 500   | 970      | 3.85714285714286 |
| 500   | 971      |            4.625 |
| 500   | 972      | 3.45161290322581 |
| 500   | 973      | 4.81818181818182 |
| 500   | 974      | 4.64102564102564 |
| 500   | 975      | 4.65714285714286 |
| 500   | 976      | 4.93478260869565 |
| 500   | 978      |                5 |
| 500   | 979      | 4.90909090909091 |
+-------+----------+------------------+
10 rows in set (0.03 sec)

mysql> SELECT queue,operator,AVG(valuation) FROM service_survey WHERE queue = '500' AND date BETWEEN '2021-06-01' AND '2021-06-16' GROUP BY operator ORDER BY operator;
+-------+----------+------------------+
| queue | operator | AVG(valuation)   |
+-------+----------+------------------+
| 500   | 970      |                4 |
| 500   | 971      | 4.63636363636364 |
| 500   | 972      | 3.23684210526316 |
| 500   | 973      | 4.80769230769231 |
| 500   | 974      |            4.625 |
| 500   | 975      |              4.6 |
| 500   | 976      | 4.80769230769231 |
| 500   | 978      |                5 |
| 500   | 979      |              4.8 |
+-------+----------+------------------+
9 rows in set (0.00 sec)

в результат запроса по oper_survey добавить столбец AVG(valuation) из второго запроса (service_survey) с соответствующими операторам данными.
  • Вопрос задан
  • 105 просмотров
Пригласить эксперта
Ответы на вопрос 1
@Akina
Сетевой и системный админ, SQL-программист.
В простом варианте:

SELECT 500 queue, operator, os.avg_os, ss.avg_ss
FROM ( SELECT operator,AVG(valuation) avg_os
       FROM oper_survey 
       WHERE queue = '500' AND `date` BETWEEN '2021-06-01' AND '2021-06-16' 
       GROUP BY operator ) os
JOIN ( SELECT operator, AVG(valuation) avg_ss
       FROM service_survey 
       WHERE queue = '500' AND `date` BETWEEN '2021-06-01' AND '2021-06-16' 
       GROUP BY operator ) ss USING (operator)


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

SELECT 500 queue, operator, COALESCE(os.avg_os, 0) avg_os, COALESCE(ss.avg_ss, 0) avg_ss
FROM ( SELECT operator FROM oper_survey WHERE queue = '500'
       UNION 
       SELECT operator FROM service_survey WHERE queue = '500' ) op
LEFT JOIN ( SELECT operator,AVG(valuation) avg_os
            FROM oper_survey 
            WHERE queue = '500' AND `date` BETWEEN '2021-06-01' AND '2021-06-16' 
            GROUP BY operator ) os USING (operator)
LEFT JOIN ( SELECT operator, AVG(valuation) avg_ss
            FROM service_survey 
            WHERE queue = '500' AND `date` BETWEEN '2021-06-01' AND '2021-06-16' 
            GROUP BY operator ) ss USING (operator)
Ответ написан
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы