cancel
Showing results for 
Search instead for 
Did you mean: 

Join the community at Nodes 2022, our free virtual event on November 16 - 17.

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

redha_benhisse1
Node Clone

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

1 ACCEPTED SOLUTION

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

View solution in original post

4 REPLIES 4

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

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
Node Clone

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

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