Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Дублирование с форматированием строк  [new]
drunya2999
Member

Откуда:
Сообщений: 6
Здравствуйте!
бяда...
(читайте внимательно, а то запутаетесь во времени :D)
(ещё лучше нарисовать график)

есть таблица с простоями [id][время начала простоя][время окончания простоя][и прочая ненужная ерунда]
id | start_time | stop_time
рабочая смена с 19:00 до 19:00 следующего дня (с вечера до вечера)
предположим есть простой с [20.10.2020 18:00:00] по [21.10.2020 20:00:00]
он захватывает 2 дня и 3 (!) рабочих смены (один час от смены с 19 на 20 число, всю смену с 20 на 21, один час с 21 на 22)
необходимо вытащить этот простой и разбить его на три простоя (три одинаковые строки с одним id, но с разным временем) по рабочим сменам
1. с [20.10.2020 18:00:00] по [20.10.2020 19:00:00]
2. с [20.10.2020 19:00:00] по [21.10.2020 19:00:00]
3. с [21.10.2020 19:00:00] по [21.10.2020 20:00:00]
Буду очень признателен за помощь!
26 мар 20, 09:55    [22106144]     Ответить | Цитировать Сообщить модератору
 Re: Дублирование с форматированием строк  [new]
-2-
Member

Откуда:
Сообщений: 15279
drunya2999,

смены join простои
26 мар 20, 10:04    [22106150]     Ответить | Цитировать Сообщить модератору
 Re: Дублирование с форматированием строк  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2260
drunya2999,

1) start/stop сместить влево на 19:00
2) размножить по днях
3) greatest/least начало/конец простоя в день
4) сместить вправо на 19:00

.....
stax
26 мар 20, 10:08    [22106152]     Ответить | Цитировать Сообщить модератору
 Re: Дублирование с форматированием строк  [new]
drunya2999
Member

Откуда:
Сообщений: 6
Stax,

Это всё круто очень, но забыл сказать...
этим запросом надо вытаскивать простои, которые могут длиться одну рабочую смену, могут длиться 2 и 3 и более
мы заранее не знаем о иго длительности в рабочих сменах
а в итоговой таблице должно получиться следующее:
если простой был пару часов за одну рабочую смену, то в итоговой таблице он будет выглядеть как одна строка с датой и временем начала и окончания
простой, продлившийся пару часов, который начался в конце одной смены, а закончился в начале следующей, должен выглядеть как две строки, у первой дата и время начала совпадают с временем начала простоя, а время окончания 19:00 того же дня.
вторая строка соответственно начало в 19:00 этого дня, а окончание совпадает с окончанием простоя

суть в том, что простой может быть несколько рабочих смен (от 19:00 до19:00)
если простой 5 рабочих смен, то и строк должно быть соответствующее

Сообщение было отредактировано: 26 мар 20, 11:05
26 мар 20, 10:59    [22106180]     Ответить | Цитировать Сообщить модератору
 Re: Дублирование с форматированием строк  [new]
-2-
Member

Откуда:
Сообщений: 15279
drunya2999
о иго
Перед гласными звуками пишется предлог об.
26 мар 20, 11:05    [22106188]     Ответить | Цитировать Сообщить модератору
 Re: Дублирование с форматированием строк  [new]
drunya2999
Member

Откуда:
Сообщений: 6
-2-,

не смущает то что я там вообще написал о Иго?
:D
26 мар 20, 11:07    [22106192]     Ответить | Цитировать Сообщить модератору
 Re: Дублирование с форматированием строк  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2260
drunya2999,
SQL> ed
Wrote file afiedt.buf

  1  with t (id, start_time, stop_time) as (
  2  select 1, to_date('20.10.2020 18:00:00','dd.mm.yyyy hh24:mi:ss')
  3                ,to_date('21.10.2020 20:00:00','dd.mm.yyyy hh24:mi:ss') from dual union all
  4  select 2, to_date('20.10.2020 19:00:00','dd.mm.yyyy hh24:mi:ss')
  5                ,to_date('20.10.2020 18:59:59','dd.mm.yyyy hh24:mi:ss') from dual union all
  6  select 3, to_date('18.10.2020 18:00:00','dd.mm.yyyy hh24:mi:ss')
  7                ,to_date('21.10.2020 20:00:00','dd.mm.yyyy hh24:mi:ss') from dual
  8  )
  9  ,tt as (select id,start_time,stop_time,start_time-19/24 st,stop_time-19/24 et from t)
 10  select id,start_time,stop_time,st+19/24+level-1 start_ti,st+19/24+level-1 stop_ti from tt
 11*  connect by level<=(trunc(et)-trunc(st))+1  and id=prior id and prior SYS_GUID() is not null
SQL> /

        ID START_TIME          STOP_TIME           START_TI            STOP_TI
---------- ------------------- ------------------- ------------------- -------------------
         1 20.10.2020 18:00:00 21.10.2020 20:00:00 20.10.2020 18:00:00 20.10.2020 18:00:00
         1 20.10.2020 18:00:00 21.10.2020 20:00:00 21.10.2020 18:00:00 21.10.2020 18:00:00
         1 20.10.2020 18:00:00 21.10.2020 20:00:00 22.10.2020 18:00:00 22.10.2020 18:00:00
         2 20.10.2020 19:00:00 20.10.2020 18:59:59 20.10.2020 19:00:00 20.10.2020 19:00:00
         3 18.10.2020 18:00:00 21.10.2020 20:00:00 18.10.2020 18:00:00 18.10.2020 18:00:00
         3 18.10.2020 18:00:00 21.10.2020 20:00:00 19.10.2020 18:00:00 19.10.2020 18:00:00
         3 18.10.2020 18:00:00 21.10.2020 20:00:00 20.10.2020 18:00:00 20.10.2020 18:00:00
         3 18.10.2020 18:00:00 21.10.2020 20:00:00 21.10.2020 18:00:00 21.10.2020 18:00:00
         3 18.10.2020 18:00:00 21.10.2020 20:00:00 22.10.2020 18:00:00 22.10.2020 18:00:00

9 rows selected.


размножил строки
осталось внимательно прописать (least/greatest) для start_ti/stop_ti

.....
stax
26 мар 20, 11:39    [22106215]     Ответить | Цитировать Сообщить модератору
 Re: Дублирование с форматированием строк  [new]
drunya2999
Member

Откуда:
Сообщений: 6
Слегка не пойму, как такое возможно...
дата окончания раньше даты начала на 1 секунду
Stax


  4  select 2, to_date('20.10.2020 19:00:00','dd.mm.yyyy hh24:mi:ss')
  5                ,to_date('20.10.2020 18:59:59','dd.mm.yyyy hh24:mi:ss') from dual union all


 
ID            START_TIME          STOP_TIME            START_TI               STOP_TI
---------- ------------------- ------------------- ------------------- -------------------
         2 20.10.2020 19:00:00 20.10.2020 18:59:59 20.10.2020 19:00:00 20.10.2020 19:00:00


Ладно, не суть...

Вопрос тогда появился ещё один к вам
Как же мне взять и в нужные ячейки записывать нужную дату и время 19:00:00?
Каким образом это правильно организовать?
Двойная благодарность за помощь
26 мар 20, 15:13    [22106374]     Ответить | Цитировать Сообщить модератору
 Re: Дублирование с форматированием строк  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2260
drunya2999
Слегка не пойму, как такое возможно...
дата окончания раньше даты начала на 1 секунду

ето пример как размножить строки, врема начала конца надо досчитать

нужно внимательно/аккуратно для строк прописать формулы start/stop

а с внимательностью у меня напряг


.....
stax
26 мар 20, 15:28    [22106386]     Ответить | Цитировать Сообщить модератору
 Re: Дублирование с форматированием строк  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2260
drunya2999

Как же мне взять и в нужные ячейки записывать нужную дату и время 19:00:00?

не люблю такие задачки, нужно аккуратненько внимательно проверить на разных интервалах (особенно граничных)
часто ето рутинная работа, причем иногда баг может жить достаточно долго, напр для простоя в 1-2 сек

нюансики поправите, мож я что-то упустил

  1  with t (id, start_time, stop_time) as (
  2  select 1, to_date('20.10.2020 18:00:00','dd.mm.yyyy hh24:mi:ss')
  3                ,to_date('21.10.2020 20:00:00','dd.mm.yyyy hh24:mi:ss') from dual union all
  4  select 2, to_date('20.10.2020 18:59:59','dd.mm.yyyy hh24:mi:ss')
  5                ,to_date('20.10.2020 19:00:00','dd.mm.yyyy hh24:mi:ss') from dual union all
  6  select 3, to_date('18.10.2020 18:00:00','dd.mm.yyyy hh24:mi:ss')
  7                ,to_date('21.10.2020 20:00:00','dd.mm.yyyy hh24:mi:ss') from dual
  8  )
  9  ,tt as (select id,start_time,stop_time,trunc(start_time-19/24) st,trunc(stop_time-19/24) et from t)
 10  select
 11     id
 12    ,start_time
 13    ,stop_time
 14    ,greatest(st+level-1+19/24,start_time) start_ti
 15    ,least(st+level+19/24-1/24/60/60,stop_time) stop_ti from tt
 16*  connect by level<=(et-st)+1  and id=prior id and prior SYS_GUID() is not null
SQL> /

        ID START_TIME          STOP_TIME           START_TI            STOP_TI
---------- ------------------- ------------------- ------------------- -------------------
         1 20.10.2020 18:00:00 21.10.2020 20:00:00 20.10.2020 18:00:00 20.10.2020 18:59:59
         1 20.10.2020 18:00:00 21.10.2020 20:00:00 20.10.2020 19:00:00 21.10.2020 18:59:59
         1 20.10.2020 18:00:00 21.10.2020 20:00:00 21.10.2020 19:00:00 21.10.2020 20:00:00
         2 20.10.2020 18:59:59 20.10.2020 19:00:00 20.10.2020 18:59:59 20.10.2020 18:59:59
         2 20.10.2020 18:59:59 20.10.2020 19:00:00 20.10.2020 19:00:00 20.10.2020 19:00:00
         3 18.10.2020 18:00:00 21.10.2020 20:00:00 18.10.2020 18:00:00 18.10.2020 18:59:59
         3 18.10.2020 18:00:00 21.10.2020 20:00:00 18.10.2020 19:00:00 19.10.2020 18:59:59
         3 18.10.2020 18:00:00 21.10.2020 20:00:00 19.10.2020 19:00:00 20.10.2020 18:59:59
         3 18.10.2020 18:00:00 21.10.2020 20:00:00 20.10.2020 19:00:00 21.10.2020 18:59:59
         3 18.10.2020 18:00:00 21.10.2020 20:00:00 21.10.2020 19:00:00 21.10.2020 20:00:00

10 rows selected.


.....
stax
26 мар 20, 16:02    [22106415]     Ответить | Цитировать Сообщить модератору
 Re: Дублирование с форматированием строк  [new]
drunya2999
Member

Откуда:
Сообщений: 6
Stax,

Хорошее решение, благодарю!
Я пока ждал, решил более "неделикатным" способом

case when TB.START_DATE + (level-1) = TB.START_DATE then TB.START_DATE ELSE TRUNC(TB.START_DATE)+19/24+1/86400+(level-1) end startD,
case when TB.START_DATE + (level) > TB.STOP_DATE then TB.STOP_DATE ELSE TRUNC(TB.START_DATE)+19/24+(level) end stopD,


Надеюсь за такое мне руки не вырвут :D

Вопрос ещё...
а что если нужно делить не только в 19:00, но в нескольких точках, например в 7:00 и 19:00
сами точки устанавливаются пользователем (какие он захочет и их может быть больше 1)
результат уже должен получиться с большим количеством строк:

1. начало простоя - одна из ближайших точек деления
2. предыдущая ближайшая точка деления - следующая точка
......................................................................................
N. предыдущая ближайшая точка деления - конец простоя


Сообщение было отредактировано: 27 мар 20, 08:51
27 мар 20, 08:42    [22106687]     Ответить | Цитировать Сообщить модератору
 Re: Дублирование с форматированием строк  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2260
drunya2999

а что если нужно делить не только в 19:00

обычно так и есть (смен не одна)

я б забил на чистоту sql и сделал ф-цию, пусть размножает

.....
stax
27 мар 20, 09:46    [22106707]     Ответить | Цитировать Сообщить модератору
 Re: Дублирование с форматированием строк  [new]
drunya2999
Member

Откуда:
Сообщений: 6
Stax,

Это понятно.
Но мне нужно это всё замутить в MatView
27 мар 20, 11:28    [22106800]     Ответить | Цитировать Сообщить модератору
 Re: Дублирование с форматированием строк  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2260
drunya2999
Stax,

Это понятно.
Но мне нужно это всё замутить в MatView


разве в MatView нельзя использовать ф-ции?

....
stax
27 мар 20, 12:23    [22106839]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить