data2fst

# load packages
pkg <- c('data.table', 'dygraphs', 'fst', 'RMySQL', 'xts')
lapply(pkg, require, char = TRUE)

# load all data
dbc <- dbConnect(MySQL(), group = 'dataOps', dbname = 'cycle_hire_ldn')
dts <- data.table(dbGetQuery(dbc,
        "SELECT start_station_id, start_day, start_hour, end_station_id, end_day, end_hour, duration FROM hires"
))
dbDisconnect(dbc)

# save order by start station
setorderv(dts, c('start_station_id', 'start_day', 'start_hour', 'end_station_id', 'end_day', 'end_hour'))
y <- dts[, .N, .(station = start_station_id)]
write.fst(y, 'D:/tmp/fst/idxs')
write.fst(dts, 'D:/tmp/fst/dtss')

# save order by month
setorderv(dts, c('start_day', 'start_station_id', 'start_hour', 'end_station_id', 'end_day', 'end_hour'))
y <- dts[, .N, .(month = substr(start_day, 1, 6))]
write.fst(dts, 'D:/tmp/fst/dtsm')
write.fst(y, 'D:/tmp/fst/idxm')

get_station_data <- function(x, by_station = TRUE, cols = NULL){
    y <- read.fst( paste0('D:/tmp/fst/idx', ifelse(by_station, 's', 'm')), as.data.table = TRUE)
    if(by_station){
        rs <- y[station < x, sum(N)] + 1
        re <- y[station <= x, sum(N)]
    } else {
        if(length(x) == 1){
            rs <- y[month < x, sum(N)] + 1
            re <- y[month <= x, sum(N)]
        } else {
            if(x[1] == '.'){
                rs <- 1
                re <- y[month <= x[2], sum(N)]
            } else if(x[2] == '.'){
                rs <- y[month < x[1], sum(N)] + 1
                re <- y[, sum(N)]
            } else {
                rs <- y[month < x[1], sum(N)] + 1
                re <- y[month <= x[2], sum(N)]
            }
        }
    }
    read.fst( paste0('D:/tmp/fst/dts', ifelse(by_station, 's', 'm')), columns = cols, from = rs, to = re, as.data.table = TRUE)
}

# test
y <- as.data.frame(get_station_data(12)[, .N, .(start_day = as.Date(as.character(start_day), '%Y%m%d'))])
y <- xts(y[, -1], order.by = y[, 1])
dygraph(y) %>%
            dyLegend(width = 700, show = "always", hideOnMouseOut = FALSE) %>%
            dyAxis('y', label = 'Total Number of hires', drawGrid = TRUE) %>%
            dyHighlight( highlightCircleSize = 4, highlightSeriesBackgroundAlpha = 0.4, hideOnMouseOut = TRUE, highlightSeriesOpts = list(strokeWidth = 2) ) %>%
#            dyRangeSelector( dateWindow = c(Sys.Date() - 61, Sys.Date() - 1), height = 30, strokeColor = 'black' ) %>%
            dyRoller(rollPeriod = 7)