Time Analyzed: 2004-09-01 to 2004-11-30
Query to check the most active users within this time period:
mysql> select person_oid,count(distinct device_oid) as count from devicespan where device_oid in (select oid from device where person_oid>0) and (starttime > ‘2004-09-07 00:00:00′ and endtime < ‘2005-05-04 24::00′) group by person_oid order by count desc limit 10;
+————+——-+
| person_oid | count |
+————+——-+
| 29 | 101 |
| 57 | 97 |
| 86 | 95 |
| 39 | 95 |
| 43 | 93 |
| 96 | 93 |
| 95 | 92 |
| 14 | 87 |
| 16 | 84 |
| 59 | 82 |
+————+——-+
10 rows in set, 1 warning (1.87 sec)
Now we will just check these 10 users.
All Mondays in the Time period:
2004-09-06
2004-09-13
2004-09-20
2004-09-27
2004-10-04
2004-10-11
2004-10-18
2004-10-25
2004-11-01
2004-11-08
2004-11-15
2004-11-22
2004-11-29
2004-12-06
2004-12-13
2004-12-20
2004-12-27
All Tuesdays in the time period:
2004-09-07
2004-09-14
2004-09-21
2004-09-28
2004-10-05
2004-10-12
2004-10-19
2004-10-26
2004-11-02
2004-11-09
2004-11-16
2004-11-23
2004-11-30
2004-12-07
2004-12-14
2004-12-21
2004-12-28
All Wednesday in the time period:
2004-09-08
2004-09-15
2004-09-22
2004-09-29
2004-10-06
2004-10-13
2004-10-20
2004-10-27
2004-11-03
2004-11-10
2004-11-17
2004-11-24
2004-12-01
2004-12-08
2004-12-15
2004-12-22
2004-12-29
Query to check the contact in date and time:
select person_oid,device_oid, starttime,endtime from devicespan where person_oid = 29 and device_oid in (select oid from device where person_oid>0) and (starttime > ‘2004-09-13 06:00:00′ and endtime < ‘2004-09-13 12:00:00′);
Find the core nodes: People who are interacting the most:
mysql> SELECT person_oid, device_oid, COUNT(*) tot FROM devicespan where device_oid in (select oid from device where person_oid>0)and (starttime > ‘2004-09-01 00:00:00′ and endtime < ‘2005-05-30 24:00:00′) GROUP BY person_oid, device_oid order by tot desc limit 10;
+————+————+—–+
| person_oid | device_oid | tot |
+————+————+—–+
| 94 | 15 | 584 |
| 57 | 91 | 543 |
| 57 | 30 | 509 |
| 39 | 102 | 483 |
| 57 | 102 | 462 |
| 86 | 61 | 445 |
| 14 | 102 | 412 |
| 39 | 30 | 405 |
| 95 | 30 | 385 |
| 29 | 61 | 383 |
+————+————+—–+
10 rows in set, 1 warning (1.97 sec)
Some Useful queries:
select WEEKDAY(starttime), SUBTIME(time(starttime),time(endtime)) from devicespan limit 200;
Weekday = number of the weekday.
Time = to convert date-time format to time only.
Subtime = subtract the two times.
Create a view for devisespan table with weekdays and time of inetraction:
create view data_view as select person_oid, device_oid, starttime, endtime, WEEKDAY(starttime) weekday, SUBTIME(time(endtime),time(starttime)) as timediff from devicespan where time(starttime) > ‘06:00:00′ and time(starttime) 0)and (starttime > ‘2004-09-01 00:00:00′ and endtime < ‘2004-12-30 24:00:00′) GROUP BY person_oid, device_oid order by tot;
Filed under: Uncategorized
