Complex query, grouping and counting challenge
Very complex Grouping and counting challenge Can anyone offer guidance and suggest SQL which will assist in resolving this complex and challenging (for me) issue? I hope that my attempt to clearly state this problem is successful. The Environment: Queries and presentation using PHP Version 4.0.4pl1, have an MySQL 3.23.33 dataset with the following columns of interest. Due to the number of records (literally 100s of thousands which must be calculated) I think that the more that can be done in the query using the MySQL engine the better. Using array_walk() or stepping through row by row of thousands of records takes too long! The Problem: Running a simple query against the underlying data for EvDate between 2001-09-01 and 2001-09-07 (7 day period used in AveConc) will return a dataset from db rows similar to those in the table A. In the query they are ordered using EvDate so that the earliest hour/start time will be in order. I have shown ifCalc'd and the Conc to show what needs to process. (Conc means concurrent) Using Table A as an example of the data, indicates that an event, RID 31911 starting at 01:12:20 on the 1st of Sep occurred for a Duration of 9 hrs, 53 mins and 25 seconds. If one were to add the duration to the EvDate_Time the ifCalc'd or end time would be 11:05:25. The event in RID 31912 starts later then RID 31911, and occurs during the window when 31911 is occurring. Likewise, RID 31913 has a period of concurrent time with both 31911 and 31912. The number of events which are occuring concurrently is 3. Looking at the remaining rows shows that RID 31915 on the 4th is concurrent with 31914 as the start time for 31915 is less then or equal and therefore concurrent with the ifCalc'd end time of 31914. Neither 31916 or 31917 overlap so the conc for those records is 1, even though 2 events occured. 31918, 31919 and 31920 have concurrency for 31918 and 31919 so the concurrency for that date is 2, even though 3 events occured. Looking at the remaining rows should look similar and familiar to those above. The AveConc uses the MAXConc on each day divided by number of days in the original request. In this example it was 7 days, 9/1 - 9/6, but could have been 3 to X. Table A --- RIDEvent EvDate_Time Duration ifCalc'd 31911 EventType12001-09-01 01:12:20 09:53:05 11:05:25 x \ 31912 EventType12001-09-01 10:12:40 05:56:39 16:09:19 x -- Conc= 3 31913 EventType12001-09-01 11:08:05 05:53:36 17:01:41 x / 31914 EventType12001-09-04 00:01:42 01:11:09 01:12:51 x - Conc= 2 31915 EventType12001-09-04 01:12:51 22:48:12 24:01:03 x / 31916 EventType12001-09-05 00:01:03 01:11:32 01:12:35 -- Conc= 1 31917 EventType12001-09-05 01:12:36 22:48:46 24:01:22 / 31918 EventType12001-09-06 00:01:23 01:11:02 01:12:25 x - Conc= 2 31919 EventType12001-09-06 01:12:28 07:27:36 08:40:03 x / 31920 EventType12001-09-06 09:45:32 00:00:16 09:45:48 - -- n+1... 33111 EventType422001-09-01 00:12:20 08:53:05 11:05:25 -- Conc= 1 33112 EventType422001-09-01 11:12:40 05:56:39 17:09:19 / 33113 EventType422001-09-03 18:08:05 05:53:36 24:01:41 - Conc= 1 N+1 to end of data set Desired output results to be returned and processed as an HTML table. Table B Event EventsMAXConc AveConcTotalDuration EventType110 3 1.1478:21:53 ... other eventtypes rows .. EventType423 1 0.2920:43:20 = Guidance or code example gratefully appreciated. Thoughts or suggestions equally welcomed. Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Any help? SUM of type TIME in Query?
Have MySQL db with hundreds of thousands of event records from geographically dispersed logging devices. The logs are batched and auto loaded/parsed into MySQL on a daily/weekly basis depending on external factors. As an example, during a 7 period, one device provided 128,000 discrete events of 220 Types. The columns of primary interest are shown here. Col4 is generated by the logging devices as a duration/elapsed time in HH:MM:SS format and is the column of interest in this problem. An abbreviated table of the columns (ordered and numbered for example only) and records looks like; Col1(Char), Col2 (DATE),Col4(TIME) EventType1, -MM-DD HH:MM:SS, 09:53:05 EventType1, -MM-DD HH:MM:SS, 05:56:39 EventType1, -MM-DD HH:MM:SS, 05:53:36 EventType1, -MM-DD HH:MM:SS, 01:11:09 EventType1, -MM-DD HH:MM:SS, 22:48:12 EventType2, -MM-DD HH:MM:SS, 01:11:32 EventType2, -MM-DD HH:MM:SS, 22:48:46 ... etc. n+1 The shell query is: select cols, count(xy), (sum(col4)) as t_dura from db where xyz group by Col1 order by col1 The results using the above as an example would be: Col1 Char, xy, t_dura EventType1, 5, 442201 EventType2, 2, 235978 ... ... n+1 Can anyone provide insight into the SQL syntax to perform the sum and return the results as HH:MM:SS? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
