如何计算每小时的“停机时间”

q9yhzks0  于 2021-06-19  发布在  Mysql
关注(0)|答案(1)|浏览(1060)

我已经计算了停机时间,但我想显示为“每小时停机时间”。请参见下图。

在上表中,时间计算如下
[停机时间]=[开始时间]-[停止时间]
但是我想计算每天每小时的停机时间,如下所示↓ 形象。

我想在火堆里做这个。我想我必须在r或terr中做一个函数才能做到这一点,但我不知道。
我真的很感谢你的帮助。谢谢!
娜塔莎。

7rtdyuoh

7rtdyuoh1#

有点难,因为没有提供样本数据。。所以。。我用我自己的(见下文)
停工日期示例


# id                from                  to

# 1:  1 2018-01-02 14:51:30 2018-01-02 19:55:44

# 2:  2 2018-01-05 16:00:30 2018-01-07 10:08:39

首先是结果

library( lubridate )
library( data.table )
library( ggplot2 )

# table with downtimes

df.down <- data.frame( id = c(1,2),
                    from = c( as.POSIXct( "2018-01-02 14:51:30", format = "%Y-%m-%d %H:%M:%S"),as.POSIXct( "2018-01-05 16:00:30", format = "%Y-%m-%d %H:%M:%S") ),
                    to   = c( as.POSIXct( "2018-01-02 19:55:44", format = "%Y-%m-%d %H:%M:%S"),as.POSIXct( "2018-01-07 10:08:39", format = "%Y-%m-%d %H:%M:%S") ),
                    stringsAsFactors = FALSE )

# id                from                  to

# 1:  1 2018-01-02 14:51:30 2018-01-02 19:55:44

# 2:  2 2018-01-05 16:00:30 2018-01-07 10:08:39

# create a sequence of minutes

df.min <- data.frame( from = seq( from = as.POSIXct( "2018-01-01"), to = as.POSIXct("2018-01-8"), by = "1 min" ),
                      stringsAsFactors = FASLE ) %>% 
  mutate( to = lead( from ) ) %>%
  #remove the last row
  filter( !row_number() == n())

# from                  to

# 1: 2018-01-01 00:00:00 2018-01-01 00:01:00

# 2: 2018-01-01 00:01:00 2018-01-01 00:02:00

# 3: 2018-01-01 00:02:00 2018-01-01 00:03:00

# 4: 2018-01-01 00:03:00 2018-01-01 00:04:00

# 5: 2018-01-01 00:04:00 2018-01-01 00:05:00

# ---

# 43196: 2018-01-30 23:55:00 2018-01-30 23:56:00

# 43197: 2018-01-30 23:56:00 2018-01-30 23:57:00

# 43198: 2018-01-30 23:57:00 2018-01-30 23:58:00

# 43199: 2018-01-30 23:58:00 2018-01-30 23:59:00

# 43200: 2018-01-30 23:59:00 2018-01-31 00:00:00

# set as data.tables

setDT(df.min)
setDT(df.down)

# set keys for overlap join

setkey(df.down, from, to)

# overlap join

dt <- foverlaps(df.min, df.down, type = "within", mult = "first", nomatch = NA)

# add variables

dt[, i.from := lubridate::force_tz(dt$i.from, tzone = "UTC")]
dt[, date := as.character( as.Date( i.from ))]
dt[, hour := lubridate::hour( i.from )]
dt[!is.na(id), percentage_down := 100/60 ]

# calculate result

result <- dt[, sum( percentage_down, na.rm = TRUE ), by = list( date, hour)][]

# > result[ V1 >0 ]

# date hour        V1

# 1: 2018-01-02   14  13.33333

# 2: 2018-01-02   15 100.00000

# 3: 2018-01-02   16 100.00000

# 4: 2018-01-02   17 100.00000

# 5: 2018-01-02   18 100.00000

# 6: 2018-01-02   19  91.66667

# 7: 2018-01-05   16  98.33333

# 8: 2018-01-05   17 100.00000

# 9: 2018-01-05   18 100.00000

# 10: 2018-01-05   19 100.00000

# prepare for plot

result[, timestamp := as.POSIXct( paste0( date, " ", hour ), format = "%Y-%m-%d %H", tz = "UTC") ]

# plot

ggplot( result, aes( x = timestamp, y = V1 ) ) + geom_bar( stat = "identity", fill = "lightblue", color = "black")

相关问题