基于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
相关产品推荐
相关产品推荐

