`base search | stats values(zipcode), count(zipcode) as c by country | sort -c | head 10`
which gives me most appeared 10 countries in the base search.
but I still need to count how many times each zipcode shows and get head 10 most zipcode under each country.
Anyone can figure out how to write this?
base search
| stats count by zipcode country
| sort 0 country -num(count)
| streamstats count as rank by country
| where rank <= 10
Thank you, but that's not what I mean.
base search will give
country | zipcode |
US | AAAAAA |
CA | BBB BBB |
US | AAACCC |
US | AAAAAA |
I want to find top 10 countries appear most in the country column
based on that, in each country, zipcode also need to be counted and sorted to get most appeard 10 zipcode as well
the expected output for above table is
country | zipcode | count |
US | AAAAAA | 2 |
AAACCC | 1 | |
CA | BBB BBB | 1 |
Thank you for your help!
| makeresults
| eval _raw="country zipcode
US AAAAAA
CA BBB BBB
US AAACCC
PE AAAAAA
AR AAAAAA
CA BBB BBB
UK AAACCC
BE AAAAAA
US AAAAAA
NL BBB BBB
US AAACCC
DE AAAAAA
SA AAAAAA
FR BBB BBB
ES AAACCC
NL AAAAAA
US AAAAAA
CA BBB BBB
US AAACCC
US AAAAAA
US AAAAAA
CA BBB BBB
UK AAACCC
BE AAAAAA
US AAAAAA
NL BBB BBB
US AAACCC
DE AAAAAA
SA AAAAAA
FR BBB BBB
ES AAACCC
BR AAAAAA"
| multikv forceheader=1
| table country zipcode
| eventstats count by country
| sort -count country
| streamstats dc(country) as countryrank
| where countryrank<=10
| stats count values(countryrank) as countryrank by country zipcode
| sort country -count
| streamstats dc(zipcode) as rank by country
| where rank<=10
| sort countryrank rank