By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,154 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,154 IT Pros & Developers. It's quick & easy.

count at each change in value

P: 3
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!
Feb 21 '12 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 1,035
suppose i have this:
Expand|Select|Wrap|Line Numbers
  1. mysql> select wanneer, temp_c from weer where waar='Amersfoort,NL' order by wanneer limit 20;
  2. +---------------------+--------+
  3. | wanneer             | temp_c |
  4. +---------------------+--------+
  5. | 2011-05-07 16:26:14 |     26 |
  6. | 2011-05-07 16:28:55 |     26 |
  7. | 2011-05-07 16:33:04 |     26 |
  8. | 2011-05-07 17:00:01 |     26 |
  9. | 2011-05-07 18:00:01 |     27 |
  10. | 2011-05-07 19:00:02 |     28 |
  11. | 2011-05-07 20:00:02 |     28 |
  12. | 2011-05-07 21:00:01 |     27 |
  13. | 2011-05-07 22:00:02 |     26 |
  14. | 2011-05-07 23:00:02 |     22 |
  15. | 2011-05-08 00:00:01 |     20 |
  16. | 2011-05-08 01:00:01 |     15 |
  17. | 2011-05-08 02:00:02 |     16 |
  18. | 2011-05-08 03:00:02 |     18 |
  19. | 2011-05-08 04:00:02 |     18 |
  20. | 2011-05-08 05:00:02 |     18 |
  21. | 2011-05-08 06:00:01 |     17 |
  22. | 2011-05-08 07:00:02 |     17 |
  23. | 2011-05-08 08:00:02 |     16 |
  24. | 2011-05-08 09:00:01 |     16 |
  25. +---------------------+--------+
  26. 20 rows in set (0.00 sec)
  27.  
and this procedure
Expand|Select|Wrap|Line Numbers
  1. BEGIN
  2.     #Routine body goes here...
  3.   DECLARE no_more_rows INT DEFAULT 0;
  4.     DECLARE prev_temp INTEGER DEFAULT 0;
  5.     DECLARE curr_temp INTEGER DEFAULT 0;
  6.     DECLARE curr_when DATETIME;
  7.     DECLARE prev_when DATETIME;
  8.   DECLARE max_val INTEGER DEFAULT 99999;
  9.   DECLARE num_rows INTEGER DEFAULT 0;
  10.     DECLARE test_cur CURSOR FOR
  11.         select wanneer, temp_c from weer where waar='Amersfoort,NL' order by wanneer;
  12.  
  13.   DROP TABLE IF EXISTS test456;
  14.   CREATE TEMPORARY TABLE test456 (i INTEGER PRIMARY KEY AUTO_INCREMENT, 
  15.                                                                                     wanneer datetime, x INTEGER);
  16.  
  17.   OPEN test_cur;
  18.   select FOUND_ROWS() into num_rows;
  19.   FETCH test_cur INTO curr_when, curr_temp;
  20.   SET prev_when = curr_when;
  21.   SET prev_temp = curr_temp;
  22.  
  23.     loop1: LOOP
  24.  
  25.         FETCH test_cur INTO curr_when, curr_temp;
  26.  
  27.         IF no_more_rows THEN
  28.             LEAVE loop1;
  29.         END IF; 
  30. --    SELECT 'test', text_val, length_val, max_val;
  31.         IF prev_temp <> curr_temp THEN
  32.                INSERT INTO test456 (wanneer, x) VALUES (prev_when, prev_temp);
  33.          SET prev_temp = curr_temp;
  34.          SET prev_when = curr_when;
  35.       END IF;
  36.         SET num_rows = num_rows - 1;
  37.  
  38.     END LOOP loop1;
  39.   CLOSE test_cur;
  40.   SELECT * from test456;
  41. END
after running this (with 'CALL test2_test')
Expand|Select|Wrap|Line Numbers
  1. mysql> select * from test456 limit 20;
  2. +----+---------------------+------+
  3. | i  | wanneer             | x    |
  4. +----+---------------------+------+
  5. |  1 | 2011-05-07 16:26:14 |   26 |
  6. |  2 | 2011-05-07 18:00:01 |   27 |
  7. |  3 | 2011-05-07 19:00:02 |   28 |
  8. |  4 | 2011-05-07 21:00:01 |   27 |
  9. |  5 | 2011-05-07 22:00:02 |   26 |
  10. |  6 | 2011-05-07 23:00:02 |   22 |
  11. |  7 | 2011-05-08 00:00:01 |   20 |
  12. |  8 | 2011-05-08 01:00:01 |   15 |
  13. |  9 | 2011-05-08 02:00:02 |   16 |
  14. | 10 | 2011-05-08 03:00:02 |   18 |
  15. | 11 | 2011-05-08 06:00:01 |   17 |
  16. | 12 | 2011-05-08 08:00:02 |   16 |
  17. | 13 | 2011-05-08 10:00:02 |   17 |
  18. | 14 | 2011-05-08 11:00:02 |   18 |
  19. | 15 | 2011-05-08 12:00:02 |   20 |
  20. | 16 | 2011-05-08 13:00:01 |   23 |
  21. | 17 | 2011-05-08 14:00:02 |   24 |
  22. | 18 | 2011-05-08 15:00:02 |   25 |
  23. | 19 | 2011-05-08 16:00:01 |   27 |
  24. | 20 | 2011-05-08 21:00:01 |   25 |
  25. +----+---------------------+------+
  26. 20 rows in set (0.00 sec)
  27.  
I stored the results in this temporary table, but you can, ouf course, change that to a normal table ;)
Apr 7 '12 #2

P: 3
Thanks Luuk, didn't see the reply until today
Dec 13 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.