Query is taking too much time, giving me timeout error or GPE down.
in gsql I applied a parametrization of target Node
Below is the snippet of code (removed most of line due to validation)
CREATE QUERY affinity_segmentation(String str) FOR GRAPH clientC_retail_analytics syntax v2{
/* Write query logic here */
JSONOBJECT jsonobj;
jsonobj = parse_json_object(str);
JSONARRAY arr;
arr = jsonobj.getJsonArray("conditions");
MapAccum<STRING, MapAccum<STRING, DOUBLE>> @customer_spend_level_kpis; # require for spend_category_customerindex (Previously known as customer_category_spend (Shoe200, Maternity100))
MapAccum<STRING, Double> @customer_total_spend_average_spend_level_kpis; # previously known as category_total_spend (total spend aata hai particular customer ke liye saari categories ka)
SumAccum<Double> @category_avg_spend;
MapAccum<STRING, DOUBLE> @spend_category_customer_index; # previously known as spend_category_customer_index
MapAccum<STRING, MapAccum<STRING, SetAccum<STRING>>> @customer_visit_level_kpis; # previously known as total_category_visit_accum
MapAccum<STRING, MapAccum<STRING, DOUBLE>> @@global_kpis_across_all_customers;
MapAccum<STRING, MapAccum<STRING, SetAccum<STRING>>> @@global_kpis_across_all_customers_count;
start = {customer.*};
#### Get Max Order Date in System
MaxAccum<DATETIME>@@max_order_date;
if not jsonobj.containsKey("___max_order_date") THEN
# if max order is not passed by short term loyalty (First priority to check hidden field)
max_order_date = select o from orders:o POST-ACCUM @@max_order_date += o.transaction_datetime ORDER BY o.transaction_datetime DESC LIMIT 1;
ELSE
# if max order is passed by short term loyalty
@@max_order_date += to_datetime(jsonobj.getString("___max_order_date"));
end;
#####
########
MaxAccum<DATETIME>@@from_date;
if not jsonobj.containsKey("___look_back_days") THEN
# If look back days is not passed by short term loyalty (First priority to check hidden field)
@@from_date = datetime_sub(@@max_order_date,
INTERVAL jsonobj.getInt("look_back_days") DAY);
ELSE
@@from_date = datetime_sub(@@max_order_date,
INTERVAL jsonobj.getInt("___look_back_days") DAY);
end;
######
foreach cnt in range[0, arr.size()-1] do
BOOL is_spend_found = FALSE;
BOOL is_visit_found = FALSE;
#BOOL is_quantity_found = FALSE;
# Calculate Bare Minimum API (Need to store data on vertex cat, sub, cat (becuase if vertex is created then and only edge would be connected))
active_customers_in_lookback = select s from start:s-(customer_orders:custord)-orders:ord-(orders_product:op)-product:p-(:pc)-:tt
where ord.transaction_datetime >= @@from_date and ord.transaction_datetime <= @@max_order_date accum
IF tt.type == arr.getJsonObject(cnt).getString("Operation") THEN
s.@customer_spend_level_kpis += ("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_spend" -> (tt.name -> p.unit_price)),
@@global_kpis_across_all_customers += (arr.getJsonObject(cnt).getString("Operation") + "_spend_across_all_customers" -> (tt.name -> p.unit_price)),
@@global_kpis_across_all_customers_count += (arr.getJsonObject(cnt).getString("Operation") + "_across_all_customers_count" ->(tt.name -> s.customer_id)),
@@global_kpis_across_all_customers_count += (arr.getJsonObject(cnt).getString("Operation") + "_visit_across_all_customers" ->
(tt.name -> (ord.order_id))),
@@global_kpis_across_all_customers += (arr.getJsonObject(cnt).getString("Operation") + "_total_quantity" -> (tt.name -> op.item_qty))
END;
active_customers_index = select s from active_customers_in_lookback:s POST-ACCUM
FOREACH (mykey, myvalue) IN s.@customer_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_spend") DO
IF arr.getJsonObject(cnt).getString("Operation") == "category" THEN
INSERT INTO dynamic_segment_category (PRIMARY_ID, spend_per_profile, spend_per_visit, total_spend, total_visit, total_profiles, visit_per_profile, total_quantity) VALUES (mykey,
@@global_kpis_across_all_customers.get(arr.getJsonObject(cnt).getString("Operation") + "_spend_across_all_customers").get(mykey)/@@global_kpis_across_all_customers_count.get(arr.getJsonObject(cnt).getString("Operation") + "_across_all_customers_count").get(mykey).size(),
@@global_kpis_across_all_customers.get(arr.getJsonObject(cnt).getString("Operation") + "_spend_across_all_customers").get(mykey)/@@global_kpis_across_all_customers_count.get(arr.getJsonObject(cnt).getString("Operation") + "_visit_across_all_customers").get(mykey).size(),
@@global_kpis_across_all_customers.get(arr.getJsonObject(cnt).getString("Operation") + "_spend_across_all_customers").get(mykey),
@@global_kpis_across_all_customers_count.get(arr.getJsonObject(cnt).getString("Operation") + "_visit_across_all_customers").get(mykey).size(),
@@global_kpis_across_all_customers_count.get(arr.getJsonObject(cnt).getString("Operation") + "_across_all_customers_count").get(mykey).size(),
# Visit per profile
#@@total_category_visit_across_all_customers.get(mykey).size()/@@category_across_all_customers_count.get(mykey).size(),
@@global_kpis_across_all_customers_count.get(arr.getJsonObject(cnt).getString("Operation") + "_visit_across_all_customers").get(mykey).size()/@@global_kpis_across_all_customers_count.get(arr.getJsonObject(cnt).getString("Operation") + "_across_all_customers_count").get(mykey).size(),
# Total Quantity
#@@total_category_quantity.get(mykey)),
@@global_kpis_across_all_customers.get(arr.getJsonObject(cnt).getString("Operation") + "_total_quantity").get(mykey))
END
END;
foreach kpicnt in range[0, arr.getJsonObject(cnt).getJsonArray("kpi").size()-1] do
if arr.getJsonObject(cnt).getJsonArray("kpi").getString(kpicnt) == "spend" THEN
is_spend_found = TRUE;
active_customers_in_lookback = select s from start:s-(customer_orders:custord)-orders:ord-(orders_product:op)-product:p-(:pc)-:tt
where ord.transaction_datetime >= @@from_date and ord.transaction_datetime <= @@max_order_date accum
IF tt.type == arr.getJsonObject(cnt).getString("Operation") THEN
# Sample => customer_category_spend
#s.@customer_spend_level_kpis += ("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_spend" -> (tt.name -> p.unit_price)),
s.@customer_total_spend_average_spend_level_kpis += ("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_total_spend" -> p.unit_price)
#@@category_spend_across_all_customers += (cat.category_name -> p.unit_price)
#@@global_kpis_across_all_customers += (arr.getJsonObject(cnt).getString("Operation") + "_spend_across_all_customers" -> (tt.name -> p.unit_price)),
#s.@category_total_spend += p.unit_price
#@@category_across_all_customers_count += (cat.category_name -> s.customer_id)
#@@global_kpis_across_all_customers_count += (arr.getJsonObject(cnt).getString("Operation") + "_across_all_customers_count" ->(tt.name -> s.customer_id))
END
POST-ACCUM
s.@customer_total_spend_average_spend_level_kpis += ("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_average_spend" -> s.@customer_total_spend_average_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_total_spend")/s.@customer_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_spend").size());
active_customers_index = select s from active_customers_in_lookback:s POST-ACCUM
FOREACH (mykey, myvalue) IN s.@customer_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_spend") DO
s.@customer_spend_level_kpis += ("spend_"+ arr.getJsonObject(cnt).getString("Operation") + "_customer_index" -> (mykey -> (myvalue/s.@customer_total_spend_average_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_average_spend")) * 100)),
#s.@spend_category_customer_index += (),
# Save total spend (total spend on particular category)
#INSERT INTO dynamic_segment_category (PRIMARY_ID) VALUES (mykey),
#INSERT INTO profile_calendar_category_affinity (FROM, To,spend_category_customer_index) VALUES (mykey, s.customer_name + "_" + s.customer_id + "_" + datetime_format(max_order_date)
#, (myvalue/s.@category_avg_spend) * 100)
IF arr.getJsonObject(cnt).getString("Operation") == "category" THEN
#INSERT INTO dynamic_segment_category (PRIMARY_ID, spend_per_profile, spend_per_visit, total_spend, total_visit, total_profiles, visit_per_profile, total_quantity) VALUES (mykey,
# Spend per profile
#@@category_spend_across_all_customers.get(mykey)/@@category_across_all_customers_count.get(mykey).size(),
#@@global_kpis_across_all_customers.get(arr.getJsonObject(cnt).getString("Operation") + "_spend_across_all_customers").get(mykey)/@@global_kpis_across_all_customers_count.get(arr.getJsonObject(cnt).getString("Operation") + "_across_all_customers_count").get(mykey).size(),
# Spend per visit
#@@category_spend_across_all_customers.get(mykey)/@@total_category_visit_across_all_customers.get(mykey).size(),
#@@category_spend_across_all_customers.get(mykey),
#@@total_category_visit_across_all_customers.get(mykey).size(),
#@@category_across_all_customers_count.get(mykey).size(),
# Visit per profile
#@@total_category_visit_across_all_customers.get(mykey).size()/@@category_across_all_customers_count.get(mykey).size(),
# Total Quantity
#@@total_category_quantity.get(mykey)),
INSERT INTO profile_calendar_category_affinity (FROM, To, total_spend, spend_category_customer_index) VALUES (mykey, s.customer_name + "_" + s.customer_id + "_" + datetime_format(@@max_order_date),
myvalue,
s.@customer_spend_level_kpis.get("spend_"+ arr.getJsonObject(cnt).getString("Operation") + "_customer_index").get(mykey))
ELSE IF arr.getJsonObject(cnt).getString("Operation") == "subcategory" THEN
INSERT INTO profile_calendar_subcategory_affinity (FROM, To, total_spend, spend_subcategory_customer_index) VALUES (mykey, s.customer_name + "_" + s.customer_id + "_" + datetime_format(@@max_order_date),
myvalue,
s.@customer_spend_level_kpis.get("spend_"+ arr.getJsonObject(cnt).getString("Operation") + "_customer_index").get(mykey))
ELSE IF arr.getJsonObject(cnt).getString("Operation") == "department" THEN
INSERT INTO profile_calendar_department_affinity (FROM, To, total_spend, spend_department_customer_index) VALUES (mykey, s.customer_name + "_" + s.customer_id + "_" + datetime_format(@@max_order_date),
myvalue,
s.@customer_spend_level_kpis.get("spend_"+ arr.getJsonObject(cnt).getString("Operation") + "_customer_index").get(mykey))
END
END;
FOREACH (mykey, myvalue) IN @@global_kpis_across_all_customers.get(arr.getJsonObject(cnt).getString("Operation") + "_spend_across_all_customers") DO
#@@category_avg_spend_across_all_customers += (mykey -> (myvalue/@@category_across_all_customers_count.get(mykey).size()));
@@global_kpis_across_all_customers += (arr.getJsonObject(cnt).getString("Operation") + "_avg_spend_across_all_customers" ->
(mykey -> (myvalue/@@global_kpis_across_all_customers_count.get(arr.getJsonObject(cnt).getString("Operation") + "_across_all_customers_count").get(mykey).size())));
#PRINT "hello";
END;
category_based_index = select s from active_customers_index:s POST-ACCUM
FOREACH (mykey, myvalue) IN s.@customer_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_spend") DO
#s.@spend_category_category_index += (mykey -> (myvalue/@@category_avg_spend_across_all_customers.get(mykey)) * 100),
s.@customer_spend_level_kpis += ("spend_"+ arr.getJsonObject(cnt).getString("Operation") + "_category_index" -> (mykey -> (myvalue/@@global_kpis_across_all_customers.get(arr.getJsonObject(cnt).getString("Operation") + "_avg_spend_across_all_customers").get(mykey)) * 100)),
# Hybrid Index, level category, kpi spend
# s.@spend_category_hybrid_index += (mykey -> (s.@spend_category_customer_index.get(mykey)+s.@spend_category_category_index.get(mykey))/2),
s.@customer_spend_level_kpis += ("spend_"+ arr.getJsonObject(cnt).getString("Operation") + "_hybrid_index" ->
(mykey -> (s.@customer_spend_level_kpis.get("spend_"+ arr.getJsonObject(cnt).getString("Operation") + "_customer_index").get(mykey)+
s.@customer_spend_level_kpis.get("spend_"+ arr.getJsonObject(cnt).getString("Operation") + "_category_index").get(mykey))/2)),
IF arr.getJsonObject(cnt).getString("Operation") == "category" THEN
INSERT INTO profile_calendar_category_affinity (FROM, To, spend_category_category_index, spend_category_hybrid_index) VALUES (mykey, s.customer_name + "_" + s.customer_id + "_" + datetime_format(@@max_order_date)
, s.@customer_spend_level_kpis.get("spend_"+ arr.getJsonObject(cnt).getString("Operation") + "_category_index").get(mykey)
, s.@customer_spend_level_kpis.get("spend_"+ arr.getJsonObject(cnt).getString("Operation") + "_hybrid_index").get(mykey))
END
END;
else if arr.getJsonObject(cnt).getJsonArray("kpi").getString(kpicnt) == "visit" THEN
is_visit_found = True;
active_customer_category_visit = select s from start:s-(customer_orders:custord)-orders:ord-(orders_product:op)-product:p-(:pc)-:tt
where ord.transaction_datetime >= @@from_date and ord.transaction_datetime <= @@max_order_date accum
IF tt.type == arr.getJsonObject(cnt).getString("Operation") THEN
s.@customer_visit_level_kpis += ("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_visit" -> (tt.name -> ord.order_id)),
# Store (How many visit to all categories)
#@@total_category_visit_across_all_customers += (cat.category_name -> ord.order_id)
#@@global_kpis_across_all_customers_count += (arr.getJsonObject(cnt).getString("Operation") + "_visit_across_all_customers" ->
#(tt.name -> (ord.order_id))),
@@global_kpis_across_all_customers_count += (arr.getJsonObject(cnt).getString("Operation") + "_across_all_customers_count" ->(tt.name -> s.customer_id))
END
POST-ACCUM
FOREACH (mykey, myvalue) IN s.@customer_visit_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_visit") DO
#s.@category_total_visit += myvalue.size()
s.@customer_total_spend_average_spend_level_kpis += ("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_total_visit" -> myvalue.size())
END,
s.@customer_total_spend_average_spend_level_kpis += ("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_average_visit" -> s.@customer_total_spend_average_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_total_visit")/s.@customer_visit_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_visit").size());
FOREACH (mykey, myvalue) IN @@global_kpis_across_all_customers_count.get(arr.getJsonObject(cnt).getString("Operation") + "_visit_across_all_customers") DO
@@global_kpis_across_all_customers += (arr.getJsonObject(cnt).getString("Operation") + "_avg_visit_across_all_customers" -> (mykey -> (myvalue.size()/@@global_kpis_across_all_customers_count.get(arr.getJsonObject(cnt).getString("Operation") + "_across_all_customers_count").get(mykey).size())));
#PRINT "hello";
END;
active_customers_index = select s from active_customer_category_visit:s POST-ACCUM
FOREACH (mykey, myvalue) IN s.@customer_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_spend") DO
s.@customer_spend_level_kpis += ("visit_"+ arr.getJsonObject(cnt).getString("Operation") + "_customer_index" -> ((mykey -> (s.@customer_visit_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_visit").get(mykey).size()/s.@customer_total_spend_average_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_average_visit")) * 100))),
s.@customer_spend_level_kpis += ("visit_"+ arr.getJsonObject(cnt).getString("Operation") + "_category_index" ->
((mykey -> (s.@customer_visit_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_visit").get(mykey).size()/
@@global_kpis_across_all_customers.get(arr.getJsonObject(cnt).getString("Operation") + "_avg_visit_across_all_customers").get(mykey)) * 100))),
# Hybrid Index, level category, kpi visit
s.@customer_spend_level_kpis += ("visit_"+ arr.getJsonObject(cnt).getString("Operation") + "_hybrid_index" ->
(mykey -> (s.@customer_spend_level_kpis.get("visit_"+ arr.getJsonObject(cnt).getString("Operation") + "_customer_index").get(mykey)+
s.@customer_spend_level_kpis.get("visit_"+ arr.getJsonObject(cnt).getString("Operation") + "_category_index").get(mykey)) / 2)),
#s.@visit_category_hybrid_index += (mykey -> (s.@visit_category_customer_index.get(mykey) + s.@visit_category_category_index.get(mykey))/2),
IF arr.getJsonObject(cnt).getString("Operation") == "category" THEN
INSERT INTO profile_calendar_category_affinity (FROM, To, total_visit, visit_category_customer_index, visit_category_category_index, visit_category_hybrid_index) VALUES (mykey, s.customer_name + "_" + s.customer_id + "_" + datetime_format(@@max_order_date),
s.@customer_visit_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_visit").get(mykey).size(),
s.@customer_spend_level_kpis.get("visit_"+ arr.getJsonObject(cnt).getString("Operation") + "_customer_index").get(mykey),
s.@customer_spend_level_kpis.get("visit_"+ arr.getJsonObject(cnt).getString("Operation") + "_category_index").get(mykey),
s.@customer_spend_level_kpis.get("visit_"+ arr.getJsonObject(cnt).getString("Operation") + "_hybrid_index").get(mykey))
END
END;
# Hybrid Index, level category, kpi visit
#s.@visit_category_hybrid_index += (mykey -> (s.@visit_category_customer_index.get(mykey) + s.@visit_category_category_index.get(mykey))/2),
else if arr.getJsonObject(cnt).getJsonArray("kpi").getString(kpicnt) == "quantity" THEN
#is_quantity_found = TRUE;
active_customers_in_lookback = select s from start:s-(customer_orders:custord)-orders:ord-(orders_product:op)-product:p-(:pc)-:tt
where ord.transaction_datetime >= @@from_date and ord.transaction_datetime <= @@max_order_date accum
IF tt.type == arr.getJsonObject(cnt).getString("Operation") THEN
# Sample => customer_category_spend
#s.@total_category_quantity_accum += (cat.category_name -> op.item_qty),
s.@customer_spend_level_kpis += ("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_quantity" -> (tt.name -> op.item_qty)),
# KPI quantity, level category
#s.@category_total_quantity += (op.item_qty)
s.@customer_total_spend_average_spend_level_kpis += ("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_total_quantity" -> (op.item_qty)),
# Store (Total Quantity)
#@@global_kpis_across_all_customers += (arr.getJsonObject(cnt).getString("Operation") + "_total_quantity" -> (tt.name -> op.item_qty)),
#@@total_category_quantity += (cat.category_name -> op.item_qty)
@@global_kpis_across_all_customers_count += (arr.getJsonObject(cnt).getString("Operation") + "_across_all_customers_count" ->(tt.name -> s.customer_id))
END
POST-ACCUM
#s.@category_avg_quantity = s.@category_total_quantity/s.@total_category_quantity_accum.size();
s.@customer_total_spend_average_spend_level_kpis += ("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_average_quantity" -> s.@customer_total_spend_average_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_total_quantity")/s.@customer_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_quantity").size());
FOREACH (mykey, myvalue) IN @@global_kpis_across_all_customers.get(arr.getJsonObject(cnt).getString("Operation") + "_total_quantity") DO
@@global_kpis_across_all_customers += (arr.getJsonObject(cnt).getString("Operation") + "_avg_quantity_across_all_customers" -> (mykey -> (myvalue/@@global_kpis_across_all_customers_count.get(arr.getJsonObject(cnt).getString("Operation") + "_across_all_customers_count").get(mykey).size())));
#@@category_avg_quantity_across_all_customers += (mykey -> (myvalue/@@category_across_all_customers_count.get(mykey).size()));
#PRINT "hello";
END;
active_customers_index = select s from active_customers_in_lookback:s POST-ACCUM
FOREACH (mykey, myvalue) IN s.@customer_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_spend") DO
#s.@quantity_category_customer_index += (mykey -> (s.@total_category_quantity_accum.get(mykey)/s.@category_avg_quantity) * 100),
s.@customer_spend_level_kpis += ("quantity_"+ arr.getJsonObject(cnt).getString("Operation") + "_customer_index" -> ((mykey -> (s.@customer_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_quantity").get(mykey)/s.@customer_total_spend_average_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_average_quantity")) * 100))),
s.@customer_spend_level_kpis += ("quantity_"+ arr.getJsonObject(cnt).getString("Operation") + "_category_index" ->
((mykey -> (s.@customer_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_quantity").get(mykey)/
@@global_kpis_across_all_customers.get(arr.getJsonObject(cnt).getString("Operation") + "_avg_quantity_across_all_customers").get(mykey)) * 100))),
#s.@quantity_category_category_index += (mykey -> (s.@total_category_quantity_accum.get(mykey)/@@category_avg_quantity_across_all_customers.get(mykey)) * 100),
# Hybrid Index, level category, kpi quantity
s.@customer_spend_level_kpis += ("quantity_"+ arr.getJsonObject(cnt).getString("Operation") + "_hybrid_index" ->
(mykey -> (s.@customer_spend_level_kpis.get("quantity_"+ arr.getJsonObject(cnt).getString("Operation") + "_customer_index").get(mykey)+
s.@customer_spend_level_kpis.get("quantity_"+ arr.getJsonObject(cnt).getString("Operation") + "_category_index").get(mykey)) / 2)),
#s.@quantity_category_hybrid_index += (mykey -> (s.@quantity_category_customer_index.get(mykey) + s.@quantity_category_category_index.get(mykey))/2)
IF arr.getJsonObject(cnt).getString("Operation") == "category" THEN
INSERT INTO profile_calendar_category_affinity (FROM, To, quantity_category_customer_index, quantity_category_category_index, quantity_category_hybrid_index) VALUES (mykey, s.customer_name + "_" + s.customer_id + "_" + datetime_format(@@max_order_date)
, s.@customer_spend_level_kpis.get("quantity_"+ arr.getJsonObject(cnt).getString("Operation") + "_customer_index").get(mykey),
s.@customer_spend_level_kpis.get("quantity_"+ arr.getJsonObject(cnt).getString("Operation") + "_category_index").get(mykey),
s.@customer_spend_level_kpis.get("quantity_"+ arr.getJsonObject(cnt).getString("Operation") + "_hybrid_index").get(mykey))
END
END;
end;
if (kpicnt == arr.getJsonObject(cnt).getJsonArray("kpi").size()-1) AND (is_spend_found == False) THEN
# Last iteration and still spend is not found (callculate basic KPIs)
active_customers_index = select s from active_customers_in_lookback:s POST-ACCUM
FOREACH (mykey, myvalue) IN s.@customer_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_spend") DO
IF arr.getJsonObject(cnt).getString("Operation") == "category" THEN
INSERT INTO profile_calendar_category_affinity (FROM, To, total_spend) VALUES (mykey, s.customer_name + "_" + s.customer_id + "_" + datetime_format(@@max_order_date),
myvalue)
ELSE IF arr.getJsonObject(cnt).getString("Operation") == "subcategory" THEN
INSERT INTO profile_calendar_subcategory_affinity (FROM, To, total_spend) VALUES (mykey, s.customer_name + "_" + s.customer_id + "_" + datetime_format(@@max_order_date),
myvalue)
ELSE IF arr.getJsonObject(cnt).getString("Operation") == "department" THEN
INSERT INTO profile_calendar_department_affinity (FROM, To, total_spend) VALUES (mykey, s.customer_name + "_" + s.customer_id + "_" + datetime_format(@@max_order_date),
myvalue)
END
END;
end;
if (kpicnt == arr.getJsonObject(cnt).getJsonArray("kpi").size()-1) AND (is_visit_found == False) THEN
# Last iteration and still visit is not found (callculate basic KPIs)
active_customer_category_visit = select s from start:s-(customer_orders:custord)-orders:ord-(orders_product:op)-product:p-(:pc)-:tt
where ord.transaction_datetime >= @@from_date and ord.transaction_datetime <= @@max_order_date accum
IF tt.type == arr.getJsonObject(cnt).getString("Operation") THEN
s.@customer_visit_level_kpis += ("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_visit" -> (tt.name -> ord.order_id))
END;
active_customers_index = select s from active_customer_category_visit:s POST-ACCUM
FOREACH (mykey, myvalue) IN s.@customer_spend_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_visit") DO
IF arr.getJsonObject(cnt).getString("Operation") == "category" THEN
INSERT INTO profile_calendar_category_affinity (FROM, To, total_visit) VALUES (mykey, s.customer_name + "_" + s.customer_id + "_" + datetime_format(@@max_order_date),
s.@customer_visit_level_kpis.get("customer_"+ arr.getJsonObject(cnt).getString("Operation") + "_visit").get(mykey).size())
END
END;
end;
end;
end;
# Commented due to lots of customer
#PRINT active_customers_in_lookback;
#PRINT active_customers_index;
PRINT "affinity_segmentation works!";
}
Parameter which is passed to run the query
{
"segment_type_label": "Affinity Segment (Batch Segments)",
"segment_type_id": "affinitySegment",
"conditions": [
{
"Operation": "category",
"kpi": ["spend", "visit"]
},
{
"Operation": "category",
"kpi": ["spend"]
},
{
"Operation": "department",
"kpi": ["quantity"]
}
]
}
**My question is on this below line**
active_customers_in_lookback = select s from start:s-(customer_orders:custord)-orders:ord-(orders_product:op)-product:p-(:pc)-:tt
where ord.transaction_datetime >= @@from_date and ord.transaction_datetime <= @@max_order_date accum
IF tt.type == arr.getJsonObject(cnt).getString("Operation")
how can make the query like this:
select s from start:s-(customer_orders:custord)-orders:ord-(orders_product:op)-product:p-(:pc)-:arr.getJsonObject(cnt).getString("Operation").tt
If we declare the target node in the gsql traversal then it would take less time, because in my schema i have 4 target vertex and by accum every edge would be traversed first then it would be check in the IF condition whether the target vertex is matched with the passed parameter target or not which is taking too much time. that cause to GPE failure and Timeout approx 4 lakh customer I have in my dummy data and their 8 lakhs of orders