Вопрос по sql, mysql – SQL-запрос с avg и группировать по

12

У меня есть некоторые проблемы с написанием запроса SQL для MySQL. У меня есть таблица со следующей структурой:

mysql> select id, pass, val from data_r1 limit 10;
+------------+--------------+----------------+
| id         | pass         | val            |
+------------+--------------+----------------+
| DA02959106 | 5.0000000000 |  44.4007000000 |
| 08A5969201 | 1.0000000000 | 182.4100000000 |
| 08A5969201 | 2.0000000000 | 138.7880000000 |
| DA02882103 | 5.0000000000 |  44.7265000000 |
| DA02959106 | 1.0000000000 | 186.1470000000 |
| DA02959106 | 2.0000000000 | 148.2660000000 |
| DA02959106 | 3.0000000000 | 111.9050000000 |
| DA02959106 | 4.0000000000 |  76.1485000000 |
| DA02959106 | 5.0000000000 |  44.4007000000 |
| DA02959106 | 4.0000000000 |  76.6485000000 |

Я хочу создать запрос, который извлекает следующую информацию из таблицы:

id, AVG of 'val' for 'pass' = 1, AVG of 'val' for 'pass' = 2, etc

Результат запроса должен выглядеть следующим образом:

+------------+---------+---------+---------+---------+---------+---------+---------+
| id         | val_1   | val_2   | val_3   | val_4   | val_5   | val_6   | val_7   |
+------------+---------+---------+---------+---------+---------+---------+---------+
| DA02959106 | 186.147 | 148.266 | 111.905 | 76.3985 | 44.4007 | 0       | 0       |
+------------+---------+---------+---------+---------+---------+---------+---------+

конечно же, с большим количеством строк для каждого уникального «идентификатора».

Я уже пробовал некоторые запросы, такие как

SELECT id, pass, AVG(val) AS val_1 FROM data_r1 WHERE pass = 1 GROUP BY id;

Это возвращает правильный результат, но мне нужно расширить его результатами для других возможных значений «pass». (до 7)

Я пытался использовать вложенный SELECT в AVG, но это не сработало, потому что я не понял, как правильно ограничить его текущим идентификатором.

Затем я создал представления, чтобы представить результат каждого запроса для 'pass' apos; = 1, «пройти» = 2 и т. Д. Но для большинства идентификаторов наибольшее значение для 'pass' apos; равен 5. При использовании запросов JOIN для получения окончательного результата из представлений я получил пустой набор результатов, поскольку некоторые из представлений являются пустыми / не имеют значений для определенного идентификатора 'id'.

Есть идеи?

Я отредактировал свой вопрос и добавил таблицу, как я ожидаю мой результат. Я должен передать результат в другую программу, поэтому в строке должен быть один идентификатор. theFen
Должно ли ваше значение AVG быть в столбцах или желаемый запрос может вернуть столько строк, сколько существует различных значений прохода? Grzegorz W
У тебя есть3 записи сpass = 5, все с разными идентификаторами. Какой из этихid вы хотите? Пожалуйста, опубликуйте результаты, которые вы ожидаете от предоставленных вами данных. Quassnoi
Мой отредактированный запрос дает именно тот результат, который вы просили ... Marco

Ваш Ответ

2   ответа
29

SELECT id, pass, AVG(val) AS val_1 
FROM data_r1 
GROUP BY id, pass;

Или, если вы хотите только одну строку для каждого идентификатора, это:

SELECT d1.id,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 1) as val_1,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 2) as val_2,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 3) as val_3,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 4) as val_4,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 5) as val_5,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 6) as val_6,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 7) as val_7
from data_r1 d1
GROUP BY d1.id
@theFen: попробуйте мой отредактированный запрос.
он этого не хочет. это вернет 5 строк, и он просто хочет один со всеми данными.
Марко, я только что попробовал твое решение, и похоже, что оно делает именно то, что я хотел. Не знал, что вы можете ссылаться на «внутренний»; ВЫБЕРИТЕ к внешнему. Спасибо вам большое! theFen
1

вы хотите среднее значение для каждого идентификатора на каждом проходе. Решение

SELECT id, pass, avg(value) FROM data_r1
GROUP BY id, pass;

Похожие вопросы