Construct an SQLite query — sqlStatement • rsyncrosim

Creates SELECT, GROUP BY and WHERE SQL statements. The resulting list of SQL statements will be converted to an SQLite database query by the datasheet function.

sqlStatement(
  groupBy = NULL,
  aggregate = NULL,
  aggregateFunction = "SUM",
  where = NULL
)

Arguments

groupBy: character string or vector of these. Vector of variables (column names) to GROUP BY (optional)
aggregate: character string of vector of these. Vector of variables (column names) to aggregate using aggregateFunction (optional)
aggregateFunction: character string. An SQL aggregate function (e.g. SUM, COUNT). Default is SUM
where: named list. A list of subset variables. Names are column names, and elements are the values to be selected from each column (optional)

Value

Returns a list of SELECT, GROUP BY and WHERE SQL statements used by the datasheet function to construct an SQLite database query.

Details

Variables are column names of the Datasheet. See column names using datasheet(,empty=TRUE) Variables not included in groupBy, aggregate or where will be dropped from the table. Note that it is not possible to construct a complete SQL query at this stage, because the datasheet function may add ScenarioID and/or ProjectID to the query.

Examples

# \donttest{
# Query total Amount for each combination of ScenarioID, Iteration, Timestep and StateLabelXID,
# including only Timesteps 0,1 and 2, and Iterations 3 and 4.
mySQL <- sqlStatement(
  groupBy = c("ScenarioID", "Iteration", "Timestep"),
  aggregate = c("yCum"),
  aggregateFunction = "SUM",
  where = list(Timestep = c(0, 1, 2), Iteration = c(3, 4))
)
mySQL
#> $select
#> [1] "SELECT ScenarioID,Iteration,Timestep,SUM(yCum) AS yCum"
#> 
#> $groupBy
#> [1] "GROUP BY ScenarioID,Iteration,Timestep"
#> 
#> $where
#> [1] "WHERE (Timestep IN (0,1,2)) AND (Iteration IN (3,4))"
#> 

# The SQL statement can then be used in the datasheet function
# Install helloworldSpatial package
addPackage("helloworldSpatial")
#> Package <helloworldSpatial> is already installed

# Set the file path and name of the new SsimLibrary
myLibraryName <- file.path(tempdir(),"testlib_sqlStatement")

# Set the SyncroSim Session, SsimLibrary, Project, and Scenario
mySession <- session()
myLibrary <- ssimLibrary(name = myLibraryName,
                         session = mySession, 
                         package = "helloworldSpatial",
                         template = "example-library",
                         forceUpdate = TRUE)
#> Error in tt[[1]]: subscript out of bounds
myProject <- project(myLibrary, project = "Definitions")
#> Error in eval(expr, envir, enclos): object 'myLibrary' not found
myScenario <- scenario(myProject, scenario = "My Scenario")
#> Error in eval(expr, envir, enclos): object 'myProject' not found

# Run Scenario to generate results
resultScenario <- run(myScenario)
#> Error in h(simpleError(msg, call)): error in evaluating the argument 'ssimObject' in selecting a method for function 'run': object 'myScenario' not found

# Use the SQL statement when loading the Datasheet
myAggregatedDataFrame <- datasheet(resultScenario, name = "OutputDatasheet",
                                   sqlStatement = mySQL)
#> Error in h(simpleError(msg, call)): error in evaluating the argument 'ssimObject' in selecting a method for function 'datasheet': object 'resultScenario' not found
                                   
# View aggregated DataFrame
myAggregatedDataFrame
#> Error in eval(expr, envir, enclos): object 'myAggregatedDataFrame' not found
# }