Count over group by

GSQL
1

Hi

I have a vertex with event as an attribute. having values (“call”,“sms”).

I want to track total calls and total SMS between two parties. below is the query.

start_phones = SELECT tgt FROM start:s-((phone_made_call|phone_received_call):e)->phone_call:tgt

WHERE (e.call_date BETWEEN begin_time AND end_time)      ############AND tgt.event=="cdr.cs.call"

    ACCUM @@edge_set += e;

phone_call has the attribute “event”.

I would like to add accumulator to store total calls and sms counts.

    start_phones = SELECT tgt FROM start:s-((phone_made_call|phone_received_call):e)->phone_call:tgt

    WHERE (e.call_date BETWEEN begin_time AND end_time)      ############AND tgt.event=="cdr.cs.call"

        ACCUM @@edge_set += e

                           ,@tgt.total_calls ? 

                           ,@tgt.total_sms ?;

appreciate your help.

Thanks

2

Hi K,

I’m assuming your schema is like this :

person - (made_or_received) - phone_call - (made_or_received) - person

Is that correct?

If so, by using v1 syntax, you can create a local GroupByAcc (string ID, string phone_type, SumAccum numberOfEvents) and a local SumAccum

When traversing from person to phone call, pass the phone vertex ID to phone_call’s SumAccum.

When traversing from phone_call to person, use the SumAccum value from phone_call as the first key in the targer vertex GroupByAccum, phone_call’s “call”/“SMS” value as the second key, and increment the SumAccum.

If you’re using v2 syntax, you can create a multi-hop patten match query :

Start = SELECT t2 FROM Start:s - (phone_event:e1) - event:t1 - (phone_event:e2) - phone:t2

Since you’ll have access to all possible vertices within one single phone call instance, you can create a GroupByAccum<vertex phone1, vertex phone2, string eventType, SumAccum eventCount> @@list

update the values in the ACCUM clause all at once.

Something like this:

CREATE QUERY testing() FOR GRAPH phoneGraph syntax v2 { 

OrAccum @visited;

GroupByAccum<vertex phone1, vertex phone2, string eventType, SumAccum<int> eventCount> @@list;

Start (ANY) = {phone.*};

Start = SELECT t2

        FROM Start:s - (phone_event:e1) - event:t1 - (phone_event:e2) - phone:t2

        where s != t2

        ACCUM @@list += (s,t2,t1.event_type ->1);

print @@list;

}