Hi team,
Can you please provide me the DQL and Graph QL for the below GSQL query.
active_customers = select s from customers:s-(customer_orders:custord)-orders:ord accum
s.@total_spend += ord.net_spend_amount, s.@distinct_weeks+=ord.week_id
post-ACCUM s.@avg_weekly_spend = s.@total_spend/s.@distinct_weeks.size();
Thanks
Team please guide me please…
i think this is not possible in Dgraph to do this all in a single Query
we need to do sum or aggregation logic further in python ?
1. First, query for all customers and their orders without aggregation:
graphqlCopy code
{
allCustomers(func: has(customer.order)) {
uid
customer_name
customer.order {
net_spend_amount
}
}
}
2. After executing this query and obtaining the results, you can process the data in your application code to calculate the total net spend amount for each customer and add it as an attribute in the customer object.
Here's a simplified Python example using the `requests` library and some post-processing:
pythonCopy code
import requests
endpoint = ‘http://dgraph.genome-services.net/query’
query = ‘’’
{
allCustomers(func: has(customer.order)) {
uid
customer_name
customer.order {
net_spend_amount
}
}
}
‘’’
response = requests.post(endpoint, json={‘query’: query})
data = response.json()
customers = data[‘data’][‘allCustomers’]
for customer in customers:
total_net_spend = sum(order[‘net_spend_amount’] for order in customer[‘customer’][‘order’])
customer[‘total_net_spend’] = total_net_spend
print(customers)
@Damon
Sorry to tagged your directly here
because no one is replying me in this forum
Can you please please guide me in this, its urgent i’m stuck in this from yesterday.
Can you define the schema and query in text? I’m not great at reading cypher.
Vars can be tricky, and are usually needed to do aggregations. Note that every var is really a map keyed by uid. So when you set a var in some context, you are actually adding that value to a map keyed by the uid of the enclosing object/context. When you use the var, you have to use it in a context that has the same uids or the var will not return anything for val(var).
@Damon thanks for giving me your precious time to reply.
My Mutation is **1. Cutomer having some order
2. and order contains net_spend_amount and week_id**
3. In below Mutation Script Customer having 3 orders
**Mutation**
{
"set": [
{
"uid": "_:customer1x",
"customer.customer_id": "12345x",
"customer.customer_name": "Mohn Doe",
"customer.email": "mohndoe@example.com"
"customer.order": [
{
"order.order_id": "order1x",
"order.transaction_datetime": "2023-09-07T12:00:00Z",
"order.net_spend_amount": 80.00,
"order.week_id": 28,
"order.product": {
"uid": "_:product1x"
}
},
{
"order.order_id": "order2x",
"order.transaction_datetime": "2023-09-08T14:00:00Z",
"order.net_spend_amount": 120.00,
"order.week_id": 32,
"order.product": {
"uid": "_:product2x"
}
},
{
"order_id": "order3x",
"order.transaction_datetime": "2023-09-09T16:00:00Z",
"order.net_spend_amount": 60.00,
"order.week_id": 32,
"order.product": {
"uid": "_:product3x"
}
}
]
}
]
}
**Query**:
{
allcustomers_having_orders(func: has(customer.order)) {
customer.customer_name
customer.order {expand(order)}
}
}
Query Response:
I want to get average weekly spend of all customer
So for that below steps i want to do in the above query.
that above 130 i want in a customer.avg_weekly_spend variable of a customer
Thanks
OK.
No possible in Dgraph. If you transform your weeks into Nodes. We could use groupby. So we would groupby week. Quick question, is that the number of the week in the calendar? or some custom/specific number?
In the future we will be able to group by value.
I did tried. But i fear that is not what you want
{
users_orders(func: has(customer.customer_id)) {
customer.customer_name
order.net_spend_amount
customer.order {
order.week_id
ONSA as order.net_spend_amount
}
total_spend as sum(val(ONSA))
avg_weekly_spend: avg(val(ONSA))
avg_weekly_spend_test: math(total_spend/2)
}
}
I would recommend to avoid complex computation in the cluster.
If I were an architect responsible for the software, I would do an ops plan. Taking care where there will be a bottleneck and where would be the best place to employ computing. I would prevent a database from doing calculations for one simple reason (and this is my personal opinion as a programmer), databases should only work to store data and not to compute it. Except when computing is strictly linked to indexing. But that’s my opinion. I see many users creating complex queries all the time. For me it is a bad practice.
thanks for giving me your time to reply
your above query works perfect.
but I want to count the distinct weeks i.e in the above exmple we have 3 weeks (28, 32, 32) so the distinct week count is “2”
so that 2 i want to make it dynamic
right now 2 is hardcoded in the above provided query
{
users_orders(func: has(customer.customer_id)) {
customer.customer_name
#order.net_spend_amount
customer.order {
WEEK as order.week_id
ONSA as order.net_spend_amount
}
total_spend as sum(val(ONSA))
distinct_weeks_count: count(set(val(WEEK)))
avg_weekly_spend_test: math(total_spend/distinct_weeks_count)
}
}
This is i want to do , but the aggreagte “set” is not there in DQL , how can we approach this as of now.
week is a attribute as of now in a order table
we dont have year/calendar all of those as of now.
Yeah, we don’t have that feature. Feel free to open a feature request. But what “set” function do? I have almost zero knowledge on TigerGraph.
@MichelDiz thanks for your reply.
what “set” function do ?
The set maintains a collection of unique elements.
In the above-mentioned example, we have week ids of {28, 32, 32}:
So to calculate average_weekly_spend
we need total_spend as well as count distinct weeks shopped
Formula: average_weekly_spend = total_spend/count(count distinct weeks shopped)
Now please please guide me how can i get the unique element count of week_id in Dgraph? total_spend is working perfect
we have “order.week_id”: 28 (week_id) like this no seperate node for this
do we need to call this query in python and apply for loop iteration on all customers to get average_weekly_spend ? any other alternate way to do this in dgraph ?
please guide
thanks
You can convert the week_id into entities. (aka “node”). And then you could use groupby. See GroupBy - Query language
In Dgraph entities are unique. As long you use always the same UID for that entity. So, when you use groupby, it will act like “distinct” or set as mentioned.
hi @MichelDiz
Thanks for your guidance.
I created a seperate entity for “week” , below is the Query response for the same
my 32 week uid is same for the 2 seperate orders, becuase they both are purchased in the same week_id
but i’m stuck while applying group by in the query. Kindly help please
Below ones i tried, please guide what i applied wrong in groupby to get 2 distinct_weeks_shopped for my average_weekly_spend formula.
Query1.
{
users_orders(func: uid(0x88ba6)) {
customer.customer_name
customer.order {
ONSA as order.net_spend_amount
order.week{
week.week_id
}
}
total_spend as sum(val(ONSA))
#rough as ONSA1
avg_weekly_spend: math(total_spend/2)
}
}
Response 1
{
"data": {
"users_orders": [
{
"customer.customer_name": "Mohn Doe",
"customer.order": [
{
"order.net_spend_amount": 60,
"order.week": {
"week.week_id": "28"
}
},
{
"order.net_spend_amount": 80,
"order.week": {
"week.week_id": "32"
}
},
{
"order.net_spend_amount": 120,
"order.week": {
"week.week_id": "32"
}
}
],
"sum(val(ONSA))": 260,
"avg_weekly_spend": 130
}
]
},
"extensions": {
"server_latency": {
"parsing_ns": 96792,
"processing_ns": 1417424,
"encoding_ns": 65533,
"assign_timestamp_ns": 714638,
"total_ns": 2400078
},
"txn": {
"start_ts": 17554001
},
"metrics": {
"num_uids": {
"": 1,
"_total": 11,
"customer.customer_name": 1,
"customer.order": 1,
"order.net_spend_amount": 3,
"order.week": 3,
"week.week_id": 2
}
}
}
}
Query2:
{
users_orders(func: uid(0x88ba6)) {
customer.customer_name
customer.order {
ONSA as order.net_spend_amount
}
customer.order @groupby(week){
count(uid)
}
total_spend as sum(val(ONSA))
#rough as ONSA1
avg_weekly_spend: math(total_spend/2)
}
}
Response 2:
{
"data": {
"users_orders": [
{
"customer.customer_name": "Mohn Doe",
"customer.order": [
{
"order.net_spend_amount": 60
},
{
"order.net_spend_amount": 80
},
{
"order.net_spend_amount": 120
}
],
"sum(val(ONSA))": 260,
"avg_weekly_spend": 130
}
]
},
"extensions": {
"server_latency": {
"parsing_ns": 224887,
"processing_ns": 1428683,
"encoding_ns": 52710,
"assign_timestamp_ns": 1486957,
"total_ns": 3295485
},
"txn": {
"start_ts": 17554020
},
"metrics": {
"num_uids": {
"": 1,
"_total": 7,
"customer.customer_name": 1,
"customer.order": 2,
"order.net_spend_amount": 3
}
}
}
}
@amaster507
sorry to tag you here directly but im stuck from a long
please guide me in this dictinct count
Try something like this(I had to change a bit some preds name, but ignore it)
{
var(func: has(customer.customer_name))
@filter(eq(customer.customer_name, "Mohn Doe") and has(customer.customer_id_)) {
customer.customer_name
order.net_spend_amount
customer.order @groupby(order.week_id_) {
CO as count(uid)
}
}
users_orders(func: uid(CO)) {
week.week_id
count: val(CO)
<~order.week_id_> {
ONSA as order.net_spend_amount
}
total_spend as sum(val(ONSA))
avg_weekly_spend: avg(val(ONSA))
avg_weekly_spend_test: math(total_spend/2)
}
}
It should return
{
"data": {
"users_orders": [
{
"week.week_id": "32",
"count": 2,
"~order.week_id_": [
{
"order.net_spend_amount": 120
},
{
"order.net_spend_amount": 60
}
],
"sum(val(ONSA))": 180,
"avg_weekly_spend": 90,
"avg_weekly_spend_test": 90
},
{
"week.week_id": "28",
"count": 1,
"~order.week_id_": [
{
"order.net_spend_amount": 80
}
],
"sum(val(ONSA))": 80,
"avg_weekly_spend": 80,
"avg_weekly_spend_test": 40
}
]
},
}
}
I have also added a reverse indexing in the order.week_id predicate. So we can expand its incoming edges.
@MichelDiz this above one works for me. I got the concept of “var” and “multiple var blocks” too
thanks
So please correct my conclusion, if i’m wrong
Hence the conclusion is if we want
count distinct of unique value from vertex attribute
or
result of unique elements only
then we need to go with your above provided “groupby” approach** **
For ex:
{
all_unique_names_count(func: has(customer.customer_name)) {
customer_names_list as customer.customer_name
?? then at this time we don't have a seperate entity for customer name, just like we have created for Week entity in the above discussion ?
and aggregation on set is also not applicable ?
then for this way how we can proceed on ?
}
Thanks