Hey,
this is a complicated one but i hope you can help me.
I have 2 different measurements
In the first one is all the data which is important for an order like articlenumber and order-ID.
In the second one i get the live data from a process where i can extract how long an order was within each processing step.
In all the processing steps are between 2 and 20 machines where the order can go through. Sometimes the order gets processed by 2 machines at the step at the same time.
What i want to achive is to get the duration of how long each order ID was within a processing step.
Then i want to make the difference between this time and the 12 months mean time of the corresponding articlenumber. I was able to get this far with 2 queries which i combined with the “transform” functions of Grafana. The problem is that the calculating time is way to long.
Is there a way to achive this in maybe one single query?
My working queries look like this:
import "contrib/tomhollingworth/events"
tab1 = from(bucket: "Bucket")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) =>
r._measurement == "OrderData" or
r.short == "quantity" or
r.short == "number" or
r.short == "ID"
)
|>keep(columns: ["_field", "_time","short","_value"])
|> pivot(
rowKey:["_time"],
columnKey: ["short","_field"],
valueColumn: "_value"
)
|> map(fn: (r) => ({
_time: r._time,
ID: string(v: r.ID_value),
Bestellmenge: r.quantity_value,
Artikelnummer: string(v: r.number_value_str)
}))
tab2 = from(bucket: "Bucket")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) =>
r._measurement == "LiveData" and
r.short == "IDmachine1step1"
)
|>keep(columns: ["_field", "_time","short","_value","_stop"])
|> pivot(
rowKey:["_time"],
columnKey: ["short","_field"],
valueColumn: "_value"
)
|> map(fn: (r) => ({
_time: r._time,
_stop:r._stop,
ID: string(v: r.IDmachine1step1_value_str),
}))
|>events.duration(
unit:1m,
columnName: "M1",
timeColumn: "_time",
stopColumn: "_stop"
)
|>drop(columns: ["_time","_stop"])
|>group(columns:["ID"])
|>sum(column:"M1")
|>group()
tab3 = from(bucket: "Bucket")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) =>
r._measurement == "LiveData" and
r.short == "IDmachine2step1"
)
|>keep(columns: ["_field", "_time","short","_value","_stop"])
|> pivot(
rowKey:["_time"],
columnKey: ["short","_field"],
valueColumn: "_value"
)
|> map(fn: (r) => ({
_time: r._time,
_stop:r._stop,
ID: string(v: r.IDmachine2step1_value_str),
}))
|>events.duration(
unit:1m,
columnName: "M2",
timeColumn: "_time",
stopColumn: "_stop"
)
|>drop(columns: ["_time","_stop"])
|>group(columns:["ID"])
|>sum(column:"M2")
|>group()
tab4=union(
tables: [tab2,tab3]
)
join(
tables: {t1: tab1, t2: tab4},
on: ["ID"]
)
|>fill(column: "M1", value: 0)
|>fill(column: "M2", value: 0)
|> map(fn: (r) => ({
"time per quantity": (float(v: r.M1)+float(v: r.M2))/(r.quantity/1000.0),
number: r.number,
ID: r.ID
}))
import "contrib/tomhollingworth/events"
tab1 = from(bucket: "Bucket")
|> range(start: -365d)
|> filter(fn: (r) =>
r._measurement == "OrderData" or
r.short == "quantity" or
r.short == "number" or
r.short == "ID"
)
|>keep(columns: ["_field", "_time","short","_value"])
|> pivot(
rowKey:["_time"],
columnKey: ["short","_field"],
valueColumn: "_value"
)
|> map(fn: (r) => ({
_time: r._time,
ID: string(v: r.ID_value),
Bestellmenge: r.quantity_value,
Artikelnummer: string(v: r.number_value_str)
}))
tab2 = from(bucket: "Bucket")
|> range(start: -365d)
|> filter(fn: (r) =>
r._measurement == "LiveData" and
r.short == "IDmachine1step1"
)
|>keep(columns: ["_field", "_time","short","_value","_stop"])
|> pivot(
rowKey:["_time"],
columnKey: ["short","_field"],
valueColumn: "_value"
)
|> map(fn: (r) => ({
_time: r._time,
_stop:r._stop,
ID: string(v: r.IDmachine1step1_value_str),
}))
|>events.duration(
unit:1m,
columnName: "M1",
timeColumn: "_time",
stopColumn: "_stop"
)
|>drop(columns: ["_time","_stop"])
|>group(columns:["ID"])
|>sum(column:"M1")
|>group()
tab3 = from(bucket: "Bucket")
|> range(start: -365d)
|> filter(fn: (r) =>
r._measurement == "LiveData" and
r.short == "IDmachine2step1"
)
|>keep(columns: ["_field", "_time","short","_value","_stop"])
|> pivot(
rowKey:["_time"],
columnKey: ["short","_field"],
valueColumn: "_value"
)
|> map(fn: (r) => ({
_time: r._time,
_stop:r._stop,
ID: string(v: r.IDmachine2step1_value_str),
}))
|>events.duration(
unit:1m,
columnName: "M2",
timeColumn: "_time",
stopColumn: "_stop"
)
|>drop(columns: ["_time","_stop"])
|>group(columns:["ID"])
|>sum(column:"M2")
|>group()
tab4=union(
tables: [tab2,tab3]
)
join(
tables: {t1: tab1, t2: tab4},
on: ["ID"]
)
|>fill(column: "M1", value: 0)
|>fill(column: "M2", value: 0)
|> map(fn: (r) => ({
"time per quantity 12M": (float(v: r.M1)+float(v: r.M2))/(r.quantity/1000.0),
number: r.number,
ID: r.ID
}))
|>group(columns:["number"])
|>mean(column:"time per quantity 12M")
|>group()
With these two queries i used the transformations “OuterJoin”, “filter data by values” and “Add field from calculation” to get the difference.
I hope somebody can help me with this. Thank you so much in advance.