computeBarchart: Compute one or more aggregates across single class.

In toaster: Big Data in-Database Analytics that Scales with Teradata Aster Distributed Platform

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

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.

Value

Data frame to use for bar chart plots with createHistogram.

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

toaster documentation built on May 30, 2017, 3:51 a.m.