I have the following table definition for time series data: ID (int) time (DATETIME) state (int) value (int)
I want to make a state based grouping and calculate the mean of each grouping. The state based grouping should be done by creating a new group whenever the state changes, from one point in time to another. To explain what I mean I have made a small example: ID time state value 1 2004-01-01 00:00 0 5 2 2004-01-01 00:02 0 3 3 2004-01-01 00:04 1 7 4 2004-01-01 00:07 1 9 5 2004-01-01 00:08 1 2 6 2004-01-01 00:10 0 2 7 2004-01-01 00:12 0 1 8 2004-01-01 00:13 2 2 9 2004-01-01 00:14 2 4 10 2004-01-01 00:15 2 2 The grouping for the above table would then be: (1,2); (3,4,5); (6,7); (8,9,10), as the state changed at ID 3, 6, and 8. How do express a select statement in SQL which gives me the mean of each group? Greetings, Mads Lindstrøm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
