56,681
社区成员
发帖
与我相关
我的任务
分享
mysql> desc minlog;
+--------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| time | int(10) unsigned | NO | PRI | NULL | | //分钟的时间戳,精确到分钟
| count_online | int(10) unsigned | NO | | NULL | | //在线人数
+--------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc hourlog;
+---------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------------------+------+-----+---------+-------+
| hour | int(10) unsigned | NO | PRI | 0 | | //小时的timestamp ,精确到秒
| count_online | int(10) unsigned | NO | | 0 | | //采样此刻的在线人数
| count_reguser | int(10) unsigned | NO | | 0 | | //总注册用户数
| total_notloss | mediumint(8) unsigned | NO | | 0 | | //未流失用户数
| total_mayloss | mediumint(8) unsigned | NO | | 0 | | //可疑流失
| total_isloss | mediumint(8) unsigned | NO | | 0 | | //已经流失
| avg_online | int(10) unsigned | NO | | 0 | | //平均在线
+---------------+-----------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> desc losslog;
+---------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| hour | int(10) unsigned | NO | | 0 | | //这个和hourlog表里面的hour是对应
| notloss | int(10) unsigned | NO | | 0 | |
| mayloss | int(10) unsigned | NO | | 0 | |
| isloss | int(10) unsigned | NO | | 0 | |
| level | smallint(1) unsigned | NO | | 0 | |
| total | mediumint(8) unsigned | NO | | 0 | |
+---------+-----------------------+------+-----+---------+----------------+
7 rows in set (0.02 sec)
select 1 from hourlog where from_unixtime(hour,'%Y%m%d%H')=from_unixtime(unix_timestamp(),'%Y%m%d%H') limit 1
insert into hourlog(hour,count_online,....)values(unix_timestamp(),'$count_online',....)
select 1 from losslog where from_unixtime(hour,'%Y%m%d%H')=from_unixtime(unix_timestamp(),'%Y%m%d%H') limit 1