co-relationship refined sql statement (more accurate and less effective)

[caiwang]

Issue by unissoft-bj Saturday Jan 24, 2015 at 07:15 GMT Originally opened as https://github.com/caiwang/ihostsrc/issues/18

---

echo "DROP TABLE IF EXISTS z_activesta;" | mysql -uroot -prootatussp wlsp echo "Create table z_activesta(select mac, rssi, rectime as timebysec, SUBSTRING(rectime,1,16) as timebymin,CONCAT(SUBSTRING(rectime,1,18),'5') as timebyten from wlpkt where rectime >= '2015-01-23 19:00:00' and rectime < '2015-01-23 19:15:00' and mac in ( select distinct mac from wlpkt where rssi >= '-75' and rectime >= '2015-01-23 19:00:00' and rectime < '2015-01-23 19:15:00'));" | mysql -uroot -prootatussp wlsp

echo "select mac,avg(rssi) as avgrssi,timebymin from z_activesta group by mac,timebymin;" | mysql -uroot -prootatussp wlsp > activesta1.txt

echo "select mac,avg(rssi) as avgrssi,timebyten from z_activesta group by mac,timebyten;" | mysql -uroot -prootatussp wlsp > activesta2.txt

echo "select mac,avg(rssi) as avgrssi,timebysec from z_activesta group by mac,timebysec;" | mysql -uroot -prootatussp wlsp > activesta3.txt

[caiwang]

Comment by unissoft-bj Saturday Jan 24, 2015 at 07:19 GMT

---

this sql is ok:

echo "DROP TABLE IF EXISTS z_activesta;" | mysql -uroot -prootatussp wlsp echo "Create table z_activesta(select mac, rssi, rectime as timebysec, SUBSTRING(rectime,1,16) as timebymin,CONCAT(SUBSTRING(rectime,1,18),'5') as timebyten from wlpkt where rectime >= '2015-01-23 19:00:00' and rectime < '2015-01-23 19:15:00' and rssi >= '-75');" | mysql -uroot -prootatussp wlsp

echo "select mac,avg(rssi) as avgrssi,timebymin from z_activesta group by mac,timebymin;" | mysql -uroot -prootatussp wlsp > activesta1.txt

echo "select mac,avg(rssi) as avgrssi,timebyten from z_activesta group by mac,timebyten;" | mysql -uroot -prootatussp wlsp > activesta2.txt

echo "select mac,avg(rssi) as avgrssi,timebysec from z_activesta group by mac,timebysec;" | mysql -uroot -prootatussp wlsp > activesta3.txt