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

Question

[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'" т.е. в подзапросе скорелированного запроса уже не видно поле родителя.

Каким образом можно сделать требуемую выборку?

Comments

[Slava Rozhnev]

Уточните версию MySQL

[Akina]

Не надо тут использовать коррелированный подзапрос в выходном наборе. Используй нормальный JOIN. Если версия позволяет, можно LATERAL подзапрос, попроще получится.

[MaxxxZ]

Akina, к сожалению, JOIN очевиден, но не подходит. Так как в базе тысячи id, а статистику надо сделать только четырём. При использовании JOIN статистика добросовестно вычислится для всех строк и 900+ результатов будут ненужными, а подцепится только 4. Это займёт кучу времени. Видимо придётся апдейтить MySQL или уходить в циклы на языке высокого уровня

[MaxxxZ]

Akina, не правильно сначала ответил вам. JOIN под другой причине не вставить. Если не уходить в окна, как в предложенном ответе, то требуется сделать LIMIT 50 для каждого id. И если коррелированный запрос будет вызван нужное число раз, то JOIN будет выполнен разово. А это значит, что LIMIT 50 будет действовать не для каждого id, а для всего результата.

[Akina]

О, нашёл в комментарии, что версия MySQL - 5.7. Вы поправьте вопрос, и добавьте туда эту информацию - в самое начало, да покрупнее. Критичные же условия...

Да, на этой версии - согласен, без коррелированного запроса, да ещё и при указанных статистиках, никак не обойтись.

А на версии 8 есть совершенно шикарное решение

SELECT params.id, params.title, AVG(data2.value) avg_value
FROM params,
LATERAL ( SELECT *
          FROM data
          WHERE data.p_id = params.id
          ORDER BY data.value DESC LIMIT 50 ) data2
WHERE params.id IN (347, 564, 252, 256)
GROUP BY params.id, params.title

[MaxxxZ]

Поправил, спасибо

Answer 1

[Slava Rozhnev]

Ниже решение для 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
;

Comments

[MaxxxZ]

Спасибо. MySQL старая - 5.7. Буду думать, или обновлять до оконных функций, или задействовать язык высокого уровня с циклами.
Надеялся, что есть простое решение, как показать подзапросу параметр из родителя коррелированного. Видимо не всё так просто.