Можно ли рассчитать эффективное время смены на основе даты начала/окончания и пауз в oracle sql

У меня есть таблица в Oracle

shift_idtimestamp_(оракул)тип_
0000000105.17.2022 08:000001
0000000105.17.2022 09:000002
0000000105.17.2022 09:150003
0000000105.17.2022 12:000002
0000000117.05.2022 13:000003
0000000117.05.2022 15:000004

тип 1 — время начала, тип 2 — пауза, тип 3 — продолжение после паузы, тип 4 — время окончания

Я хочу рассчитать эффективное время смены, один из способов сделать это, на мой взгляд, - рассчитать сумму всех времен начала и окончания и вычесть их, например:

(сумма (тип 0002) + сумма (тип 0004)) - (сумма (тип 0001) + сумма (тип 0003))

Но как это сделать в оракуле sql?

Я пробовал: выберите shift_id, sum(to_number_to_char(timestamp_,'sssss') где (type_ = 0002 или тип 0004) сгруппируйте по shift_id

Затем я получаю результат вроде 00000001, 82442 Но сумма неправильная, она рассчитывается только с секундами после полуночи.

Вопрос: Как получить такой результат: 00000001, 05:45

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
0
32
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Выполнение LAG дает вам доступ к текущим и предыдущим значениям

select 
  shift_id,
  typ,
  tstamp,
  lag(typ) over ( order by tstamp ) prev_typ,
  lag(tstamp) over ( order by tstamp ) prev_tstamp
from ...

Как только вы это сделаете, вы можете определить интервалы по мере необходимости, например

select 
  shift_id,
  min(case when typ = 1 then tstamp end ) start_time
  max(case when typ = 4 then tstamp end ) end_time
  sum(case when typ in (2,4) then tstamp - prev_tstamp  end ) 
from 
  ( < above > 
group by shift_id

или аналогичный в зависимости от того, как вы хотите нарезать и нарезать кубиками

Ответ принят как подходящий

Вы можете использовать оконную функцию LAG, чтобы вычислить разницу во времени, а затем СУММИТЬ ее, чтобы получить общую сумму -

CALC AS (SELECT d.*,
                EXTRACT(DAY FROM timestamp_oracle - LAG(timestamp_oracle) OVER(PARTITION BY shift_id ORDER BY timestamp_oracle)) * 24 * 60 +
                EXTRACT(HOUR FROM timestamp_oracle - LAG(timestamp_oracle) OVER(PARTITION BY shift_id ORDER BY timestamp_oracle)) * 60 +  
                EXTRACT(MINUTE FROM timestamp_oracle - LAG(timestamp_oracle) OVER(PARTITION BY shift_id ORDER BY timestamp_oracle)) tm
  FROM data d)
  SELECT shift_id,
         TRUNC(ROUND(SUM(CASE WHEN type_ <> '0003' then tm else null end))/ 60) || ':' ||
         MOD(ROUND(SUM(CASE WHEN type_ <> '0003' then tm else null end)), 60) tot_tm
    from calc
   GROUP BY shift_id;

Демо.

Привет, Анкит Баджпай, спасибо за ответ. Демо работало идеально. Только, видимо, формат временных меток в моей базе данных работает не так, как ожидалось; Я просмотрел тип данных, и это TIMESTAMP(6) для timestamp_(oracle). Поэтому, когда я запускаю запрос, как предполагалось, я получаю сообщение об ошибке: ORA-00932: несовместимые типы данных: ожидаемое ЧИСЛО получило ИНТЕРВАЛ ДЕНЬ ДО ВТОРОЙ. Вы тоже знаете, как это исправить?

Dreqnoid 17.05.2022 13:28

@Dreqnoid, не могли бы вы опубликовать оператор создания таблицы для своей таблицы?

Ankit Bajpai 17.05.2022 13:38

Здравствуйте, это невозможно, потому что он создан поставщиком, у меня есть доступ к базе данных, есть ли команда, которую я могу выполнить?

Dreqnoid 17.05.2022 13:45

@Dreqnoid, попробуйте отредактированный запрос.

Ankit Bajpai 17.05.2022 13:49

Работает как часы!! Спасибо за ваше решение, я не совсем понимаю, что здесь происходит, но я изучу это.

Dreqnoid 17.05.2022 14:26

Привет, Анкит, можно ли включить секунды? Не все времена в таблице имеют ноль секунд.

Dreqnoid 17.05.2022 20:03

@Dreqnoid, включите секунды в свои данные и в запрос вместе с днями, часами и минутами. Вы получите это в результате.

Ankit Bajpai 17.05.2022 21:33

Давайте продолжить обсуждение в чате.

Ankit Bajpai 18.05.2022 00:34

Другие вопросы по теме