r - 从 Postgres DB 对 dplyr 中的时间序列数据进行下采样

Question

我正在尝试使用 dplyr 从 psql 查询数据。我的数据采用以下格式。

date                  name    value
10-12-2012 10:01:00   var1    100
10-12-2012 10:55:00   var1    200
10-12-2012 11:01:00   var1    150
10-12-2012 11:50:00   var1    100
10-12-2012 12:11:00   var1    50
10-12-2012 12:40:00   var1    150
10-12-2012 10:01:00   var2    10
10-12-2012 10:33:00   var2    20
10-12-2012 11:04:00   var2    15
10-12-2012 11:45:00   var2    25
10-12-2012 12:02:00   var2    10
10-12-2012 12:55:00   var2    8

我想将数据聚合到每小时(或预定义的采样时间)，并想要该小时内“值”列的平均值。

要求的输出:

date                  name    value
10-12-2012 10:00:00   var1    150
10-12-2012 11:00:00   var1    125
10-12-2012 12:00:00   var1    100
10-12-2012 10:00:00   var2    15
10-12-2012 11:00:00   var2    20
10-12-2012 12:00:00   var2    9

我可以直接使用这个查询在 postgres 中执行此操作:

"SELECT date_trunc('hour', date), name, mean_arr(array_agg(cast(value as double precision))) FROM TABLENAME WHERE name IN ("var1","var2")

我想知道，我们是否可以使用 dplyr 做同样的事情。

我目前正在 dplyr 中使用此命令连接到数据库:

my_db <- rc_postgres(dbname="DBNAME",host="HOST",port="PORT",user="USER",password="PASSWD")
tbl_df <- tbl(my_db, "TABLENAME")

原始查询如下:

dataOut <- data.frame(tbl_df%>% select(date,name,value) %>% filter(name %in% c('var1','var2') 

请注意，我目前还没有聚合数据，但我想这样做。

如果有人有答案，请告诉我。谢谢普拉迪普

Answer 1

这不会直接与您的 SQL 表一起使用。但这是我会做的:

library(tidyr)
library(dplyr)

df <- tbl_df %>%
  separate(date, into = c("date", "time"), sep = " ") %>%
  separate(time, into = c("hour", "minute", "seccond"), sep = ":") %>%
  group_by(date, hour, name) %>%
  summarise(mean(value)) %>%
  ungroup() %>%
  arrange(name) %>%
  collect()

#> df
#Source: local data frame [6 x 4]
#
#        date hour name mean(value)
#1 10-12-2012   10 var1         150
#2 10-12-2012   11 var1         125
#3 10-12-2012   12 var1         100
#4 10-12-2012   10 var2          15
#5 10-12-2012   11 var2          20
#6 10-12-2012   12 var2           9