mysql - Complex SQL query suggestions please -
i have 3 tables schema below:
table: apps
| id (bigint) | userid (bigint)| start_time (datetime) | ------------------------------------------------------------- | 1 | 13 | 2013-05-03 04:42:55 | | 2 | 13 | 2013-05-12 06:22:45 | | 3 | 13 | 2013-06-12 08:44:24 | | 4 | 13 | 2013-06-24 04:20:56 | | 5 | 13 | 2013-06-26 08:20:26 | | 6 | 13 | 2013-09-12 05:48:27 | table: hosts
| id (bigint) | appid (bigint)| device_id (bigint) | ------------------------------------------------------------- | 1 | 1 | 1 | | 2 | 2 | 1 | | 3 | 1 | 1 | | 4 | 3 | 3 | | 5 | 1 | 4 | | 6 | 2 | 3 | table: usage
| id (bigint) | appid (bigint)| hostid (bigint) | factor (varchar) | ------------------------------------------------------------------------------------- | 1 | 1 | 1 | low | | 2 | 1 | 3 | high | | 3 | 2 | 2 | low | | 4 | 3 | 4 | medium | | 5 | 1 | 5 | low | | 6 | 2 | 2 | medium | now if put userid, want count of rows of table rows each month (of app) each "factor" month wise last 6 months.
if device_id appears more once in month (based on start_time, based on joining apps , hosts), latest rows of usage (based on combination of apps, hosts , usage) considered calculating count.
example output of query above example should be: (for input user id=13)
| month | usage_count | factor | ------------------------------------------------------------- | 5 | 0 | high | | 6 | 0 | high | | 7 | 0 | high | | 8 | 0 | high | | 9 | 0 | high | | 10 | 0 | high | | 5 | 2 | low | | 6 | 0 | low | | 7 | 0 | low | | 8 | 0 | low | | 9 | 0 | low | | 10 | 0 | low | | 5 | 1 | medium | | 6 | 1 | medium | | 7 | 0 | medium | | 8 | 0 | medium | | 9 | 0 | medium | | 10 | 0 | medium | how calculated?
- for month may 2013 (05-2013), there 2 apps table apps
- in table hosts , these apps associated device_id's 1,1,1,4,3
- for month (05-2013) device_id=1, latest value of start_time is: 2013-05-12 06:22:45 (from tables hosts,apps), in table usage, combination of appid=2&hostid=2 there 2 rows 1 factor low , other medium,
- for month (05-2013) device_id=4, following same procedure 1 entry i.e 0 low
- similarly values calculated.
to last 6 months via query i'm trying following:
select month(date_add(now(), interval aint month)) amonth ( select 0 aint union select -1 union select -2 union select -3 union select -4 union select -5 ) please check sqlfiddle: http://sqlfiddle.com/#!2/55fc2
because calculation you're doing involves same join multiple times, started creating view.
create view `app_host_usage` select a.id "appid", h.id "hostid", u.id "usageid", a.userid, a.start_time, h.device_id, u.factor apps left outer join hosts h on h.appid = a.id left outer join `usage` u on u.appid = a.id , u.hostid = h.id a.start_time > date_add(now(), interval -7 month) the where condition there because made assumption don't want july 2005 , july 2006 grouped in same count.
with view in place, query becomes
select months.month, count(distinct device_id), factors.factor ( -- last 6 months select (month(now()) + aint + 11) % 12 + 1 "month" (select 0 aint union select -1 union select -2 union select -3 union select -4 union select -5) lastsix ) months join ( -- known factors select distinct factor `usage` ) factors left outer join ( -- factors each device... select month(start_time) "month", device_id, factor app_host_usage userid=13 , start_time in ( -- ...where corresponding usage row connected -- app row highest start time of -- month device. select max(start_time) app_host_usage a2 a2.device_id = a.device_id group month(start_time) ) group month(start_time), device_id, factor ) usageids on usageids.month = months.month , usageids.factor = factors.factor group factors.factor, months.month order factors.factor, months.month which insanely complicated, i've tried comment explaining each part does. see sqlfiddle: http://sqlfiddle.com/#!2/5c871/1/0
Comments
Post a Comment