@MaxxxZ

Как реализовать подзапрос в коррелированном запросе?

Есть две таблицы:
1. Справочник парамтеров params с полями id и title
2. Таблица значений параметров data, содержащая столбцы p_id (идентификатор параметра) и value (для одного идентификатора параметра могут быть тысячи разных значений).

Задача: в коррелированном запросе вытащить среднее из 50 наибольших значений для заданных параметров, база MySQL 5.7

Если решать в лоб, то кажется, что можно написать так:
SELECT title,
(SELECT AVG(value) FROM (SELECT value FROM data WHERE p_id=id ORDER BY value DESC LIMIT 50) AS t1)
FROM params WHERE id IN (347, 564, 252, 256) ORDER BY title

Однако, запрос завершается с ошибкой "#1054 - Неизвестный столбец 'id' в 'where clause'" т.е. в подзапросе скорелированного запроса уже не видно поле родителя.

Каким образом можно сделать требуемую выборку?
  • Вопрос задан
  • 68 просмотров
Решения вопроса 1
rozhnev
@rozhnev
Fullstack programmer, DBA, медленно, дорого
Ниже решение для MySQL 8 используюшее window functions:
select 
	id, title, avg_value
from params
join (
select 
	p_id, 
	`value`, 
	avg(`value`) over (partition by p_id order by value desc) avg_value,
	row_number() over (partition by p_id order by value desc) rn
from data
) avg_data on avg_data.p_id = params.id
where rn = 50
;


MySQL fiddle

или так:

select 
	id, title, avg(value) avg_value
from params
join (
select 
	p_id, 
	`value`, 
	row_number() over (partition by p_id order by value desc) rn
from data
) avg_data on avg_data.p_id = params.id
where rn <= 50
group by id, title
;
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

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

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