StackOverflow 48405262

View All Public Scripts Login to Run Script

Script Name StackOverflow 48405262
Visibility Unlisted - anyone with the share link can access
Description StackOverflow 48405262
Contributor martynt0@googlemail.com
Created Tuesday January 23, 2018

Statement 1

CREATE TABLE my_data ( the_date, value ) AS 
  SELECT TRUNC(SYSDATE, 'HH') - INTERVAL '1' HOUR * LEVEL, 
         ROUND(DBMS_RANDOM.value(0,100),0) 
  FROM   DUAL  
  CONNECT BY ROWNUM < 366*24

Table created.

Statement 2

SELECT TRUNC(the_date, 'DD') as my_day, MAX(value) AS value  
FROM   my_data  
GROUP by TRUNC(the_date, 'DD') 
ORDER BY 1


MY_DAY VALUE
22-JAN-17 92
23-JAN-17 100
24-JAN-17 96
25-JAN-17 92
26-JAN-17 97
27-JAN-17 93
28-JAN-17 92
29-JAN-17 81
30-JAN-17 100
31-JAN-17 94
01-FEB-17 93
02-FEB-17 99
03-FEB-17 93
04-FEB-17 99
05-FEB-17 84
06-FEB-17 100
07-FEB-17 89
08-FEB-17 97
09-FEB-17 99
10-FEB-17 94
11-FEB-17 99
12-FEB-17 99
13-FEB-17 99
14-FEB-17 90
15-FEB-17 99
16-FEB-17 97
17-FEB-17 95
18-FEB-17 99
19-FEB-17 100
20-FEB-17 95
21-FEB-17 96
22-FEB-17 92
23-FEB-17 99
24-FEB-17 97
25-FEB-17 98
26-FEB-17 98
27-FEB-17 96
28-FEB-17 99
01-MAR-17 97
02-MAR-17 100
03-MAR-17 99
04-MAR-17 99
05-MAR-17 99
06-MAR-17 97
07-MAR-17 98
08-MAR-17 92
09-MAR-17 97
10-MAR-17 95
11-MAR-17 95
12-MAR-17 99
13-MAR-17 99
14-MAR-17 99
15-MAR-17 99
16-MAR-17 97
17-MAR-17 99
18-MAR-17 99
19-MAR-17 93
20-MAR-17 98
21-MAR-17 95
22-MAR-17 90
23-MAR-17 98
24-MAR-17 98
25-MAR-17 100
26-MAR-17 94
27-MAR-17 97
28-MAR-17 99
29-MAR-17 99
30-MAR-17 100
31-MAR-17 99
01-APR-17 95
02-APR-17 95
03-APR-17 99
04-APR-17 88
05-APR-17 97
06-APR-17 98
07-APR-17 100
08-APR-17 90
09-APR-17 99
10-APR-17 87
11-APR-17 97
12-APR-17 99
13-APR-17 94
14-APR-17 95
15-APR-17 91
16-APR-17 96
17-APR-17 98
18-APR-17 92
19-APR-17 95
20-APR-17 91
21-APR-17 99
22-APR-17 94
23-APR-17 100
24-APR-17 99
25-APR-17 94
26-APR-17 97
27-APR-17 98
28-APR-17 100
29-APR-17 96
30-APR-17 98
01-MAY-17 97
02-MAY-17 99
03-MAY-17 99
04-MAY-17 99
05-MAY-17 97
06-MAY-17 88
07-MAY-17 99
08-MAY-17 98
09-MAY-17 99
10-MAY-17 95
11-MAY-17 97
12-MAY-17 98
13-MAY-17 94
14-MAY-17 100
15-MAY-17 96
16-MAY-17 93
17-MAY-17 99
18-MAY-17 81
19-MAY-17 97
20-MAY-17 88
21-MAY-17 93
22-MAY-17 90
23-MAY-17 97
24-MAY-17 100
25-MAY-17 96
26-MAY-17 99
27-MAY-17 93
28-MAY-17 97
29-MAY-17 97
30-MAY-17 96
31-MAY-17 90
01-JUN-17 99
02-JUN-17 95
03-JUN-17 99
04-JUN-17 95
05-JUN-17 82
06-JUN-17 97
07-JUN-17 97
08-JUN-17 95
09-JUN-17 99
10-JUN-17 95
11-JUN-17 89
12-JUN-17 98
13-JUN-17 99
14-JUN-17 98
15-JUN-17 96
16-JUN-17 97
17-JUN-17 96
18-JUN-17 94
19-JUN-17 96
20-JUN-17 99
21-JUN-17 99
22-JUN-17 99
23-JUN-17 98
24-JUN-17 96
25-JUN-17 97
26-JUN-17 100
27-JUN-17 98
28-JUN-17 92
29-JUN-17 98
30-JUN-17 94
01-JUL-17 98
02-JUL-17 98
03-JUL-17 100
04-JUL-17 93
05-JUL-17 99
06-JUL-17 99
07-JUL-17 99
08-JUL-17 97
09-JUL-17 98
10-JUL-17 86
11-JUL-17 96
12-JUL-17 99
13-JUL-17 98
14-JUL-17 99
15-JUL-17 89
16-JUL-17 99
17-JUL-17 88
18-JUL-17 92
19-JUL-17 99
20-JUL-17 96
21-JUL-17 98
22-JUL-17 97
23-JUL-17 95
24-JUL-17 95
25-JUL-17 100
26-JUL-17 96
27-JUL-17 98
28-JUL-17 97
29-JUL-17 95
30-JUL-17 96
31-JUL-17 82
01-AUG-17 95
02-AUG-17 97
03-AUG-17 100
04-AUG-17 92
05-AUG-17 89
06-AUG-17 98
07-AUG-17 99
08-AUG-17 97
09-AUG-17 100
10-AUG-17 98
11-AUG-17 84
12-AUG-17 100
13-AUG-17 99
14-AUG-17 96
15-AUG-17 100
16-AUG-17 99
17-AUG-17 94
18-AUG-17 97
19-AUG-17 98
20-AUG-17 96
21-AUG-17 89
22-AUG-17 95
23-AUG-17 100
24-AUG-17 99
25-AUG-17 95
26-AUG-17 96
27-AUG-17 94
28-AUG-17 92
29-AUG-17 95
30-AUG-17 95
31-AUG-17 87
01-SEP-17 99
02-SEP-17 95
03-SEP-17 95
04-SEP-17 100
05-SEP-17 99
06-SEP-17 90
07-SEP-17 94
08-SEP-17 92
09-SEP-17 98
10-SEP-17 94
11-SEP-17 100
12-SEP-17 99
13-SEP-17 96
14-SEP-17 100
15-SEP-17 87
16-SEP-17 98
17-SEP-17 98
18-SEP-17 99
19-SEP-17 99
20-SEP-17 99
21-SEP-17 97
22-SEP-17 90
23-SEP-17 100
24-SEP-17 97
25-SEP-17 94
26-SEP-17 98
27-SEP-17 94
28-SEP-17 95
29-SEP-17 91
30-SEP-17 99
01-OCT-17 98
02-OCT-17 95
03-OCT-17 97
04-OCT-17 96
05-OCT-17 93
06-OCT-17 98
07-OCT-17 95
08-OCT-17 96
09-OCT-17 100
10-OCT-17 99
11-OCT-17 97
12-OCT-17 99
13-OCT-17 94
14-OCT-17 99
15-OCT-17 96
16-OCT-17 96
17-OCT-17 98
18-OCT-17 99
19-OCT-17 100
20-OCT-17 100
21-OCT-17 95
22-OCT-17 89
23-OCT-17 100
24-OCT-17 96
25-OCT-17 96
26-OCT-17 98
27-OCT-17 97
28-OCT-17 98
29-OCT-17 99
30-OCT-17 95
31-OCT-17 94
01-NOV-17 99
02-NOV-17 93
03-NOV-17 99
04-NOV-17 96
05-NOV-17 100
06-NOV-17 100
07-NOV-17 95
08-NOV-17 98
09-NOV-17 100
10-NOV-17 92
11-NOV-17 92
12-NOV-17 87
13-NOV-17 99
14-NOV-17 96
15-NOV-17 100
16-NOV-17 99
17-NOV-17 97
18-NOV-17 94
19-NOV-17 94
20-NOV-17 100
21-NOV-17 97
22-NOV-17 94
23-NOV-17 89
24-NOV-17 95
25-NOV-17 93
26-NOV-17 97
27-NOV-17 98
28-NOV-17 97
29-NOV-17 76
30-NOV-17 94
01-DEC-17 99
02-DEC-17 98
03-DEC-17 99
04-DEC-17 99
05-DEC-17 98
06-DEC-17 96
07-DEC-17 99
08-DEC-17 96
09-DEC-17 99
10-DEC-17 98
11-DEC-17 98
12-DEC-17 97
13-DEC-17 97
14-DEC-17 99
15-DEC-17 95
16-DEC-17 99
17-DEC-17 100
18-DEC-17 97
19-DEC-17 95
20-DEC-17 96
21-DEC-17 92
22-DEC-17 87
23-DEC-17 92
24-DEC-17 87
25-DEC-17 99
26-DEC-17 96
27-DEC-17 99
28-DEC-17 95
29-DEC-17 99
30-DEC-17 99
31-DEC-17 94
01-JAN-18 98
02-JAN-18 93
03-JAN-18 95
04-JAN-18 100
05-JAN-18 87
06-JAN-18 99
07-JAN-18 97
08-JAN-18 99
09-JAN-18 97
10-JAN-18 91
11-JAN-18 91
12-JAN-18 96
13-JAN-18 100
14-JAN-18 85
15-JAN-18 97
16-JAN-18 96
17-JAN-18 93
18-JAN-18 99
19-JAN-18 99
20-JAN-18 99
21-JAN-18 94
22-JAN-18 94
23-JAN-18 74

367 rows selected.

MY_DAY	VALUE
22-JAN-17	92
23-JAN-17	100
24-JAN-17	96
25-JAN-17	92
26-JAN-17	97
27-JAN-17	93
28-JAN-17	92
29-JAN-17	81
30-JAN-17	100
31-JAN-17	94
01-FEB-17	93
02-FEB-17	99
03-FEB-17	93
04-FEB-17	99
05-FEB-17	84
06-FEB-17	100
07-FEB-17	89
08-FEB-17	97
09-FEB-17	99
10-FEB-17	94
11-FEB-17	99
12-FEB-17	99
13-FEB-17	99
14-FEB-17	90
15-FEB-17	99
16-FEB-17	97
17-FEB-17	95
18-FEB-17	99
19-FEB-17	100
20-FEB-17	95
21-FEB-17	96
22-FEB-17	92
23-FEB-17	99
24-FEB-17	97
25-FEB-17	98
26-FEB-17	98
27-FEB-17	96
28-FEB-17	99
01-MAR-17	97
02-MAR-17	100
03-MAR-17	99
04-MAR-17	99
05-MAR-17	99
06-MAR-17	97
07-MAR-17	98
08-MAR-17	92
09-MAR-17	97
10-MAR-17	95
11-MAR-17	95
12-MAR-17	99
13-MAR-17	99
14-MAR-17	99
15-MAR-17	99
16-MAR-17	97
17-MAR-17	99
18-MAR-17	99
19-MAR-17	93
20-MAR-17	98
21-MAR-17	95
22-MAR-17	90
23-MAR-17	98
24-MAR-17	98
25-MAR-17	100
26-MAR-17	94
27-MAR-17	97
28-MAR-17	99
29-MAR-17	99
30-MAR-17	100
31-MAR-17	99
01-APR-17	95
02-APR-17	95
03-APR-17	99
04-APR-17	88
05-APR-17	97
06-APR-17	98
07-APR-17	100
08-APR-17	90
09-APR-17	99
10-APR-17	87
11-APR-17	97
12-APR-17	99
13-APR-17	94
14-APR-17	95
15-APR-17	91
16-APR-17	96
17-APR-17	98
18-APR-17	92
19-APR-17	95
20-APR-17	91
21-APR-17	99
22-APR-17	94
23-APR-17	100
24-APR-17	99
25-APR-17	94
26-APR-17	97
27-APR-17	98
28-APR-17	100
29-APR-17	96
30-APR-17	98
01-MAY-17	97
02-MAY-17	99
03-MAY-17	99
04-MAY-17	99
05-MAY-17	97
06-MAY-17	88
07-MAY-17	99
08-MAY-17	98
09-MAY-17	99
10-MAY-17	95
11-MAY-17	97
12-MAY-17	98
13-MAY-17	94
14-MAY-17	100
15-MAY-17	96
16-MAY-17	93
17-MAY-17	99
18-MAY-17	81
19-MAY-17	97
20-MAY-17	88
21-MAY-17	93
22-MAY-17	90
23-MAY-17	97
24-MAY-17	100
25-MAY-17	96
26-MAY-17	99
27-MAY-17	93
28-MAY-17	97
29-MAY-17	97
30-MAY-17	96
31-MAY-17	90
01-JUN-17	99
02-JUN-17	95
03-JUN-17	99
04-JUN-17	95
05-JUN-17	82
06-JUN-17	97
07-JUN-17	97
08-JUN-17	95
09-JUN-17	99
10-JUN-17	95
11-JUN-17	89
12-JUN-17	98
13-JUN-17	99
14-JUN-17	98
15-JUN-17	96
16-JUN-17	97
17-JUN-17	96
18-JUN-17	94
19-JUN-17	96
20-JUN-17	99
21-JUN-17	99
22-JUN-17	99
23-JUN-17	98
24-JUN-17	96
25-JUN-17	97
26-JUN-17	100
27-JUN-17	98
28-JUN-17	92
29-JUN-17	98
30-JUN-17	94
01-JUL-17	98
02-JUL-17	98
03-JUL-17	100
04-JUL-17	93
05-JUL-17	99
06-JUL-17	99
07-JUL-17	99
08-JUL-17	97
09-JUL-17	98
10-JUL-17	86
11-JUL-17	96
12-JUL-17	99
13-JUL-17	98
14-JUL-17	99
15-JUL-17	89
16-JUL-17	99
17-JUL-17	88
18-JUL-17	92
19-JUL-17	99
20-JUL-17	96
21-JUL-17	98
22-JUL-17	97
23-JUL-17	95
24-JUL-17	95
25-JUL-17	100
26-JUL-17	96
27-JUL-17	98
28-JUL-17	97
29-JUL-17	95
30-JUL-17	96
31-JUL-17	82
01-AUG-17	95
02-AUG-17	97
03-AUG-17	100
04-AUG-17	92
05-AUG-17	89
06-AUG-17	98
07-AUG-17	99
08-AUG-17	97
09-AUG-17	100
10-AUG-17	98
11-AUG-17	84
12-AUG-17	100
13-AUG-17	99
14-AUG-17	96
15-AUG-17	100
16-AUG-17	99
17-AUG-17	94
18-AUG-17	97
19-AUG-17	98
20-AUG-17	96
21-AUG-17	89
22-AUG-17	95
23-AUG-17	100
24-AUG-17	99
25-AUG-17	95
26-AUG-17	96
27-AUG-17	94
28-AUG-17	92
29-AUG-17	95
30-AUG-17	95
31-AUG-17	87
01-SEP-17	99
02-SEP-17	95
03-SEP-17	95
04-SEP-17	100
05-SEP-17	99
06-SEP-17	90
07-SEP-17	94
08-SEP-17	92
09-SEP-17	98
10-SEP-17	94
11-SEP-17	100
12-SEP-17	99
13-SEP-17	96
14-SEP-17	100
15-SEP-17	87
16-SEP-17	98
17-SEP-17	98
18-SEP-17	99
19-SEP-17	99
20-SEP-17	99
21-SEP-17	97
22-SEP-17	90
23-SEP-17	100
24-SEP-17	97
25-SEP-17	94
26-SEP-17	98
27-SEP-17	94
28-SEP-17	95
29-SEP-17	91
30-SEP-17	99
01-OCT-17	98
02-OCT-17	95
03-OCT-17	97
04-OCT-17	96
05-OCT-17	93
06-OCT-17	98
07-OCT-17	95
08-OCT-17	96
09-OCT-17	100
10-OCT-17	99
11-OCT-17	97
12-OCT-17	99
13-OCT-17	94
14-OCT-17	99
15-OCT-17	96
16-OCT-17	96
17-OCT-17	98
18-OCT-17	99
19-OCT-17	100
20-OCT-17	100
21-OCT-17	95
22-OCT-17	89
23-OCT-17	100
24-OCT-17	96
25-OCT-17	96
26-OCT-17	98
27-OCT-17	97
28-OCT-17	98
29-OCT-17	99
30-OCT-17	95
31-OCT-17	94
01-NOV-17	99
02-NOV-17	93
03-NOV-17	99
04-NOV-17	96
05-NOV-17	100
06-NOV-17	100
07-NOV-17	95
08-NOV-17	98
09-NOV-17	100
10-NOV-17	92
11-NOV-17	92
12-NOV-17	87
13-NOV-17	99
14-NOV-17	96
15-NOV-17	100
16-NOV-17	99
17-NOV-17	97
18-NOV-17	94
19-NOV-17	94
20-NOV-17	100
21-NOV-17	97
22-NOV-17	94
23-NOV-17	89
24-NOV-17	95
25-NOV-17	93
26-NOV-17	97
27-NOV-17	98
28-NOV-17	97
29-NOV-17	76
30-NOV-17	94
01-DEC-17	99
02-DEC-17	98
03-DEC-17	99
04-DEC-17	99
05-DEC-17	98
06-DEC-17	96
07-DEC-17	99
08-DEC-17	96
09-DEC-17	99
10-DEC-17	98
11-DEC-17	98
12-DEC-17	97
13-DEC-17	97
14-DEC-17	99
15-DEC-17	95
16-DEC-17	99
17-DEC-17	100
18-DEC-17	97
19-DEC-17	95
20-DEC-17	96
21-DEC-17	92
22-DEC-17	87
23-DEC-17	92
24-DEC-17	87
25-DEC-17	99
26-DEC-17	96
27-DEC-17	99
28-DEC-17	95
29-DEC-17	99
30-DEC-17	99
31-DEC-17	94
01-JAN-18	98
02-JAN-18	93
03-JAN-18	95
04-JAN-18	100
05-JAN-18	87
06-JAN-18	99
07-JAN-18	97
08-JAN-18	99
09-JAN-18	97
10-JAN-18	91
11-JAN-18	91
12-JAN-18	96
13-JAN-18	100
14-JAN-18	85
15-JAN-18	97
16-JAN-18	96
17-JAN-18	93
18-JAN-18	99
19-JAN-18	99
20-JAN-18	99
21-JAN-18	94
22-JAN-18	94
23-JAN-18	74

Statement 3

SELECT TRUNC( my_day, 'MM' ), 
       AVG( value ) 
FROM   ( 
  SELECT TRUNC(the_date, 'DD') as my_day, MAX(value) AS value  
  FROM   my_data  
  GROUP by TRUNC(the_date, 'DD') 
) 
GROUP BY TRUNC( my_day, 'MM' ) 
ORDER BY 1


TRUNC(MY_DAY,'MM') AVG(VALUE)
01-JAN-17 93.7
01-FEB-17 96.21428571428571428571428571428571428571
01-MAR-17 97.38709677419354838709677419354838709677
01-APR-17 95.7
01-MAY-17 95.51612903225806451612903225806451612903
01-JUN-17 96.2
01-JUL-17 95.83870967741935483870967741935483870968
01-AUG-17 95.64516129032258064516129032258064516129
01-SEP-17 96.2
01-OCT-17 96.83870967741935483870967741935483870968
01-NOV-17 95.33333333333333333333333333333333333333
01-DEC-17 96.5483870967741935483870967741935483871
01-JAN-18 94.47826086956521739130434782608695652174

13 rows selected.

TRUNC(MY_DAY,'MM')	AVG(VALUE)
01-JAN-17	93.7
01-FEB-17	96.21428571428571428571428571428571428571
01-MAR-17	97.38709677419354838709677419354838709677
01-APR-17	95.7
01-MAY-17	95.51612903225806451612903225806451612903
01-JUN-17	96.2
01-JUL-17	95.83870967741935483870967741935483870968
01-AUG-17	95.64516129032258064516129032258064516129
01-SEP-17	96.2
01-OCT-17	96.83870967741935483870967741935483870968
01-NOV-17	95.33333333333333333333333333333333333333
01-DEC-17	96.5483870967741935483870967741935483871
01-JAN-18	94.47826086956521739130434782608695652174

Statement 4

WITH daily_max AS 
( 
  SELECT TRUNC(the_date, 'DD') as my_day, MAX(value) AS value  
    FROM my_data  
   GROUP by TRUNC(the_date, 'DD') 
) 
SELECT trunc(my_day, 'MM'), AVG(value)  
FROM daily_max 
GROUP BY trunc(my_day, 'MM') 
order by 1


TRUNC(MY_DAY,'MM') AVG(VALUE)
01-JAN-17 93.7
01-FEB-17 96.21428571428571428571428571428571428571
01-MAR-17 97.38709677419354838709677419354838709677
01-APR-17 95.7
01-MAY-17 95.51612903225806451612903225806451612903
01-JUN-17 96.2
01-JUL-17 95.83870967741935483870967741935483870968
01-AUG-17 95.64516129032258064516129032258064516129
01-SEP-17 96.2
01-OCT-17 96.83870967741935483870967741935483870968
01-NOV-17 95.33333333333333333333333333333333333333
01-DEC-17 96.5483870967741935483870967741935483871
01-JAN-18 94.47826086956521739130434782608695652174

13 rows selected.

TRUNC(MY_DAY,'MM')	AVG(VALUE)
01-JAN-17	93.7
01-FEB-17	96.21428571428571428571428571428571428571
01-MAR-17	97.38709677419354838709677419354838709677
01-APR-17	95.7
01-MAY-17	95.51612903225806451612903225806451612903
01-JUN-17	96.2
01-JUL-17	95.83870967741935483870967741935483870968
01-AUG-17	95.64516129032258064516129032258064516129
01-SEP-17	96.2
01-OCT-17	96.83870967741935483870967741935483870968
01-NOV-17	95.33333333333333333333333333333333333333
01-DEC-17	96.5483870967741935483870967741935483871
01-JAN-18	94.47826086956521739130434782608695652174

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database