apoc.agg.rollup
函数 Apoc 扩展
apoc.agg.rollup(<ANY>, [groupKeys], [aggKeys])
模拟 Oracle/Mysql 的 ROLLUP 命令：ROLLUP groupKeys, SUM(aggKey1), AVG(aggKey1), COUNT(aggKey1), SUM(aggKey2), AVG(aggKey2), …。
请注意，[NULL] 值（请参阅此处关于“在结果中解释 '[NULL]' 值”的章节）将由该过程作为 [NULL] 返回。
签名
apoc.agg.rollup(value :: ANY | RELATIONSHIP, groupKeys :: LIST OF STRING, aggKeys :: LIST OF STRING) :: (MAP?)用法示例
给定此数据集
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});我们可以通过如下方式模拟 ROLLUP 子句
SELECT SupplierID, CategoryID, anotherID,
SUM(Price), AVG(Price), COUNT(Price), SUM(otherNum), AVG(otherNum), COUNT(otherNum)
GROUP BY ROLLUP(SupplierID, CategoryID, anotherID)通过执行
MATCH (p:Product)
RETURN apoc.agg.rollup(p,
["SupplierID", "CategoryID", "anotherID"],
["Price", "otherNum"]
) as data| data |
|---|
|
请注意，[NULL] 值（请参阅此处关于“在结果中解释 '[NULL]' 值”的章节）将由该过程作为 [NULL] 返回。
或者像这样的 ROLLUP 子句
SELECT CategoryID, SupplierID, anotherID,
SUM(Price), AVG(Price), COUNT(Price), SUM(otherNum), AVG(otherNum), COUNT(otherNum)
GROUP BY ROLLUP(CategoryID, SupplierID, anotherID)使用此查询
MATCH (p:Product)
RETURN apoc.agg.rollup(p,
["CategoryID", "SupplierID", "anotherID"],
["Price", "otherNum"]
) as data| data |
|---|
|
我们也可以通过如下方式模拟 CUBE 子句
SELECT SupplierID, CategoryID, anotherID,
SUM(Price), AVG(Price), COUNT(Price), SUM(otherNum), AVG(otherNum), COUNT(otherNum)
GROUP BY CUBE(SupplierID, CategoryID, anotherID)执行
MATCH (p:Product)
RETURN apoc.agg.rollup(p,
["SupplierID", "CategoryID", "anotherID"],
["Price", "otherNum"],
{cube: true}
) as data| data |
|---|
|
或者像这样的 CUBE 子句
SELECT CategoryID, SupplierID, anotherID,
SUM(Price), AVG(Price), COUNT(Price), SUM(otherNum), AVG(otherNum), COUNT(otherNum)
GROUP BY CUBE(CategoryID, SupplierID, anotherID)使用此查询
MATCH (p:Product)
RETURN apoc.agg.rollup(p,
["CategoryID", "SupplierID", "anotherID"],
["Price", "otherNum"],
{cube: true}
) as data| data |
|---|
|