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 "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) as durabystage, timebyday,sender,netid from z_passenger);" | mysql -uroot -p0ffs4t? wlsp
echo "select tbldura.mac,durabystage as dura,tblfreq.freq, tbldura.timebyday,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-9.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 "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) as durabystage, timebyday,sender,netid from z_passenger);" | mysql -uroot -p0ffs4t? wlsp
echo "select tbldura.mac,durabystage as dura,tblfreq.freq, tbldura.timebyday,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-9.txt
按频次统计的客流曲线
按时长统计的客流曲线
累计时长在频次上的分布
累计时长在时长上的分布
