Oracle Live SQL - Script: Analytics - The RANGE options for windows

Statement 1

drop table tweets purge

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

create table tweets ( 
  dte date primary key,
  tweet_millions number(16,6)
  )

Table created.

Statement 3

insert into tweets values (to_date('01-JAN-06','dd-mon-rr'),0.000001)

1 row(s) inserted.

Statement 4

insert into tweets values (to_date('01-FEB-06','dd-mon-rr'),0.000387)

1 row(s) inserted.

Statement 5

insert into tweets values (to_date('01-MAR-06','dd-mon-rr'),0.000773)

1 row(s) inserted.

Statement 6

insert into tweets values (to_date('01-APR-06','dd-mon-rr'),0.00116)

1 row(s) inserted.

Statement 7

insert into tweets values (to_date('01-JUN-06','dd-mon-rr'),0.001932)

1 row(s) inserted.

Statement 8

insert into tweets values (to_date('01-JUL-06','dd-mon-rr'),0.002318)

1 row(s) inserted.

Statement 9

insert into tweets values (to_date('01-SEP-06','dd-mon-rr'),0.003091)

1 row(s) inserted.

Statement 10

insert into tweets values (to_date('01-OCT-06','dd-mon-rr'),0.003477)

1 row(s) inserted.

Statement 11

insert into tweets values (to_date('01-DEC-06','dd-mon-rr'),0.004249)

1 row(s) inserted.

Statement 12

insert into tweets values (to_date('01-JAN-07','dd-mon-rr'),0.005)

1 row(s) inserted.

Statement 13

insert into tweets values (to_date('01-FEB-07','dd-mon-rr'),0.027565)

1 row(s) inserted.

Statement 14

insert into tweets values (to_date('01-MAR-07','dd-mon-rr'),0.05013)

1 row(s) inserted.

Statement 15

insert into tweets values (to_date('01-APR-07','dd-mon-rr'),0.072694)

1 row(s) inserted.

Statement 16

insert into tweets values (to_date('01-JUN-07','dd-mon-rr'),0.117824)

1 row(s) inserted.

Statement 17

insert into tweets values (to_date('01-JUL-07','dd-mon-rr'),0.140389)

1 row(s) inserted.

Statement 18

insert into tweets values (to_date('01-SEP-07','dd-mon-rr'),0.185518)

1 row(s) inserted.

Statement 19

insert into tweets values (to_date('01-OCT-07','dd-mon-rr'),0.208083)

1 row(s) inserted.

Statement 20

insert into tweets values (to_date('01-NOV-07','dd-mon-rr'),0.230648)

1 row(s) inserted.

Statement 21

insert into tweets values (to_date('01-DEC-07','dd-mon-rr'),0.253213)

1 row(s) inserted.

Statement 22

insert into tweets values (to_date('01-JAN-08','dd-mon-rr'),0.3)

1 row(s) inserted.

Statement 23

insert into tweets values (to_date('01-FEB-08','dd-mon-rr'),0.473757)

1 row(s) inserted.

Statement 24

insert into tweets values (to_date('01-MAR-08','dd-mon-rr'),0.647514)

1 row(s) inserted.

Statement 25

insert into tweets values (to_date('01-APR-08','dd-mon-rr'),0.821271)

1 row(s) inserted.

Statement 26

insert into tweets values (to_date('01-JUN-08','dd-mon-rr'),1.168785)

1 row(s) inserted.

Statement 27

insert into tweets values (to_date('01-JUL-08','dd-mon-rr'),1.342543)

1 row(s) inserted.

Statement 28

insert into tweets values (to_date('01-SEP-08','dd-mon-rr'),1.690057)

1 row(s) inserted.

Statement 29

insert into tweets values (to_date('01-OCT-08','dd-mon-rr'),1.863814)

1 row(s) inserted.

Statement 30

insert into tweets values (to_date('01-NOV-08','dd-mon-rr'),2.037571)

1 row(s) inserted.

Statement 31

insert into tweets values (to_date('01-DEC-08','dd-mon-rr'),2.211328)

1 row(s) inserted.

Statement 32

insert into tweets values (to_date('01-JAN-09','dd-mon-rr'),2.5)

1 row(s) inserted.

Statement 33

insert into tweets values (to_date('01-FEB-09','dd-mon-rr'),4.57)

1 row(s) inserted.

Statement 34

insert into tweets values (to_date('01-MAR-09','dd-mon-rr'),6.64)

1 row(s) inserted.

Statement 35

insert into tweets values (to_date('01-APR-09','dd-mon-rr'),8.71)

1 row(s) inserted.

Statement 36

insert into tweets values (to_date('01-JUN-09','dd-mon-rr'),12.85)

1 row(s) inserted.

Statement 37

insert into tweets values (to_date('01-AUG-09','dd-mon-rr'),16.99)

1 row(s) inserted.

Statement 38

insert into tweets values (to_date('01-SEP-09','dd-mon-rr'),19.05)

1 row(s) inserted.

Statement 39

insert into tweets values (to_date('01-OCT-09','dd-mon-rr'),21.12)

1 row(s) inserted.

Statement 40

insert into tweets values (to_date('01-DEC-09','dd-mon-rr'),25.26)

1 row(s) inserted.

Statement 41

insert into tweets values (to_date('01-JAN-10','dd-mon-rr'),30)

1 row(s) inserted.

Statement 42

insert into tweets values (to_date('01-FEB-10','dd-mon-rr'),31.54)

1 row(s) inserted.

Statement 43

insert into tweets values (to_date('01-MAR-10','dd-mon-rr'),33.08)

1 row(s) inserted.

Statement 44

insert into tweets values (to_date('01-APR-10','dd-mon-rr'),34.63)

1 row(s) inserted.

Statement 45

insert into tweets values (to_date('01-MAY-10','dd-mon-rr'),36.17)

1 row(s) inserted.

Statement 46

insert into tweets values (to_date('01-JUN-10','dd-mon-rr'),37.71)

1 row(s) inserted.

Statement 47

insert into tweets values (to_date('01-JUL-10','dd-mon-rr'),39.25)

1 row(s) inserted.

Statement 48

insert into tweets values (to_date('01-AUG-10','dd-mon-rr'),40.79)

1 row(s) inserted.

Statement 49

insert into tweets values (to_date('01-SEP-10','dd-mon-rr'),42.34)

1 row(s) inserted.

Statement 50

insert into tweets values (to_date('01-OCT-10','dd-mon-rr'),43.88)

1 row(s) inserted.

Statement 51

insert into tweets values (to_date('01-NOV-10','dd-mon-rr'),45.42)

1 row(s) inserted.

Statement 52

insert into tweets values (to_date('01-DEC-10','dd-mon-rr'),46.96)

1 row(s) inserted.

Statement 53

insert into tweets values (to_date('01-JAN-11','dd-mon-rr'),50)

1 row(s) inserted.

Statement 54

insert into tweets values (to_date('01-FEB-11','dd-mon-rr'),53.87)

1 row(s) inserted.

Statement 55

insert into tweets values (to_date('01-MAR-11','dd-mon-rr'),57.74)

1 row(s) inserted.

Statement 56

insert into tweets values (to_date('01-APR-11','dd-mon-rr'),61.61)

1 row(s) inserted.

Statement 57

insert into tweets values (to_date('01-MAY-11','dd-mon-rr'),65.47)

1 row(s) inserted.

Statement 58

insert into tweets values (to_date('01-JUN-11','dd-mon-rr'),69.34)

1 row(s) inserted.

Statement 59

insert into tweets values (to_date('01-JUL-11','dd-mon-rr'),73.21)

1 row(s) inserted.

Statement 60

insert into tweets values (to_date('01-AUG-11','dd-mon-rr'),77.08)

1 row(s) inserted.

Statement 61

insert into tweets values (to_date('01-SEP-11','dd-mon-rr'),80.95)

1 row(s) inserted.

Statement 62

insert into tweets values (to_date('01-OCT-11','dd-mon-rr'),84.82)

1 row(s) inserted.

Statement 63

insert into tweets values (to_date('01-NOV-11','dd-mon-rr'),88.68)

1 row(s) inserted.

Statement 64

insert into tweets values (to_date('01-DEC-11','dd-mon-rr'),92.55)

1 row(s) inserted.

Statement 65

insert into tweets values (to_date('01-JAN-12','dd-mon-rr'),100)

1 row(s) inserted.

Statement 66

insert into tweets values (to_date('01-FEB-12','dd-mon-rr'),108.1)

1 row(s) inserted.

Statement 67

insert into tweets values (to_date('01-MAR-12','dd-mon-rr'),116.21)

1 row(s) inserted.

Statement 68

insert into tweets values (to_date('01-APR-12','dd-mon-rr'),124.31)

1 row(s) inserted.

Statement 69

insert into tweets values (to_date('01-MAY-12','dd-mon-rr'),132.41)

1 row(s) inserted.

Statement 70

insert into tweets values (to_date('01-JUN-12','dd-mon-rr'),140.52)

1 row(s) inserted.

Statement 71

insert into tweets values (to_date('01-JUL-12','dd-mon-rr'),148.62)

1 row(s) inserted.

Statement 72

insert into tweets values (to_date('01-SEP-12','dd-mon-rr'),164.83)

1 row(s) inserted.

Statement 73

insert into tweets values (to_date('01-OCT-12','dd-mon-rr'),172.93)

1 row(s) inserted.

Statement 74

insert into tweets values (to_date('01-NOV-12','dd-mon-rr'),181.03)

1 row(s) inserted.

Statement 75

insert into tweets values (to_date('01-DEC-12','dd-mon-rr'),189.13)

1 row(s) inserted.

Statement 76

insert into tweets values (to_date('01-JAN-13','dd-mon-rr'),200)

1 row(s) inserted.

Statement 77

insert into tweets values (to_date('01-FEB-13','dd-mon-rr'),204.13)

1 row(s) inserted.

Statement 78

insert into tweets values (to_date('01-MAR-13','dd-mon-rr'),208.26)

1 row(s) inserted.

Statement 79

insert into tweets values (to_date('01-MAY-13','dd-mon-rr'),216.52)

1 row(s) inserted.

Statement 80

insert into tweets values (to_date('01-JUN-13','dd-mon-rr'),220.64)

1 row(s) inserted.

Statement 81

insert into tweets values (to_date('01-AUG-13','dd-mon-rr'),228.9)

1 row(s) inserted.

Statement 82

insert into tweets values (to_date('01-SEP-13','dd-mon-rr'),233.03)

1 row(s) inserted.

Statement 83

insert into tweets values (to_date('01-OCT-13','dd-mon-rr'),237.16)

1 row(s) inserted.

Statement 84

insert into tweets values (to_date('01-DEC-13','dd-mon-rr'),245.42)

1 row(s) inserted.

Statement 85

insert into tweets values (to_date('01-JAN-14','dd-mon-rr'),250)

1 row(s) inserted.

Statement 86

insert into tweets values (to_date('01-MAR-14','dd-mon-rr'),264.45)

1 row(s) inserted.

Statement 87

insert into tweets values (to_date('01-APR-14','dd-mon-rr'),271.68)

1 row(s) inserted.

Statement 88

insert into tweets values (to_date('01-MAY-14','dd-mon-rr'),278.91)

1 row(s) inserted.

Statement 89

insert into tweets values (to_date('01-JUN-14','dd-mon-rr'),286.13)

1 row(s) inserted.

Statement 90

insert into tweets values (to_date('01-AUG-14','dd-mon-rr'),300.59)

1 row(s) inserted.

Statement 91

insert into tweets values (to_date('01-SEP-14','dd-mon-rr'),307.81)

1 row(s) inserted.

Statement 92

insert into tweets values (to_date('01-OCT-14','dd-mon-rr'),315.04)

1 row(s) inserted.

Statement 93

insert into tweets values (to_date('01-DEC-14','dd-mon-rr'),329.49)

1 row(s) inserted.

Statement 94

insert into tweets values (to_date('01-JAN-15','dd-mon-rr'),340)

1 row(s) inserted.

Statement 95

insert into tweets values (to_date('01-APR-15','dd-mon-rr'),376.6)

1 row(s) inserted.

Statement 96

insert into tweets values (to_date('01-MAY-15','dd-mon-rr'),388.8)

1 row(s) inserted.

Statement 97

insert into tweets values (to_date('01-JUL-15','dd-mon-rr'),413.2)

1 row(s) inserted.

Statement 98

insert into tweets values (to_date('01-AUG-15','dd-mon-rr'),425.41)

1 row(s) inserted.

Statement 99

insert into tweets values (to_date('01-OCT-15','dd-mon-rr'),449.81)

1 row(s) inserted.

Statement 100

insert into tweets values (to_date('01-NOV-15','dd-mon-rr'),462.01)

1 row(s) inserted.

Statement 101

insert into tweets values (to_date('01-DEC-15','dd-mon-rr'),474.21)

1 row(s) inserted.

Statement 102

commit

Statement processed.

Statement 103

select 
  dte,
  tweet_millions
from tweets
order by dte

 
DTE TWEET_MILLIONS
01-JAN-06 .000001
01-FEB-06 .000387
01-MAR-06 .000773
01-APR-06 .00116
01-JUN-06 .001932
01-JUL-06 .002318
01-SEP-06 .003091
01-OCT-06 .003477
01-DEC-06 .004249
01-JAN-07 .005
01-FEB-07 .027565
01-MAR-07 .05013
01-APR-07 .072694
01-JUN-07 .117824
01-JUL-07 .140389
01-SEP-07 .185518
01-OCT-07 .208083
01-NOV-07 .230648
01-DEC-07 .253213
01-JAN-08 .3
01-FEB-08 .473757
01-MAR-08 .647514
01-APR-08 .821271
01-JUN-08 1.168785
01-JUL-08 1.342543
01-SEP-08 1.690057
01-OCT-08 1.863814
01-NOV-08 2.037571
01-DEC-08 2.211328
01-JAN-09 2.5
01-FEB-09 4.57
01-MAR-09 6.64
01-APR-09 8.71
01-JUN-09 12.85
01-AUG-09 16.99
01-SEP-09 19.05
01-OCT-09 21.12
01-DEC-09 25.26
01-JAN-10 30
01-FEB-10 31.54
01-MAR-10 33.08
01-APR-10 34.63
01-MAY-10 36.17
01-JUN-10 37.71
01-JUL-10 39.25
01-AUG-10 40.79
01-SEP-10 42.34
01-OCT-10 43.88
01-NOV-10 45.42
01-DEC-10 46.96
01-JAN-11 50
01-FEB-11 53.87
01-MAR-11 57.74
01-APR-11 61.61
01-MAY-11 65.47
01-JUN-11 69.34
01-JUL-11 73.21
01-AUG-11 77.08
01-SEP-11 80.95
01-OCT-11 84.82
01-NOV-11 88.68
01-DEC-11 92.55
01-JAN-12 100
01-FEB-12 108.1
01-MAR-12 116.21
01-APR-12 124.31
01-MAY-12 132.41
01-JUN-12 140.52
01-JUL-12 148.62
01-SEP-12 164.83
01-OCT-12 172.93
01-NOV-12 181.03
01-DEC-12 189.13
01-JAN-13 200
01-FEB-13 204.13
01-MAR-13 208.26
01-MAY-13 216.52
01-JUN-13 220.64
01-AUG-13 228.9
01-SEP-13 233.03
01-OCT-13 237.16
01-DEC-13 245.42
01-JAN-14 250
01-MAR-14 264.45
01-APR-14 271.68
01-MAY-14 278.91
01-JUN-14 286.13
01-AUG-14 300.59
01-SEP-14 307.81
01-OCT-14 315.04
01-DEC-14 329.49
01-JAN-15 340
01-APR-15 376.6
01-MAY-15 388.8
01-JUL-15 413.2
01-AUG-15 425.41
01-OCT-15 449.81
01-NOV-15 462.01
01-DEC-15 474.21

99 rows selected.

DTE	TWEET_MILLIONS
01-JAN-06	.000001
01-FEB-06	.000387
01-MAR-06	.000773
01-APR-06	.00116
01-JUN-06	.001932
01-JUL-06	.002318
01-SEP-06	.003091
01-OCT-06	.003477
01-DEC-06	.004249
01-JAN-07	.005
01-FEB-07	.027565
01-MAR-07	.05013
01-APR-07	.072694
01-JUN-07	.117824
01-JUL-07	.140389
01-SEP-07	.185518
01-OCT-07	.208083
01-NOV-07	.230648
01-DEC-07	.253213
01-JAN-08	.3
01-FEB-08	.473757
01-MAR-08	.647514
01-APR-08	.821271
01-JUN-08	1.168785
01-JUL-08	1.342543
01-SEP-08	1.690057
01-OCT-08	1.863814
01-NOV-08	2.037571
01-DEC-08	2.211328
01-JAN-09	2.5
01-FEB-09	4.57
01-MAR-09	6.64
01-APR-09	8.71
01-JUN-09	12.85
01-AUG-09	16.99
01-SEP-09	19.05
01-OCT-09	21.12
01-DEC-09	25.26
01-JAN-10	30
01-FEB-10	31.54
01-MAR-10	33.08
01-APR-10	34.63
01-MAY-10	36.17
01-JUN-10	37.71
01-JUL-10	39.25
01-AUG-10	40.79
01-SEP-10	42.34
01-OCT-10	43.88
01-NOV-10	45.42
01-DEC-10	46.96
01-JAN-11	50
01-FEB-11	53.87
01-MAR-11	57.74
01-APR-11	61.61
01-MAY-11	65.47
01-JUN-11	69.34
01-JUL-11	73.21
01-AUG-11	77.08
01-SEP-11	80.95
01-OCT-11	84.82
01-NOV-11	88.68
01-DEC-11	92.55
01-JAN-12	100
01-FEB-12	108.1
01-MAR-12	116.21
01-APR-12	124.31
01-MAY-12	132.41
01-JUN-12	140.52
01-JUL-12	148.62
01-SEP-12	164.83
01-OCT-12	172.93
01-NOV-12	181.03
01-DEC-12	189.13
01-JAN-13	200
01-FEB-13	204.13
01-MAR-13	208.26
01-MAY-13	216.52
01-JUN-13	220.64
01-AUG-13	228.9
01-SEP-13	233.03
01-OCT-13	237.16
01-DEC-13	245.42
01-JAN-14	250
01-MAR-14	264.45
01-APR-14	271.68
01-MAY-14	278.91
01-JUN-14	286.13
01-AUG-14	300.59
01-SEP-14	307.81
01-OCT-14	315.04
01-DEC-14	329.49
01-JAN-15	340
01-APR-15	376.6
01-MAY-15	388.8
01-JUL-15	413.2
01-AUG-15	425.41
01-OCT-15	449.81
01-NOV-15	462.01
01-DEC-15	474.21

Statement 104

select  dte, tweet_millions,
  round(
    avg(tweet_millions) over
         ( order by dte
           range between interval '6' month preceding and current row )) mov_avg
from tweets
order by dte

 
DTE TWEET_MILLIONS MOV_AVG
01-JAN-06 .000001 0
01-FEB-06 .000387 0
01-MAR-06 .000773 0
01-APR-06 .00116 0
01-JUN-06 .001932 0
01-JUL-06 .002318 0
01-SEP-06 .003091 0
01-OCT-06 .003477 0
01-DEC-06 .004249 0
01-JAN-07 .005 0
01-FEB-07 .027565 0
01-MAR-07 .05013 0
01-APR-07 .072694 0
01-JUN-07 .117824 0
01-JUL-07 .140389 0
01-SEP-07 .185518 0
01-OCT-07 .208083 0
01-NOV-07 .230648 0
01-DEC-07 .253213 0
01-JAN-08 .3 0
01-FEB-08 .473757 0
01-MAR-08 .647514 0
01-APR-08 .821271 0
01-JUN-08 1.168785 1
01-JUL-08 1.342543 1
01-SEP-08 1.690057 1
01-OCT-08 1.863814 1
01-NOV-08 2.037571 2
01-DEC-08 2.211328 2
01-JAN-09 2.5 2
01-FEB-09 4.57 2
01-MAR-09 6.64 3
01-APR-09 8.71 4
01-JUN-09 12.85 6
01-AUG-09 16.99 10
01-SEP-09 19.05 13
01-OCT-09 21.12 16
01-DEC-09 25.26 19
01-JAN-10 30 22
01-FEB-10 31.54 24
01-MAR-10 33.08 27
01-APR-10 34.63 29
01-MAY-10 36.17 32
01-JUN-10 37.71 33
01-JUL-10 39.25 35
01-AUG-10 40.79 36
01-SEP-10 42.34 38
01-OCT-10 43.88 39
01-NOV-10 45.42 41
01-DEC-10 46.96 42
01-JAN-11 50 44
01-FEB-11 53.87 46
01-MAR-11 57.74 49
01-APR-11 61.61 51
01-MAY-11 65.47 54
01-JUN-11 69.34 58
01-JUL-11 73.21 62
01-AUG-11 77.08 65
01-SEP-11 80.95 69
01-OCT-11 84.82 73
01-NOV-11 88.68 77
01-DEC-11 92.55 81
01-JAN-12 100 85
01-FEB-12 108.1 90
01-MAR-12 116.21 96
01-APR-12 124.31 102
01-MAY-12 132.41 109
01-JUN-12 140.52 116
01-JUL-12 148.62 124
01-SEP-12 164.83 138
01-OCT-12 172.93 147
01-NOV-12 181.03 157
01-DEC-12 189.13 166
01-JAN-13 200 176
01-FEB-13 204.13 185
01-MAR-13 208.26 189
01-MAY-13 216.52 200
01-JUN-13 220.64 206
01-AUG-13 228.9 216
01-SEP-13 233.03 221
01-OCT-13 237.16 227
01-DEC-13 245.42 233
01-JAN-14 250 239
01-MAR-14 264.45 246
01-APR-14 271.68 254
01-MAY-14 278.91 262
01-JUN-14 286.13 266
01-AUG-14 300.59 280
01-SEP-14 307.81 285
01-OCT-14 315.04 293
01-DEC-14 329.49 308
01-JAN-15 340 319
01-APR-15 376.6 340
01-MAY-15 388.8 359
01-JUL-15 413.2 380
01-AUG-15 425.41 401
01-OCT-15 449.81 411
01-NOV-15 462.01 428
01-DEC-15 474.21 445

99 rows selected.

Analytics - The RANGE options for windows

Additional Information