Add "record number" to timestamped router data to facilitate cross join

--0003255762de4330740491909ff0
Content-Type: text/plain; charset=ISO-8859-1

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

--0003255762de4330740491909ff0--

Re: Add "record number" to timestamped router data to facilitatecross join

--0015176f0f34ec80e30491e138a6
Content-Type: text/plain; charset=ISO-8859-1

On Fri, Oct 1, 2010 at 12:24 PM, Jake Peavy wrote:

> 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,
>


Hey all,

Anyone have any thoughts/advice on the best way to manage this "record
number"? What about a stored procedure? Or use of variables?

TIA

--
-jp

If you're traveling in a time machine, and you're eating corn on the cob, I
don't think it's going to affect things one way or the other. But here's the
point I'm trying to make: Corn on the cob is good, isn't it?

deepthoughtsbyjackhandey.com

--0015176f0f34ec80e30491e138a6--

RE: Add "record number" to timestamped router data to facilitate cross join

I don't think I'd seriously consider the max() option, especially if you're
expecting the table to grow large. Using a table to store the next record
number (your last_used_record_nums table) is a technique that is fairly
commonly used. You can increment and retrieve the value atomically if you
use the LAST_INSERT_ID():

insert into last_used_record_nums(routerid, recordnum)
values (####, last_insert_id(1))
on duplicate key update recordnum = last_insert_id(recordnum + 1);

then

select last_insert_id();

to get the incremented value. You could probably wrap this in an INSERT
trigger on your stats table so your application wouldn't need to worry about
it.

-Travis

-----Original Message-----
From: Jake Peavy [mailto:djstunks@gmail.com]
Sent: Tuesday, October 05, 2010 10:34 AM
To: MySQL General Mailing List
Subject: Re: Add "record number" to timestamped router data to facilitate
cross join

On Fri, Oct 1, 2010 at 12:24 PM, Jake Peavy wrote:

> 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,
>


Hey all,

Anyone have any thoughts/advice on the best way to manage this "record
number"? What about a stored procedure? Or use of variables?

TIA

--
-jp

If you're traveling in a time machine, and you're eating corn on the cob, I
don't think it's going to affect things one way or the other. But here's the
point I'm trying to make: Corn on the cob is good, isn't it?

deepthoughtsbyjackhandey.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org