[CLOSED] GPF/NPF from wlact/wlsta (instead of wlpkt)

[caiwang]

Issue by unissoft-bj Wednesday Jan 21, 2015 at 12:40 GMT Originally opened as https://github.com/caiwang/ihostsrc/issues/15

---

思路： 1，查wlact表，那些event=gone的记录，算出duration，duration长度不够的过滤掉 2，在step 1基础上，查wlsta表，查rssi（峰值，平均值），rssi不够的过滤掉 用同样的算法，给不同的参数，可分别得出毛客流、净客流（在iserver端即可）

[caiwang]

Comment by unissoft-bj Thursday Jan 22, 2015 at 10:09 GMT

---

GPF : duration >= 60 seconds

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) >='60' and SUBSTRING(lastseen,1,4) = '2015' and weekofyear(lastseen) = '4');" | mysql -uroot -p0ffs4t? wlsp

echo "select count(distinct(mac)) as flow,netid,timebyday from z_passenger group by netid,timebyday;" | mysql -uroot -p0ffs4t? wlsp > gpfbyday.txt echo "select count(distinct(mac)) as flow,netid,timebyhour from z_passenger group by netid,timebyhour;" | mysql -uroot -p0ffs4t? wlsp > gpfbyhour.txt echo "select count(distinct(mac)) as flow,netid,timebyten from z_passenger group by netid,timebyten;" | mysql -uroot -p0ffs4t? wlsp > gpfbyten.txt

echo "DROP TABLE IF EXISTS z_passenger;" | mysql -uroot -p0ffs4t? wlsp

[caiwang]

Comment by unissoft-bj Thursday Jan 22, 2015 at 16:39 GMT

---

NPF : duartion>=180, avgrssibymin>=-70, count of big avgrssi >=10

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) >='180' and SUBSTRING(lastseen,1,4) = '2015' and weekofyear(lastseen) = '4');" | mysql -uroot -p0ffs4t? wlsp

echo "DROP TABLE IF EXISTS z_station;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_station (select mac, rssi, sender,netid, optime as timebysec, SUBSTRING(optime,1,16) as timebymin from wlsta where CONCAT(mac,'-',sender,'-',netid) in (select distinct(CONCAT(mac,'-',sender,'-',netid)) from z_passenger) and SUBSTRING(optime,1,4) = '2015' and weekofyear(optime) = '4');" | mysql -uroot -p0ffs4t? wlsp

echo "DROP TABLE IF EXISTS z_avgrssibymin;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_avgrssibymin (select mac, avg(rssi) as avgrssi, sender,netid, timebymin from z_station group by mac,sender,netid,timebymin);" | mysql -uroot -p0ffs4t? wlsp

echo "DROP TABLE IF EXISTS z_netmac;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_netmac (select CONCAT(mac,'-',sender,'-',netid) as macmix from (select mac, count(mac) as cntmac,sender,netid from z_avgrssibymin where avgrssi >= '-70' group by mac, sender,netid) tbl1 where cntmac>='10');" | mysql -uroot -p0ffs4t? wlsp echo "delete from z_netmac where macmix in (select CONCAT(replace(mac,'-',':'),'-',sender,'-',netid) from usermacs where CONCAT(userid,'-',sender,'-',netid) in (select CONCAT(userid,'-',sender,'-',netid) from useraccounts where usertype='100' and stat>='100') and stat>='100');" | mysql -uroot -p0ffs4t? wlsp

echo "DROP TABLE IF EXISTS z_netpassenger;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_netpassenger (select mac, rssi, sender,netid, optime as timebysec, 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, CONCAT(SUBSTRING(optime,1,15),'0') as timebyten, SUBSTRING(optime,1,16) as timebymin from wlsta where CONCAT(mac,'-',sender,'-',netid) in (select * from z_netmac) and SUBSTRING(optime,1,4) = '2015' and weekofyear(optime) = '4');" | mysql -uroot -p0ffs4t? wlsp

echo "select mac,avg(rssi) as avgrssi,netid,timebyday from z_netpassenger where netid='ihost-xx' group by mac,netid,timebyday;" | mysql -uroot -p0ffs4t? wlsp > npfbyday.txt echo "select mac,avg(rssi) as avgrssi,netid,timebyhour from z_netpassenger where netid='ihost-xx' group by mac,netid,timebyhour;" | mysql -uroot -p0ffs4t? wlsp > npfbyhour.txt echo "select mac,avg(rssi) as avgrssi,netid,timebyten from z_netpassenger where netid='ihost-xx' and timebyday='2015-01-22' group by mac,netid,timebyten;" | mysql -uroot -p0ffs4t? wlsp > npfbyten.txt

[caiwang]

Comment by unissoft-bj Thursday Jan 22, 2015 at 17:05 GMT

---

echo "select count(distinct(mac)) as flow,netid,timebyday from z_netpassenger group by netid,timebyday;" | mysql -uroot -p0ffs4t? wlsp > npfbyday1.txt echo "select count(distinct(mac)) as flow,netid,timebyhour from z_netpassenger group by netid,timebyhour;" | mysql -uroot -p0ffs4t? wlsp > npfbyhour1.txt echo "select count(distinct(mac)) as flow,netid,timebyten from z_netpassenger group by netid,timebyten;" | mysql -uroot -p0ffs4t? wlsp > npfbyten1.txt

[caiwang]

Comment by unissoft-bj Thursday Jan 22, 2015 at 23:38 GMT

---

PPF : duartion>=600, avgrssibymin>=-60, count of big avgrssi >=10

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, firstseen as timebysec, SUBSTRING(firstseen,1,7) as timebymonth, CONCAT(SUBSTRING(firstseen,1,4),':',IF(weekofyear(firstseen) < 10, CONCAT('0',weekofyear(firstseen)),weekofyear(firstseen))) as timebyweek, SUBSTRING(firstseen,1,10) as timebyday, SUBSTRING(firstseen,1,13) as timebyhour, CONCAT(SUBSTRING(firstseen,1,15),'0') as timebyten, SUBSTRING(firstseen,1,16) as timebymin from wlact where event='gone' and timestampdiff(second,firstseen,lastseen) >='600' and SUBSTRING(firstseen,1,4) = '2015' and weekofyear(firstseen) = '4');" | mysql -uroot -p0ffs4t? wlsp

echo "DROP TABLE IF EXISTS z_station;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_station (select mac, rssi, sender,netid, optime as timebysec, SUBSTRING(optime,1,16) as timebymin from wlsta where CONCAT(mac,'-',sender,'-',netid) in (select distinct(CONCAT(mac,'-',sender,'-',netid)) from z_passenger) and SUBSTRING(optime,1,4) = '2015' and weekofyear(optime) = '4');" | mysql -uroot -p0ffs4t? wlsp

echo "DROP TABLE IF EXISTS z_avgrssibymin;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_avgrssibymin (select mac, avg(rssi) as avgrssi, sender,netid, timebymin from z_station group by mac,sender,netid,timebymin);" | mysql -uroot -p0ffs4t? wlsp

echo "DROP TABLE IF EXISTS z_netmac;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_netmac (select CONCAT(mac,'-',sender,'-',netid) as macmix from (select mac, count(mac) as cntmac,sender,netid from z_avgrssibymin where avgrssi >= '-60' group by mac, sender,netid) tbl1 where cntmac>='10');" | mysql -uroot -p0ffs4t? wlsp echo "delete from z_netmac where macmix in (select CONCAT(replace(mac,'-',':'),'-',sender,'-',netid) from usermacs where CONCAT(userid,'-',sender,'-',netid) in (select CONCAT(userid,'-',sender,'-',netid) from useraccounts where usertype='100' and stat>='100') and stat>='100');" | mysql -uroot -p0ffs4t? wlsp

echo "DROP TABLE IF EXISTS z_netpassenger;" | mysql -uroot -p0ffs4t? wlsp echo "Create table z_netpassenger (select mac, rssi, sender,netid, optime as timebysec, 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, CONCAT(SUBSTRING(optime,1,15),'0') as timebyten, SUBSTRING(optime,1,16) as timebymin from wlsta where CONCAT(mac,'-',sender,'-',netid) in (select * from z_netmac) and SUBSTRING(optime,1,4) = '2015' and weekofyear(optime) = '4');" | mysql -uroot -p0ffs4t? wlsp

echo "select mac,avg(rssi) as avgrssi,netid,timebyday from z_netpassenger where netid='ihost-xx' group by mac,netid,timebyday;" | mysql -uroot -p0ffs4t? wlsp > npfbyday.txt echo "select mac,avg(rssi) as avgrssi,netid,timebyhour from z_netpassenger where netid='ihost-xx' group by mac,netid,timebyhour;" | mysql -uroot -p0ffs4t? wlsp > npfbyhour.txt echo "select mac,avg(rssi) as avgrssi,netid,timebyten from z_netpassenger where netid='ihost-xx' and timebyday='2015-01-22' group by mac,netid,timebyten;" | mysql -uroot -p0ffs4t? wlsp > npfbyten.txt

echo "select mac,avg(rssi) as avgrssi,netid,timebyhour from z_netpassenger where netid='ihost-xx' and timebyday='2015-01-22' group by mac,netid,timebyhour;" | mysql -uroot -p0ffs4t? wlsp > npfbyhour0.txt

[caiwang]

Comment by unissoft-bj Friday Jan 23, 2015 at 00:10 GMT

---

echo "select count(distinct(mac)) as flow,netid,timebyday from z_netpassenger group by netid,timebyday;" | mysql -uroot -p0ffs4t? wlsp > npfbyday1.txt echo "select count(distinct(mac)) as flow,netid,timebyhour from z_netpassenger group by netid,timebyhour;" | mysql -uroot -p0ffs4t? wlsp > npfbyhour1.txt echo "select count(distinct(mac)) as flow,netid,timebyten from z_netpassenger group by netid,timebyten;" | mysql -uroot -p0ffs4t? wlsp > npfbyten1.txt