How to merge aggregated results


#1

How can I merge two aggregated results together since there are not any links between aggregated results?


#2

Hi @Tracy

What do you mean exactly? Can you post a simple SQL example?

Thanks

Luigi


#3

A: calculating “cnt1” for every “homos”:

select homos,count() as cnt1 from(

MATCH  {class: Con, where: (CONS_NO = '3620067544')}.out("hasTag"){ as:homo_tag },

  { as:homo_tag }.in("hasTag"){ as: homos, where: (CONS_NO <> '3620067544')} RETURN homo_tag,homos)

group by homos order by cnt1 desc;

B: calculating “cnt2” for every “homos”:

select homos,count() as cnt2 from(

MATCH  {class: Con, where: (CONS_NO <> '3620067544'),as:homos}.out("hasTag"){ as:homo_all_tag } RETURN homo_all_tag,homos)

group by homos order by cnt2 desc;

C: calculating “cnt3”:

select count(TAG_ID) as cnt3 from (select expand(out("hasTag")) from Con where CONS_NO = '3620067544');

Now I want to calculate “cnt1/(cnt2+cnt3-cnt1)”, with traditional SQL syntax, the query could be:

select cnt1/(cnt2+(select cnt3 from C )-cnt1) from A,B where A.homos=B.homos;

Howerver, with orient SQL_MATCH syntax, I can’t find a way to achieve this.


#4

Hi @Tracy

You can try to use LET clauses and pre-aggregate information, eg.

SELECT $a[0].cnt1/($b[0].cnt2+$c[0].cnt3-$a[0].cnt1)
LET 
$a = ( select homos,count() as cnt1 from ... ),
$b = ( select homos,count() as cnt2 from ... ),
$c = ( select count(TAG_ID) as cnt3 ... ),

Thanks

Luigi


#5

But how can I control the merge condition for A and B? I only want to merge the records having the same value of “homos”…


#6

So there is no solution?


#7

I’d say the easiest way is to define a server function to do it. Probably you can also find a way to do it with a batch script, using some manual iteration (see https://orientdb.com/docs/3.0.x/sql/SQL-batch.html#loops) but probably it will be trickier

Thanks

Luigi