Составление запрос, тяжелая структура.

Yaponchick · 4 Авг 2006

Составление запрос, тяжелая структура.

Код:

mysql> DESC `sentences`;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI |         | auto_increment |
| sentence | varchar(255) | NO   |     |         |                |
+----------+--------------+------+-----+---------+----------------+

mysql> DESC `response`;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI |         | auto_increment |
| server_name | varchar(20) | NO   |     |         |                |
| site_id     | int(11)     | NO   |     |         |                |
| time        | int(11)     | NO   |     |         |                |
| word_id     | int(11)     | NO   |     |         |                |
| page        | int(11)     | NO   |     |         |                |
| place       | int(11)     | NO   |     |         |                |
+-------------+-------------+------+-----+---------+----------------+

mysql> DESC `sites`;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI |         | auto_increment |
| name  | varchar(128) | NO   |     |         |                |
+-------+--------------+------+-----+---------+----------------+

Задача:
Искать в поисковиках определенные сайты(таблица sites) + определять позиции(response.page, response.place) по ключевым слова(таблица sentences).

Проблема, не могу составить запрос для выборки ключевого слова + сайта, который еще не проиндексирован или разница во времени `time` <= UNIX_TIMESTAMP() - 86400.

Более коротко: "как получить `name` слово из sentences выбрать `name` из sites, при этом они не должны уже быть в `response` или могут там быть но разница во времени 86400(24 hours)"

Bitterman · 4 Авг 2006

Что значит "сайт не проиндексирован"? Нет записи в response?
Как связаны тогда таблицы sentences и sites, кроме как через response?

Yaponchick · 4 Авг 2006

Окей, индексировать надо:
когда пара site.name && sentences.sentence не найдена в response
когда пара site.name && sentences.sentence найдена в респонзе и time у нее < 86400

Связь только через респонсе, т.к. тут получается что sentences для каждого sites.name одинаковы, т.к. если 10000 sentences, а sites.name 5, то после полного перебора в response будет вставлено 50000 строк, если до этого она была пуста, или предыдущие запросы был сделаны от 24 часов назад.

Bitterman · 4 Авг 2006

Запутал окончательно. Объясни тогда механизм поиска ключевого слова + сайта если сайт не проиндексирован? Совпадение sites.name и sentences.sentence? По твоему второму посту не похоже. Тогда как?

Yaponchick · 4 Авг 2006

связь через response(word_id, site_id)

Нужно получить `sentences`.`sentence` и `sites`.`name` которых нету в response или `response`.`time` для этой пары больше 86400

Clubber · 4 Авг 2006

ну на ум приходит подобный запрос:

from sentences left join response (по word_id) right join sites (по site_id) where response.id is null or response time < ...

но я не уверен, что оно действительно разрулит join-ы так, как я и задумал.

Yaponchick · 4 Авг 2006

Еще 1 просьба, дайте ссылку на описание (LEFT|RIGHT|INNER|&etc.) JOIN т.е. в чем отличия.(язык любой)

PHP:

SELECT * FROM sentences LEFT JOIN response ON (sentences.id = response.word_id)  RIGHT JOIN sites ON (response.site_id = sites.id) WHERE response.id IS NULL OR response.time <= (UNIX_TIMESTAMP() - 86400);

Работает, но не выбирает записи которые еще не были ниразу проверены(еще не разу не прописаны в `response`);

т.е.:
в sites:

Код:

ID | name
1 | phpclub.ru

в sentences:

Код:

ID | sentence
1 | php xml xsly
2 | inner join

в response:

Код:

ID | server_name | site_id | time | word_id | page | place
1 | yandex | 1 | 100 | 1 | 3 | 35

после запросы выведет:

Код:

1 | php xml xsly | 1 | yandex | 1 | 100 | 1 | 3 | 35 | 1 | phpclub.ru

а надо:

Код:

sentences.sentence | site.name | response.time
php xml xsly | phpclub.ru | 100 ## эта выборка потому что response.time меньше time() на 86400 или более
inner join     | phpclub.ru | (тут что угодно) ## эта выборка потомучто она еще не описана в response

Clubber · 4 Авг 2006

http://dev.mysql.com/doc/refman/5.1/en/join.html

Bitterman · 4 Авг 2006

Я бы скорее сделал так:
SELECT sn.sentence, s.name FROM sentences as sn, sites as s
-- Это даст все возможные пары слово-сайт. Дальше отсеиваем.
LEFT JOIN response ON (word_id...) and (site_id...)
WHERE response.id is null or response.time < ...

Сразу предупреждаю - не проверял. Но по идее должно быть похоже.

Clubber · 4 Авг 2006

проверил на тестовых табличках. наверное тогда такой подойдет:

select * from
sites
inner join sentences on (true)
left join response on (sentences.id = response.word_id and response.site_id = sites.id)
where response.id IS NULL OR response.time <= (UNIX_TIMESTAMP() - 86400);

-~{}~ 04.08.06 16:47:

Bitterman
опередил >:]

Yaponchick · 4 Авг 2006

Bitterman
Clubber
Спасибо большое, работает, если хотите кудайте кошельки в личку, вознагражу =)

Clubber · 4 Авг 2006

спасибо, не надо.
пиво в холодильнике уже имеется

Yaponchick · 7 Авг 2006

Всплыла дырка.

Допустим в `response`, если 10 одинаковых записей, отличающиеся только по `time` и `id`, допустим ихние ид: «1,2,3,4,5,7,12,34, 55, 60»
у ид 60, time различается в 10 секунд, у 55 в 108, у 34 в 8640
у всех других различие > 86400, получается, что при выборке, сюда попадут 1,2,3,4,5,7,12 строки, а этио есть плохо.

Помогите доделать запрос, чтобы выбиралась последняя добавленая запись с идентичными данными кроме `id`, `time`

т.е. response.time <= (UNIX_TIMESTAMP() - 86400) должно проверяется для последней записи

Bitterman · 7 Авг 2006

С самого начала хотел спросить - а зачем тебе старые записи. Перезаписывай их и все - всех проблем-то.

Yaponchick · 7 Авг 2006

Нужна полная статистика, этот скрипт берет через yandex.xml позиции сайтов, и сохраняет, нужно вести статистику «кто, когда, на какой странице, какая позиция»

Bitterman · 7 Авг 2006

Ну добавь тогда в SELECT MAX(r.time), а в условие - GROUP BY r.time. Как-то так. А вообще доработать запрос можно уже и самому - не такая уж проблема, ИМХО.

Yaponchick · 7 Авг 2006

Bitterman
Что-то не получается =(

P.S. я настолько запутался, что для меня простые вещи уже трудно.

Bitterman · 7 Авг 2006

Что именно не получается?

Yaponchick · 7 Авг 2006

Код:

SELECT 
	`sentences`.`id`,
	`sentences`.`sentence`,
	`sites`.`id`,
	`sites`.`name`,
	MAX(`response`.`time`) as 'rtime'
FROM
	`sites`
INNER JOIN
	`sentences`
		ON (TRUE)
LEFT JOIN
	`response`
		ON (
			`sentences`.`id` = `response`.`word_id`
				AND
			`response`.`site_id` = `sites`.`id`
		)
LEFT JOIN
	`checking`
		ON(
			`sentences`.`id` = `checking`.`sentence_id`
		)
WHERE (
	`response`.`id` IS NULL OR 
	`rtime` < (UNIX_TIMESTAMP() - 86400)
)
		AND
	`checking`.`id` IS NULL;

пишет что `rtime` undefined.

Bitterman · 7 Авг 2006

Посмотри в мануале, я не уверен, что в условии WHERE можно писать алиасы полей, по-моему это допустимо только в ORDER BY. Кроме того, где у тебя GROUP BY, о котором я писал? Ты пробовал его поставить?

Составление запрос, тяжелая структура.

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок