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 )
groupBy | character string or vector of these. Vector of variables
(column names) to |
---|---|
aggregate | character string of vector of these. Vector of variables
(column names) to aggregate using |
aggregateFunction | character string. An SQL aggregate function
(e.g. |
where | named list. A list of subset variables. Names are column names, and elements are the values to be selected from each column (optional) |
Returns a list of SELECT
, GROUP BY
and WHERE
SQL statements used by the
datasheet
function to construct an SQLite database query.
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.
# \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")#># 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")#>myProject <- project(myLibrary, project = "Definitions") myScenario <- scenario(myProject, scenario = "My Scenario") # Run Scenario to generate results resultScenario <- run(myScenario)#> [1] "Running scenario [1] My Scenario"# Use the SQL statement when loading the Datasheet myAggregatedDataFrame <- datasheet(resultScenario, name = "OutputDatasheet", sqlStatement = mySQL) # View aggregated DataFrame myAggregatedDataFrame#> Iteration Timestep yCum #> 1 3 1 -204.3572 #> 2 3 2 -608.7377 #> 3 4 1 202.7891 #> 4 4 2 612.7014# }