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 ----------- RID Event EvDate_Time Duration ifCalc'd 31911 EventType1 2001-09-01 01:12:20 09:53:05 11:05:25 x \ 31912 EventType1 2001-09-01 10:12:40 05:56:39 16:09:19 x -- Conc= 3 31913 EventType1 2001-09-01 11:08:05 05:53:36 17:01:41 x / 31914 EventType1 2001-09-04 00:01:42 01:11:09 01:12:51 x - Conc= 2 31915 EventType1 2001-09-04 01:12:51 22:48:12 24:01:03 x / 31916 EventType1 2001-09-05 00:01:03 01:11:32 01:12:35 -- Conc= 1 31917 EventType1 2001-09-05 01:12:36 22:48:46 24:01:22 / 31918 EventType1 2001-09-06 00:01:23 01:11:02 01:12:25 x - Conc= 2 31919 EventType1 2001-09-06 01:12:28 07:27:36 08:40:03 x / 31920 EventType1 2001-09-06 09:45:32 00:00:16 09:45:48 - -- n+1... 33111 EventType42 2001-09-01 00:12:20 08:53:05 11:05:25 -- Conc= 1 33112 EventType42 2001-09-01 11:12:40 05:56:39 17:09:19 / 33113 EventType42 2001-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 Events MAXConc AveConc TotalDuration EventType1 10 3 1.14 78:21:53 ... other eventtypes rows ...... EventType42 3 1 0.29 20: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
