SQL/create_table_test_peak_hour.sql at master · Testudinate/SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
--Создание объектов в БД Oracle
--drop table TEST_PEAK_HOUR;

CREATE TABLE TEST_PEAK_HOUR
(
    RECDATE DATE, -- час, к которому относится строка статистики с объекта
    OBJNAME CHAR(4 CHAR), -- имя маршрутизатора
    TRAF NUMBER, -- трафик маршрутизатора
    COUNTER1 NUMBER, -- число занятых каналов маршрутизатора
    CONSTRAINT TEST_PEAK_HOUR_PK PRIMARY KEY (RECDATE, OBJNAME)
);

Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/11/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ1', 10, 21);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/11/2011 01:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ1', 9, 22);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/11/2011 21:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ1', 11, 23);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/11/2011 23:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ1', 11, 24);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/12/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ1', NULL, 25);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/12/2011 01:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ1', 13, 26);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/12/2011 02:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ1', 12, 27);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/12/2011 04:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ1', 12, 28);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/12/2011 05:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ1', 13, 29);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/11/2011 20:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ2', 3, 1);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/11/2011 21:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ2', 2, 2);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/11/2011 22:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ2', 1, 3);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/11/2011 23:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ2', 0, 2);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/12/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ3', 1, 1);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/12/2011 01:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ3', 2, 0);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/12/2011 02:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ3', 2, 1);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/12/2011 03:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ3', 2, 2);
Insert into TEST_PEAK_HOUR
   (RECDATE, OBJNAME, TRAF, COUNTER1)
 Values
   (TO_DATE('04/12/2011 23:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OBJ3', 1, 3);
COMMIT;
-- uncomment to make decision 1 not working :)
--union all
--select trunc(sysdate)+4/24 as RECDATE, 'OBJ4' as OBJNAME, NULL as TRAF, 0 as COUNTER1 from dual