[CLOSED] 时长分析

[caiwang]

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

---

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_weekdurabyh;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_weekdurabyh (select mac, CONCAT(SUBSTRING(dura,1,length(dura)-2),'00') as durabyh, timebyweek,sender,netid from z_passenger);" | mysql -uroot -p0ffs4t? wlsp

echo "DROP TABLE IF EXISTS z_weekdura;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_weekdura (select count(mac) as cnt,IF(durabyh<=3600,durabyh,'1h+') as dura,timebyweek,sender,netid from z_weekdurabyh group by dura,timebyweek,sender,netid);" | mysql -uroot -p0ffs4t? wlsp

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

---

横坐标是顾客停留时间，纵坐标是发生的次数

[caiwang]

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

---

散点图

[caiwang]

Comment by unissoft-bj Sunday Jan 25, 2015 at 08:49 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_daydurabyh;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_daydurabyh (select mac, CONCAT(SUBSTRING(dura,1,length(dura)-2),'00') as durabyh, timebyday,sender,netid from z_passenger);" | mysql -uroot -p0ffs4t? wlsp

echo "DROP TABLE IF EXISTS z_daydura;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_daydura (select count(mac) as cnt,IF(durabyh<=3600,durabyh,'1h+') as dura,timebyday,sender,netid from z_daydurabyh group by dura,timebyday,sender,netid);" | mysql -uroot -p0ffs4t? wlsp

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

[caiwang]

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

---

各周图形