caiwang
commented
9 years ago Open caiwang opened 9 years ago
caiwang
commented
9 years ago caiwang
commented
9 years ago caiwang
commented
9 years ago 
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 "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 tbldura.mac,durabyh as dura,tblfreq.freq, tbldura.timebyweek,tbldura.sender,tbldura.netid from z_weekdurabyh as tbldura INNER JOIN z_weekfreq as tblfreq on tbldura.mac=tblfreq.mac and tbldura.sender=tblfreq.sender and tbldura.netid=tblfreq.netid;" | mysql -uroot -p0ffs4t? wlsp > weekmac-dura-freq-1.txt
频次-总时长分布折线图