Custom Query for Range / Bucket Aggregation

[bhavin.shah]

Hi,

ELK setup - 3 nodes/ 7.10.0 version

Query :-
I am trying to create one query for business where below is raw data and expected output as well.

Left side - is raw data and final data is dealer ID wise brk_amt wise range summed range.
For example :- Dealer 5 has 3 different clients and they given different different revenues on different days. In the middle table - total revenue is summed and as per business requirement against dealer wise we need list of clients who has given revenue more than 200 and less 200.

Attempted query :-

POST _sql/?format=txt
{
  "query":""" select dealer_id , histogram(g,200) from (
                    select dealer_id, ent_id  , sum(brk_amt) g
                    FROM  alias_brkg_details
                    WHERE trade_date between '2020-11-01'  and 
                    '2020-11-03' and source_1 in('OWS','TWS')
                    and dealer_id = 'AS109504'
                    group by dealer_id,ent_id  )
    """
}

Error :-

{
"error" : {
"root_cause" : [
{
"type" : "verification_exception",
"reason" : "Found 1 problem\nline 1:21: [histogram(g,200)] needs to be part of the grouping"
}
],
"type" : "verification_exception",
"reason" : "Found 1 problem\nline 1:21: [histogram(g,200)] needs to be part of the grouping"
},
"status" : 400
}

Your support on the same is highly appreciated.

With Regards
Bhavin

[fbaligand (Fabien Baligand)]

If you wish a Kibana vis, you could achieve this using Elasticsearch Data Transforms, or Canvas expression language, or TSVB vis or Timelion vis or VEGA vis.

[bhavin.shah]

Thanks alot Fabien. I will check and let you know if need any support. Regards Bhavin