how to do search median

[thomas22966710]

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

[jkat54 · Accepted solution]

... | stats median(field_*) as median by date item

[DalJeanis]

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.

[DalJeanis]

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.