Rate aggregation
editRate aggregation
editA rate metrics aggregation can be used only inside a date_histogram or composite aggregation. It calculates a rate of documents
or a field in each bucket. The field values can be extracted from specific numeric or
histogram fields in the documents.
For composite aggregations, there must be exactly one date_histogram source for the rate aggregation to be supported.
Syntax
editA rate aggregation looks like this in isolation:
{
"rate": {
"unit": "month",
"field": "requests"
}
}
The following request will group all sales records into monthly buckets and then convert the number of sales transactions in each bucket into per annual sales rate.
resp = client.search(
index="sales",
size=0,
aggs={
"by_date": {
"date_histogram": {
"field": "date",
"calendar_interval": "month"
},
"aggs": {
"my_rate": {
"rate": {
"unit": "year"
}
}
}
}
},
)
print(resp)
response = client.search(
index: 'sales',
body: {
size: 0,
aggregations: {
by_date: {
date_histogram: {
field: 'date',
calendar_interval: 'month'
},
aggregations: {
my_rate: {
rate: {
unit: 'year'
}
}
}
}
}
}
)
puts response
const response = await client.search({
index: "sales",
size: 0,
aggs: {
by_date: {
date_histogram: {
field: "date",
calendar_interval: "month",
},
aggs: {
my_rate: {
rate: {
unit: "year",
},
},
},
},
},
});
console.log(response);
GET sales/_search
{
"size": 0,
"aggs": {
"by_date": {
"date_histogram": {
"field": "date",
"calendar_interval": "month"
},
"aggs": {
"my_rate": {
"rate": {
"unit": "year"
}
}
}
}
}
}
The response will return the annual rate of transactions in each bucket. Since there are 12 months per year, the annual rate will be automatically calculated by multiplying the monthly rate by 12.
{
...
"aggregations" : {
"by_date" : {
"buckets" : [
{
"key_as_string" : "2015/01/01 00:00:00",
"key" : 1420070400000,
"doc_count" : 3,
"my_rate" : {
"value" : 36.0
}
},
{
"key_as_string" : "2015/02/01 00:00:00",
"key" : 1422748800000,
"doc_count" : 2,
"my_rate" : {
"value" : 24.0
}
},
{
"key_as_string" : "2015/03/01 00:00:00",
"key" : 1425168000000,
"doc_count" : 2,
"my_rate" : {
"value" : 24.0
}
}
]
}
}
}
Instead of counting the number of documents, it is also possible to calculate a sum of all values of the fields in the documents in each bucket or the number of values in each bucket. The following request will group all sales records into monthly bucket and than calculate the total monthly sales and convert them into average daily sales.
resp = client.search(
index="sales",
size=0,
aggs={
"by_date": {
"date_histogram": {
"field": "date",
"calendar_interval": "month"
},
"aggs": {
"avg_price": {
"rate": {
"field": "price",
"unit": "day"
}
}
}
}
},
)
print(resp)
response = client.search(
index: 'sales',
body: {
size: 0,
aggregations: {
by_date: {
date_histogram: {
field: 'date',
calendar_interval: 'month'
},
aggregations: {
avg_price: {
rate: {
field: 'price',
unit: 'day'
}
}
}
}
}
}
)
puts response
const response = await client.search({
index: "sales",
size: 0,
aggs: {
by_date: {
date_histogram: {
field: "date",
calendar_interval: "month",
},
aggs: {
avg_price: {
rate: {
field: "price",
unit: "day",
},
},
},
},
},
});
console.log(response);
GET sales/_search
{
"size": 0,
"aggs": {
"by_date": {
"date_histogram": {
"field": "date",
"calendar_interval": "month"
},
"aggs": {
"avg_price": {
"rate": {
"field": "price",
"unit": "day"
}
}
}
}
}
}
The response will contain the average daily sale prices for each month.
{
...
"aggregations" : {
"by_date" : {
"buckets" : [
{
"key_as_string" : "2015/01/01 00:00:00",
"key" : 1420070400000,
"doc_count" : 3,
"avg_price" : {
"value" : 17.741935483870968
}
},
{
"key_as_string" : "2015/02/01 00:00:00",
"key" : 1422748800000,
"doc_count" : 2,
"avg_price" : {
"value" : 2.142857142857143
}
},
{
"key_as_string" : "2015/03/01 00:00:00",
"key" : 1425168000000,
"doc_count" : 2,
"avg_price" : {
"value" : 12.096774193548388
}
}
]
}
}
}
You can also take advantage of composite aggregations to calculate the average daily sale price for each item in
your inventory
resp = client.search(
index="sales",
filter_path="aggregations",
size="0",
aggs={
"buckets": {
"composite": {
"sources": [
{
"month": {
"date_histogram": {
"field": "date",
"calendar_interval": "month"
}
}
},
{
"type": {
"terms": {
"field": "type"
}
}
}
]
},
"aggs": {
"avg_price": {
"rate": {
"field": "price",
"unit": "day"
}
}
}
}
},
)
print(resp)
response = client.search(
index: 'sales',
filter_path: 'aggregations',
size: 0,
body: {
aggregations: {
buckets: {
composite: {
sources: [
{
month: {
date_histogram: {
field: 'date',
calendar_interval: 'month'
}
}
},
{
type: {
terms: {
field: 'type'
}
}
}
]
},
aggregations: {
avg_price: {
rate: {
field: 'price',
unit: 'day'
}
}
}
}
}
}
)
puts response
const response = await client.search({
index: "sales",
filter_path: "aggregations",
size: 0,
aggs: {
buckets: {
composite: {
sources: [
{
month: {
date_histogram: {
field: "date",
calendar_interval: "month",
},
},
},
{
type: {
terms: {
field: "type",
},
},
},
],
},
aggs: {
avg_price: {
rate: {
field: "price",
unit: "day",
},
},
},
},
},
});
console.log(response);
GET sales/_search?filter_path=aggregations&size=0
{
"aggs": {
"buckets": {
"composite": {
"sources": [
{
"month": {
"date_histogram": {
"field": "date",
"calendar_interval": "month"
}
}
},
{
"type": {
"terms": {
"field": "type"
}
}
}
]
},
"aggs": {
"avg_price": {
"rate": {
"field": "price",
"unit": "day"
}
}
}
}
}
}
|
Composite aggregation with a date histogram source and a source for the item type. |
|
|
The date histogram source grouping monthly |
|
|
The terms source grouping for each sale item type |
|
|
Calculate sum of all sale prices, per month and item |
|
|
Convert to average daily sales per item |
The response will contain the average daily sale prices for each month per item.
{
"aggregations" : {
"buckets" : {
"after_key" : {
"month" : 1425168000000,
"type" : "t-shirt"
},
"buckets" : [
{
"key" : {
"month" : 1420070400000,
"type" : "bag"
},
"doc_count" : 1,
"avg_price" : {
"value" : 4.838709677419355
}
},
{
"key" : {
"month" : 1420070400000,
"type" : "hat"
},
"doc_count" : 1,
"avg_price" : {
"value" : 6.451612903225806
}
},
{
"key" : {
"month" : 1420070400000,
"type" : "t-shirt"
},
"doc_count" : 1,
"avg_price" : {
"value" : 6.451612903225806
}
},
{
"key" : {
"month" : 1422748800000,
"type" : "hat"
},
"doc_count" : 1,
"avg_price" : {
"value" : 1.7857142857142858
}
},
{
"key" : {
"month" : 1422748800000,
"type" : "t-shirt"
},
"doc_count" : 1,
"avg_price" : {
"value" : 0.35714285714285715
}
},
{
"key" : {
"month" : 1425168000000,
"type" : "hat"
},
"doc_count" : 1,
"avg_price" : {
"value" : 6.451612903225806
}
},
{
"key" : {
"month" : 1425168000000,
"type" : "t-shirt"
},
"doc_count" : 1,
"avg_price" : {
"value" : 5.645161290322581
}
}
]
}
}
}
By adding the mode parameter with the value value_count, we can change the calculation from sum to the number of values of the field:
resp = client.search(
index="sales",
size=0,
aggs={
"by_date": {
"date_histogram": {
"field": "date",
"calendar_interval": "month"
},
"aggs": {
"avg_number_of_sales_per_year": {
"rate": {
"field": "price",
"unit": "year",
"mode": "value_count"
}
}
}
}
},
)
print(resp)
response = client.search(
index: 'sales',
body: {
size: 0,
aggregations: {
by_date: {
date_histogram: {
field: 'date',
calendar_interval: 'month'
},
aggregations: {
avg_number_of_sales_per_year: {
rate: {
field: 'price',
unit: 'year',
mode: 'value_count'
}
}
}
}
}
}
)
puts response
const response = await client.search({
index: "sales",
size: 0,
aggs: {
by_date: {
date_histogram: {
field: "date",
calendar_interval: "month",
},
aggs: {
avg_number_of_sales_per_year: {
rate: {
field: "price",
unit: "year",
mode: "value_count",
},
},
},
},
},
});
console.log(response);
GET sales/_search
{
"size": 0,
"aggs": {
"by_date": {
"date_histogram": {
"field": "date",
"calendar_interval": "month"
},
"aggs": {
"avg_number_of_sales_per_year": {
"rate": {
"field": "price",
"unit": "year",
"mode": "value_count"
}
}
}
}
}
}
|
Histogram is grouped by month. |
|
|
Calculate number of all sale prices |
|
|
Convert to annual counts |
|
|
Changing the mode to value count |
The response will contain the average daily sale prices for each month.
{
...
"aggregations" : {
"by_date" : {
"buckets" : [
{
"key_as_string" : "2015/01/01 00:00:00",
"key" : 1420070400000,
"doc_count" : 3,
"avg_number_of_sales_per_year" : {
"value" : 36.0
}
},
{
"key_as_string" : "2015/02/01 00:00:00",
"key" : 1422748800000,
"doc_count" : 2,
"avg_number_of_sales_per_year" : {
"value" : 24.0
}
},
{
"key_as_string" : "2015/03/01 00:00:00",
"key" : 1425168000000,
"doc_count" : 2,
"avg_number_of_sales_per_year" : {
"value" : 24.0
}
}
]
}
}
}
By default sum mode is used.
-
"mode": "sum" - calculate the sum of all values field
-
"mode": "value_count" - use the number of values in the field
Relationship between bucket sizes and rate
editThe rate aggregation supports all rate that can be used calendar_intervals parameter of date_histogram
aggregation. The specified rate should compatible with the date_histogram aggregation interval, i.e. it should be possible to
convert the bucket size into the rate. By default the interval of the date_histogram is used.
-
"rate": "second" - compatible with all intervals
-
"rate": "minute" - compatible with all intervals
-
"rate": "hour" - compatible with all intervals
-
"rate": "day" - compatible with all intervals
-
"rate": "week" - compatible with all intervals
-
"rate": "month" -
compatible with only with
month,quarterandyearcalendar intervals -
"rate": "quarter" -
compatible with only with
month,quarterandyearcalendar intervals -
"rate": "year" -
compatible with only with
month,quarterandyearcalendar intervals
There is also an additional limitations if the date histogram is not a direct parent of the rate histogram. In this case both rate interval
and histogram interval have to be in the same group: [second, ` minute`, hour, day, week] or [month, quarter, year]. For
example, if the date histogram is month based, only rate intervals of month, quarter or year are supported. If the date histogram
is day based, only second, ` minute`, hour, day, and week rate intervals are supported.
Script
editIf you need to run the aggregation against values that aren’t indexed, run the aggregation on a runtime field. For example, if we need to adjust our prices before calculating rates:
resp = client.search(
index="sales",
size=0,
runtime_mappings={
"price.adjusted": {
"type": "double",
"script": {
"source": "emit(doc['price'].value * params.adjustment)",
"params": {
"adjustment": 0.9
}
}
}
},
aggs={
"by_date": {
"date_histogram": {
"field": "date",
"calendar_interval": "month"
},
"aggs": {
"avg_price": {
"rate": {
"field": "price.adjusted"
}
}
}
}
},
)
print(resp)
response = client.search(
index: 'sales',
body: {
size: 0,
runtime_mappings: {
'price.adjusted' => {
type: 'double',
script: {
source: "emit(doc['price'].value * params.adjustment)",
params: {
adjustment: 0.9
}
}
}
},
aggregations: {
by_date: {
date_histogram: {
field: 'date',
calendar_interval: 'month'
},
aggregations: {
avg_price: {
rate: {
field: 'price.adjusted'
}
}
}
}
}
}
)
puts response
const response = await client.search({
index: "sales",
size: 0,
runtime_mappings: {
"price.adjusted": {
type: "double",
script: {
source: "emit(doc['price'].value * params.adjustment)",
params: {
adjustment: 0.9,
},
},
},
},
aggs: {
by_date: {
date_histogram: {
field: "date",
calendar_interval: "month",
},
aggs: {
avg_price: {
rate: {
field: "price.adjusted",
},
},
},
},
},
});
console.log(response);
GET sales/_search
{
"size": 0,
"runtime_mappings": {
"price.adjusted": {
"type": "double",
"script": {
"source": "emit(doc['price'].value * params.adjustment)",
"params": {
"adjustment": 0.9
}
}
}
},
"aggs": {
"by_date": {
"date_histogram": {
"field": "date",
"calendar_interval": "month"
},
"aggs": {
"avg_price": {
"rate": {
"field": "price.adjusted"
}
}
}
}
}
}
{
...
"aggregations" : {
"by_date" : {
"buckets" : [
{
"key_as_string" : "2015/01/01 00:00:00",
"key" : 1420070400000,
"doc_count" : 3,
"avg_price" : {
"value" : 495.0
}
},
{
"key_as_string" : "2015/02/01 00:00:00",
"key" : 1422748800000,
"doc_count" : 2,
"avg_price" : {
"value" : 54.0
}
},
{
"key_as_string" : "2015/03/01 00:00:00",
"key" : 1425168000000,
"doc_count" : 2,
"avg_price" : {
"value" : 337.5
}
}
]
}
}
}