toaster (version 0.5.5)

computeBarchart: Compute one or more aggregates across single class.

Description

Compute aggregates across category class represented by the table column. Values are one or more SQL aggregates that are valid expressions with GROUP BY . Class column usually is of character or other discrete type. Typical example is computing a bar chart for the column using SQL COUNT(*) ... GROUP BY - hence the name of the function. Result is a data frame to visualize as bar charts or heatmaps (see creating visualizations with createHistogram and createHeatmap).

Usage

computeBarchart(channel, tableName, category, aggregates = "COUNT(*) cnt", where = NULL, orderBy = NULL, top = NULL, by = NULL, withMelt = FALSE, ..., test = FALSE)

Arguments

channel

connection object as returned by odbcConnect

tableName

table name

category

column name or expression associated with categories. Name may be valid SQL expression and can contain otional alias (e.g. "UPPER(car_make) make")

aggregates

SQL aggregates to compute. Each aggregate corresponds to category value. Aggregates may have optional aliases like in "AVG(era) era"

where

specifies criteria to satisfy by the table rows before applying computation. The creteria are expressed in the form of SQL predicates (inside WHERE clause).

orderBy

list of column names, aliases, references or their combinations to use in SQL ORDER BY clause. Use in combination with top below to compute only limited number of results in certain order.

top

if specified indicates number of bars to include in bar plot. In combination with orderBy it works as computing first top results in certain order.

by

for optional grouping by one or more columns for faceting or alike (effectively these elements will be part of GROUP BY ...)

withMelt

logical if TRUE then uses reshape2 melt to transform result data frame aggregate values into a molten data frame

...

additional arguments to be passed to sqlQuery for more control over performance and data type conversion. By default, stringsAsFactors is set to FALSE.

test

logical: if TRUE show what would be done, only (similar to parameter test in RODBC functions like sqlQuery and sqlSave).

Value

Data frame to use for bar chart plots with createHistogram.

Details

No columns returned as character and not excluded by as.is are converted to factors by default, i.e. stringsAsFactors = FALSE when calling sqlQuery if not specified when calling this function.

See Also

computeHistogram, createHistogram

Examples

if(interactive()){
# initialize connection to Lahman baseball database in Aster 
conn = odbcDriverConnect(connection="driver={Aster ODBC Driver};
                         server=<dbhost>;port=2406;database=<dbname>;uid=<user>;pwd=<pw>")

# Compute average team season era, walks, and hits for each decade starting with 1980
computeBarchart(channel=conn, "teams_enh", "teamid team", 
                aggregates=c("avg(era) era", "avg(bb) bb", "avg(h) h"), 
                where="yearid >=1980", by=c("decadeid"))
                
# multipe aggregates in the same bar chart (with melt)
bc = computeBarchart(channel=conn, tableName="pitching_enh", category="teamid",
                    aggregates=c("AVG(era) era", "AVG(whip) whip"), withMelt=TRUE,
                    where="yearid >= 2000 and lgid='AL'")
        
# adding facets by decadeid          
bc = computeBarchart(channel=conn, tablelName="pitching_enh", category="teamid", 
                     aggregates=c("AVG(era) era", "AVG(whip) whip", "AVG(ktobb) ktobb"),
                     where="yearid >= 1990 and lgid='AL'", by="decadeid", withMelt=TRUE)
}