mysql - 上一年每个用户每月的平均帖子- MYSQL

Question

用户表:

+------------+---------+---------------------+|    id      |  name   |created_at           |+------------+---------+---------------------+| 1          | AAA     | 2013-01-10 01:30:00 |             | 2          | BBB     | 2013-02-14 01:30:00 || 3          | CCC     | 2013-03-29 01:30:00 || 4          | DDD     | 2013-04-30 01:30:00 || 5          | EEE     | 2013-05-10 01:30:00 || 6          | FFF     | 2013-06-10 01:30:00 || 7          | GGG     | 2013-07-10 01:30:00 || 8          | IIS     | 2013-08-10 01:30:00 || 9          | IIE     | 2013-09-10 01:30:00 || 10         | IIF     | 2013-10-10 01:30:00 || 11         | IIG     | 2013-11-10 01:30:00 || 12         | IIH     | 2013-12-18 01:30:00 || 13         | IHH     | 2013-10-18 01:30:00 || 14         | AHH     | 2013-02-18 01:30:00 || 15         | AEH     | 2013-02-18 01:30:00 |+------------+---------+---------------------+

帖子表:

+------------+-------------------+---------------------+|    id      |  user_id          | created_at          || 1          | 1                 | 2013-02-10 01:30:00 || 2          | 2                 | 2013-03-10 01:30:00 || 3          | 2                 | 2013-03-10 01:30:00 || 4          | 7                 | 2013-04-10 01:30:00 || 5          | 1                 | 2013-05-10 01:30:00 || 6          | 8                 | 2013-05-10 01:30:00 || 7          | 3                 | 2013-06-10 01:30:00 || 8          | 6                 | 2013-07-10 01:30:00 || 9          | 7                 | 2013-08-10 01:30:00 || 10         | 9                 | 2013-09-10 01:30:00 || 11         | 11                | 2013-10-10 01:30:00 || 12         | 4                 | 2013-11-10 01:30:00 |+------------+-------------------+---------------------+

从上表中，我如何找出上一年每个用户按月的平均帖子。请注意:一月和十二月没有职位。因此，那些月份的平均值将为 0。

临时表(不存在，仅供示例):每月发帖数和累计用户数:

上一年的每月总用户数(累计):

+--------------+-------------------+|    month     |  total_users(cumulative)      | Jan          | 1                 || Feb          | 4                 || Mar          | 5                 || Apr          | 6                 || May          | 7                 || Jun          | 8                 || Jul          | 9                 || Aug          | 10                || Sep          | 11                || Oct          | 13                || Nov          | 14                || Dec          | 15                |+--------------+-------------------+

每月 total_posts(上一年):

+--------------+-------------------+|    month     |  total_posts      || Jan          | 0                 || Feb          | 1                 || Mar          | 2                 || Apr          | 1                 || May          | 2                 || Jun          | 1                 || Jul          | 1                 || Aug          | 1                 || Sep          | 1                 || Oct          | 1                 || Nov          | 1                 || Dec          | 0                 |+--------------+-------------------+

方程:

average_posts_per_user:

 total_posts_per_month / total_users_per_month (cumulative)

预期结果:

+--------------+-------------------+|    month     |  avg_posts        || Jan          | 0                 || Feb          | 0.25              || Mar          | 0.4               || Apr          | 0.16              || May          | 0.28              || Jun          | 0.12              || Jul          | 0.11              || Aug          | 0.1               || Sep          | 0.09              || Oct          | 0.8               || Nov          | 0.7               || Dec          | 0                 |+--------------+-------------------+

提前致谢。

Answer 1

怎么样

SELECT
  userqry.`month`,
  total_users,
  IFNULL(month_posts,0) AS total_posts,
  IFNULL(month_posts,0)/total_users AS avg_posts
FROM
  (SELECT
    `month`,
    @usernum:=@usernum+new_users AS total_users
  FROM (
    SELECT
      DATE_FORMAT(created_at,'%M') AS `month`,
      COUNT(id) AS new_users
    FROM users
    GROUP BY 
      MONTH(created_at)
  ) AS baseview,
  (SELECT @usernum:=0) AS usernuminit
  ) AS userqry
LEFT JOIN
  (SELECT
    DATE_FORMAT(created_at,'%M') AS `month`,
    COUNT(id) AS month_posts
  FROM posts
  GROUP BY 
    MONTH(created_at)
  ) AS postqry
ON userqry.`month`=postqry.`month`

SQLfiddle

编辑

对于年份比较，可以使用:

SELECT
  2013 AS reportyear,
  view2013.*
FROM (
  -- query from above with "WHERE created_at BETWEEN '2013-01-01' AND '2013-12-31'" in both inner queries
) AS view2013
UNION
SELECT
  2014 AS reportyear,
  view2014.*
FROM (
  -- query from above with "WHERE created_at BETWEEN '2014-01-01' AND '2014-12-31'" in both inner queries
) AS view2015

或(推荐)从您的应用程序多次运行上述查询，每次都使用不同的年份。