Подзапрос MySQL для ссылки на поле в родительском запросе

Я создаю запрос, который выполняет некоторую фильтрацию данных рейтинга.

Предположим, что у меня есть простая таблица под названием ratings, как показано ниже: сохранение данных из инструмента онлайн-рейтинга:

+----------------+----------------+--------+
| page_title     | timestamp      | rating |
+----------------+----------------+--------+
| Abc            | 20110417092134 | 1      |
| Abc            | 20110418110831 | 2      |
| Def            | 20110417092205 | 3      |
+----------------+----------------+--------+

Мне нужно извлечь страницы с высокой частотой низких значений в последних 10 рейтингах и ограничить этот запрос страницами, на которых была выпущена не менее 20 оценок за предыдущую неделю. Это <забавный > смешной длинный запрос, с которым я пришел:

SELECT a1.page_title, COUNT(*) AS rvol, AVG(a1.rating) AS theavg, 
(
     SELECT COUNT(*) FROM
     (
         SELECT * FROM ratings a2 WHERE a2.page_title = a1.page_title 
         AND DATE(timestamp) <= '2011-04-24' ORDER BY timestamp DESC LIMIT 10
     ) 
     AS latest WHERE rating >=1 AND rating <=2 ORDER BY timestamp DESC
)
AS lowest FROM ratings a1
WHERE DATE(a1.timestamp) <= "2011-04-24" AND DATE(a1.timestamp) >= "2011-04-17" 
GROUP BY a1.page_title HAVING COUNT(*) > 20

запрос верхнего уровня ищет страницы с более чем 20 рейтингами на неделе, заканчивающейся в 2011-04-24, предполагается, что подзапрос должен получить количество рейтингов со значениями между [1,2] из последних 10 оценок каждая статья из запроса верхнего уровня.

MySQL жалуется, что a1.page_title в предложении WHERE подбуквации является неизвестным столбцом, я подозреваю, что это потому, что a1 не определяется как псевдоним в запросе второго уровня, а только в запросе верхнего уровня, но Я не знаю, как это исправить.

(отредактирован)

Я добавляю в качестве объяснения моего подозреваемого выше, что касается перекрестного уровня, ссылающегося на другой запрос, который работает абсолютно нормально, обратите внимание, что здесь a1 не определен в подзапросе, но он находится в непосредственном родителе:

SELECT a1.page_title, COUNT(*) AS rvol, AVG(a1.rating) AS theavg, 
(
    SELECT COUNT(*) FROM ratings a2 WHERE DATE(timestamp) <= '2011-04-24'
    AND DATE(timestamp) >= '2011-04-17' AND rating >=1 
    AND rating <=2 AND a2.page_title = a1.page_title
) AS lowest FROM ratings a1 
WHERE DATE(a1.timestamp) <= '2011-04-17' AND DATE(a1.aa_timestamp) >= '2011-04-11' 
GROUP BY a1.page_title HAVING COUNT(*) > 20

Ответ 1

Я думаю, вы могли бы подумать о присоединении к двум строкам в виде строк, которые могли бы сделать вещи eaiser.

SELECT * 
FROM   (SELECT COUNT(*), 
               a2.page_title 
        FROM   ratings a2 
        WHERE  DATE(timestamp) <= '2011-04-24' 
               AND DATE(timestamp) >= '2011-04-17' 
               AND rating >= 1 
               AND rating <= 2 

        GROUP  BY a2.page_title) current 
       JOIN 
        (SELECT a1.page_title, 
                    COUNT(*)       AS rvol, 
                    AVG(a1.rating) AS theavg 
             FROM   ratings a1 
             WHERE  DATE(a1.timestamp) <= '2011-04-17' 
                    AND DATE(a1.a_timestamp) >= '2011-04-11' 
             GROUP  BY a1.page_title 
             HAVING COUNT(*) > 20) morethan20 
         ON current .page_title = morethan20.page_title

Ответ 2

Если все, что у вас есть, это одна простая таблица, я понятия не имею, куда вы тянете все эти другие имена таблиц, например: a1, a2, оценки. Я чувствую, что либо ваш SQL совсем немного выключен, либо ваш комментарий.

Причина, по которой вы делаете ошибку, состоит в том, что в вашем суб-подзапросе вы не включаете a1 в свой оператор "FROM"... так, чтобы таблица не была включена, на нее нельзя ссылаться в вашем предложении WHERE в этом суб-запросе.

SELECT * 
FROM
    (SELECT *
        FROM a1
        WHERE a1.timestamp <= (NOW()-604800)
            AND a1.timestamp >= (NOW()-1209600)
        GROUP BY a1.page_title
        HAVING COUNT(a1.page_title)>20)
    AS priorWeekCount
WHERE
    rating <= 2
ORDER BY timestamp DESC
LIMIT 10

так как у меня нет полной таблицы, чтобы проверить это... Я ДУМАЮ, что это то, что вы ищете.. но он непроверен и, зная мои привычки кодирования, очень редко то, что я набираю на 100% в первый раз;)

Ответ 3

Ваш анализ ошибки верен: lowest известен в подзапросе, a1 - нет.

Я думаю, что логика наизнанку. Следующий, вероятно, не самый лучший, но оптимизатор может быть достаточно умным, чтобы объединить два подзапроса в самом удаленном SELECT. (Если это не так, с риском удобочитаемости вы можете ввести другой уровень подзапроса.)

SELECT r20plus.page_title,
 AVG((SELECT rating 
      FROM ratings r WHERE r.page_title=r20plus.page_title 
      ORDER BY timestamp DESC LIMIT 10) ) as av,
 SUM((SELECT CASE WHEN rating BETWEEN 1 AND 2 THEN 1 ELSE 0 END 
      FROM ratings r WHERE r.page_title=r20plus.page_title
      ORDER BY timestamp DESC LIMIT 10) ) as n_low,
FROM
(SELECT page_title FROM ratings  
WHERE DATE(a1.timestamp) <= "2011-04-24" AND DATE(a1.timestamp) >= "2011-04-17"
GROUP BY page_title
HAVING COUNT(rating) >= 20) AS r20plus;