У меня есть таблица в Oracle
shift_id | timestamp_(оракул) | тип_ |
---|---|---|
00000001 | 05.17.2022 08:00 | 0001 |
00000001 | 05.17.2022 09:00 | 0002 |
00000001 | 05.17.2022 09:15 | 0003 |
00000001 | 05.17.2022 12:00 | 0002 |
00000001 | 17.05.2022 13:00 | 0003 |
00000001 | 17.05.2022 15:00 | 0004 |
тип 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
Выполнение 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;
@Dreqnoid, не могли бы вы опубликовать оператор создания таблицы для своей таблицы?
Здравствуйте, это невозможно, потому что он создан поставщиком, у меня есть доступ к базе данных, есть ли команда, которую я могу выполнить?
@Dreqnoid, попробуйте отредактированный запрос.
Работает как часы!! Спасибо за ваше решение, я не совсем понимаю, что здесь происходит, но я изучу это.
Привет, Анкит, можно ли включить секунды? Не все времена в таблице имеют ноль секунд.
@Dreqnoid, включите секунды в свои данные и в запрос вместе с днями, часами и минутами. Вы получите это в результате.
Давайте продолжить обсуждение в чате.
Привет, Анкит Баджпай, спасибо за ответ. Демо работало идеально. Только, видимо, формат временных меток в моей базе данных работает не так, как ожидалось; Я просмотрел тип данных, и это TIMESTAMP(6) для timestamp_(oracle). Поэтому, когда я запускаю запрос, как предполагалось, я получаю сообщение об ошибке: ORA-00932: несовместимые типы данных: ожидаемое ЧИСЛО получило ИНТЕРВАЛ ДЕНЬ ДО ВТОРОЙ. Вы тоже знаете, как это исправить?