Среднее количество сообщений в час на 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

Это выводит следующие данные:

Я могу отредактировать этот запрос, чтобы получить любой день, который я хочу. Но то, что я хочу сейчас, это СРЕДНИЕ каждого часа каждого дня, так что если на 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

Это дает вам количество всех сообщений, разделенных на количество дней, по часам.