Basically data looks like this. I want to calculate the average time to complete an order across many orders each with different amounts of items.
I have a query that works, but its hugely inefficient and throws errors due to hitting the stats limit. There has to be a better way to do this, streamstats or delta just gave me the duration from the previous event in the chain and I need to have that evaluate against the order time for all items in an order, not the previous item.
index=index "@mt"="itemComplete" OR @mt="orderReceived"
| fillnull value=orderReceived itemComplete
| stats earliest(_time) as orderReceived list(itemId) as itemId,list(_time) as prepTime by orderId
| mvexpand prepTime
| eval timeToPrep=prepTime-orderReceived
| stats avg(timeToPrep) as avgItemCompletedDuration
| where startOfPreperation>0