是否可以根据开始/结束日期计算有效的换档时间，而停顿了Oracle SQL

发布于 2025-01-29 12:18:46 字数 1026 浏览 3 评论 0原文

我在Oracle

Shift_ID	Timestamp_（Oracle）	type_
00000001	17/05/2022 08:00	0001
00000001	17/05/2022 09:00	0002
00000001	17/05/2022 09:15	0003
0000000001	17/05/2022 12： 00	0002
00000001	17/05/2022 13:00	0003
00000001	17/05/2022 15:00	15:00

type 1是开始时间，类型2是暂停，pause，type 3在暂停之后继续，type 4 is end_time

我想计算有效的偏移时间，我认为这样做的一种方法是计算所有启动和结束时间的总和，例如：（

sum（类型0002） + sum（类型0004））） - （sum（类型0001） + sum （类型0003））

但是如何在Oracle SQL中执行此操作？

我尝试了：选择shift_id，sum（to_number_to_char（timestamp _，'sssss'），其中（type_ = 0002或类型为0004）组通过shift_id，

然后我得到了一个结果 00000001，82442 但是总和不是合适的总和，它仅在午夜之后以秒数计算。

问题：如何获得类似的结果： 00000001，05：45

原文

I have an table in Oracle

shift_id	timestamp_(oracle)	type_
00000001	17/05/2022 08:00	0001
00000001	17/05/2022 09:00	0002
00000001	17/05/2022 09:15	0003
00000001	17/05/2022 12:00	0002
00000001	17/05/2022 13:00	0003
00000001	17/05/2022 15:00	0004

type 1 is starttime, type 2 is pause, type 3 is continue after pause, type 4 is end_time

I want to calculate the effective shift time, one way to do this in my opinion is to calculate the sum of all start and end times and substract them, for example:

(sum(type 0002) + sum (type 0004)) - (sum (type 0001) + sum (type 0003))

But how to do this in oracle sql?

I've tried:
select shift_id, sum(to_number_to_char(timestamp_,'sssss') where (type_ = 0002 or type is 0004) group by shift_id

Then I get a result like
00000001, 82442
But the sum is not the right sum, it only calculates with seconds after midnight.

Question: How to get a result like:
00000001, 05:45

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

源来凯始玺欢你 2025-02-05 12:18:46

您可以使用lag窗口函数来计算时间差，然后将其概括以获取总量-e

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;

href =“ https://dbfiddle.uk/?rdbms=oracle_21& fiddle = 1aea5eee1dfb257b257b27b27b27b27b3219cefd205220522205225225225252525252252525252522525252252522522522522525225252522522522522522522522522522太平洋演示。

You can use LAG window function to calculate the time differencea and thn SUM it to get the total amount -

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;

Demo.

回复收藏 0 原文

岁月打碎记忆 2025-02-05 12:18:46

滞后使您一旦拥有当前值和以前的值

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

，然后根据需要切片和骰子的方式

Doing a LAG gives you access to the current and previous values

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

Once you have that, then you can work out intervals as required, eg

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

or similar depending on how you want to slice and dice

回复收藏 0 原文

~没有更多了~