Suppose this is the record set in sql.
number | net amt | total amount |
---|---|---|
229W0330014229 | 0.69 | 11.07 |
229W0330014229 | 0.05 | 11.07 |
21380365288472 | 26.25 | 44.02 |
21380365288472 | 1.2 | 44.02 |
64950353437733 | 19.95 | 57.39 |
64950353437733 | 2.53 | 57.39 |
64950352648123 | 11.55 | 37.6 |
64950352648123 | 1.76 | 37.6 |
65410310307858 | 1.12 | 16.56 |
65410310307849 | 1.12 | 16.56 |
800W0346263038 | 0.8 | 11.81 |
Total: 11 | 67.02 | 195.01 |
And expected result from ES aggregation bucket is :-
ES net amount | ES results total amount | number distinct count |
---|---|---|
30.49000007 | 88.94999981 | |
36.53000091 | 106.0599976 | |
67.02 | 195.001 | 7 |
I achieved the above results in ES by using below query, but unwanted buckets get created due to which performance issue gets created for millions of records, please help how this could be done without using terms query to get total_amount sum.
"aggs": {
"accessorials": {
"date_histogram": {
"field": "invoicedt",
"interval": "year",
"format": "Y-MM-dd:Y",
"keyed": true
},
"aggs": {
"amount": {
"sum": {
"field": "netamt"
}
},
"total_amount": {
"terms": {
"field": "number",
"size":"2147483647"
},
"aggs": {
"MAX_COST": {
"max": {
"field": "total_amount"
}
}
}
},
"derived_total_amount": {
"sum_bucket": {
"buckets_path": "total_amount>MAX_COST"
}
},
"distinct_number": {
"stats_bucket": {
"buckets_path": "total_amount._count"
}
}
The total_amount bucket is not required and i want to get rid of it, although i want to fetch sum of total_amount by number. Can we do this through scripts? is it a good approach? if yes How?
Need sum of total amount group by number field. An euivalent sql query would be:-
SELECT sum(distinct total_amount),number, sum(netamt) from table WHERE id = 6 group by number;