caiwang
commented
9 years ago 
echo "DROP TABLE IF EXISTS z_passenger_raw;" | mysql -uroot -p0ffs4t? wlsp
echo "Create table z_passenger_raw (select mac, timestampdiff(second,firstseen,lastseen) as dura_raw, sender,netid, SUBSTRING(lastseen,1,13) as timebyhour 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
remove duplicated rows
echo "DROP TABLE IF EXISTS z_passenger_raw1;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_passenger_raw1 (select distinct(CONCAT(mac,dura_raw,sender,netid,timebyhour)) as flag,mac,dura_raw,sender,netid,timebyhour from z_passenger_raw group by mac, sender, netid, timebyhour);" | mysql -uroot -p0ffs4t? wlsp
connect segments in the same hour
echo "DROP TABLE IF EXISTS z_passenger;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_passenger (select mac, sum(dura_raw) as dura, sender,netid, CONCAT(SUBSTRING(timebyhour,1,4),'-',IF(weekofyear(timebyhour) < 10, CONCAT('0',weekofyear(timebyhour)),weekofyear(timebyhour))) as timebyweek,timebyhour from z_passenger_raw1 group by mac, sender, netid, timebyhour);" | 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_weekdurabystage;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_weekdurabystage (select mac,IF(dura<=1800,'1-<30min',IF(dura<=2200,'2-<2h','3->2h')) as durabystage, timebyweek,sender,netid from z_passenger);" | 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-8.txt
echo "DROP TABLE IF EXISTS z_passenger_raw;" | mysql -uroot -p0ffs4t? wlsp
echo "Create table z_passenger_raw (select mac, timestampdiff(second,firstseen,lastseen) as dura_raw, sender,netid, SUBSTRING(lastseen,1,13) as timebyhour 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
remove duplicated rows
echo "DROP TABLE IF EXISTS z_passenger_raw1;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_passenger_raw1 (select distinct(CONCAT(mac,dura_raw,sender,netid,timebyhour)) as flag,mac,dura_raw,sender,netid,timebyhour from z_passenger_raw group by mac, sender, netid, timebyhour);" | mysql -uroot -p0ffs4t? wlsp
connect segments in the same hour
echo "DROP TABLE IF EXISTS z_passenger;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_passenger (select mac, sum(dura_raw) as dura, sender,netid, CONCAT(SUBSTRING(timebyhour,1,4),'-',IF(weekofyear(timebyhour) < 10, CONCAT('0',weekofyear(timebyhour)),weekofyear(timebyhour))) as timebyweek,timebyhour from z_passenger_raw1 group by mac, sender, netid, timebyhour);" | 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_weekdurabystage;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_weekdurabystage (select mac,IF(dura<=600,'1-<10min',IF(dura<=1200,'2-<20min',IF(dura<=1800,'3-<30min',IF(dura<=3600,'4-<1h',IF(dura<=7200,'5-<2h',IF(dura<=10800,'6-<3h','7->3h')))))) as durabystage, timebyweek,sender,netid from z_passenger);" | 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-7.txt
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(timebyhour,1,7) as timebymonth, CONCAT(SUBSTRING(timebyhour,1,4),':',IF(weekofyear(timebyhour) < 10, CONCAT('0',weekofyear(timebyhour)),weekofyear(timebyhour))) as timebyweek, SUBSTRING(timebyhour,1,10) as timebyday