powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Посчитать продолжительность пересечений
4 сообщений из 4, страница 1 из 1
Посчитать продолжительность пересечений
    #39980155
007alex3
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Здравствуйте.
Помогите , пожалуйста , посчитать продолжительность пересечений.
Есть таблица:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
with cte as (
select 1 EmployeeId, to_date('20.02.2020 12:15','dd.mm.yyyy HH24:MI') startDate, to_date('20.02.2020 21:15','dd.mm.yyyy HH24:MI') EndDate from dual
union all
select 2 EmployeeId, to_date('20.02.2020 13:15','dd.mm.yyyy HH24:MI') startDate, to_date('20.02.2020 22:15','dd.mm.yyyy HH24:MI') EndDate from dual
union all
select 3 EmployeeId, to_date('20.02.2020 17:15','dd.mm.yyyy HH24:MI') startDate, to_date('20.02.2020 23:15','dd.mm.yyyy HH24:MI') EndDate from dual
union all
select 4 EmployeeId, to_date('20.02.2020 20:15','dd.mm.yyyy HH24:MI') startDate, to_date('21.02.2020 01:15','dd.mm.yyyy HH24:MI') EndDate from dual
union all
select 5 EmployeeId, to_date('20.02.2020 01:15','dd.mm.yyyy HH24:MI') startDate, to_date('20.02.2020 12:00','dd.mm.yyyy HH24:MI') EndDate from dual
union all
select 6 EmployeeId, to_date('20.02.2020 00:15','dd.mm.yyyy HH24:MI') startDate, to_date('20.02.2020 06:00','dd.mm.yyyy HH24:MI') EndDate from dual
)
select *
from cte
order by startDate



нужно вывести следующее:

кол-во рабочих (смены которых пересекаются) | длительность пересечения

2 | 04:45 (пересекаются 6 и 5)
4 | 01:00 (пересекаются 1,2,3,4)
...
Рейтинг: 0 / 0
Посчитать продолжительность пересечений
    #39980165
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Модератор форума
007alex3,

Код: 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.
with cte as ( -- your sample data:
select 1 EmployeeId, to_date('20.02.2020 12:15','dd.mm.yyyy HH24:MI') startDate, to_date('20.02.2020 21:15','dd.mm.yyyy HH24:MI') EndDate from dual
union all
select 2 EmployeeId, to_date('20.02.2020 13:15','dd.mm.yyyy HH24:MI') startDate, to_date('20.02.2020 22:15','dd.mm.yyyy HH24:MI') EndDate from dual
union all
select 3 EmployeeId, to_date('20.02.2020 17:15','dd.mm.yyyy HH24:MI') startDate, to_date('20.02.2020 23:15','dd.mm.yyyy HH24:MI') EndDate from dual
union all
select 4 EmployeeId, to_date('20.02.2020 20:15','dd.mm.yyyy HH24:MI') startDate, to_date('21.02.2020 01:15','dd.mm.yyyy HH24:MI') EndDate from dual
union all
select 5 EmployeeId, to_date('20.02.2020 01:15','dd.mm.yyyy HH24:MI') startDate, to_date('20.02.2020 12:00','dd.mm.yyyy HH24:MI') EndDate from dual
union all
select 6 EmployeeId, to_date('20.02.2020 00:15','dd.mm.yyyy HH24:MI') startDate, to_date('20.02.2020 06:00','dd.mm.yyyy HH24:MI') EndDate from dual
)
,time_points as (
   -- using UNPIVOT to get all time points from all intervals from both tables
   select distinct
      case when pt_type='STARTDATE' then pt else pt + interval '1' second end as pit
   from (select distinct EmployeeId,startDate, EndDate from cte)
   unpivot (
     pt for pt_type in (STARTDATE, ENDDATE)
   )
)
,intervals as (
   -- combining time points into intervals:
   select * 
   from (
      select 
        row_number()over(order by pit) rnk
       ,pit as startDate
       ,lead(pit)over(order by pit)-interval '1'second as EndDate
      from time_points
   )
   where EndDate is not null
)
select i.*, round((i.EndDAte-i.StartDate)*24*60) as e_interval_minutes
      ,v.*
from intervals i
    ,lateral(
        select 
           count(*) cnt
          ,listagg(EmployeeId,',')
            within group(order by rnk) r
        from cte c
        where i.startDate <= c.EndDate
          and i.EndDate   >= c.startDate
    ) v;
     
       RNK STARTDATE           ENDDATE             E_INTERVAL_MINUTES        CNT R
---------- ------------------- ------------------- ------------------ ---------- --------------------
         1 2020-02-20 00:15:00 2020-02-20 01:14:59                 60          1 6
         2 2020-02-20 01:15:00 2020-02-20 06:00:00                285          2 5,6
         3 2020-02-20 06:00:01 2020-02-20 12:00:00                360          1 5
         4 2020-02-20 12:00:01 2020-02-20 12:14:59                 15          0
         5 2020-02-20 12:15:00 2020-02-20 13:14:59                 60          1 1
         6 2020-02-20 13:15:00 2020-02-20 17:14:59                240          2 1,2
         7 2020-02-20 17:15:00 2020-02-20 20:14:59                180          3 1,2,3
         8 2020-02-20 20:15:00 2020-02-20 21:15:00                 60          4 1,2,3,4
         9 2020-02-20 21:15:01 2020-02-20 22:15:00                 60          3 2,3,4
        10 2020-02-20 22:15:01 2020-02-20 23:15:00                 60          2 3,4
        11 2020-02-20 23:15:01 2020-02-21 01:15:00                120          1 4

11 rows selected.
...
Рейтинг: 0 / 0
Посчитать продолжительность пересечений
    #39980303
007alex3
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
xtender,спасибо большое! А можно вывести в две строки и два столбца? Просто именно такой формат выгрузки нужен, к сожалению...
...
Рейтинг: 0 / 0
Посчитать продолжительность пересечений
    #39980318
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
007alex3,

строка cnt=0 разбивает на группы (не покрыто интервальчиком)


.....
stax
...
Рейтинг: 0 / 0
4 сообщений из 4, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Посчитать продолжительность пересечений
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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