I have a table that consist of multiple data of same ID with different-different time stamp(each interval of 6 minutes) in one column, and its redcorded temperature at each given time stamp.

| ID | Time_Stamp |Temperature_1|Temperature_2 |
|----------|---------------------|-------------|---------------|
| 101 | 18-09-2020 17:05:40 | 98.50 | 87.63|
| 101 | 18-09-2020 17:11:40 | 96.60 | 46.3|
| 101 | 18-09-2020 17:17:40 | 80.50 | 65.30|
| 101 | 18-09-2020 17:23:40 | 65.30 | 77.21|
| 101 | 18-09-2020 17:29:40 | 36.20 | 63.30|
| 101 | 18-09-2020 17:35:40 | 69.30 | 54.70|


..... up to 614 rows

Output should be:

| ID | Time_Stamp |Avg_Temperature_1| Avg_Temperature_2 |
|----------|---------------------|-----------------|----------------|
| 101 | 18-09-2020 17:29:40 | 98.50 | 87.63|
| 101 | 18-09-2020 18:29:40 | 96.60 | 46.3|
| 101 | 18-09-2020 19:29:40 | 80.50 | 65.30|

..... up to 61 rows

Elaboration:

Lets assume it has 614 rows.
i have to first search all the data in ascending manner(by time_stamp) (e.g `select * from table where id=101 order by time_stamp asc`).

Now, I have 614 row of that data.but I have to consider only nearest 10 data. e.g if here 614 rows then i have to consider only 610 data. similarly if i will have 219 data, then i have to consider only 210 data (if 220 then i have to consider 220 data), if 155 then 150, if 314 then 310 data and so on..

so after considering 610 row i have to divide it by 10. so that in my final o/p i will have only 61 rows .(each of 10-10 set)
Also note that if i am taking 10-10 set then i will have each row showing avg of each hour in my final o/p)

how? (the data has came at interval of every 6-6 minute, so if i take 10 data together then it will have data of each 1-1 hour(6*10=60 min) representing by each row).

so finally i have to take set of 10-10 row and find the average of each temp column and represent it as a single row.
Note that in time_stamp column we can take any mid value of 10 set,either 4th one,5th one or 6th one.
And in Temp1 column it should be avg of 10 row.

i have to show the avg temp data of each 1-1 hour interval time or for 10-10 set of rows.

How to write sql query for this?

What i tried so far is as below:

For this i thought to write a store procedure

step 1:- starting i will fetch all data and floor(cound(id)) value
by:-
select * from table WHERE id =1 order by Time_Stamp asc
and then,
select floor(count(id)/10) from table_name WHERE id=1 (for deciding num of time loop should execute)
here it will give 61

Step 2:-looping on upto n times (here 61 times).
And within each loop i suppose limit upto 10 rows and take avg of temperature and all.
In each loop:-finding the avg of column w.r.t id.( but i am unable to include time stamp)

i use below for finding the avg with respect to id of first 10 data by:-
```
select id,avg(Temperature_1) as TempAVG1,avg(Temperature_2) as TempAVG2
from table_name
where Time_stamp>=TO_CHAR('18-09-2020 17:05') and Time_stamp<=TO_CHAR('18-09-2020 18:05:40') and id=101 group by id
```

here unable to include the time stamp(4,5 or 6th one of 10 set)

so for that i tried to write another query for finding only time stamp and willing to do union with first query,but i am unable to union both query because avg column and time column have diff data types(also all columns are not same)

Also cannot think how to left last odd rows ( e.g if lastly if there is only1 to 9 rows left)

Please provide another efficient way if possible to write query for this or try to help me to write this Store procedure
Or else if it is/can be mixing of query and C# code (e.g., using datatable and all) then also its welcome.

Technology i am using C#,Oracle db

count(*) over () 

ntile ( 10 ) over ( order by ... ) 

nth_value ( val, N ) over ( 
  partition by ... order by ..
)

with rws as (
  select level id,
         systimestamp + numtodsinterval ( level, 'minute' ) ts,
         round ( dbms_random.value ( 1, 10 ), 1 ) val,
         row_number () over ( order by level ) rn,
         trunc ( count(*) over () / 10 ) * 10 num_rows
  from   dual
  connect by level <= 615
), grps as (
  select r.*, 
         ntile ( 10 ) over ( order by ts ) grp
  from   rws r
  where  rn <= num_rows
), ns as (
  select nth_value ( ts, 5 ) over ( 
           partition by grp order by ts 
         ) nth,
         g.*
  from   grps g
)
  select min ( nth ), avg ( val )
  from   ns
  group  by grp;
  
MIN(NTH)                                 AVG(VAL)                                   
02-AUG-2021 18.03.06.424096000 +00       5.27868852459016393442622950819672131148 
02-AUG-2021 12.58.06.424096000 +00       4.99508196721311475409836065573770491803 
02-AUG-2021 19.04.06.424096000 +00       5.61803278688524590163934426229508196721 
02-AUG-2021 13.59.06.424096000 +00       5.76721311475409836065573770491803278689 
02-AUG-2021 20.05.06.424096000 +00       5.11803278688524590163934426229508196721 
02-AUG-2021 16.01.06.424096000 +00       5.33442622950819672131147540983606557377 
02-AUG-2021 17.02.06.424096000 +00       5.00983606557377049180327868852459016393 
02-AUG-2021 22.07.06.424096000 +00       5.58196721311475409836065573770491803279 
02-AUG-2021 15.00.06.424096000 +00       5.15081967213114754098360655737704918033 
02-AUG-2021 21.06.06.424096000 +00       5.30983606557377049180327868852459016393