monetdb, group by

Question

Working in MonetDB, I have an 'entrytime' column that contains a time value. I have:

   select "minute"(entrytime) as unitTime, period
   from schema1.zones
   where date1 >=  eDate and date1 <= xDate and id=256
   group by  unitTime, period

But I want to group the results on a 15 minute boundary, and then return the greatest 'period' value for that 15 minute block, any ideas?

Thanks.

score 0 · Answer 1 · answered Feb 05 '17 at 07:46

here's an example R solution. i think you just need to convert your entrytime column into the intervals that you want? then you can run a group by with MAX()?

# run this line once
# install.packages( "MonetDBLite" )

library(DBI)

x <- data.frame( entrytime = sample( 1:1000 , 10000 , replace = TRUE ) , period = sample( 1:10 , 10000 , replace = TRUE ) )

head( x )

x$intervals <- findInterval( x$entrytime , seq( 0 , 1000 , by = 15 ) )

db <- dbConnect( MonetDBLite::MonetDBLite() )

dbWriteTable( db , 'x' , x )

# max period for distinct minutes
head( dbGetQuery( db , "SELECT entrytime , MAX( period ) FROM x GROUP BY entrytime" ) )

# max period for 15 minute intervals
head( dbGetQuery( db , "SELECT intervals , MAX( period ) FROM x GROUP BY intervals" ) )

monetdb, group by

1 Answers1