Как выбрать со сравнением по времени в ограниченном диапазоне?

Question

[Алекс Иванов]

Помогите советом. Есть таблица постов на сайте, и надо найти 10 участников, которые оставили больше всех сообщений за 60 минут с сохранением id этих постов.
Т.е. кто-то может постить по 100 сообщений в день, через каждых 15-20 минут, а кто-то постит только 50 но на протяжении 60 минут. Вот мне надо из всех кто есть в таблице найти 10 тех, кто оставил наиболее много постов за час,

Таблица "posts" имеет поля
usrid | data | post | postid

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

Answer 1

[Rsa97]

Если за конкретный интервал времени, то

SELECT `t1`.`usrid`, `t2`.`data`, `t2`.`postid`
    FROM (
        SELECT `usrid`, COUNT(*) AS `cnt`
            FROM `posts` 
            WHERE `data` BETWEEN :start AND :end 
            GROUP BY `usrid`
            ORDER BY `cnt` DESC
            LIMIT 10
    ) AS `t1`
    JOIN `posts` AS `t2` USING(`usrid`)
    WHERE `t2`.`data` BETWEEN :start AND :end
    ORDER BY `t1`.`cnt`, `t1`.`usrid`, `t2`.`data`

Вариант с плавающим окном в комментарии

Comments

[Алекс Иванов]

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

[Rsa97]

Protossan: Ну почему, можно сделать и запрос с плавающим окном

SELECT * 
    FROM (
        SELECT `p1`.`usrid` AS `usrid`, COUNT(*) AS `cnt`,
               `p1`.`data` AS `data`
            FROM `posts` AS `p1`
            JOIN `posts` AS `p2` ON `p1`.`usrid` = `p2`.`usrid`
                AND `p2`.`data` BETWEEN `p1`.`data` 
                                AND `p1`.`data`+INTERVAL 1 HOUR
            GROUP BY `p1`.`postid`
            ORDER BY `cnt` DESC
            LIMIT 10
    ) AS `t1`
    JOIN `posts` AS `t2` ON `t1`.`usrid` = `t2`.`usrid`
        AND `t2`.`data` BETWEEN `t1`.`data` 
                        AND `t1`.`data`+INTERVAL 1 HOUR
    ORDER BY `t1`.`cnt`, `t1`.`usrid`, `t2`.`data`

Но тут есть неприятность. Если, скажем, окно в 1 час, а кто-то два часа постил раз в минуту, то получится, что с 00:00 по 01:00 у него 60 постов, с 00:01 по 01:01 60 постов,... с 00:59 по 01:59 - тоже 60 постов.

[Алекс Иванов]

Спасибо большое, эта неприятность явно будет иметь место, поскольку так постить точно никто не будет! Результат пока сказать не могу, поскольку сервер выдает 504 ошибку, надо запускать с командной строки. Можете изменить вышестоящий код, чтоб я отметил его как решение?

[Алекс Иванов]

Rsa97: Не могу выполнить запрос потому что тупо ложиться сервер :( слишком большая таблица. Можно ли как-то облегчить его если сделать перебор по одному юзеру? Например сначала выбирать максимально число постов за 1 час за все время постинга для юзера usrid=5 - т.е. usrid я буду через скрипт перебором делать?

Делаю вот так

SELECT * 
    FROM (
        SELECT `p1`.`usrid` AS `usrid`, COUNT(*) AS `cnt`,
               `p1`.`data` AS `data`
            FROM `posts` AS `p1`
            JOIN `posts` AS `p2` ON `p1`.`usrid` = `p2`.`usrid`
                AND `p2`.`data` BETWEEN `p1`.`data` 
                                AND `p1`.`data`+INTERVAL 1 HOUR
            GROUP BY `p1`.`postid`
            ORDER BY `cnt` DESC
            LIMIT 10
    ) AS `t1`
    JOIN `posts` AS `t2` ON `t1`.`usrid` = `t2`.`usrid` AND  `t1`.`usrid` = 5 
        AND `t2`.`data` BETWEEN `t1`.`data` 
                        AND `t1`.`data`+INTERVAL 1 HOUR
    ORDER BY `t1`.`cnt`, `t1`.`usrid`, `t2`.`data`

Но результат тот -же - не могу дождаться ответа от сервера

[Алекс Иванов]

А если вот так:

SELECT *
FROM (
SELECT `p1`.`usrid` AS `usrid`, COUNT(*) AS `cnt`,
`p1`.`data` AS `data`
FROM `posts` AS `p1`
JOIN `posts` AS `p2` ON `p1`.`usrid` = `p2`.`usrid` AND `p1`.`usrid` =5
AND `p2`.`data` BETWEEN `p1`.`data`
AND `p1`.`data`+INTERVAL 1 HOUR
GROUP BY `p1`.`postid`
ORDER BY `cnt` DESC
LIMIT 10
) AS `t1`
JOIN `posts` AS `t2` ON `t1`.`usrid` = `t2`.`usrid` AND `t1`.`usrid` = 5
AND `t2`.`data` BETWEEN `t1`.`data`
AND `t1`.`data`+INTERVAL 1 HOUR
ORDER BY `t1`.`cnt`, `t1`.`usrid`, `t2`.`data`

То вообще пустой результат

[Rsa97]

Protossan: Попробуйте выполнить сначала первую часть запроса (в скобках), а потом уже отдельно выбрать нужные посты исходя из полученной таблицы.

[Rsa97]

Protossan: Можно и по отдельному пользователю, тогда во внутреннюю часть запроса добавляете WHERE `p1`.`usrid` = :uid и ставите LIMIT 1

[Алекс Иванов]

SELECT `p1`.`usrid` AS `usrid`, COUNT(*) AS `cnt`,
`p1`.`data` AS `data`
FROM `posts` AS `p1`
JOIN `posts` AS `p2` ON `p1`.`usrid` = `p2`.`usrid` AND `p1`.`usrid` =5
AND `p2`.`data` BETWEEN `p1`.`data`
AND `p1`.`data`+INTERVAL 1 HOUR
WHERE `p1`.`usrid` = 5 
GROUP BY `p1`.`postid`

И получился непонятный результат

usrid cnt data
5 1104 1302280218
5 1103 1302280335
5 1102 1302280835

При том что у юзера всего 1108 постов.

[Rsa97]

Protossan: А дата у вас в каком формате хранится? Запрос расcчитан на DATETIME или TIMESTAMP

[Алекс Иванов]

в Юникс формате 1302280218

[Rsa97]

Protossan: Тогда меняйте +INTERVAL 1 HOUR на +3600

[Алекс Иванов]

Да, первая часть сработал как положено. Спасибо!

SELECT `p1`.`usrid` AS `usrid`, COUNT(*) AS `cnt`,
`p1`.`data` AS `data`
FROM `posts` AS `p1`
JOIN `posts` AS `p2` ON `p1`.`usrid` = `p2`.`usrid` AND `p1`.`usrid` =5
AND `p2`.`data` BETWEEN `p1`.`data`
AND `p1`.`data`+3600
WHERE `p1`.`usrid` = 5
GROUP BY `p1`.`posts`
ORDER BY `cnt` DESC
LIMIT 1

А как бы увидеть какие именно посты попали в это число "максимального постинга" ?

[Rsa97]

Protossan: Ну либо попробовать снова полный запрос, использовав эту часть как подзапрос, либо просто взять все запросы этого пользователя с полученного момента и до +3600

[Алекс Иванов]

Rsa97: Большое спасибо! Вы мне очень помогли с решением вопроса!

Answer 2

[Робот]

Смотря в каком виде там время, если datetime или timestamp то:

// Начало часа
$N1 = date("2015-07-26 14:00");
// Конец часа
$N2 = date("2015-07-26 15:00");

//Теперь простой запрос с BEETWEN
SELECT `column_name`
FROM `table_name`
WHERE `time` BETWEEN $N1 AND $N2

Ну по sql-запросу с переменными псевдокод, встройте их в строку правильно только ))