I want a SQL query for the following. I am a novice in SQL. And below table is just an example of the kind of data I have. I have very big data of about 30 million rows and would like to write a query to get the below output table.
Id type data time
-----------------------------------------------------------
1 30 3.9 15:50:10.660555
1 30 4.0 15:50:10.660777
1 70 11.5 15:50:10.797966
1 30 4.1 15:50:10.834444
1 70 12.6 15:50:10.853114
1 70 16.7 15:50:10.955086
1 30 5 15:50:10.99
11 30 3.8 15:50:11.660555
11 30 4.1 15:50:11.660777
11 70 12.5 15:50:11.797966
11 30 4.7 15:50:11.834444
11 70 12.68 15:50:11.853114
11 70 16.76 15:50:11.955086
11 30 5.1 15:50:11.99
I have a table like the above. For each type 70, I need to calculate something with the last known type 30. For instance, for Id = 1, for the first type = 70 data at 15:50:10.797966, I need to get type = 30 data at 15:50:10.660777 so that I can calculate result = 11.5/4.0. Similarly, for type = 70 at 15:50:10.853114, I want data of type = 30 at 15:50:10.834444, so my result = 12.6/4.1.
I want the output to look like this:
Id type result time
------------------------------------------------------
1 70 11.5/4.0 15:50:10.797966
1 70 12.6/4.1 15:50:10.853114
1 70 16.7/4.1 15:50:10.955086
11 70 12.5/4.1 15:50:11.797966
11 70 12.68/4.7 15:50:11.853114
11 70 16.76/4.7 15:50:11.955086
I would like to be able to execute these SQL queries in python using pyodbc.
Any help would be greatly appreciated! Thanks in advance!!