How could to replace groupe by roll up and group by cube in cypher (aggregation)

[redha_benhisse1]

Hi evrybody

I built a datawarehouse in neo4j, but I have a probleme to remplace group by roll up or group by cube in cypher?

for exmple :

Q1 : SELECT ProductID, CustomerID, SUM(SalesAmount) FROM Sales GROUP
BY ROLLUP(ProductID, CustomerID)

OR : SELECT ProductID, CustomerID, SUM(SalesAmount) FROM Sales GROUP
BY CUBE(ProductID, CustomerID)

I m using northwind database from microsoft

anybody can help me about this? thanks by advance

[michael_hunger]

You should be able to use

MATCH (s:Sales)
WITH s.ProductID as pid, s.CustomerID as cid, sum(s.salesAmount) as sa
CALL {
   WITH pid, sum(sa) as saProduct RETURN saProduct 
} 
CALL {
   WITH cid, sum(sa) as saProduct RETURN saCustomer 
} 
RETURN pid, cid, saProduct, saCustomer, sa

You can also check out apoc.agg.stats
and comment on this issue

github.com/neo4j-contrib/neo4j-apoc-procedures

multi-dimensional aggregation + aggregation functions for community sizes etc.

opened 09:34AM - 20 Aug 21 UTC

jexp

For a property you want to have more than one statistic: size, count, avg, me…dian, ... which is probably already covered by `apoc.agg.stats()` but then, how about multi-dimensional aggregation. e.g. `apoc.agg.multiStats([key1,key2,key3]) -> Map<Key,Map<agg="sum,count,avg", number>>` e.g. ``` match (p:Person) with apoc.agg.multiStats(p, ["wcc","lpa","louvain"]) as data match (p:Person) return p.name, data[toString(p.wcc)].count as size ``` see https://community.neo4j.com/t/listing-the-community-size-of-different-community-detection-algorithms-already-calculated/42895/2?u=michael.hunger

[redha_benhisse1]

thanks for your answer

but the problem persist again for me

by the way , my graph scema is like that

and when i use call command in my request, it does'nt get the same result.

but when i use union command, thats work

MATCH (c:customer)-[rel:buy]->(s:sales)<-[rel2:sold]-(p:product) 
MATCH (sm:sales_amount {sales_amount_id: s.sales_id})
WITH c.customer_id as cid,p.product_id as pid,sum(sm.sales_amount) as sum
RETURN cid, pid, sum
UNION
MATCH (c:customer)-[rel:buy]->(s:sales)<-[rel2:sold]-(p:product) 
MATCH (sm:sales_amount {sales_amount_id: s.sales_id})
WITH  null as cid, null as pid, sum(sm.sales_amount) as sum
RETURN cid, pid, sum
UNION
MATCH (c:customer)-[rel:buy]->(s:sales)<-[rel2:sold]-(p:product) 
MATCH (sm:sales_amount {sales_amount_id: s.sales_id})
WITH  c.customer_id as cid, null as pid, sum(sm.sales_amount) as sum
RETURN cid, pid, sum;

do you have any idea how can i use call and with to replace union im my requeste to optimize it?

than you so by advance

[redha_benhisse1]

hi

of course, in my real data base implimented in neo4j, my nodes are labaled like that (comparativly with schema):

Acr_pruduct = product
Acr_customer = customer
Acr_Fact_Sales = sales
H_Att_SalesAmount = sales_amount

[michael_hunger · Accepted solution]

Subqueries with CALL are available from 4.1, so best would be to upgrade anyhow to a recent version like 4.3