vaneque...
Грубый пример. Система_1 работала 28 апреля в двух потоках, один отработал с 11:00 до 13:00, второй с 12:00 до 14:00. если предположить, что в течение 28 апреля система_1 больше не работала, то для 28 апреля результат будет 3 часа, но не 4, т.к. периоды с 12 до 13 часов пересекаются. Понятно, что потоков может быть больше и интервалов в течение дня могут быть десятки и даже сотни.
я думал-думал, как это сделать через SQL, в итоге плюнул и реализовал все хранимой функцией, но может кто подскажет, как это сделать через SQL? и можно ли?
Спасибо!
Как вариант решения примера:
SQL
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.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
select max(s1_h1) + max(s1_h2) + max(s1_h3) + max(s1_h4) + max(s1_h5) + max(s1_h6) + max(s1_h7) + max(s1_h8) + max(s1_h9) +
max(s1_h10) + max(s1_h11) + max(s1_h12) + max(s1_h13) + max(s1_h14) + max(s1_h15) + max(s1_h16) + max(s1_h17) +
max(s1_h18) + max(s1_h19) + max(s1_h20) + max(s1_h21) + max(s1_h22) + max(s1_h23) + max(s1_h24) hours_of_work_system_1
/*, max(s2_h1) hours_of_work_system_2 ...*/
from (select system_number,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 00', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 00', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h1,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 01', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 01', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h2,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 02', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 02', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h3,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 03', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 03', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h4,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 04', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 04', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h5,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 05', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 05', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h6,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 06', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 06', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h7,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 07', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 07', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h8,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 08', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 08', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h9,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 09', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 09', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h10,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 10', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 10', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h11,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 11', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 11', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h12,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 12', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 12', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h13,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 13', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 13', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h14,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 14', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 14', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h15,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 15', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 15', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h16,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 16', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 16', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h17,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 17', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 17', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h18,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 18', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 18', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h19,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 19', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 19', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h20,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 20', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 20', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h21,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 21', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 21', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h22,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 22', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 22', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h23,
case when system_number = 'система_1' and
start_date <= to_date('28.04.2015 23', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 23', 'DD.MM.YYYY hh24')
then 1
else 0
end s1_h24
/*, case when system_number = 'система_2' and
start_date <= to_date('28.04.2015 00', 'DD.MM.YYYY hh24') and
end_date > to_date('28.04.2015 00', 'DD.MM.YYYY hh24')
then 1
else 0
end s2_h1 ...*/
from (select 'система_1' system_number, 1 thread_number, to_date('28.04.2015 11', 'DD.MM.YYYY hh24') start_date,
to_date('28.04.2015 13', 'DD.MM.YYYY hh24') end_date
from dual
UNION ALL
select 'система_1', 2, to_date('28.04.2015 12', 'DD.MM.YYYY hh24'), to_date('28.04.2015 14', 'DD.MM.YYYY hh24')
from dual
/*UNION ALL
select 'система_2', 2, to_date('28.04.2015 00', 'DD.MM.YYYY hh24'), to_date('28.04.2015 01', 'DD.MM.YYYY hh24') from dual ...*/
)
);
Суть - разбивка анализируемого временного интервала на части с последующей проверкой на работу системы в каждой части временного интервала. Затем применение функции max для вынесения конечного решения о том, работала ли система в данной части временной интервал. После чего суммируются части временных интервалов для соответствующих систем.
Если временной интервал разбить не на часы а на минуты или секунды то получится огромная конструкция SQL-запроса... |
