I am having trouble wrapping my head around how to accomplish the following:
Given a dbi result set that consists of datetime, servername, databasename, procedurename and average elapsed time.
Convert this into an data structure suitable for export to a file for use with gnuplot. The data is hourly and there may be gaps in the hours. The base problem is that I have to pivot the result sets so that for each unique occurrence of server/database/procedure I can associate the elapsed time to a reporting datetime. I think I understand what I have to feed gnuplot, ie. a file with a column for each server/database/procedure of elapsed times for each hour in the period. I believe that I want to make a hash of arrays, but I am confused as to how to pivot the data and accumulate/assign the arrays.
Here is a sample of the test data that I am using, (the full set for one application for a two week period is over 15,000 rows)
<sql_result>
report_datetime server_name database_name procedure_name avg_elapsed
10/30/2003 1:00:00.000 PM BLOGSDB_D01A_SYB BlogData001 CommentEntry_Del 0.0004
10/30/2003 3:00:00.000 PM BLOGSDB_D01A_SYB BlogData001 CommentEntry_Del 0.0020
10/30/2003 4:00:00.000 PM BLOGSDB_D01A_SYB BlogData001 CommentEntry_Del 0.0004
10/30/2003 9:00:00.000 PM BLOGSDB_D01A_SYB BlogData001 CommentEntry_Del 0.0013
10/30/2003 12:00:00.000 PM BLOGSDB_D01A_SYB BlogData001 Comment_RtrByEntry 0.0007
10/30/2003 1:00:00.000 PM BLOGSDB_D01A_SYB BlogData001 Comment_RtrByEntry 0.0007
10/30/2003 2:00:00.000 PM BLOGSDB_D01A_SYB BlogData001 Comment_RtrByEntry 0.0011
10/30/2003 3:00:00.000 PM BLOGSDB_D01A_SYB BlogData001 Comment_RtrByEntry 0.0012
10/30/2003 4:00:00.000 PM BLOGSDB_D01A_SYB BlogData001 Comment_RtrByEntry 0.0007
10/30/2003 2:00:00.000 PM BLOGSDB_D01A_SYB BlogData002 Comment_Rtr 0.0008
10/30/2003 9:00:00.000 PM BLOGSDB_D01A_SYB BlogData002 Comment_Rtr 0.0007
10/30/2003 10:00:00.000 PM BLOGSDB_D01A_SYB BlogData002 Comment_Rtr 0.0006
10/30/2003 11:00:00.000 PM BLOGSDB_D01A_SYB BlogData002 Comment_Rtr 0.0007
</sql_result>
What I am having difficulty with is how do I end up with :
<some_fancy_data_structure>
BLOGSDB_D01A_SYB BLOGSDB_D01A_SYB BLOGSDB_D01A_SYB
BlogData001 BlogData001 BlogData002
report_datetime CommentEntry_Del Comment_RtrByEntry Comment_Rtr 10/30/2003 12:00:00.000 PM 0.0013 0.0007 under
10/30/2003 1:00:00.000 PM 0.0004 0.0007 under
10/30/2003 2:00:00.000 PM undef 0.0011 0.0008
10/30/2003 3:00:00.000 PM 0.0020 0.0012 under
10/30/2003 4:00:00.000 PM 0.0004 0.0007 under
10/30/2003 9:00:00.000 PM undef undef 0.0007
10/30/2003 10:00:00.000 PM undef undef 0.0006
10/30/2003 11:00:00.000 PM undef undef 0.0007
</some_fancy_data_structure>
Ultimately, I am writing the above structure to a file so that I can take advantage of gnuplot (wish I had the luxury of GD or Chart, but system calls will have to do). I think I have to parse the data twice, once to determine the names of all the nodes and then again to populate each node. Is there a single pass solution ?
<groveling>
Thanks in advance, this is a wonderful list, I have over 500 tips saved for the 3 or 4 months that I have been listening in.
</groveling>
Chuck Fox
-- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
