@markmegerian
Hi there I am stuck in one problem, please help me out
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
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.
@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;
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);
hi @markmegerian can you please help me out in multiple edges
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)