You need to enable JavaScript to run this app. 基于posts表编写MySQL查询计算用户周均、月均发帖数
优惠活动
大模型
产品
解决方案
定价
更多

基于posts表编写MySQL查询计算用户周均、月均发帖数

各用户发贴频率统计MySQL实现

核心计算逻辑

这里的月度/周度平均发贴数,是基于单个用户自身的发贴时间跨度计算的实际发布频率,而非固定按全局自然月/自然周切片统计,避免两类常见结果偏差:

  • 月度平均 = 用户累计总发贴数 / 用户从首篇到最后一篇发贴覆盖的实际月份数,不足1个月时按1个月兜底,避免出现除零错误
  • 周度平均 = 用户累计总发贴数 / 用户从首篇到最后一篇发贴覆盖的实际周数,不足1周时按1周兜底,避免出现除零错误

可直接运行的查询语句

SELECT 
  user_id,
  -- 计算月度平均:最小分母设为1,规避时间跨度过短导致的除零问题
  post_total / GREATEST(1, TIMESTAMPDIFF(MONTH, first_post_time, last_post_time)) AS monthly_average,
  -- 计算周度平均:最小分母设为1,规避时间跨度过短导致的除零问题
  post_total / GREATEST(1, TIMESTAMPDIFF(WEEK, first_post_time, last_post_time)) AS weekly_average
FROM (
  -- 先聚合每个用户的基础统计值,减少外层计算的开销
  SELECT 
    user_id,
    COUNT(id) AS post_total,
    MIN(created_at) AS first_post_time,
    MAX(created_at) AS last_post_time
  FROM posts
  GROUP BY user_id
) user_base_stat;

可调整的适配规则

如果业务规则有变动,可以直接调整分母的计算逻辑适配:

  • 要是需要固定按近N个月的周期算平均,只要在内层查询加时间过滤就行,比如统计近6个月的均值就加WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
  • 要是需要严格把发贴涉及的第一个、最后一个自然月/自然周算完整周期,不按实际天数折算,可以把分母替换成TIMESTAMPDIFF(MONTH, DATE_FORMAT(first_post_time, '%Y-%m-01'), DATE_FORMAT(last_post_time, '%Y-%m-01')) + 1

内容的提问来源于stack exchange,提问作者daniel

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.10 06:15:02