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
for passenger flow
echo "delete from z_passenger where dura >'18000' or dura <'30';" | mysql -uroot -p0ffs4t? wlsp
echo "DROP TABLE IF EXISTS z_pass_dura_list;" | mysql -uroot -p0ffs4t? wlsp
echo "Create table z_pass_dura_list (select distinct(CONCAT(mac,'-',sender,'-',netid)) as macmix from z_passenger);" | mysql -uroot -p0ffs4t? wlsp
echo "DROP TABLE IF EXISTS z_pass_rssi_list;" | mysql -uroot -p0ffs4t? wlsp
echo "Create table z_pass_rssi_list (select distinct(CONCAT(mac,'-',sender,'-',netid)) as macmix from wlsta where rssi>='-65'and SUBSTRING(optime,1,4) = '2015' and weekofyear(optime) = '4' );" | mysql -uroot -p0ffs4t? wlsp
echo "DROP TABLE IF EXISTS z_pass_both_list;" | mysql -uroot -p0ffs4t? wlsp
echo "Create table z_pass_both_list (select tbldura.macmix from z_pass_dura_list as tbldura INNER JOIN z_pass_rssi_list as tblrssi on tbldura.macmix=tblrssi.macmix);" | mysql -uroot -p0ffs4t? wlsp
gross passenger flow
echo "DROP TABLE IF EXISTS z_gpf;" | mysql -uroot -p0ffs4t? wlsp
echo "Create table z_gpf (select mac, rssi, sender,netid, SUBSTRING(optime,1,7) as timebymonth, CONCAT(SUBSTRING(optime,1,4),'-',IF(weekofyear(optime) < 10, CONCAT('0',weekofyear(optime)),weekofyear(optime))) as timebyweek, SUBSTRING(optime,1,10) as timebyday,SUBSTRING(optime,1,13) as timebyhour,SUBSTRING(optime,12,2) as timehour from wlsta where CONCAT(mac,'-',sender,'-',netid) in (select macmix from z_pass_dura_list) and timestampdiff(second,firstseen,lastseen)>='1' and SUBSTRING(optime,1,4) = '2015' and weekofyear(optime) = '4');" | mysql -uroot -p0ffs4t? wlsp
net passenger flow
echo "DROP TABLE IF EXISTS z_npf;" | mysql -uroot -p0ffs4t? wlsp
echo "Create table z_npf (select mac, rssi, sender,netid, SUBSTRING(optime,1,7) as timebymonth, CONCAT(SUBSTRING(optime,1,4),'-',IF(weekofyear(optime) < 10, CONCAT('0',weekofyear(optime)),weekofyear(optime))) as timebyweek, SUBSTRING(optime,1,10) as timebyday,SUBSTRING(optime,1,13) as timebyhour,SUBSTRING(optime,12,2) as timehour from wlsta where CONCAT(mac,'-',sender,'-',netid) in (select macmix from z_pass_both_list) and timestampdiff(second,firstseen,lastseen)>='30' and SUBSTRING(optime,1,4) = '2015' and weekofyear(optime) = '4');" | mysql -uroot -p0ffs4t? wlsp
output
echo "select 'gpf' as type,count(distinct(mac)) as flow,sender,netid,timebyday,timebyhour, timehour from z_gpf group by netid,timebyhour union select 'npf' as type,count(distinct(mac)) as flow,sender,netid,timebyday,timebyhour,timehour from z_npf group by sender,netid,timebyhour;" | mysql -uroot -p0ffs4t? wlsp > passflow.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
for passenger flow
echo "delete from z_passenger where dura >'18000' or dura <'30';" | mysql -uroot -p0ffs4t? wlsp
echo "DROP TABLE IF EXISTS z_pass_dura_list;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_pass_dura_list (select distinct(CONCAT(mac,'-',sender,'-',netid)) as macmix from z_passenger);" | mysql -uroot -p0ffs4t? wlsp
echo "DROP TABLE IF EXISTS z_pass_rssi_list;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_pass_rssi_list (select distinct(CONCAT(mac,'-',sender,'-',netid)) as macmix from wlsta where rssi>='-65'and SUBSTRING(optime,1,4) = '2015' and weekofyear(optime) = '4' );" | mysql -uroot -p0ffs4t? wlsp
echo "DROP TABLE IF EXISTS z_pass_both_list;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_pass_both_list (select tbldura.macmix from z_pass_dura_list as tbldura INNER JOIN z_pass_rssi_list as tblrssi on tbldura.macmix=tblrssi.macmix);" | mysql -uroot -p0ffs4t? wlsp
gross passenger flow
echo "DROP TABLE IF EXISTS z_gpf;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_gpf (select mac, rssi, sender,netid, SUBSTRING(optime,1,7) as timebymonth, CONCAT(SUBSTRING(optime,1,4),'-',IF(weekofyear(optime) < 10, CONCAT('0',weekofyear(optime)),weekofyear(optime))) as timebyweek, SUBSTRING(optime,1,10) as timebyday,SUBSTRING(optime,1,13) as timebyhour,SUBSTRING(optime,12,2) as timehour from wlsta where CONCAT(mac,'-',sender,'-',netid) in (select macmix from z_pass_dura_list) and timestampdiff(second,firstseen,lastseen)>='1' and SUBSTRING(optime,1,4) = '2015' and weekofyear(optime) = '4');" | mysql -uroot -p0ffs4t? wlsp
net passenger flow
echo "DROP TABLE IF EXISTS z_npf;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_npf (select mac, rssi, sender,netid, SUBSTRING(optime,1,7) as timebymonth, CONCAT(SUBSTRING(optime,1,4),'-',IF(weekofyear(optime) < 10, CONCAT('0',weekofyear(optime)),weekofyear(optime))) as timebyweek, SUBSTRING(optime,1,10) as timebyday,SUBSTRING(optime,1,13) as timebyhour,SUBSTRING(optime,12,2) as timehour from wlsta where CONCAT(mac,'-',sender,'-',netid) in (select macmix from z_pass_both_list) and timestampdiff(second,firstseen,lastseen)>='30' and SUBSTRING(optime,1,4) = '2015' and weekofyear(optime) = '4');" | mysql -uroot -p0ffs4t? wlsp
output
echo "select 'gpf' as type,count(distinct(mac)) as flow,sender,netid,timebyday,timebyhour, timehour from z_gpf group by netid,timebyhour union select 'npf' as type,count(distinct(mac)) as flow,sender,netid,timebyday,timebyhour,timehour from z_npf group by sender,netid,timebyhour;" | mysql -uroot -p0ffs4t? wlsp > passflow.txt