count at each change in value

[izamu]

I have a million records or so I need to count how many consecutive records have the same value. At each change in value I initiate the count from one again. this summary will be used to perform statistical analysis down the road.
The output of this excercise will be a summary that tells me count at each tempreture but without sorting the raw data because the time and date of tempreture reading is importat, so just to be clear I can't sort and then do the counting that would have been too easy.

I am using Python to read the data from the source, I am new to python too, should this be done in python rather than sql and how if it did, but I will be happy with an sql answer!

thanks for the help!

[Luuk]

suppose i have this:

mysql> select wanneer, temp_c from weer where waar='Amersfoort,NL' order by wanneer limit 20;
+---------------------+--------+
| wanneer             | temp_c |
+---------------------+--------+
| 2011-05-07 16:26:14 |     26 |
| 2011-05-07 16:28:55 |     26 |
| 2011-05-07 16:33:04 |     26 |
| 2011-05-07 17:00:01 |     26 |
| 2011-05-07 18:00:01 |     27 |
| 2011-05-07 19:00:02 |     28 |
| 2011-05-07 20:00:02 |     28 |
| 2011-05-07 21:00:01 |     27 |
| 2011-05-07 22:00:02 |     26 |
| 2011-05-07 23:00:02 |     22 |
| 2011-05-08 00:00:01 |     20 |
| 2011-05-08 01:00:01 |     15 |
| 2011-05-08 02:00:02 |     16 |
| 2011-05-08 03:00:02 |     18 |
| 2011-05-08 04:00:02 |     18 |
| 2011-05-08 05:00:02 |     18 |
| 2011-05-08 06:00:01 |     17 |
| 2011-05-08 07:00:02 |     17 |
| 2011-05-08 08:00:02 |     16 |
| 2011-05-08 09:00:01 |     16 |
+---------------------+--------+
20 rows in set (0.00 sec)

and this procedure

BEGIN
	#Routine body goes here...
  DECLARE no_more_rows INT DEFAULT 0;
	DECLARE prev_temp INTEGER DEFAULT 0;
	DECLARE curr_temp INTEGER DEFAULT 0;
	DECLARE curr_when DATETIME;
	DECLARE prev_when DATETIME;
  DECLARE max_val INTEGER DEFAULT 99999;
  DECLARE num_rows INTEGER DEFAULT 0;
	DECLARE test_cur CURSOR FOR
		select wanneer, temp_c from weer where waar='Amersfoort,NL' order by wanneer;

  DROP TABLE IF EXISTS test456;
  CREATE TEMPORARY TABLE test456 (i INTEGER PRIMARY KEY AUTO_INCREMENT, 
																					wanneer datetime, x INTEGER);

  OPEN test_cur;
  select FOUND_ROWS() into num_rows;
  FETCH test_cur INTO curr_when, curr_temp;
  SET prev_when = curr_when;
  SET prev_temp = curr_temp;

	loop1: LOOP

		FETCH test_cur INTO curr_when, curr_temp;

		IF no_more_rows THEN
			LEAVE loop1;
		END IF; 
--    SELECT 'test', text_val, length_val, max_val;
		IF prev_temp <> curr_temp THEN
			   INSERT INTO test456 (wanneer, x) VALUES (prev_when, prev_temp);
         SET prev_temp = curr_temp;
         SET prev_when = curr_when;
	  END IF;
		SET num_rows = num_rows - 1;

	END LOOP loop1;
  CLOSE test_cur;
  SELECT * from test456;
END

after running this (with 'CALL test2_test')

mysql> select * from test456 limit 20;
+----+---------------------+------+
| i  | wanneer             | x    |
+----+---------------------+------+
|  1 | 2011-05-07 16:26:14 |   26 |
|  2 | 2011-05-07 18:00:01 |   27 |
|  3 | 2011-05-07 19:00:02 |   28 |
|  4 | 2011-05-07 21:00:01 |   27 |
|  5 | 2011-05-07 22:00:02 |   26 |
|  6 | 2011-05-07 23:00:02 |   22 |
|  7 | 2011-05-08 00:00:01 |   20 |
|  8 | 2011-05-08 01:00:01 |   15 |
|  9 | 2011-05-08 02:00:02 |   16 |
| 10 | 2011-05-08 03:00:02 |   18 |
| 11 | 2011-05-08 06:00:01 |   17 |
| 12 | 2011-05-08 08:00:02 |   16 |
| 13 | 2011-05-08 10:00:02 |   17 |
| 14 | 2011-05-08 11:00:02 |   18 |
| 15 | 2011-05-08 12:00:02 |   20 |
| 16 | 2011-05-08 13:00:01 |   23 |
| 17 | 2011-05-08 14:00:02 |   24 |
| 18 | 2011-05-08 15:00:02 |   25 |
| 19 | 2011-05-08 16:00:01 |   27 |
| 20 | 2011-05-08 21:00:01 |   25 |
+----+---------------------+------+
20 rows in set (0.00 sec)

I stored the results in this temporary table, but you can, ouf course, change that to a normal table ;)

[izamu]

Thanks Luuk, didn't see the reply until today