All, I have a number of routers which report in with various stats periodicially. This period is not a regular interval and can drift based on other factors. Each router drifts independently. The stats the routers provide need to be analyzed in terms of deltas between reports (rather than the absolute number). Therefore I need to perform a cross join to compare the rows for a given routerID (something like SELECT r1.timestamp, r2.counter1-r1.counter1 FROM router_data as r1 JOIN router_data as r2 ON (r2.recordNum = r1.recordNum + 1))
Here's an example of some raw data to give you an idea showing 3 records each from 2 devices: +----------+------------------+----------+----------+----------+ | routerID | timestamp | counter1 | counter2 | counter3 | +----------+------------------+----------+----------+----------+ | 1 | 24/08/2010 10:36 | 40 | 55 | 70 | | 2 | 24/08/2010 10:51 | 31 | 79 | 29 | | 2 | 24/08/2010 12:19 | 94 | 61 | 64 | | 1 | 24/08/2010 12:41 | 4 | 84 | 82 | | 1 | 24/08/2010 14:58 | 26 | 9 | 62 | | 2 | 24/08/2010 14:51 | 36 | 75 | 31 | +----------+------------------+----------+----------+----------+ My plan, to facilitate the cross join, was to add a per-device "record number" like follows: +----------+------------------+-----------+----------+----------+----------+ | routerID | timestamp | recordNum | counter1 | counter2 | counter3 | +----------+------------------+-----------+----------+----------+----------+ | 1 | 24/08/2010 10:36 | 1 | 40 | 55 | 70 | | 2 | 24/08/2010 10:51 | 1 | 31 | 79 | 29 | | 2 | 24/08/2010 12:19 | 2 | 94 | 61 | 64 | | 1 | 24/08/2010 12:41 | 2 | 4 | 84 | 82 | | 1 | 24/08/2010 14:58 | 3 | 26 | 9 | 62 | | 2 | 24/08/2010 14:51 | 3 | 36 | 75 | 31 | +----------+------------------+-----------+----------+----------+----------+ So here's my question, first, of course, is there a better way to perform the cross join? If not, what's the easiest way to add and increment the recordNum field? Can I do it directly in SQL somehow? Or do I need to do it in my parser? If I do it in my parser, it runs periodically (as it receives the reports) so I think it would need to figure out what record number it assigned to which device last so it would know where to restart the numbering. Should I hold that in the parser itself, or a separate table (SELECT routerID,last_used_record_num FROM last_used_record_nums; then parse, incrementing record num, then write the last ones back to that table) or by querying the main table itself (SELECT routerID, MAX(recordNum) FROM router_data GROUP BY routerID)? My only concern with the last approach is that router_data is going to get very large and that query may get very slow. TIA for any advice, Tks, -- -jp I wish everybody would have to have an electric thing implanted in our heads that gave us a shock whenever we did something to disobey the president. Then somehow I get myself elected president. deepthoughtsbyjackhandey.com
