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, SUBSTRING(timebyhour,1,10) as timebyday,timebyhour from z_passenger_raw1 group by mac, sender, netid, timebyhour);" | mysql -uroot -p0ffs4t? wlsp
echo "delete from z_passenger where dura >'18000';" | 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,timebyday, sender, netid from z_passenger group by mac, timebyday,sender,netid);" | mysql -uroot -p0ffs4t? wlsp
echo "DROP TABLE IF EXISTS z_weekdurabystage;" | mysql -uroot -p0ffs4t? wlsp
echo "Create table z_weekdurabystage (select mac,round(dura/600)+1 as durabystage, timebyday,sender,netid from z_passenger);" | mysql -uroot -p0ffs4t? wlsp
echo "select tbldura.mac,tbldura.durabystage as dura,tblfreq.freq, tbldura.timebyday,CONCAT(SUBSTRING(tbldura.timebyday,1,4),'-',IF(weekofyear(tbldura.timebyday) < 10, CONCAT('0',weekofyear(tbldura.timebyday)),weekofyear(tbldura.timebyday))) as 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 and tbldura.timebyday=tblfreq.timebyday ;" | mysql -uroot -p0ffs4t? wlsp > weekmac-dura-freq-13.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, SUBSTRING(timebyhour,1,10) as timebyday,timebyhour from z_passenger_raw1 group by mac, sender, netid, timebyhour);" | mysql -uroot -p0ffs4t? wlsp
echo "delete from z_passenger where dura >'18000';" | 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,timebyday, sender, netid from z_passenger group by mac, timebyday,sender,netid);" | mysql -uroot -p0ffs4t? wlsp
echo "DROP TABLE IF EXISTS z_weekdurabystage;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_weekdurabystage (select mac,round(dura/600)+1 as durabystage, timebyday,sender,netid from z_passenger);" | mysql -uroot -p0ffs4t? wlsp
echo "select tbldura.mac,tbldura.durabystage as dura,tblfreq.freq, tbldura.timebyday,CONCAT(SUBSTRING(tbldura.timebyday,1,4),'-',IF(weekofyear(tbldura.timebyday) < 10, CONCAT('0',weekofyear(tbldura.timebyday)),weekofyear(tbldura.timebyday))) as 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 and tbldura.timebyday=tblfreq.timebyday ;" | mysql -uroot -p0ffs4t? wlsp > weekmac-dura-freq-13.txt