Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MySQL > помогите с запросом

Автор: DooZ 27.5.2008, 19:26

есть таблица с картинками

id - уникальный номер
url - урл страницы где эта картинка
thumb - урл картинки
shows - кол-во показов
clicks - кол-во кликов

пример:

1
http://domain.com/index.html
http://domain.com/1.jpg
0
0

2
http://domain.com/index.html
http://domain.com/2.jpg
0
0

3
http://domain.com/index.html
http://domain.com/3.jpg
0
0

4
http://domain1.com/index.html
http://domain1.com/1.jpg
0
0

5
http://domain2.com/index.html
http://domain2.com/1.jpg
0
0

6
http://domain2.com/index.html
http://domain2.com/2.jpg
0
0

т.е. как видите картинки уникальны, а вот урлы нет
т.е. у одного урла может быть несколько картинок

запрос:
SELECT id FROM images GROUP BY url ORDER BY shows/(clicks+1)
возвращает только первые три уникальные картинки с разных хостов (оно и понятно, т.к. групируется и все тут)
как написать запрос что бы возвращались уникальные картинки (1 картинка от 1-го хоста за один раз), но(!) с учетом order by ?

заранее спасибо

Автор: v2v 27.5.2008, 19:30

не понял что тебе надо.. покажи на примере что должно вернуться smile

Автор: DooZ 27.5.2008, 19:36

забыл добавить что после выборки делается
update images set shows=shows+1 where id=текущий_id (выборка в цикле)

так вот щас он выводит что-то типа:

1
4
5
увеличивает у каждого этого id счетчик shows +1 и все равно выводит 1 4 5

а надо что бы потом выводил например:
2 4 6
потом например:
3 4 5
потом
1 4 6
ну и т.д.

теперь понятно?

Автор: skyboy 27.5.2008, 20:31

Цитата(DooZ @ 27.5.2008, 18:36

)

теперь понятно?

непонятно.
если тебе надо уникальные картинки, а id - как раз уникальное поле, а id - в каждой записи вообще уникальное(первичный ключ, я прав?), то тебе группировка набок не нужна.
просто :

Код


SELECT id, shows/(clicks+1)
FROM images 
ORDER BY 2

Автор: DooZ 27.5.2008, 20:38

еще раз повторяю
мне надо выбрать из базы
УНИКАЛЬНЫЕ КАРТИНКИ а не ИД

есть много одинаковых УРЛ, у каждого урл может быть от ОДНОЙ до НЕСКОЛЬКИХ картинок
НО(!!!) выбрать надо ОДНУ УНИКАЛЬНУЮ КАРТИНКУ для каждого урла!

т.е. будет в базе 100 урл, у каждого урла по 10 картинок (итого 1000 записей)
надо выбрать 100 ИД (тоесть 100 картинок, 1 картинка = 1 урл)

так понятно?

Автор: v2v 27.5.2008, 21:13

то что ты описываешь соответствует твоему первому запросу, который ты сказал что не правилен.
объясняйся чётче...

Автор: DooZ 27.5.2008, 21:25

елки палки куда четче???
внесите в базу то что я дал и поймете о чем я

SELECT id FROM images GROUP BY url ORDER BY shows/(clicks+1)
да этот запрос возвращает три картинки
в данном случае:
1 4 5
потом у каждой увеличивает shows + 1
и при следующем запросе должен вернуть уже:
2 4 6
а возвращает ВСЕ РАВНО(!!!) 1 4 5

теперь понятно????
помоему яснее уже некуда

Добавлено через 1 минуту и 40 секунд
для ленивых вот запросы:
SELECT id FROM images GROUP BY url ORDER BY shows/(clicks+1)
-> 1, 4, 5
UPDATE images SET shows=shows+1 WHERE id=1
UPDATE images SET shows=shows+1 WHERE id=4
UPDATE images SET shows=shows+1 WHERE id=5

SELECT id FROM images GROUP BY url ORDER BY shows/(clicks+1)
-> 1,4,5 (а должно быть что-то вроде: 2,4,6)

вот

Автор: v2v 27.5.2008, 21:39

Добавлено через 34 секунды
запрос у тебя возвращает ID , а ты изменяешь поле SHOWS ))

Автор: DooZ 27.5.2008, 21:48

ты видимо издиваешься или не внимательно читал посты?

SELECT id FROM images GROUP BY url ORDER BY shows/(clicks+1)
я выбираю значиния исходя (сортируя) ПО shows/(click+1)

выбираться должна та картинка которая меньше всего была показана!

Автор: skyboy 27.5.2008, 21:49

странно.
если тебе надо уникальные картинки, а id никакого отношения к картинкам не имеет, то что для каждой картинки уникально? thumbs? если да, то почему группируешь по url, когда для картинки уникально только thumbs? если нет, то как вообще на такой структуре определяется отдельная уникальная картинка?

Цитата(DooZ @ 27.5.2008, 20:25

)

елки палки куда четче???

туда. туда четче.
вместо истерик лучше бы внятно объяснил, что у тебя есть и что тебе надобно.
если держишь нас за идиотов, зачем вопросы задаешь?

Автор: Fortop 27.5.2008, 21:53

Цитата(DooZ @ 27.5.2008, 20:38

)

т.е. будет в базе 100 урл, у каждого урла по 10 картинок (итого 1000 записей)
надо выбрать 100 ИД (тоесть 100 картинок, 1 картинка = 1 урл)

так понятно?

Ничерта не понятно smile

По какому критерию ты собираешься выбрать 1 из 10 картинок для одного и того же урл?

Автор: v2v 27.5.2008, 21:55

не дели на кликс + 1

Автор: Fortop 27.5.2008, 21:56

Цитата(DooZ @ 27.5.2008, 19:26

)

как написать запрос что бы возвращались уникальные картинки (1 картинка от 1-го хоста за один раз), но(!) с учетом order by ?

Если я правильно помню, то никак.
Поскольку в подзапросах ORDER BY не работает.

Автор: DooZ 27.5.2008, 22:01

я никого не держу за идиотов, я помоему достаточно внятно объяснил уже несколько раз
что нужно выбрать из базы ИД картинок
одна картинка = один урл
у одного урл может быть сотня картинок
но надо выбрать только одну отсортированную по показам (order by shows)

попробую еще раз
в базе нет уникальных элементов как таковых
есть совокупность этих самых элементов

урл, у этой урл есть картинки, у каждой картинки есть кол-во показов

надо просто выбрать ИД этих картинок, НО так что бы не было совпадающих УРЛ

т.е. одна картинка один урл

пример:
урл1 = (ид1 -> картинка1 - 0 показов, ид2 ->картинка2 - 0 показов, ид3 ->картинка3 - 0 показов)
урл2 = (ид4 ->картинка4 - 0 показов, ид5 ->картинка5 - 0 показов, ид6 ->картинка6 - 0 показов)
урл3 = (ид7 ->картинка7 - 0 показов, ид8 ->картинка8 - 0 показов, ид9 ->картинка9 - 0 показов)

в первый раз делаем выборку:
должно выдать что-то типа: ид1, ид4, ид7
далее делаем увеличение показов:
ид1 -> показы + 1
ид4 -> показы + 1
ид7 -> показы + 1

далее опять делаем выборку
и должно выдать что-то типа (вот тут внимание)
ид2, ид5, ид8
далее делаем увеличение показов:
ид2 -> показы + 1
ид5 -> показы + 1
ид8 -> показы + 1

и опять делаем выборку:
должно выдать:
ид3, ид6, ид9
опять увеличиваем показы этих ИД
ид3 -> показы + 1
ид6 -> показы + 1
ид9 -> показы + 1

и все по кругу должно выдавать
т.е. опять ид1, ид4, ид7

и т .д.

теперь понятно?

Автор: Fortop 27.5.2008, 22:02

Хотя можно попробовать реализовать что-то вроде

Код


SELECT id FROM images WHERE
shows IN (SELECT MAX(shows) FROM images GROUP BY url)

Автор: v2v 27.5.2008, 22:07

Цитата(DooZ @ 27.5.2008, 22:01

)

и т .д.

Код


SELECT id FROM images GROUP BY url ORDER BY shows ASC

пробовал ?

Автор: DooZ 27.5.2008, 22:12

INSERT INTO `images` (`id`, `url`, `thumb`, `shows`) VALUES
(1, 'http://domain1.com', 'http://domain1.com/image1.jpg', '0'),
(2, 'http://domain1.com', 'http://domain1.com/image2.jpg', '0'),
(3, 'http://domain1.com', 'http://domain1.com/image3.jpg', '0'),
(4, 'http://domain2.com', 'http://domain2.com/image1.jpg', '0'),
(5, 'http://domain2.com', 'http://domain2.com/image2.jpg', '0'),
(6, 'http://domain2.com', 'http://domain2.com/image3.jpg', '0'),
(7, 'http://domain3.com', 'http://domain3.com/image1.jpg', '0'),
(8, 'http://domain3.com', 'http://domain3.com/image2.jpg', '0'),
(9, 'http://domain3.com', 'http://domain3.com/image3.jpg', '0'),

вот данные в таблице
может быть так будет более понятно

далее делаю запрос:
select id from images group by url order by shows
выдает 1, 4, 7

(кто-то может спросит почему нельзя сделать запрос просто: select id from images order by shows) отвечу:
мне надо уникальные урл (1 урл = 1 картинка)

итак после того как выдали ИД я увеличиваю их значения shows
update images set shows=shows+1 where id=1
update images set shows=shows+1 where id=4
update images set shows=shows+1 where id=7

и снова делаю запрос:
select id from images group by url order by shows
НО мне выдает 1, 4, 7
а надо что бы выдало что-то вроде: 2, 5, 8 т.е. те ИД у которых shows меньше

есть вопросы?

Добавлено через 1 минуту и 55 секунд
2v2v - почитай последнюю мессагу
я там подробно описал что нужно, может теперь объяснил как надо smile

Добавлено через 3 минуты и 32 секунды

Цитата(Fortop @ 27.5.2008, 22:02)

Хотя можно попробовать реализовать что-то вроде

Код


SELECT id FROM images WHERE
shows IN (SELECT MAX(shows) FROM images GROUP BY url)

не катит, тоже самое выдает (если смотреть последний пример) то так же 1, 4, 7 вместо 2, 5, 8

Автор: Fortop 27.5.2008, 22:16

Код


SELECT id FROM images WHERE
shows IN (SELECT DISTINCT MIN(shows) FROM images GROUP BY url)

Автор: DooZ 27.5.2008, 22:21

Цитата(Fortop @ 27.5.2008, 22:16)

Код


SELECT id FROM images WHERE
shows IN (SELECT DISTINCT MIN(shows) FROM images GROUP BY url)

выдает
2, 3, 5, 6, 8, 9
а должно
2, 5, 8
т.к. 3, 6, 9 это дубликаты урла

Автор: Fortop 27.5.2008, 22:25

не понял дубликаты чего?
ну так и сделай еще один GROUP

Код


SELECT id FROM images WHERE
shows IN (SELECT DISTINCT MIN(shows) FROM images GROUP BY url)
GROUP BY url

Автор: DooZ 27.5.2008, 22:31

Цитата(Fortop @ 27.5.2008, 22:25)

не понял дубликаты чего?
ну так и сделай еще один GROUP

Код


SELECT id FROM images WHERE
shows IN (SELECT DISTINCT MIN(shows) FROM images GROUP BY url)
GROUP BY url

работает, ПОЧТИ верно
если обновлять все shows которые она вывела то будет выдавать как надо
НО если будет вот так как у меня сейчас:

1    http://domain1.com    http://domain1.com/image1.jpg 1
2    http://domain1.com    http://domain1.com/image2.jpg 1
3    http://domain1.com    http://domain1.com/image3.jpg 1
4    http://domain2.com    http://domain2.com/image1.jpg 1
5    http://domain2.com    http://domain2.com/image2.jpg 1
6    http://domain2.com    http://domain2.com/image3.jpg 1
7    http://domain3.com    http://domain3.com/image1.jpg 1
8    http://domain3.com    http://domain3.com/image2.jpg 1
9    http://domain3.com    http://domain3.com/image3.jpg 0
т.е. все кроме 9-го ИД имеют shows равный 1

при запросе вида:
SELECT id FROM images WHERE
shows IN (SELECT DISTINCT MIN(shows) FROM images GROUP BY url)
GROUP BY url
выдает
1, 4, 7
а должно поидее выдать
1, 4, 9 (так как ИД-9 имеет меньше shows чем 7)

есть идеи?

Добавлено через 1 минуту и 6 секунд
а вообще если кто-то действительно может помочь, то не поленитесь создайте табличку в мискуле как я вверху написал заполните ее и протестируйте

очень надо решить эту задачу...

Автор: Fortop 27.5.2008, 22:33

Сделай отдельно на той таблице, что ты привел только что

Код


SELECT DISTINCT url, MIN(shows) FROM images GROUP BY url

и проверь, действительно ли у твоего
http://domain3.com есть 0 shows

Добавлено через 2 минуты и 25 секунд
А еще лучше вот так

Код


SELECT id FROM images
JOIN (SELECT DISTINCT url, MIN(shows) AS minshow FROM images GROUP BY url)  AS S
ON S.minshow = images.shows AND S.url = images.url

Автор: DooZ 27.5.2008, 22:39

Цитата(Fortop @ 27.5.2008, 22:33)

Сделай отдельно на той таблице, что ты привел только что

Код


SELECT DISTINCT url, MIN(shows) FROM images GROUP BY url

и проверь, действительно ли у твоего
http://domain3.com есть 0 shows

Добавлено @ 22:35
А еще лучше вот так

Код


SELECT id FROM images
JOIN (SELECT DISTINCT url, MIN(shows) AS minshow FROM images GROUP BY url)  AS S
ON S.minshow = images.shows AND S.url = images.url

абсолютно точно ИД9 имеет shows = 0
я в таблице работаю и выдаю именно оттуда инфу

вот экспорт если есть сомнения (таблица вместо images, называется galleries, но это не имеет значние)

Код


CREATE TABLE IF NOT EXISTS `galleries` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `url` varchar(255) NOT NULL,
  `thumb_url` varchar(255) NOT NULL,
  `shows` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

INSERT INTO `galleries` (`id`, `url`, `thumb_url`, `shows`) VALUES
(1, 'http://domain1.com', 'http://domain1.com/image1.jpg', 1),
(2, 'http://domain1.com', 'http://domain1.com/image2.jpg', 1),
(3, 'http://domain1.com', 'http://domain1.com/image3.jpg', 1),
(4, 'http://domain2.com', 'http://domain2.com/image1.jpg', 1),
(5, 'http://domain2.com', 'http://domain2.com/image2.jpg', 1),
(6, 'http://domain2.com', 'http://domain2.com/image3.jpg', 1),
(7, 'http://domain3.com', 'http://domain3.com/image1.jpg', 1),
(8, 'http://domain3.com', 'http://domain3.com/image2.jpg', 1),
(9, 'http://domain3.com', 'http://domain3.com/image3.jpg', 0);

Добавлено через 3 минуты и 8 секунд
на предположениях долго будем выяснять как сделать smile

надо тестить на таблице, последний пример:
SELECT id FROM galleries
JOIN (SELECT DISTINCT url, MIN(shows) AS minshow FROM galleries GROUP BY url) AS S
ON S.minshow = galleries.shows AND S.url = galleries.url

выводит:
1, 2, 3, 4, 5, 6, 9
а надо просто:
1, 4, 9 в данном случае (т.к. у ИД9 shows = 0)

Автор: v2v 27.5.2008, 22:43

Цитата(DooZ @ 27.5.2008, 22:39

)

SELECT DISTINCT url, MIN(shows) AS minshow FROM images GROUP BY url

вот этот подзапрос вернёт не правильные url -ы

Автор: DooZ 27.5.2008, 22:48

Цитата(v2v @ 27.5.2008, 22:43)

Цитата(DooZ @ 27.5.2008, 22:39

)

SELECT DISTINCT url, MIN(shows) AS minshow FROM images GROUP BY url

вот этот подзапрос вернёт не правильные url -ы

написал бы лучше как правильно сделать запрос...

Автор: Fortop 27.5.2008, 22:49

Код


SELECT id, S.url, S.minshow FROM galleries
JOIN (SELECT DISTINCT url, MIN(shows) AS minshow FROM galleries GROUP BY url)  AS S
ON S.minshow = galleries.shows AND S.url = galleries.url
GROUP BY url;

Добавлено через 1 минуту и 19 секунд

Цитата(v2v @ 27.5.2008, 22:43

)

вот этот подзапрос вернёт не правильные url -ы

Что значит неправильные?

Автор: v2v 27.5.2008, 22:52

Цитата(Fortop @ 27.5.2008, 22:49

)

Что значит неправильные?

те которые первые в списке тоесть соответствуют ид 1,4,7 не смотря на то , что ты выбрал минимальное количество показов.

Автор: DooZ 27.5.2008, 22:54

Цитата(Fortop @ 27.5.2008, 22:49)

Код


SELECT id, S.url, S.minshow FROM galleries
JOIN (SELECT DISTINCT url, MIN(shows) AS minshow FROM galleries GROUP BY url)  AS S
ON S.minshow = galleries.shows AND S.url = galleries.url
GROUP BY url;

Добавлено @ 22:51

Цитата(v2v @ 27.5.2008, 22:43

)

вот этот подзапрос вернёт не правильные url -ы

Что значит неправильные?

проверил, работает как надо
огромное спасибо за помощь smile

побольше бы таких профессиональных людей!!!

останется теперь протестировать при нагрузке скажем в 100к урл, но это уже позже smile

Автор: v2v 27.5.2008, 22:55

можно попробовать что то вроде

Код


SELECT * from images where shows not in (select max(shows) from images group by url) group by url

Автор: Fortop 27.5.2008, 22:57

Цитата(v2v @ 27.5.2008, 22:52

)

Он нам нужен только для того чтобы выбрать минимальное количество показов для конкретного урл.
Дальше мы его джойним с основной таблицей и группируем по урл.

Если будет несколько id с минимальным значением - то серевер выберет первый попавшийся из данного урл.

Добавлено через 2 минуты

Цитата(v2v @ 27.5.2008, 22:55

)

not in (select max(shows).....

ты просто реверсировал запрос

Цитата(Fortop @ 27.5.2008, 22:16

)

IN (SELECT DISTINCT MIN(shows)

Автор: DooZ 29.5.2008, 00:44

протестировал я скорость
печальные данные
на базе всего в 1000 галерей, скорость со всеми индексами выдала 0.04 на запрос, что очень медленно
придется искать другой вариант сортировки и выбора ИД

Автор: Fortop 29.5.2008, 02:47

DooZ,
сделай EXPLAIN, чтобы посмотреть, что долго кушает.
Плюс можно подумать над вопросом заменить IN на HAVING