Меню
Что нового?

Сортировка результата по данным из другой таблицы

phprus

phprus

Moderator
Команда форума
Сортировка результата по данным из другой таблицы

Есть таблица log в которую заносится информация о запросах.
id - не уникальное поле в случае прихода повторного запроса добавляется новая запись.
id bigint(20)
old_id bigint(20)
ip int(11)
time int(11)
userid int(10)
uid binary(32)
start int(10)
rescount bigint(20)
Нажмите для раскрытия...
И таблица query в которой хранятся запросы.
id bigint(20)
query text
Нажмите для раскрытия...
Нужно выбрать из таблицы query записи у которых поле query совпадает с несколькими строками (с этим проблем нет) и отсортировать их следующим образом:
Во первых по популярности. То есть чем больше отношение (кол-во записей с таким id в log) / (разница минимального и максимального значений поля time в таблице log для записей с таким id) (это получается кол-во запросов в единицу времени) тем выше должен быть данный запрос в результате.
Во вторых по максимальному значению поля rescount в таблице log для записей с таким id.


Подскажите пожалуйста как это можно сделать и какие индексы необходимо добавить, чтобы такая сортировка работала быстро.
 

Vallar_ultra

Любитель выпить :)
Блин, может только я ничего толком не понял.....

Тебе надо выбрать из query записи по кол-ву вхождений в log-е, потом по разнице timestamp-ов max и min и по rescount - у?
query.id = log.id
я првильно тебя понял?
 
phprus

phprus

Moderator
Команда форума
Vallar_ultra
Нет. В:
(кол-во записей с таким id в log) / (разница минимального и максимального значений поля time в таблице log для записей с таким id)
Нажмите для раскрытия...
/ - обозначает операцию деления.
Те надо выбрать по кол-ву вхождений деленную на разность timestamp-ов max и min. ( Это получится кол-во вхождений в единицу времени )

А в случае совпадения данных величин у нескольких записей их надо сортировать по rescount.

>query.id = log.id
Да. Я совсем забыл указать это в своем первом сообщении.

iNTa
Как они помогут при сортировке?
 

alpine

Новичок
phprus
Ну и как ты делал и в каком месте конкретно не получается?
 
phprus

phprus

Moderator
Команда форума
alpine
Вот так:
SELECT * FROM query WHERE query LIKE 'test%'

А как к этому прикрутить нужную мне сортировку я не знаю и мыслей никаких нету.
 

Serg Karpenko

Новичок
имхо нужен group by id по первой таблице с соответствующими агрегирующими ф-ями, потом приджойнить вторую - сортировать по агрегатам.
 

alpine

Новичок
phprus
- Заджоиниваешь(left join) таблицу query и таблицу log
- Группируешь по query.id
- Используешь в select_statement неоходимые агрегатные функции например COUNT(*) as cnt, MAX(time) as max_time, MIN(time) as min_time, MAX(rescount) as max_rescount
- ORDER BY (cnt/(max_time-min_time)) DESC, max_rescount DESC
 
phprus

phprus

Moderator
Команда форума
Написал такой запрос:
[sql]SELECT *,

COUNT(*) as cnt, MAX(log.time) as max_time, MIN(log.time) as min_time, MAX(log.rescount) as max_rescount

FROM `query`
LEFT JOIN log ON (
log.id = query.id
)
WHERE query.query LIKE 'php%'
AND cnt > 1 AND max_time != min_time
GROUP BY id

ORDER BY (cnt/(max_time-min_time)) DESC, max_rescount DESC[/sql]

Но он не работавет MySQL ругается: #1054 - Unknown column 'cnt' in 'where clause'

Подскажите пожалуйста что я не правильно делаю.
 
phprus

phprus

Moderator
Команда форума
Апельсин
Понял. Исправил.

Но возникла другая ошибка:
#1054 - Unknown column 'cnt' in 'order clause'
[sql]SELECT * , COUNT( * ) AS cnt, MAX( log.time ) AS max_time, MIN( log.time ) AS min_time, MAX( log.rescount ) AS max_rescount
FROM `query`
LEFT JOIN log ON ( log.id = query.id )
WHERE query.query LIKE 'php%'
GROUP BY log.id
HAVING (
cnt >1
AND max_time != min_time
)
ORDER BY (
cnt / ( max_time - min_time )
) DESC , max_rescount DESC [/sql]

В чем я опять не прав? Подскажите пожалуйста.
 

alpine

Новичок
phprus
попробуй выражение (cnt / ( max_time - min_time )) вынести в select_statement дать ему алиас и сортировать по этому алиасу.
 
phprus

phprus

Moderator
Команда форума
alpine
Вынес, но всеравно получаю ошибку: #1054 - Unknown column 'cnt' in 'field list'.
В чем я еще мог ошибиться?
 

alpine

Новичок
phprus
Попробуй (cnt / ( max_time - min_time )) заменить на (COUNT( * ) / ( MAX( log.time ) - MIN( log.time ) )) as some_alias
 
phprus

phprus

Moderator
Команда форума
alpine
Заменил. Заработало.

Всем спасибо за помощь.
 

WP

^_^
По поводу WHERE, сообщение Апельсин не совсем правильно.
WHERE выполняется до интерпретации fields, фильтруя записи в таблицах. А HAVING фильтрует результат запроса.
 

WP

^_^
Дело не в "алисах полей", и они вовсе не должны быть равными по значению с полями неких таблиц (как и в нашем случае) =) Просто Imho, нужно уяснить что WHERE фильтрует объект FROM (таблицу/запрос), а HAVING результат =) И в случае с группировкой конечно же HAVING т.к. он выполняется после группировки =)
 
Войдите или зарегистрируйтесь для ответа.
Сверху