1\name{groupingsets}
2\alias{rollup}
3\alias{cube}
4\alias{groupingsets}
5\alias{rollup.data.table}
6\alias{cube.data.table}
7\alias{groupingsets.data.table}
8\title{ Grouping Set aggregation for data tables }
9\description{
10  Calculate aggregates at various levels of groupings producing multiple (sub-)totals. Reflects SQLs \emph{GROUPING SETS} operations.
11}
12\usage{
13rollup(x, \dots)
14\method{rollup}{data.table}(x, j, by, .SDcols, id = FALSE, \dots)
15cube(x, \dots)
16\method{cube}{data.table}(x, j, by, .SDcols, id = FALSE, \dots)
17groupingsets(x, \dots)
18\method{groupingsets}{data.table}(x, j, by, sets, .SDcols, id = FALSE, jj, \dots)
19}
20\arguments{
21	\item{x}{\code{data.table}.}
22	\item{\dots}{argument passed to custom user methods. Ignored for \code{data.table} methods.}
23	\item{j}{expression passed to data.table \code{j}.}
24	\item{by}{character column names by which we are grouping.}
25	\item{sets}{list of character vector reflecting grouping sets, used in \code{groupingsets} for flexibility.}
26	\item{.SDcols}{columns to be used in \code{j} expression in \code{.SD} object.}
27	\item{id}{logical default \code{FALSE}. If \code{TRUE} it will add leading column with bit mask of grouping sets.}
28	\item{jj}{quoted version of \code{j} argument, for convenience. When provided function will ignore \code{j} argument.}
29}
30\details{
31    All three functions \code{rollup, cube, groupingsets} are generic methods, \code{data.table} methods are provided.
32}
33\value{
34    A data.table with various aggregates.
35}
36\seealso{ \code{\link{data.table}}, \code{\link{rbindlist}}
37}
38\references{
39\url{https://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#QUERIES-GROUPING-SETS}
40\url{https://www.postgresql.org/docs/9.5/static/functions-aggregate.html#FUNCTIONS-GROUPING-TABLE}
41}
42\examples{
43n = 24L
44set.seed(25)
45DT <- data.table(
46    color = sample(c("green","yellow","red"), n, TRUE),
47    year = as.Date(sample(paste0(2011:2015,"-01-01"), n, TRUE)),
48    status = as.factor(sample(c("removed","active","inactive","archived"), n, TRUE)),
49    amount = sample(1:5, n, TRUE),
50    value = sample(c(3, 3.5, 2.5, 2), n, TRUE)
51)
52
53# rollup
54rollup(DT, j = sum(value), by = c("color","year","status")) # default id=FALSE
55rollup(DT, j = sum(value), by = c("color","year","status"), id=TRUE)
56rollup(DT, j = lapply(.SD, sum), by = c("color","year","status"), id=TRUE, .SDcols="value")
57rollup(DT, j = c(list(count=.N), lapply(.SD, sum)), by = c("color","year","status"), id=TRUE)
58
59# cube
60cube(DT, j = sum(value), by = c("color","year","status"), id=TRUE)
61cube(DT, j = lapply(.SD, sum), by = c("color","year","status"), id=TRUE, .SDcols="value")
62cube(DT, j = c(list(count=.N), lapply(.SD, sum)), by = c("color","year","status"), id=TRUE)
63
64# groupingsets
65groupingsets(DT, j = c(list(count=.N), lapply(.SD, sum)), by = c("color","year","status"),
66             sets = list("color", c("year","status"), character()), id=TRUE)
67}
68\keyword{ data }
69