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) >='1' 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, IF(count(mac)<5,'1:<5',IF(count(mac)<15,'2:5~15',IF(count(mac)<25,'3:15~25',IF(count(mac)<50,'4:25~50',IF(count(mac)<100,'5:50~100','6:>100'))))) as freq,timebyweek, sender, netid from z_passenger group by mac, timebyweek,sender,netid);" | mysql -uroot -p0ffs4t? wlsp
echo "DROP TABLE IF EXISTS z_weekdurabystage;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_weekdurabystage (select mac,IF(dura<=600,'<10min',IF(dura<=7200,'<2h','>2h')) as durabystage, 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,durabystage as dura,timebyweek,sender,netid from z_weekdurabystage group by dura,timebyweek,sender,netid);" | mysql -uroot -p0ffs4t? wlsp
echo "select tbldura.mac,durabystage as dura,tblfreq.freq, tbldura.timebyweek,tbldura.sender,tbldura.netid from z_weekdurabystage as tbldura INNER JOIN z_weekfreq as tblfreq on tbldura.mac=tblfreq.mac and tbldura.sender=tblfreq.sender and tbldura.netid=tblfreq.netid where tbldura.netid='ihost-xx';" | mysql -uroot -p0ffs4t? wlsp > weekmac-dura-freq-5.txt
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) >='1' 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, round(count(mac)/10)*10 as freq,timebyweek, sender, netid from z_passenger group by mac, timebyweek,sender,netid);" | mysql -uroot -p0ffs4t? wlsp
echo "DROP TABLE IF EXISTS z_weekdurabystage;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_weekdurabystage (select mac,IF(dura<=600,'<10min',IF(dura<=3600,'<1h',IF(dura<=7200,'<2h','>2h'))) as durabystage, 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,durabystage as dura,timebyweek,sender,netid from z_weekdurabystage group by dura,timebyweek,sender,netid);" | mysql -uroot -p0ffs4t? wlsp
echo "select tbldura.mac,durabystage as dura,tblfreq.freq, tbldura.timebyweek,tbldura.sender,tbldura.netid from z_weekdurabystage as tbldura INNER JOIN z_weekfreq as tblfreq on tbldura.mac=tblfreq.mac and tbldura.sender=tblfreq.sender and tbldura.netid=tblfreq.netid where tbldura.netid='ihost-xx';" | mysql -uroot -p0ffs4t? wlsp > weekmac-dura-freq-4.txt