[CLOSED] mac之间相关性分析（顾客组，或者顾客-销售员间关联）

[caiwang]

Issue by unissoft-bj Monday Jan 12, 2015 at 13:49 GMT Originally opened as https://github.com/caiwang/ihostsrc/issues/12

---

echo "select mac,avg(rssi),timebyminute from wlpkt where (mac like '00:73%' or mac like '00:16%') group by timebyminute;" | mysql -uroot -prootatussp wlsp > wlpkt.sql

图中两个手机 00:73:E0:67:42:E2 00:16:6d:c0:76:3c

信号强度相当（在同一位置），且先用一个，再用另一个的特征明显

[caiwang]

Comment by unissoft-bj Tuesday Jan 13, 2015 at 03:15 GMT

---

echo "select mac,avg(rssi),timebyminute from wlpkt where (mac ='18:3D:A2:60:C7:58' or mac ='0C:1D:AF:CA:94:2E' or mac='F8:A4:5F:77:35:E1' or mac='98:B8:E3:55:3D:78') group by timebyminute;" | mysql -uroot -prootatussp wlsp > wlpkt.txt

[caiwang]

Comment by unissoft-bj Tuesday Jan 13, 2015 at 03:30 GMT

---

echo "select mac,avg(rssi),timebyminute from wlpkt where (mac ='18:3D:A2:60:C7:58' or mac ='0C:1D:AF:CA:94:2E' or mac='F8:A4:5F:77:35:E1' or mac='98:B8:E3:55:3D:78' or mac='00:73:E0:67:42:E2' or mac='00:16:6d:c0:76:3c') group by timebyminute;" | mysql -uroot -prootatussp wlsp > wlpkt.txt

原始rssi

纵向拉开的rssi

[caiwang]

Comment by unissoft-bj Tuesday Jan 13, 2015 at 03:37 GMT

---

去掉18:3d:a2笔记本电脑数据

2015.1.9 8:00 ~ 2015.1.13 9:00的数据 深蓝色是coolpad，粉色是samsung 黄色是小米-li，紫色是小米-liu 青色是ipad

[caiwang]

Comment by unissoft-bj Wednesday Jan 14, 2015 at 02:50 GMT

---

上面的sql语句有误，group by mac, timebyminute

今天10点后的图形（所有mac）

分三层： 最上面的一层（-40dbm）是ihost附近，2~3米 中间这一层（-50~-70dbm）是室内的station 最下的一层（-80dbm）是室外的station（验证了一堵墙10dbm的经验）

echo "select mac,avg(rssi),timebyminute from wlpkt where (pkttime>='2015-1-14 10:00:00') group by mac,timebyminute;" | mysql -uroot -prootatussp wlsp > wlpkt.txt

[caiwang]

Comment by unissoft-bj Wednesday Jan 14, 2015 at 03:15 GMT

---

2015-1-11周日14:00 ~ 2015-1-12周一20:00的图形

echo "select mac,avg(rssi),timebyminute from wlpkt where (mac ='18:3D:A2:60:C7:58' or mac ='0C:1D:AF:CA:94:2E' or mac='F8:A4:5F:77:35:E1' or mac='98:B8:E3:55:3D:78' or mac='00:73:E0:67:42:E2' or mac='00:16:6d:c0:76:3c'or mac='28:E3:1F:A2:55:7D') and (pkttime>='2015-1-11 14:00:00') and (pkttime<='2015-1-12 20:00:00') group by mac,timebyminute;" | mysql -uroot -prootatussp wlsp > wlpkt.txt

[caiwang]

Comment by unissoft-bj Wednesday Jan 14, 2015 at 03:52 GMT

---

用10分钟间隔做avg

echo "select mac,avg(rssi),concat(left(timebyminute,15),'0:00') as timebyten from wlpkt where (mac ='18:3D:A2:60:C7:58' or mac ='0C:1D:AF:CA:94:2E' or mac='F8:A4:5F:77:35:E1' or mac='98:B8:E3:55:3D:78' or mac='00:73:E0:67:42:E2' or mac='00:16:6d:c0:76:3c'or mac='28:E3:1F:A2:55:7D') and (pkttime>='2015-1-11 14:00:00') and (pkttime<='2015-1-12 20:00:00') group by mac,timebyten;" | mysql -uroot -prootatussp wlsp > wlpkt.txt

[caiwang]

Comment by unissoft-bj Wednesday Jan 14, 2015 at 04:06 GMT

---

用小时为间隔做avg

这个图的相关性很明显

echo "select mac,avg(rssi),timebyhour from wlpkt where (mac ='18:3D:A2:60:C7:58' or mac ='0C:1D:AF:CA:94:2E' or mac='F8:A4:5F:77:35:E1' or mac='98:B8:E3:55:3D:78' or mac='00:73:E0:67:42:E2' or mac='00:16:6d:c0:76:3c'or mac='28:E3:1F:A2:55:7D') and (pkttime>='2015-1-11 14:00:00') and (pkttime<='2015-1-12 20:00:00') group by mac,timebyhour;" | mysql -uroot -prootatussp wlsp > wlpkt.txt

[caiwang]

Comment by unissoft-bj Wednesday Jan 14, 2015 at 04:41 GMT

---

全部mac地址

echo "select mac,avg(rssi),timebyhour from wlpkt where (pkttime>='2015-1-11 14:00:00') and (pkttime<='2015-1-12 20:00:00') group by mac,timebyhour;" | mysql -uroot -prootatussp wlsp > wlpkt.txt

[caiwang]

Comment by unissoft-bj Wednesday Jan 14, 2015 at 04:59 GMT

---

一周统计

echo "select mac,avg(rssi),timebyhour from wlpkt where (mac ='18:3D:A2:60:C7:58' or mac ='0C:1D:AF:CA:94:2E' or mac='F8:A4:5F:77:35:E1' or mac='98:B8:E3:55:3D:78' or mac='00:73:E0:67:42:E2' or mac='00:16:6d:c0:76:3c'or mac='28:E3:1F:A2:55:7D') group by mac,timebyhour;" | mysql -uroot -prootatussp wlsp > wlpkt.txt