473,326 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

count at each change in value

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
2 2508
Luuk
1,047 Expert 1GB
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
izamu
3
Thanks Luuk, didn't see the reply until today
Dec 13 '12 #3

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

Similar topics

1
by: Tim Begin | last post by:
I am attempting to use the ThreadPool.SetMinThreads method as shown in the MSDN example code: int minWorker, minIOPort; int newWorker,newIOPort; ThreadPool.GetAvailableThreads(out minWorker, out...
3
by: John Smith | last post by:
I'm looking into this peace of code: protected void DropDown_SelectedIndexChanged(object sender, EventArgs e) { DropDownList list = (DropDownList)sender; TableCell cell = list.Parent as...
2
by: John Smith | last post by:
Will this line of the code: item.Cells.Text = "Some text..."; change only DataGrid visual value or it will also change value in the DataSource? How can I change value in DataSource? ...
0
by: marknoten | last post by:
Hi, we are working with XML files that can be as big as 6Mb. Some XML documents that obey to a certain condition must be duplicated where only 2 elements need to change value. I thought using...
1
by: PawelR | last post by:
Hi Group, In my application I have DataTable which is displayed in DataGridView via DataView: DataView myView = new DataView(myTable); myDataGridView.DataSource = myView; One column im...
3
by: bharathi228 | last post by:
hi, iam doing one asp.net application in gridview i have 3 columns. the first two column values are coming from database. the third column is template field.here i have one...
1
by: Jafri256best | last post by:
I want to change value of a variable permanently during executing a form and want to enter the value through the text box.
4
by: mikael3432 | last post by:
hey. I wonder if anyone can help me with this one: I want to change value 21 to 210 in colum A, given there is another row that has value 10 in colum A and in both rows values in colum B, C D are...
0
by: wasim jack | last post by:
sir,I want to change value of combobox of datagridview on the basis of previous combobox value of the same raw of same datagridview
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.