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

Question

[Dmitriy Q]

есть 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) с соответствующими операторам данными.

Comments

[Akina]

DESCRIBE даёт неполные данные. Замените на вывод SHOW CREATE TABLE. Также укажите точную версию MySQL.

в первую таблицу добавить столбец AVG(valuation) с соответствующими операторам данными.

В таблицу? или в результат запроса (получить две колонки с AVG - из первой и из второй таблиц)?

[Dmitriy Q]

Akina, извиняюсь - в результат запроса - поправил неточность

mysql> SHOW CREATE TABLE oper_survey;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                            |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| oper_survey | CREATE TABLE `oper_survey` (
  `num` varchar(20) DEFAULT NULL,
  `operator` varchar(20) DEFAULT NULL,
  `queue` varchar(20) DEFAULT NULL,
  `valuation` varchar(20) DEFAULT NULL,
  `date` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Вторая таблица аналогичная

Answer 1

[Akina]

В простом варианте:

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)