apoc.agg.rollup
Function Apoc Extended
apoc.agg.rollup(<ANY>, [groupKeys], [aggKeys])
Emulate an Oracle/Mysql ROLLUP command:
ROLLUP groupKeys, SUM(aggKey1), AVG(aggKey1), COUNT(aggKey1), SUM(aggKey2), AVG(aggKey2), ….
Note that the [NULL] values (see the Interpreting "[NULL]" Values in Results section here)
are returned by the procedure as [NULL].
Signature
apoc.agg.rollup(value :: ANY | RELATIONSHIP, groupKeys :: LIST OF STRING, aggKeys :: LIST OF STRING) :: (MAP?)Usage Examples
Given this dataset:
CREATE (:Product {SupplierID: 1, CategoryID: 1, anotherID: 1, Price: 18, otherNum: 0.3}),
(:Product {SupplierID: 11, CategoryID: 3, anotherID: 1, Price: 14.0, otherNum: 0.1}),
(:Product {SupplierID: 11, CategoryID: 3, anotherID: 0, Price: 31.0, otherNum: 2}),
(:Product {SupplierID: 11, CategoryID: 4, anotherID: 0, Price: 44, otherNum: 0.7}),
(:Product {SupplierID: 1, CategoryID: null, anotherID: 1, Price: 18, otherNum: 0.7}),
(:Product {SupplierID: null, CategoryID: null, anotherID: 0, Price: 18, otherNum: 0.6}),
(:Product {SupplierID: null, CategoryID: 2, anotherID: 0, Price: 199, otherNum: 0.8});We can emulate a ROLLUP clause like this:
SELECT SupplierID, CategoryID, anotherID,
SUM(Price), AVG(Price), COUNT(Price), SUM(otherNum), AVG(otherNum), COUNT(otherNum)
GROUP BY ROLLUP(SupplierID, CategoryID, anotherID)by executing:
MATCH (p:Product)
RETURN apoc.agg.rollup(p,
["SupplierID", "CategoryID", "anotherID"],
["Price", "otherNum"]
) as data| data |
|---|
|
Note that the [NULL] values (see the Interpreting "[NULL]" Values in Results section here)
are returned by the procedure as [NULL].
or a ROLLUP clause like:
SELECT CategoryID, SupplierID, anotherID,
SUM(Price), AVG(Price), COUNT(Price), SUM(otherNum), AVG(otherNum), COUNT(otherNum)
GROUP BY ROLLUP(CategoryID, SupplierID, anotherID)with this query:
MATCH (p:Product)
RETURN apoc.agg.rollup(p,
["CategoryID", "SupplierID", "anotherID"],
["Price", "otherNum"]
) as data| data |
|---|
|
We can also emulate a CUBE clause like this:
SELECT SupplierID, CategoryID, anotherID,
SUM(Price), AVG(Price), COUNT(Price), SUM(otherNum), AVG(otherNum), COUNT(otherNum)
GROUP BY CUBE(SupplierID, CategoryID, anotherID)executing:
MATCH (p:Product)
RETURN apoc.agg.rollup(p,
["SupplierID", "CategoryID", "anotherID"],
["Price", "otherNum"],
{cube: true}
) as data| data |
|---|
|
or a CUBE clause like:
SELECT CategoryID, SupplierID, anotherID,
SUM(Price), AVG(Price), COUNT(Price), SUM(otherNum), AVG(otherNum), COUNT(otherNum)
GROUP BY CUBE(CategoryID, SupplierID, anotherID)with this query:
MATCH (p:Product)
RETURN apoc.agg.rollup(p,
["CategoryID", "SupplierID", "anotherID"],
["Price", "otherNum"],
{cube: true}
) as data| data |
|---|
|