Average posts per hour on MySQL?
I have a number of posts saved into a InnoDB table on MySQL. The table has the columns “id”, “date”, “user”, “content”. I wanted to make some statistic graphs, so I ended up using the following query to get the amount of posts per hour of yesterday:
SELECT HOUR(FROM_UNIXTIME(`date`)) AS `hour`, COUNT(date) from fb_posts
WHERE DATE(FROM_UNIXTIME(`date`)) = CURDATE() – INTERVAL 1 DAY GROUP BY hour
This outputs the following data:
I can edit this query to get any day I want. But what I want now is the AVERAGE of each hour of every day, so that if on Day 1 at 00 hours I have 20 posts and on Day 2 at 00 hours I have 40, I want the output to be “30”. I’d like to be able to pick date periods as well if it’s possible.
Thanks in advance!
You can use a sub-query to group the data by day/hour, then take the average by hour across the sub-query.
Here’s an example to give you the average count by hour for the past 7 days:
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
Aggregate the information by date and hour, and then take the average by hour:
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
By the way, I prefer including the explicit order by, since most databases do not order the results of a group by. Mysql happens to be one database that does.
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
This gives you a count of all the posts, divided by the number of days, by hour.
- Database Administration Tutorials
- Programming Tutorials & IT News
- Linux & DevOps World
- Ebook Reviews
- PES Matches, Skills & News