ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / подсчет интервала
15 сообщений из 15, страница 1 из 1
подсчет интервала
    #39411947
Hans Christian Andersen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hans Christian Andersen
Гость
Добрый день. Я попробовал найти решение своей задачки в предыдущих форумах, но к сожалению не нашел. Поэтому попробую
описать ее кратко и понятно.

Имеeтся интервал START_DATE и END_DATE в группе по WORK_ID.
Нужно подсчитать разничу в часах между END_DATE и START_DATE для каждой строки.
Причем, если интервал пересекается с интервалом предыдущих строк, тогда "пересечение" (наложение) не учитывается (смотри строку с id=2) или учитывается только часть интервала, не попавшая в прересечение (смотри строку с id=3).
Если интервалы в пределах одной и тойже группы повторяются (см. строки с id=8 и id=9 ), тогда подсчитывается интервал только для одной, любой строки.
Сортировка для подсчета интервала выглядит следующим образом order by START_DATE asc, end_date desc.
Т.е. начинается подсчет со самой старой строки по START_DATE, которая имеет наибольший интервал.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
with tab#
as
(
select 1 id, 1 WORK_ID,to_date('20.02.2017 10:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 15:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --5 чac
UNION ALL
select 2, 1 WORK_ID,to_date('20.02.2017 11:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 14:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --0 чac
UNION ALL
select 3,1 WORK_ID,to_date('20.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 17:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --2 чac
UNION ALL
select 4,1 WORK_ID,to_date('20.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 22:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --4 чac
UNION ALL ---------------
select 5,2 WORK_ID,to_date('21.02.2017 12:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 15:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --3 чac
UNION ALL
select 6,2 WORK_ID,to_date('21.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 14:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --0 чac
UNION ALL
select 7,2 WORK_ID,to_date('21.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --5 чac
UNION ALL
select 8,2 WORK_ID,to_date('21.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 22:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --4 чac
UNION ALL
select 9, 2 WORK_ID,to_date('21.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 22:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --0 чac
)
  select * from tab# t
   order by START_DATE asc, end_date desc



Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
   
        ID    WORK_ID START_DATE          END_DATE          
---------- ---------- ------------------- -------------------
         1          1 20.02.2017 10:00:00 20.02.2017 15:00:00
         2          1 20.02.2017 11:00:00 20.02.2017 14:00:00
         3          1 20.02.2017 13:00:00 20.02.2017 17:00:00
         4          1 20.02.2017 18:00:00 20.02.2017 22:00:00
         5          2 21.02.2017 12:00:00 21.02.2017 15:00:00
         7          2 21.02.2017 13:00:00 21.02.2017 18:00:00
         6          2 21.02.2017 13:00:00 21.02.2017 14:00:00
         8          2 21.02.2017 18:00:00 21.02.2017 22:00:00
         9          2 21.02.2017 18:00:00 21.02.2017 22:00:00

Результат, который должен получиться, смотрите в комментариях в with-Clouse.
Заранее спасибо!
...
Рейтинг: 0 / 0
28.02.2017, 19:14
| Ответить | Цитировать | Написать  
подсчет интервала
    #39411955
Hans Christian Andersen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hans Christian Andersen
Гость
Извените, результат для строки с id= 7 - 3 чaca
select 7 id,2 WORK_ID,to_date('21.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE from dual --5 чac 3 чac.
...
Рейтинг: 0 / 0
28.02.2017, 19:19
| Ответить | Цитировать | Написать  
подсчет интервала
    #39412056
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Код: plsql
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.
with tab#
as
(
select 1 id, 1 WORK_ID,to_date('20.02.2017 10:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 15:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --5 ?ac
UNION ALL
select 2, 1 WORK_ID,to_date('20.02.2017 11:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 14:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --0 ?ac
UNION ALL
select 3,1 WORK_ID,to_date('20.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 17:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --2 ?ac
UNION ALL
select 4,1 WORK_ID,to_date('20.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 22:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --4 ?ac
UNION ALL ---------------
select 5,2 WORK_ID,to_date('21.02.2017 12:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 15:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --3 ?ac
UNION ALL
select 6,2 WORK_ID,to_date('21.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 14:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --0 ?ac
UNION ALL
select 7,2 WORK_ID,to_date('21.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --5 ?ac
UNION ALL
select 8,2 WORK_ID,to_date('21.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 22:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --4 ?ac
UNION ALL
select 9, 2 WORK_ID,to_date('21.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 22:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE  from dual --0 ?ac
),
x as (
      select  t.*,
              nvl(
                  greatest(
                           start_date,
                           max(end_date) over(
                                              partition by work_id
                                              order by start_date,end_date
                                              rows between unbounded preceding and 1 preceding
                                             )
                          ),
                  start_date
                 ) new_start_date
        from  tab# t
     )
select  id,
        work_id,
        start_date,
        end_date,
        greatest(0,end_date - new_start_date) * 24 duration_in_hours
  from  x
/

        ID    WORK_ID START_DATE          END_DATE            DURATION_IN_HOURS
---------- ---------- ------------------- ------------------- -----------------
         1          1 02/20/2017 10:00:00 02/20/2017 15:00:00                 5
         2          1 02/20/2017 11:00:00 02/20/2017 14:00:00                 0
         3          1 02/20/2017 13:00:00 02/20/2017 17:00:00                 2
         4          1 02/20/2017 18:00:00 02/20/2017 22:00:00                 4
         5          2 02/21/2017 12:00:00 02/21/2017 15:00:00                 3
         6          2 02/21/2017 13:00:00 02/21/2017 14:00:00                 0
         7          2 02/21/2017 13:00:00 02/21/2017 18:00:00                 3
         8          2 02/21/2017 18:00:00 02/21/2017 22:00:00                 4
         9          2 02/21/2017 18:00:00 02/21/2017 22:00:00                 0

9 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
28.02.2017, 22:51
| Ответить | Цитировать | Написать  
подсчет интервала
    #39412132
fortnet
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fortnet
Участник
SY,

Здорово!

чуть-чуть уточню :

order by start_date,end_date desc
...
Рейтинг: 0 / 0
01.03.2017, 08:58
| Ответить | Цитировать | Написать  
подсчет интервала
    #39412201
Hans Christian Andersen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hans Christian Andersen
Гость
fortnet,

так SY указал сортировку в Windows
partition by work_id
order by start_date,end_date
...
Рейтинг: 0 / 0
01.03.2017, 10:29
| Ответить | Цитировать | Написать  
подсчет интервала
    #39412206
Hans Christian Andersen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hans Christian Andersen
Гость
SY,

спасибо большо!
...
Рейтинг: 0 / 0
01.03.2017, 10:35
| Ответить | Цитировать | Написать  
подсчет интервала
    #39412334
Hans Christian Andersen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hans Christian Andersen
Гость
SY,

а можно усложнить задачу?
Сортировка для подсчета интервалов должна быть по следующим критериям
order by sort_id,start_date, end_date desc;


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
with tab#
as
(
select 1 id, 1 WORK_ID,to_date('20.02.2017 10:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 15:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id  from dual --2
UNION ALL
select 2, 1 WORK_ID,to_date('20.02.2017 11:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 14:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 1 sort_id  from dual --3
UNION ALL
select 3,1 WORK_ID,to_date('20.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 17:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id  from dual --2
UNION ALL
select 4,1 WORK_ID,to_date('20.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 22:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id   from dual --4
UNION ALL ---------------
select 5,2 WORK_ID,to_date('21.02.2017 12:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 15:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id   from dual --3
UNION ALL
select 6,2 WORK_ID,to_date('21.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 14:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id   from dual --0
UNION ALL
select 7,2 WORK_ID,to_date('21.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id   from dual --3
UNION ALL
select 8,2 WORK_ID,to_date('21.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 23:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id   from dual --1
UNION ALL
select 9, 2 WORK_ID,to_date('21.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 22:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id   from dual --4
)
select * from tab#
order by sort_id,start_date, end_date desc;



Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
        ID    WORK_ID START_DATE          END_DATE               SORT_ID
---------- ---------- ------------------- ------------------- ----------
         2          1 20.02.2017 11:00:00 20.02.2017 14:00:00          1
         1          1 20.02.2017 10:00:00 20.02.2017 15:00:00          2
         3          1 20.02.2017 13:00:00 20.02.2017 17:00:00          2
         4          1 20.02.2017 18:00:00 20.02.2017 22:00:00          2
         5          2 21.02.2017 12:00:00 21.02.2017 15:00:00          2
         7          2 21.02.2017 13:00:00 21.02.2017 18:00:00          2
         6          2 21.02.2017 13:00:00 21.02.2017 14:00:00          2
         8          2 21.02.2017 18:00:00 21.02.2017 23:00:00          2
         9          2 21.02.2017 18:00:00 21.02.2017 22:00:00          2
...
Рейтинг: 0 / 0
01.03.2017, 12:50
| Ответить | Цитировать | Написать  
подсчет интервала
    #39412556
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Ожидаемый результат то огласи.

SY.
...
Рейтинг: 0 / 0
01.03.2017, 15:47
| Ответить | Цитировать | Написать  
подсчет интервала
    #39412674
Hans Christian Andersen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hans Christian Andersen
Гость
SY,
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
with tab#
as
(
select 1 id, 1 WORK_ID,to_date('20.02.2017 11:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 15:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id, 1 resultat from dual 
UNION ALL
select 2, 1 WORK_ID,to_date('20.02.2017 11:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 12:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 1 sort_id, 1 resultat  from dual   
UNION ALL
select 3, 1 WORK_ID,to_date('20.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 15:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 1 sort_id, 2 resultat  from dual
UNION ALL
select 4, 1 WORK_ID,to_date('20.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 14:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 1 sort_id, 0 resultat  from dual
UNION ALL
select 5 id, 1 WORK_ID,to_date('20.02.2017 11:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 15:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id, 0 resultat from dual
UNION ALL
select 6,1 WORK_ID,to_date('20.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 17:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id,2 resultat   from dual 
UNION ALL
select 7,1 WORK_ID,to_date('20.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 22:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id, 4 resultat    from dual 
UNION ALL -------------------
select 8 id, 222 WORK_ID,to_date('20.02.2017 09:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 15:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 1 sort_id, 6 resultat from dual 
UNION ALL
select 9, 222 WORK_ID,to_date('20.02.2017 11:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 12:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id, 0 resultat  from dual   
)
select * from tab#
order by work_id,sort_id,start_date, end_date desc;



Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
        ID    WORK_ID START_DATE          END_DATE               SORT_ID   RESULTAT
---------- ---------- ------------------- ------------------- ---------- ----------
         2          1 20.02.2017 11:00:00 20.02.2017 12:00:00          1          1
         3          1 20.02.2017 13:00:00 20.02.2017 15:00:00          1          2
         4          1 20.02.2017 13:00:00 20.02.2017 14:00:00          1          0
         1          1 20.02.2017 11:00:00 20.02.2017 15:00:00          2          1
         5          1 20.02.2017 11:00:00 20.02.2017 15:00:00          2          0
         6          1 20.02.2017 13:00:00 20.02.2017 17:00:00          2          2
         7          1 20.02.2017 18:00:00 20.02.2017 22:00:00          2          4
         8        222 20.02.2017 09:00:00 20.02.2017 15:00:00          1          6
         9        222 20.02.2017 11:00:00 20.02.2017 12:00:00          2          0


Смотри Resultat.
Логика та-же самая. Только подсчет интервалов должен начинатся со строчек, у которых sort_id = 1 (order by start_date, end_date desc).
Группы так же строяться по work_id
При этом известно, что самая минимальная дата min(start_date) от sort_id = 2 >= самой минимальной дате min(start_date) от sort_id = 1 (естественно в своей секции work_id)
...
Рейтинг: 0 / 0
01.03.2017, 17:36
| Ответить | Цитировать | Написать  
подсчет интервала
    #39412677
Hans Christian Andersen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hans Christian Andersen
Гость
SY,

И спасибо Вам за помощь!
...
Рейтинг: 0 / 0
01.03.2017, 17:40
| Ответить | Цитировать | Написать  
подсчет интервала
    #39412773
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Что-то типа:

Код: plsql
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.
with tab#
as
(
select 1 id, 1 WORK_ID,to_date('20.02.2017 11:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 15:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id, 1 resultat from dual
UNION ALL
select 2, 1 WORK_ID,to_date('20.02.2017 11:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 12:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 1 sort_id, 1 resultat  from dual
UNION ALL
select 3, 1 WORK_ID,to_date('20.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 15:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 1 sort_id, 2 resultat  from dual
UNION ALL
select 4, 1 WORK_ID,to_date('20.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 14:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 1 sort_id, 0 resultat  from dual
UNION ALL
select 5 id, 1 WORK_ID,to_date('20.02.2017 11:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 15:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id, 0 resultat from dual
UNION ALL
select 6,1 WORK_ID,to_date('20.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 17:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id,2 resultat   from dual
UNION ALL
select 7,1 WORK_ID,to_date('20.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 22:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id, 4 resultat    from dual
UNION ALL -------------------
select 8 id, 222 WORK_ID,to_date('20.02.2017 09:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 15:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 1 sort_id, 6 resultat from dual
UNION ALL
select 9, 222 WORK_ID,to_date('20.02.2017 11:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 12:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id, 0 resultat  from dual
),
x as (
      select  t.*,
              least(
                    start_date,
                    lag(end_date,1,start_date) over(
                                                    partition by work_id
                                                    order by sort_id,start_date,end_date desc,id
                                                   )
                   ) gap_start_date,
              start_date gap_end_date,
              row_number() over(
                                partition by work_id
                                order by sort_id,start_date,end_date desc,id
                               ) rn,
              row_number() over(
                                partition by work_id,start_date,end_date
                                order by sort_id,id
                               ) is_dup,
              nvl(
                  greatest(
                           start_date,
                           max(end_date) over(
                                              partition by work_id
                                              order by sort_id,start_date,end_date desc,id
                                              rows between unbounded preceding and 1 preceding
                                             )
                          ),
                  start_date
                 ) new_start_date
        from  tab# t
     )
select  id,
        work_id,
        start_date,
        end_date,
        sort_id,
        resultat,
        case
          when is_dup > 1 then 0
          else (
                (
                 select  nvl(
                             sum(greatest(least(y.gap_end_date,x.end_date) - greatest(y.gap_start_date,x.start_date),0)),
                             0
                            )
                   from  x y
                   where y.work_id = x.work_id
                     and y.rn < x.rn
                ) + greatest(0,end_date - new_start_date)
               ) * 24
        end duration_in_hours
  from  x
  order by work_id,
           sort_id,
           start_date,
           end_date desc
/

        ID    WORK_ID START_DATE          END_DATE               SORT_ID   RESULTAT DURATION_IN_HOURS
---------- ---------- ------------------- ------------------- ---------- ---------- -----------------
         2          1 02/20/2017 11:00:00 02/20/2017 12:00:00          1          1                 1
         3          1 02/20/2017 13:00:00 02/20/2017 15:00:00          1          2                 2
         4          1 02/20/2017 13:00:00 02/20/2017 14:00:00          1          0                 0
         1          1 02/20/2017 11:00:00 02/20/2017 15:00:00          2          1                 1
         5          1 02/20/2017 11:00:00 02/20/2017 15:00:00          2          0                 0
         6          1 02/20/2017 13:00:00 02/20/2017 17:00:00          2          2                 2
         7          1 02/20/2017 18:00:00 02/20/2017 22:00:00          2          4                 4
         8        222 02/20/2017 09:00:00 02/20/2017 15:00:00          1          6                 6
         9        222 02/20/2017 11:00:00 02/20/2017 12:00:00          2          0                 0

9 rows selected.

SQL>




SY.
...
Рейтинг: 0 / 0
01.03.2017, 19:21
| Ответить | Цитировать | Написать  
подсчет интервала
    #39412992
Hans Christian Andersen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hans Christian Andersen
Гость
SY,

Круто!
Сейчас буду разбираться.
...
Рейтинг: 0 / 0
02.03.2017, 10:47
| Ответить | Цитировать | Написать  
подсчет интервала
    #39413735
Hans Christian Andersen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hans Christian Andersen
Гость
fortnet,

sorry. Сортировку нужно поправить

ORDER BY START_DATE asc, end_date desc
...
Рейтинг: 0 / 0
03.03.2017, 15:35
| Ответить | Цитировать | Написать  
подсчет интервала
    #39539491
Hans Christian Andersen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hans Christian Andersen
Гость
Дорый день!
Предыдущая задача усложнилась.
Нужно подсчитать, сколько чистого времени затратил каждый team для ремонта одного объекта WORK_ID.
Причем, если интервал времени работы на объекте одного тима пересекается с интервалом другого, тогда интервалы, которые пересекаются, нельзя учитывать при подсчете времени работы следующих тимов.

Следующие условия должны быть соблюдены:
1. Подсчет чистого времени работы производится для объекта WORK_ID и тима, который в на этом объекте работал.
2. Пересечение интервалов учитывается только по объекту (WORK_ID), где велась работа (Partition By WORK_ID)
3. При подсчете чистого времени работы важно исключить предыдущие интервалы работы, если они пересекаются.
4. Сортировка для подсчета чистого времени работ одного тима должна производиться по такой схеме
Код: plsql
1.
order by sort_id,start_date, end_date desc;


5. Один и тот же тим имеет только одну запись в таблице про объект (PK = WORK_ID, TEAM_ID)
Вот пример с ожидаемым результатом DURATION_IN_HOURS

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
with tab#
as
(
--Объект Work_id 1-------------------------------------------------------------------------------------------------------------------------------------------------------------------
select 1 WORK_ID, 1 as team_id, to_date('20.02.2017 11:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 14:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 1 sort_id, 3 as DURATION_IN_HOURS  from dual
UNION ALL
select  1 WORK_ID, 2 as team_id, to_date('20.02.2017 07:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 12:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id, 4 as DURATION_IN_HOURS  from dual 
UNION ALL
select 1 WORK_ID, 3 as team_id, to_date('20.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 17:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id, 3 as DURATION_IN_HOURS  from dual
UNION ALL
select 1 WORK_ID, 4 as team_id, to_date('20.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('20.02.2017 22:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id, 4 as DURATION_IN_HOURS   from dual
UNION ALL --Объект Work_id 2-------------------------------------------------------------------------------------------------------------------------------------------------------
select 2 WORK_ID, 1 as team_id, to_date('21.02.2017 12:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 15:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 1 sort_id, 3 as DURATION_IN_HOURS   from dual
UNION ALL
select 2 WORK_ID, 2 as team_id, to_date('21.02.2017 10:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 16:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id, 3 as DURATION_IN_HOURS   from dual 
UNION ALL
select 2 WORK_ID, 3 as team_id, to_date('21.02.2017 13:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id, 2 as DURATION_IN_HOURS   from dual 
UNION ALL
select 2 WORK_ID, 4 as team_id, to_date('21.02.2017 18:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 23:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id, 5 as DURATION_IN_HOURS   from dual 
UNION ALL
select  2 WORK_ID,5 as team_id, to_date('21.02.2017 19:00:00','dd.mm.yyyy HH24:MI:SS') START_DATE, to_date('21.02.2017 22:00:00','dd.mm.yyyy HH24:MI:SS') as END_DATE, 2 sort_id, 0 as DURATION_IN_HOURS   from dual 
)
select * from tab#
order by work_id, prior_team,start_date, end_date desc;



Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
   WORK_ID    TEAM_ID START_DATE END_DATE    SORT_ID DURATION_IN_HOURS
---------- ---------- ---------- -------- ---------- -----------------
         1          1 20.02.17   20.02.17          1                 3
         1          2 20.02.17   20.02.17          2                 4
         1          3 20.02.17   20.02.17          2                 3
         1          4 20.02.17   20.02.17          2                 4
         2          1 21.02.17   21.02.17          1                 3
         2          2 21.02.17   21.02.17          2                 3
         2          3 21.02.17   21.02.17          2                 2
         2          4 21.02.17   21.02.17          2                 5
         2          5 21.02.17   21.02.17          2                 0

9 rows selected.
...
Рейтинг: 0 / 0
20.10.2017, 12:50
| Ответить | Цитировать | Написать  
подсчет интервала
    #39540369
Hans Christian Andersen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hans Christian Andersen
Гость
Hans Christian AndersenДорый день!
Предыдущая задача усложнилась.
Нужно подсчитать, сколько чистого времени затратил каждый team для ремонта одного объекта WORK_ID.


Решыл выделить последний вопрос в отдельную тему: "подсчет длительности интервала, исключая предыдущие интервалы"
http://www.sql.ru/forum/1274857/podschet-dlitelnosti-intervala-iskluchaya-predydushhie-intervaly
...
Рейтинг: 0 / 0
23.10.2017, 12:05
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / подсчет интервала
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=141&tid=1885061]:
 0ms
get settings:
 6ms
get forum list:
 11ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 39ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 35ms
get tp. blocked users:
 1ms
others: 204ms
total:  309ms
созд. / загр.: 309ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]