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!
2 2508 Luuk 1,047
Expert 1GB
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 ;)
Thanks Luuk, didn't see the reply until today
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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?
...
|
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...
|
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...
|
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...
|
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.
|
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...
|
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
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
| |