mysql 每天的数据_MySQL中统计一天之内每个时间段内的数据，很急

有两个表表一：粉丝表Fans表二：粉丝统计表Fanstrack要求Fanstrack统计从fans表里面拿取数据Total(subscribe=1)的所有的粉丝数据的总和New(subscribe=1)所有新增粉丝数据的总和ou...

有两个表

表一：粉丝表Fans

表二：粉丝统计表Fanstrack

要求

Fanstrack 统计 从fans表里面拿取数据

Total (subscribe=1)的所有的粉丝数据的总和

New (subscribe=1) 所有新增粉丝数据的总和

outflow (subscribe=0) 所有流失流失粉丝数据的总和

net 用new - outflow 净增粉丝的总和

(subscribe=1)关注的粉丝

(subscribe=0)取消关注的粉丝

(uid) 被关注的对象

按时间段(两个小时一个时间段)进行统计，定时插入到粉丝统计表里面，如果按天统计的话，通过存储过程和定时器可以实现，我这里有个sql语句

INSERT INTO yht_fanstrack(total,new,outflow,net,time,uid)

SELECT c.total,a.new,b.outflow,(a.new-b.outflow) AS net,a.time,a.uid FROM

(SELECT now() AS time,COUNT(subscribe_time) AS new,uid FROM yht_fans WHERE subscribe_time>=UNIX_TIMESTAMP(CURDATE()) and subscribe_time<=UNIX_TIMESTAMP(CURDATE())+86400 GROUP BY uid) a

JOIN

(SELECT now() AS time,COUNT(cancel_time) AS outflow,uid FROM yht_fans WHERE cancel_time>=UNIX_TIMESTAMP(CURDATE()) and cancel_time<=UNIX_TIMESTAMP(CURDATE())+86400 GROUP BY uid) b

ON

a.uid=b.uid

JOIN

(SELECT COUNT(subscribe) AS total,uid FROM yht_fans GROUP BY uid) c

ON

a.uid=c.uid

现在的问题是，按时间段先用sql语句把数据统计出来，然后每天定时自动插入，不知道如何实现。

有没哪位大神给个思路，或者写个实现方案，小弟在此谢过了！！！

展开