phpBB 数据库统计数据代码

[admin]

每月注册人数

代码： 全选

mysql> select Year(from_unixtime(user_regdate)) as Year, Month(from_unixtime(user_regdate)) as Month, count(*) as Total from phpbb_users group by Year,Month;

每月发贴量统计

代码： 全选

mysql> select Year(from_unixtime(post_time)) as Year, Month(from_unixtime(post_time)) as Month, count(*) as Total from phpbb_posts group by Year,Month;

每月活跃人数

代码： 全选

mysql> select Year(from_unixtime(user_lastvisit)) as Year, Month(from_unixtime(user_lastvisit)) as Month, count(*) as Total from phpbb_users group by Year,Month;

用户持续人数。

代码： 全选

mysql> select Year(from_unixtime(user_lastvisit-user_regdate))-1970 as Year, Month(from_unixtime(user_lastvisit-user_regdate)) as Month, count(*) as Total from phpbb_users where user_lastvisit > user_regdate group by Year,Month;

按小时统计发贴数

代码： 全选

mysql> select Hour(from_unixtime(post_time)) as Hour, count(*) as Total from phpbb_posts group by Hour;

论坛查看次数最多前20的主题：

代码： 全选

mysql> select forum_id,topic_id,topic_views,topic_title from phpbb_topics order by topic_views desc limit 20;

论坛回复次数最多前20的主题：

代码： 全选

mysql> select forum_id,topic_id,topic_replies,topic_title from phpbb_topics order by topic_replies desc limit 20;

来自 https://forum.ubuntu.com.cn/viewtopic.php?f=16&t=277604