Hi All,
I have the time-series data of NO2 in the format given below:
dt NOx
19-02-20 22:16 9.8
19-02-20 22:17 8.76
19-02-20 22:18 9.83
19-02-20 22:19 6.54
19-02-20 22:20 6.13
19-02-20 22:21 7.28
19-02-20 22:22 9. 88
19-02-20 22:23 6.31
19-02-20 22:24 9.54
19-02-20 22:25 6.48
19-02-20 22:26 6.13
19-02-20 22:27 6.63
19-02-20 22:28 7.04
19-02-20 22:29 5.43
19-02-20 22:30 6.94
19-02-20 22:31 9.96
19-02-20 22:32 8.19
19-02-20 22:33 6.5
19-02-20 22:34 9.09
19-02-20 22:35 5.17
19-02-20 22:36 5.07
19-02-20 22:37 9.03
19-02-20 22:38 7.87
19-02-20 22:39 5.26
19-02-20 22:40 8.61
19-02-20 22:41 6.94
19-02-20 22:42 7.41
19-02-20 22:43 7.85
19-02-20 22:44 8.3
19-02-20 22:45 7.76
19-02-20 22:46 6.55
19-02-20 22:47 9.64
19-02-20 22:48 6.48
19-02-20 22:49 9.76
19-02-20 22:50 6.24
19-02-20 22:51 6.63
19-02-20 22:52 7.46
19-02-20 22:53 8.56
19-02-20 22:54 9.35
19-02-20 22:55 9.88
19-02-20 22:56 7.95
19-02-20 22:57 5.22
19-02-20 22:58 7.31
19-02-20 22:59 5.47
19-02-20 23:00 5.11
19-02-20 23:01 6.47
19-02-20 23:02 6.52
19-02-20 23:03 8.12
19-02-20 23:04 6.37
19-02-20 23:05 9.84
19-02-20 23:06 5.57
19-02-20 23:07 8.06
19-02-20 23:08 9.02
19-02-20 23:09 7.51
19-02-20 23:10 7.54
19-02-20 23:11 8.41
19-02-20 23:12 5.7
19-02-20 23:13 7.24
19-02-20 23:14 9.38
19-02-20 23:15 9.43
I want is to print the average of NO2 for the past 15 minutes. The result shall look like:
1. 19/02/2020 22:30 7.51
2. 19/02/2020 22:45 7.53
3. 19/02/2020 23:00 7.44
4. 19/02/2020 23:15 7.68
Confused about whether to employ PLSQL logic or use an analytical function in SQL for this task. No idea.
The SQL to generate the data for up to a day is given below:
SELECT TO_CHAR(sysdate+(rownum/1440),'dd-mm-yy hh24:mi') ts,
ROUND(dbms_random.value(5, 10),2) val
FROM dual CONNECT BY level <=1440
ORDER BY TO_DATE(ts, 'dd-mm-yy hh24:mi') ASC;
Does anyone know how to achieve the desired results? Any idea will be appreciated.
Many Thanks and
Kind Regards,
Bilal