I have an intermediate table from some query:
... | table Stock_price_difference, start_time, end_time, company
Stock_price_difference | start_time | end_time | company |
---|---|---|---|
1.3 | 10:01 | 10:20 | Apple |
0.3 | 11:00 | 11:05 | Apple |
1.1 | 11:30 | 11:35 | Apple |
0.4 | 09:10 | 09:18 | MS |
0.8 | 09:50 | 10:00 | MS |
0.1 | 10:00 | 10:10 | MS |
1 | 10:15 | 10:20 | Amazon |
1.1 | 11:10 | 11:15 | Amazon |
I would like to get the rows by max stock price difference for each company:
Stock_price_difference | start_time | end_time | company |
---|---|---|---|
1.3 | 10:01 | 10:20 | Apple |
0.8 | 09:50 | 10:00 | MS |
1.1 | 11:10 | 11:15 | Amazon |
How can I do that?
Thanks.
This should solve the problem:
..| table Stock_price_difference, start_time, end_time, company|sort - company,Stock_price_difference| eval CountF=1|streamstats sum(CountF) as CountF by company|where CountF<2 | fields - CountF
This should give you top 1 Stock_price_difference for each company along with start_time and end_time field (or any field available)
They are many approaches, here is the quick and hack :
- using a sort and a dedup on the company, to keep the event highest value in the Stock-price-difference per company.
...| sort company -Stock_price_difference | dedup company | table Stock_price_difference, start_time, end_time, company
result is :
Stock_price_difference start_time end_time company
1.1 11:10 11:15 Amazon
1.3 10:01 10:20 Apple
0.8 09:50 10:00 MS
That's even sleeker, thanks!
This should solve the problem:
..| table Stock_price_difference, start_time, end_time, company|sort - company,Stock_price_difference| eval CountF=1|streamstats sum(CountF) as CountF by company|where CountF<2 | fields - CountF
This should give you top 1 Stock_price_difference for each company along with start_time and end_time field (or any field available)
Awesome! That works.