# Combine two Queries

@markmegerian

Below Query I used to calculate the spend per visit (average) of all profiles

`````` profiles_spend = select s from Profile:s-(profile_purchase:e)-Orders:t-(order_items:ee)-Products:tt ACCUM
s.@total_spended +=ee.item_qty*tt.item_price, s.@no_of_order+=1
post-ACCUM s.@spend_per_visit = s.@total_spended/s.@no_of_order;
PRINT profiles_spend;
``````

Groups are: Red, Black, Green

and I want to fetch out How much (Spend per visit) Average on the particular group
by using this formula: Red Group: {p1.@spend_per_visit = 22, p2.@spend_per_visit = 30}

then Red group is 22+30 = 52/2 = 26 So the average is 26 dollar because total count is 2

`group_spend_per_visit = select grp from Group:grp-(profile_group:pg)-Profile:p`

and I want this needs to be attached with First query with the require logic to get this below result

``````  @@group_level_score: {
"Red": "35 \$"
"Green": "22\$"
}``````

You can loop (i.e FOREACH) on an accumulator and do the math on it

You didnt specify the attribute name for the group, so this may not be perfect, but should get you close. Hopefully a profile can only be in one group.

MaxAccum @groupName;
MapAccum<STRING, AvgAccum> @@groupAverage;

group_spend = select g
from Group:grp - (profile_group:pg) - Profile:s-(profile_purchase:e)-Orders:t-(order_items:ee)-Products:tt
ACCUM
s.@total_spended +=ee.item_qty*tt.item_price, s.@no_of_order+=1, s.@groupName += grp.groupName
post-ACCUM s.@spend_per_visit = s.@total_spended/s.@no_of_order, @@groupAverage += (s.@groupName â†’ s.@spend_per_visit);

PRINT @@groupAverage;

@markmegerian
here I calculated all the profile average individually and the result of individual average is coming perfect, but i am stucked it to achieve a group by according to the group average

profiles_spend = select s from Profile:s-(profile_purchase:e)-Orders:t-(order_items:ee)-Products:tt ACCUM
s.@total_spended +=ee.item_qty*tt.item_price, s.@no_of_order+=1
post-ACCUM s.@spend_per_visit = s.@total_spended/s.@no_of_order;
PRINT profiles_spend;

[{â€śnameâ€ť: â€śBrunoâ€ť: â€ś@spend_per_vistâ€ť: â€ś32â€ť}, { â€śnameâ€ť: â€śPrinceâ€ť, â€ś@spend_per_vistâ€ť:â€ś12â€ť}, {â€śnameâ€ť: â€śsofekâ€ť, â€ś@spend_per_vistâ€ť:â€ś52â€ť}, {â€śnameâ€ť: â€śjackâ€ť, â€ś@spend_per_vistâ€ť: â€ś100â€ť}, {â€śnameâ€ť: â€śromanâ€ť, â€ś@spend_per_vistâ€ť: â€ś22â€ť}

and now I want Group BY according to their specific Group

If Suppose Bruno and Prince, Sofek are from Red Group then,
Bruno: 32 + 12 + 52 = 96 => 96/3 = 32 So the overall Average of Red group is 32

Same If Jack and roman are from Yellow Group
100 + 22 = 122 => 122/2 =62 So the overall Average of Yellow Group is 62

I think first we need to count the Group level Count that how many peoples are from Red Group then accordingly we can divide

@@group_average: {
â€średâ€ť: â€ś32â€ť,
â€śyellowâ€ť: â€ś62â€ť, }

Group is connected by an edge with profile_group , Group vertex having an attribute of id with as attribute true

This is easy. After your first query, just run this, keeping in mind that there is an AvgAccum so you dont need to count the number of people in each group. Also keep in mind that values for accumulators are preserved across multiple lines in an installed query

first do query 1 to calculate @spend_per_visit for every person

then do this

MapAccum<STRING, AvgAccum> @@group_average;

G1 = SELECT g FROM profiles_spend:s -(profile_group) - Group:g
ACCUM @@group_average += (g.id â†’ s.@spend_per_visit);

@markmegerian Thanks for your hep this one is working perfect.
Please suggest some videos to become master in GSQL

Now in the Front end Client requirements got changed

`````` I have a Group vertex which is directly connect to Profile with a profile_group edge
and there is a Calender vertex which is directly connect to Profile with profile_calender edge
``````

and Calender vertex having id as attr, and date as a attr,
Calender is directly connect to visit_group with an edge calender_visitgroup
Calender is directly connect to spend_group with an edge calender_spend_group

calender_visitgroup edge contains weekly visit as a attribute which I requires to store as a accumulator in a Profile List response (res) below query which are
same as calender_spend_group edge contains weekly spend this one is also requires in Profile List response (res)

Sname passed â†’ Red Group

`res = select pr from group:g-(profile_group:pg)-Profile:pr where g.id==sname and pr.customer_name!="" ;`
Now further I am stucked I want further journey where Calendar DESC by DATE LIMIT 1 and both edges need to be traversed calender_visitgroup and calender_spend_group to hold the data in every profile response

where Calendar DESC by DATE LIMIT 1 gives the latest info about their average weekly and visit spend

Here are some ideas- this approach will just store the max date of the calendar entry

``````TYPEDEF Tuple <DATETIME date, INT visitcount>  calendarVisit;
MaxAccum<calendarVisit> @latest;

GR = SELECT g FROM Group:g where g.id == sname;
PR = SELECT p FROM GR  - (profile_group:pg) - Profile:pr where pr.customer_name != "";
RES = SELECT p
FROM PR:p -(profile_calendar:pc) - Calendar:c  - (calendar_visitgroup:cv) - visit_group:v
ACCUM p.@latest += calendarVisit(c.date, cv.weeklyvisit);
``````

Products vertex is directly connected to Popularity_Index vertex with the edge of popularity_index. (High, Low values are in popularity_indexvertex)
Products vertex is directly connected to Margin_Index vertex with the edge of margin_index (High, Low values are in margin_index vertex )
Products vertex is directly connected to Segment Vertex with the edge of product_segment

products_purchased = select s from products_purchased:s -( (popularity_index|margin_index):e)- (Popularity_Index|Margin_Index):t
ACCUM CASE WHEN t.vertex is Popularity Index and t.id == â€śHighâ€ť
and t.vertex is Margin Index and t.id == â€śLowâ€ť
THEN
INSERT INTO Segment(PRIMARY_ID) VALUES (â€śLow Margin Popular Productsâ€ť);
INSERT INTO product_segment(FROM, TO)
VALUES (s.id, â€śLow Margin Popular Productsâ€ť)
END;

and so on â€¦

I am stucked in this after ACCUM how to match this condition (particular product having one edge connect with Margin Index and Popularity Index)
Note I am deleting Previously Tagged Segment with the Product vertex before running this Query, So very time fresh segment would be tagged with the particular product
z = select t from products:s-(product_segment:ps)-Segment:t post-ACCUM delete(t);

While there are many ways to solve this type of problem, I personally would not try to constantly keep updating these segment relationships. After all, one of the benefits of using a graph database is the speed and efficiency of traversing the graph and finding the vertex set that matches your criteria.

So my first suggestion would be to return a vertex set on the query call, rather than deleting and creating edges each time

For example (note that we are only using two criteria, but this idea can be adapted to any number of criteria and using the INTERSECT to ensure the set has all Index values)

``````
CREATE DISTRIBUTED QUERY  findProducts(STRING popularity = "High", STRING margin = "Low") FOR GRAPH graphname  {

products_pop = select s from products_purchased:s -( popularity_index:e)-
Popularity_Index:t
WHERE t.id == popularity;

products_margin = select s from products_purchased:s -( margin_index:e)-
Margin_Index:t
WHERE t.id ==  margin;

products_purchased  = products_pop INTERSECT products_margin;

PRINT products_purchased;
}
``````

@markmegerian please let me know how to call this distributed query in my running query

products_purchased = select s from products_purchased:s -( (popularity_index|margin_index):e)- (Popularity_Index|Margin_Index):t
ACCUM CASE WHEN t.vertex is Popularity Index and t.id == â€śHighâ€ť
and t.vertex is Margin Index and t.id == â€śLowâ€ť
THEN

I am suggesting that you use my approach in place of the query that you have. In other words, use the INTERSECT as the way to determine the products with both a margin and popularity.

Sorry to distract from the great query discussion going on. I wanted to jump in with the GSQL resources that @Mingxi_Wu sent me and the rest of the Support Engineering team. This was his recommendation for becoming a GSQL master.

• Starting with GSQL 101 (which will be deprecate in 3.3), but itâ€™s our basic syntax.
• Then, understand GSQL 102 (this will be our default syntax starting from 3.3)
• Our secret sauce , accumulator (runtime attribute),

Then, the practice guide on GSQL trick

I f you finish all above, then you can look at the LDBC benchmark queries.

Focus on BI suite

2 Likes