toaster (version 0.2.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, stringsAsFactors = 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"

by

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

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.

withMelt

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

stringsAsFactors

logical: should columns returned as character and not excluded by as.is and not converted to anything else be converted to factors?

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.

See Also

computeHistogram, createHistogram

Examples

# 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)