Коллеги, подскажите, пожалуйста, как решить следующую задачу. Есть 2 таблицы: gallery_photo c полями id_photo, file – id фото, файл и gallery_month с полями viewings, date, id_photo – просмотров, месяц, id фото Связь один ко многим. Необходимо объединить таблицы и выбрать по одной фото за каждый месяц с наибольшим количеством просмотров. Если делать запрос к одной таблице, то выводит все, что надо: Код (Text): SELECT MAX(viewings) AS viewings, date, id_photo FROM gallery_month GROUP BY date ORDER BY date DESC LIMIT 12; Но если делать запрос к объединенной таблице, то скрипт зависает: Код (Text): SELECT MAX(gm.viewings) AS viewings, gm.date AS date, gm.id_photo AS id_photo, gp.file AS file FROM gallery_photo gp LEFT JOIN gallery_month gm ON (gp.id_photo = gm.id_photo) GROUP BY gm.date ORDER BY gm.date DESC LIMIT 12; Подскажите в чем может быть проблема?
Во-первых: Для корректной выборки вам нужно использовать вложенный запрос http://dev.mysql.com/doc/refman/5.0/en/example-maximum-colu ... p-row.html Во-вторых: возможно стоит расставить ИНДЕКСЫ для увеличения скорости
Alexander Serkin, составил запрос по Вашему примеру. Но... что-то не совсем то выводит. Показывает месяц (date), максимальное количество просмотров у самой популярной фото за месяц (viewings). Однако идентификатор (id_photo) выводится фото не с наибольшим, а с наименьшим кол-вом просмотров. Как думаете, в чем здесь может быть ошибка? Вот код: Код (Text): $query = "SELECT gm.viewings AS viewings, gm.date AS date, gp.id_photo AS id_photo FROM gallery_photo gp LEFT JOIN (SELECT MAX(viewings) AS viewings, date, id_photo FROM gallery_month GROUP BY date) AS gm ON gp.id_photo=gm.id_photo ORDER BY gm.date DESC LIMIT 6;"; echo 'Дата: '.$q['date'].' Просмотров: '.$q['viewings'].' id фото: '.$q['id_photo'].'<br>'; Вот что выводит:
Nikolai_, лучше бы вы привели create table для этих двух таблиц. Не совсем понятен смысл gallery_month и что там уникально. Если бы у меня была такая задача, я бы завел gallery_hits и не пытался хранить в ней месяцы, регистрировал бы факт просмотра как одну запись (дата_время, ид_картинки). Понятие "месяц" появилось бы только в запросе Код (Text): `date` BETWEEN :d1 AND :d2 здесь d1 и d2 первый и последний дни нужного месяца. Тогда рейтинг просмотров за месяц выглядел бы как-то так: Код (Text): SELECT `photo_id`, count(*) AS `view_count`, MAX(`date`) AS `last_viewed` FROM `gallery_hits` WHERE `date` BETWEEN :d1 AND :d2 GROUP BY `photo_id` ORDER BY `view_count` DESC
artoodetoo, вот структура этих таблиц (см. ниже). При просмотре фото делается запрос к табл. gallery_month. Если там есть запись за текущий месяц (date) об этом фото (id_photo), то увеличивается кол-во просмотров (viewings). Если такой записи об этом фото за текущий месяц нет, то она создается. А теперь необходимо на странице вывести самые популярные (по просмотрам) фото по месяцам (за последние полгода - LIMIT 6). Но вот тут и проблема начинается. Составил запрос по примеру по ссылке Alexander Serkin, но он почему-то показывает наибольшее кол-во просмотров за месяц, но в то же время id фото выводит произвольное, не соответствующее фото с наибольшим кол-вом просмотром за месяц. Не пойму, где ошибка. Кто видит, где ошибка, подскажите, пожалуйста, что исправить. Код (Text): CREATE TABLE `gallery_photo` ( `id_photo` int(6) unsigned NOT NULL auto_increment, `name` varchar(64) default NULL, # название фото `file` file(64) default NULL, # файл фото PRIMARY KEY (`id_photo`), ) TYPE=MyISAM; CREATE TABLE `gallery_month` ( `id` int(9) unsigned NOT NULL auto_increment, `id_photo` int(6) unsigned NOT NULL, # файл фото `date` date NOT NULL default '0000-00-00', # месяц, например 2012-03-00 `viewings` int(6) unsigned NOT NULL, # кол-во просмотров этого фото за месяц PRIMARY KEY (`id`) ) TYPE=MyISAM;
"Самые полулярные за последние полгода" не через limit делаются. Вам надо брать диапазон дат "полгода", группировать по id_photo и сортировать по sum(viewings). Т.е. примерно как я выше писал. Только count(*) замените на sum(viewings) — это будет ваше кол-во просмотров за период. Объединять таблицы при суммировании не нужно! Готовый результат уже можно заджойнить с gallery_photo. update: Кстати, имейте в виду: в mysql есть запрет на использование limit во вложенном запросе. limit, если он есть, должен быть "снаружи", самой последней строкой сложного запроса. Считаю попыткой хранить помесячно вы сами связали себе руки для расширенной статистики — храните или просто хиты или сумму за день, тогда статистику сможете собрать за произвольный период.
Код (Text): SELECT id_photo, DATE_FORMAT(date, '%Y-%m) AS df, MAX(count(id_photo)) AS cnt FROM photo_month GROUP BY id_photo, df и уже сюда прикрутить джоин с данными.
Коллеги, еще раз прошу помочь решить задачу. Перепробовал уже какие только можно решения, но все не то - выводит не то, что надо. Почему-то не совпадают кол-во просмотров (viewings) и id фото (id_photo) к нему. Не пойму в чем проблема. Надо выбрать фото (id_photo) с наибольшим кол-вом просмотров (viewings) за каждый месяц (date) и вывести эти данные: месяц, id и просмотры. Вот, что должно выводится по идее: А вот таблица: Кто может, подскажите, пожалуйста, корректный пример.
По твоим данным итог не совсем такой должен быть. Код (Text): 3333 2012-01-00 6 2229 2012-02-00 10 22 2012-03-00 11 Смотри: находим сколько максимум просмотров было по одной картинке каждый месяц. Код (Text): SELECT `date`, max(`viewings`) AS `viewings` FROM `gallery_month` GROUP BY `date` здесь нет id_photo, т.к. его в один заход не добыть! другие диалекты SQL просто запретят сюда ставить id_photo, MySql разрешает и это засада, т.к. непонятно что оно в данном случае означает. и как нам написать агрегат тот-id-который-стоит-в-одной-строке-с-max-viewings ? ответ "никак"!!! поэтому оборачиваем предыдущий запрос в сложный запрос: Код (Text): SELECT `gm`.* FROM (SELECT `date`, max(`viewings`) AS `viewings` FROM `gallery_month` GROUP BY `date`) AS champ INNER JOIN `gallery_month` AS `gm` ON `gm`.`date`=`champ`.`date` AND `gm`.`viewings`=`champ`.`viewings` теоретически у тебя могут быть несколько фото с одинаковым рейтингом просмотров, это нормально. тогда на каждый месяц будет выведено более одной записи
Спасибо, artoodetoo! Все доходчиво объяснили. Теперь выводит то, что нужно. Подскажите, как можно объединить этот запрос с этой таблицей по id_photo: Составил вот такой запрос, но он не работает: Код (Text): $query = "SELECT gp.id_photo AS id_photo, t1.viewings AS viewings, t1.date AS date FROM gallery_photo gp LEFT JOIN (SELECT id_photo, date, max(viewings) AS viewings FROM gallery_month GROUP BY date) AS champ INNER JOIN gallery_month AS gm ON gm.date=champ.date AND gm.viewings=champ.viewings) t1 ON gp.id_photo = t1.id_photo ORDER BY date DESC LIMIT 3;"; Где тут может быть ошибка?
ошибка в том, что первый JOIN не имеет соответствующего ON не понял зачем тут LEFT JOIN. открытое (левое) объединение используется если приклеиваемая правая часть может отсутствовать. в твоем случае получается, что ты хочешь вывести инфу о всех картинках, а если вдруг картинка была чемпионом, то будет отмечен этот факт. ерунда вобщем, да еще и с ошибкой. вот это работает: Код (Text): SELECT `gp`.*, `champ`.date, `champ`.viewings FROM (SELECT `date`, max(`viewings`) AS `viewings` FROM `gallery_month` GROUP BY `date`) AS champ INNER JOIN `gallery_month` AS `gm` ON `gm`.`date`=`champ`.`date` AND `gm`.`viewings`=`champ`.`viewings` INNER JOIN `gallery_photo` AS `gp` ON `gm`.`id_photo`=`gp`.`id_photo`