[CLOSED] 频次分析

[caiwang]

Issue by unissoft-bj Saturday Jan 24, 2015 at 08:05 GMT Originally opened as https://github.com/caiwang/ihostsrc/issues/19

---

一周顾客到访频次分析

echo "DROP TABLE IF EXISTS z_passenger;" | mysql -uroot -p0ffs4t? wlsp

echo "Create table z_passenger (select mac, timestampdiff(second,firstseen,lastseen) as dura, sender,netid, lastseen as timebysec, SUBSTRING(lastseen,1,7) as timebymonth, CONCAT(SUBSTRING(lastseen,1,4),':',IF(weekofyear(lastseen) < 10, CONCAT('0',weekofyear(lastseen)),weekofyear(lastseen))) as timebyweek, SUBSTRING(lastseen,1,10) as timebyday, SUBSTRING(lastseen,1,13) as timebyhour, CONCAT(SUBSTRING(lastseen,1,15),'0') as timebyten, SUBSTRING(lastseen,1,16) as timebymin from wlact where event='gone' and timestampdiff(second,firstseen,lastseen) >='60' and SUBSTRING(lastseen,1,4) = '2015' and weekofyear(lastseen) = '4');" | mysql -uroot -p0ffs4t? wlsp

echo "DROP TABLE IF EXISTS z_weekfreq;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_weekfreq (select mac, count(mac) as freq,timebyweek, sender, netid from z_passenger group by mac, timebyweek,sender,netid);" | mysql -uroot -p0ffs4t? wlsp

echo "select * from z_weekfreq;" | mysql -uroot -p0ffs4t? wlsp > weekfreq.txt

---

横坐标为一周频次，纵坐标是发生的次数

[caiwang]

Comment by unissoft-bj Saturday Jan 24, 2015 at 12:37 GMT

---

散点图

[caiwang]

Comment by unissoft-bj Sunday Jan 25, 2015 at 09:10 GMT

---

一周逐日对比

echo "DROP TABLE IF EXISTS z_passenger;" | mysql -uroot -p0ffs4t? wlsp

echo "Create table z_passenger (select mac, timestampdiff(second,firstseen,lastseen) as dura, sender,netid, lastseen as timebysec, SUBSTRING(lastseen,1,7) as timebymonth, CONCAT(SUBSTRING(lastseen,1,4),':',IF(weekofyear(lastseen) < 10, CONCAT('0',weekofyear(lastseen)),weekofyear(lastseen))) as timebyweek, SUBSTRING(lastseen,1,10) as timebyday, SUBSTRING(lastseen,1,13) as timebyhour, CONCAT(SUBSTRING(lastseen,1,15),'0') as timebyten, SUBSTRING(lastseen,1,16) as timebymin from wlact where event='gone' and timestampdiff(second,firstseen,lastseen) >='60' and SUBSTRING(lastseen,1,4) = '2015' and weekofyear(lastseen) = '4');" | mysql -uroot -p0ffs4t? wlsp

echo "DROP TABLE IF EXISTS z_dayfreq;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_dayfreq (select mac, count(mac) as freq,timebyday, sender, netid from z_passenger group by mac, timebyday,sender,netid);" | mysql -uroot -p0ffs4t? wlsp

echo "select * from z_dayfreq where netid='ihost-xx';" | mysql -uroot -p0ffs4t? wlsp > dayfreq.txt

[caiwang]

Comment by unissoft-bj Sunday Jan 25, 2015 at 10:10 GMT

---

各周图形