每小时在MySQL上的平均帖子数? [英] Average posts per hour on MySQL?

问题描述

我在MySQL的InnoDB表中保存了许多帖子.该表具有列"id"，日期"，用户"，内容".我想制作一些统计图，所以最终使用以下查询来获取昨天每小时的帖子数量:

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

这将输出以下数据:

我可以编辑此查询以获取所需的任何一天.但是，我现在想要的是每天每一小时的平均数，因此，如果在00小时的第一天，我有20个帖子，而在00小时的第二天，我有40个帖子，我希望输出是"30".如果可能的话，我也希望能够选择日期段.

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.

提前谢谢！

推荐答案

您可以使用子查询按天/小时对数据进行分组，然后在子查询中按小时取平均值.

You can use a sub-query to group the data by day/hour, then take the average by hour across the sub-query.

下面是一个示例，可以为您提供过去7天的小时平均计数:

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

这篇关于每小时在MySQL上的平均帖子数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！