Среднее количество сообщений в час на MySQL?
У меня есть несколько сообщений, сохраненных в таблице InnoDB в MySQL. В таблице указаны столбцы "id", "date", "user", "content". Я хотел сделать некоторые статистические графики, поэтому я получил следующий запрос, чтобы получить количество сообщений в час вчера:
SELECT HOUR(FROM_UNIXTIME(`date`)) AS `hour`, COUNT(date) from fb_posts
WHERE DATE(FROM_UNIXTIME(`date`)) = CURDATE() - INTERVAL 1 DAY GROUP BY hour
Это выводит следующие данные:
![table data]()
Я могу отредактировать этот запрос, чтобы получить любой день, который я хочу. Но то, что я хочу сейчас, это СРЕДНИЕ каждого часа каждого дня, так что если на 1-й день в 00 часов у меня 20 сообщений, а на 2-й день в 00 часов у меня 40, я хочу, чтобы выход был "30". Я хотел бы иметь возможность выбирать даты, если это возможно.
Спасибо заранее!
Ответы
Ответ 1
Вы можете использовать подзапрос, чтобы группировать данные по дням/часам, а затем принимать среднее значение по часам по второму запросу.
Вот пример, чтобы дать вам среднее количество часов в течение последних 7 дней:
select the_hour,avg(the_count)
from
(
select date(from_unixtime(`date`)) as the_day,
hour(from_unixtime(`date`)) as the_hour,
count(*) as the_count
from fb_posts
where `date` >= unix_timestamp(current_date() - interval 7 day)
and created_on < unix_timestamp(current_date())
group by the_day,the_hour
) s
group by the_hour
Ответ 2
Совокупность информации по дате и часу, а затем среднее значение по часам:
select hour, avg(numposts)
from (SELECT date(`date`) as day, HOUR(FROM_UNIXTIME(`date`)) AS `hour`,
count(*) as numposts
from fb_posts
WHERE DATE(FROM_UNIXTIME(`date`)) between <date1> and <date2>
GROUP BY date(`date`), hour
) d
group by hour
order by 1
Кстати, я предпочитаю включать явный порядок, так как большинство баз данных не упорядочивает результаты группы. Mysql - это одна база данных, которая делает это.
Ответ 3
SELECT
HOUR(FROM_UNIXTIME(`date`)) AS `hour`
, COUNT(`id`) \ COUNT(DISTINCT TO_DAYS(`date`)) AS avgHourlyPostCount
FROM fb_posts
WHERE `date` > '2012-01-01' -- your optional date criteria
GROUP BY hour
Это дает вам количество всех сообщений, разделенных на количество дней, по часам.