Hi
Ihave a question
this is input
date item field_1 field_2 field_3
2016/01/01 x 1 2 3
2016/01/01 y 4 5 6
this I want the output
date item median_field
2016/01/01 x 2
2016/01/01 y 5
Thanks
... | stats median(field_*) as median by date item
Nice. Assuming that the field names match a mask, that's much more succinct, and it has the feature that it will correctly calculate the median if there are multiple records for a time and item combination.
Here's a run-anywhere example.
| makeresults
| eval mydata="2016/01/02,x,1,2,3 2016/01/02,y,6,4,5"
| makemv mydata | mvexpand mydata
| makemv delim="," mydata
| eval _time = strptime(mvindex(mydata,0),"%Y/%m/%d")
| eval item = mvindex(mydata,1)
| eval field1 = mvindex(mydata,2)
| eval field2 = mvindex(mydata,3)
| eval field3 = mvindex(mydata,4)
| table _time item field1 field2 field3
| rename COMMENT as "The above just generates test data"
| rename COMMENT as "We put together all the key fields we want to keep, in a single field."
| eval mykeystuff = _time."!!!!".item
| rename COMMENT as "We keep our key field, and all the numeric fields we want the median from."
| table mykeystuff field*
| rename COMMENT as "We untable to put the field names into field, and the field values into value, then calculate our median, ignoring the field names"
| untable mykeystuff field value
| stats median(value) as mymedian by mykeystuff
| rename COMMENT as "Now we unpack our key fields again"
| makemv delim="!!!!" mykeystuff
| eval _time = mvindex(mykeystuff,0)
| eval item = mvindex(mykeystuff,1)
| rename COMMENT as "And kill the combined key field, now that it's done its duty"
| table _time item mymedian
updated to use delim="!!!!" in case the item names have any spaces in them.