How to count number of item that sastify condition by small group

[nguyentu]

For example, I have below data:

Shop1 Day1 sell 11
Shop1 Day2 sell 14
Shop1 Day3 sell 20
Shop2 Day1 sell 15
Shop2 Day2 sell 20
Shop2 Day3 sell 5
Shop2 Day4 sell 21
Shop3 Day1 sell 6
Shop3 Day2 sell 4

If one shop sell > 10 items in 2 consecutive day, that shop is mark as popular shop.
How can I count total of popular shop ?

[DalJeanis · Accepted solution]

I would use streamstats

Let's assume that "day" is the Date portion of the _time, in a format that sorts normally (either in epoch time or "2017-02-01" format)

(your search here that produces  _time, shopname, sales)
| sort 0 shopname, _time
| streamstats window=2 global=t sum(sales) as sales2days by shopname
| where sales2days >= 10

That will get you a record for each day a shop was considered a popular shop. The following will get you information about each popular shop, including how many popular days they have had, and when the first and last were.

  | stats count as countpopulardays, min(_time) as firstpopularday, max(_time) as lastpopularday  by shopname

or, if you just want a list of shops that have ever qualified as popular, you can just

| dedup shopname

[nguyentu]

Thank you very much. I understood