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

выборка по дате с разницей в 10 мин

clevel

clevel

Новичок
выборка по дате с разницей в 10 мин

есть таблица statistic:
user(int) | when(timestamp(14))| page(int)
------------------------------------------------------
1 | 111111111111 | 53
------------------------------------------------------

надо сделать выборку уникальных page при условии, что в выборку попадают page, у которых when с ближайшим меньшим when составляет разницу в 10 мин, при этом идет акцент на user (для каждого юзера свой разрыв в 10 мин, нет общего потока).
Уже полдня бьюсь, никак не могу придумать стоящего запроса.
Помогите, плз.
 

Апельсин

Оранжевое создание
хм ..
думаю что одним запросом ты тут не обойдешься.

прервое что приходит в голову - это временная таблица, куда заливаются данные с ORDER BY `when`
тогда проще найти разницу с "ближайщим меньшим", но придется использовать variables

кстати, тебе надо "ближайшее меньшее" с равным page или все равно каким?
 
clevel

clevel

Новичок
кстати, тебе надо "ближайшее меньшее" с равным page или все равно каким?
Нажмите для раскрытия...
все равно...


можно еще раз по-русски рассказать ?
Нажмите для раскрытия...
задача: найти точки входа постоянных посетителей, т.е. с интервалом просмотра страниц в 10 мин. Если больше 10 мин интервал, то эта страница - новая точка входа...
Что надо вывести - номера страниц - точек входа, отсортированные в порядке убывания кол-ва повторов COUNT(page)
Что думается... есть ли возможность хранить в переменной предыдущее значение ячейки when?
 

Апельсин

Оранжевое создание
> Что думается... есть ли возможность хранить в переменной предыдущее значение ячейки when?

возможность то есть, только вот что это будет за предыдущее ;)

у тебя ведь получается, что для каждого when тебе надо предыдущее _по_времени_ а не по расположению в таблице.
Т.е. если у тебя таблица не отсортирована по when, то тебе придется искать запись, который будет предыдущей по времени для текущего when, а затем уже делать все остальное.

Другой вариант - выбирать во временную таблицу в отсортированном порядке, тогда у тебя предыдущее _по_расположению_в_таблице и предыдущее _по_времени_ будут совпадать.
 

su1d

Старожил PHPClubа
я не знаю сколько часов будет выполняться такой запрос, но навскидку пришло на ум вот такое:

SELECT t1.page FROM tbl t1
INNER JOIN tbl t2 ON t2.usr=t1.usr AND t1.time - t2.time > 600
LEFT JOIN tbl t3 ON t3.time BETWEEN t2.time AND t1.time
WHERE t1.usr = $USER AND t3.time IS NULL
GROUP BY t1.page

возможно, придётся его продебажить и добавить счётчик страниц.

главное здесь - идея выборки соседних элементов между которыми разница в 10 минут.
 

chira

Новичок
Вот попробуй:

SELECT COUNT(DISTINCT t1.page) FROM tbl t1
LEFT JOIN tbl t2 ON t2.usr=t1.usr AND t1.time - t2.time <= 600
WHERE t2.usr IS NULL
GROUP BY t1.user
 

chira

Новичок
может еще одно условие добавить
LEFT JOIN tbl t2 ON t2.usr=t1.usr AND t1.time - t2.time <= 600
AND t1.time > t2.time
 
clevel

clevel

Новичок
SELECT COUNT(DISTINCT t1.page) FROM statistic t1
LEFT JOIN statistic t2 ON t2.user=t1.user AND t1.when-t2.when <= 600
WHERE t2.user IS NULL
GROUP BY t1.user
Нажмите для раскрытия...
Думало секунд 13-14, потом выдала пустой результат, хотя если выводить таблицу в массив пхп и ручками находить, то должно выдавать 3-4 номера страницы с количеством повторов.
 
clevel

clevel

Новичок
может еще одно условие добавить
LEFT JOIN tbl t2 ON t2.usr=t1.usr AND t1.time - t2.time <= 600
AND t1.time > t2.time
Нажмите для раскрытия...
вообще левые данные выводит... многие результаты - для них всего одна запись в БД есть, а они выводятся под маской разницы в 10 мин между НЕСКОЛЬКИМИ записями....
 

Апельсин

Оранжевое создание
clevel
ты можешь привести пример тестовой таблички (лучше ее дамп ;) ), т.е. какие записи там есть и что ты хочешь получить. Результат не только словами, но и на примере.
 

chira

Новичок
еще попытка
не знаю на сколько можно применять этот запрос на практике ....
работать он будет долго
ради спортивного интереса и может чего полезного для себя увидишь:

SELECT COUNT(DISTINCT t1.page)
,t1.user,t1.when
FROM statistic t1
LEFT JOIN statistic t2 ON t2.user=t1.user AND t1.when < t2.when - 600
WHERE t2.user IS NOT NULL
GROUP BY t1.user,t1.when
HAVING t1.when < MIN(t2.when) - 600
 

chira

Новичок
во блин, только что внешний вид форума поменялся
по живому upgrade делают ....
 

Апельсин

Оранжевое создание
chira, не вижу глубокого смысла в этом:

HAVING t1.when < MIN(t2.when) - 600

ошибку ты не получишь, но работать эта конструкция нормально не будет, потому как sub-select'ов MySQL еще не поддерживает.
 

chira

Новичок
Это не subselect
HAVING t1.when < MIN(t2.when) - 600

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

clevel

Новичок
таблица statistic:

owner(int)| user(int) | page(int) | when(timestamp(14))
----------------------------------------------------------------------
2 | 1 | 11 | 20000108015340
----------------------------------------------------------------------
3 | 41 | 15 | 20000109015340
----------------------------------------------------------------------
2 | 1 | 12 | 20000108015345
----------------------------------------------------------------------
2 | 1 | 11 | 20000109015340
----------------------------------------------------------------------
2 | 1 | 11 | 20000110015340
----------------------------------------------------------------------
2 | 2 | 11 | 20000111015340
----------------------------------------------------------------------
2 | 2 | 11 | 20000120015340
----------------------------------------------------------------------



Выборку я делаю по owner=2, найти номера page и Count(page), где where по user>=10 мин
Результат:
page | count(page)
------------------------
11 | 4
------------------------
то есть при выборке >=10 мин мы учитываем для каждого user, но в итоге кол-во для каждой страницы - сумма count(page) всех юзеров. В принципе, можно выводить user,page,count(page) из расчета на каждого юзера...

Дополнительный вопрос: из базы когда выбираю when, пхп определеяет тип данных как строку и когда я пытаюсь прибавить к этому значению число, выдает непонятное число с экспонентой( gettype-> double). Как мне привести данную строку-число к простому числу? (для последующего проведения арифметических операций). Пробовал (int) перед переменной, settype(), $next=$var+600, ничего не помогает, левые результаты выдает:confused:
 

su1d

Старожил PHPClubа
мОлодежь, блин... =(
всё вам разжевать надо и в рот положить.
ведь дал уже идею, там чуть-чуть додумать, найти глюк в MySQL (или phpMyAdmin, я не проверял), и всё готово.

Запрос:

SELECT
t1.user AS user1, t1.page AS page1, t1.hit AS hit1
FROM pages t1
LEFT JOIN pages t2 ON t2.page = t1.page AND t1.hit - t2.hit > 600
LEFT JOIN pages t3 ON t3.page = t1.page AND t3.hit > t2.hit AND t3.hit < t1.hit
WHERE t1.user = 2 AND t3.hit IS NULL

выведет все входные страницы для юзера 2.

Таблица:

CREATE TABLE pages (
user int(10) unsigned NOT NULL default '0',
page int(10) unsigned NOT NULL default '0',
hit int(10) unsigned NOT NULL default '0',
KEY user (user,page,hit)
) TYPE=MyISAM;

Данные:

INSERT INTO pages VALUES (2, 1, 100);
INSERT INTO pages VALUES (1, 2, 120);
INSERT INTO pages VALUES (2, 1, 800);
INSERT INTO pages VALUES (2, 1, 850);
INSERT INTO pages VALUES (2, 1, 900);
INSERT INTO pages VALUES (1, 2, 1500);
INSERT INTO pages VALUES (2, 1, 10000);
 
clevel

clevel

Новичок
su1d
Нажмите для раскрытия...
по-моему твой вариант расходится с моей задачей:
у меня есть время, номер страницы и пользователь, который на эту страницу зашел. Надо найти, например, 10 страниц входа (определяются как страницы, на которые пользователь, ранее уже заходивший, и имеющий уже встречающийся user, зашел более чем через 10 мин с последнего захода), имеющих самое большое кол-во вхождений, суммарно, для всех пользователей.
 

su1d

Старожил PHPClubа
мой вариант - это лишь proof of concept.
подумаешь маленько сам, внесёшь пару-тройку лишних строчек в запрос, и получишь своё решение
 
Войдите или зарегистрируйтесь для ответа.
Сверху