Elasticsearchで期間ごとの集計値を出す
elasticsearchBucket(SQLでいうGROUP BY)にまとめて(Bucket Aggreagtion)、集計(Metric Aggregation)する。
使うデータは作ったツールで生成したこんなの。
{"@timestamp":1488635130,"os_name":"linux","score":82}
Bucket Aggregations
Date Range Aggregation
date_rangeで期間のBucketを作る。この例だと今から10分前の00秒~今の分の00秒まで。
$ curl localhost:9200/hoge/_search -d'
{
"aggs": {
"range_10minutes": {
"date_range": {
"field": "@timestamp",
"format": "HH-mm-ssZ",
"ranges": [
{ "to": "now/m", "from": "now-10m/m" }
]
}
}
}
}' | jq .aggregations
{
"range_10minutes": {
"buckets": [
{
"key": "15-17+0000-15-27+0000",
"from": 1488640620000,
"from_as_string": "15-17+0000",
"to": 1488641220000,
"to_as_string": "15-27+0000",
"doc_count": 600
}
]
}
}
Date Histogram Aggregation
date_histogramで日付の間隔でBucketを作る。この例だと1分ごとにBucketが作られる。
$ curl localhost:9200/hoge/_search -d'
{
"aggs": {
"histogram_1minute": {
"date_histogram": {
"field": "@timestamp",
"interval": "1m"
}
}
}
}' | jq .aggregations
{
"histogram_1minute": {
"buckets": [
...
{
"key_as_string": "1488640560",
"key": 1488640560000,
"doc_count": 60
},
{
"key_as_string": "1488640620",
"key": 1488640620000,
"doc_count": 31
}
]
}
}
Terms Aggregation
termsで値ごとにBucketを作る。
$ curl localhost:9200/hoge/_search -d'
{
"aggs": {
"os_names":{
"terms": { "field": "os_name" }
}
}
}' | jq .aggregations
{
"os_names": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "windows",
"doc_count": 458
},
{
"key": "android",
"doc_count": 456
},
{
"key": "mac",
"doc_count": 455
},
{
"key": "linux",
"doc_count": 447
},
{
"key": "ios",
"doc_count": 404
}
]
}
}
Metrics Aggregations
Avg Aggregation
avgで平均を出す。max、min、sumも同様。
$ curl localhost:9200/hoge/_search -d '
{
"aggs": {
"avg_score": { "avg": { "field" : "score" } }
}
}' | jq .aggregations
{
"avg_score": {
"value": 50.34639639639639
}
}
集計する
aggsを組み合わせて集計する。
$ curl localhost:9200/hoge/_search -d'
{
"aggs": {
"range": {
"date_range": {
"field": "@timestamp",
"format": "HH-mm-ssZ",
"ranges": [
{ "to": "now/m", "from": "now-10m/m" }
]
},
"aggs": {
"histogram": {
"date_histogram": {
"field": "@timestamp",
"interval": "1m"
},
"aggs": {
"os_names":{
"terms": { "field" : "os_name" },
"aggs": {
"avg_score": { "avg": { "field": "score" }}
}
}
}
}
}
}
}
}' | jq .aggregations
{
"range": {
"buckets": [
{
"key": "15-55-00+0000-16-05-00+0000",
"from": 1488642900000,
"from_as_string": "15-55-00+0000",
"to": 1488643500000,
"to_as_string": "16-05-00+0000",
"doc_count": 25,
"histogram": {
"buckets": [
{
"key_as_string": "1488643440",
"key": 1488643440000,
"doc_count": 25,
"os_names": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "linux",
"doc_count": 9,
"avg_score": {
"value": 41.44444444444444
}
},
{
"key": "ios",
"doc_count": 5,
"avg_score": {
"value": 63.6
}
},
{
"key": "mac",
"doc_count": 5,
"avg_score": {
"value": 53.6
}
},
{
"key": "android",
"doc_count": 4,
"avg_score": {
"value": 41.25
}
},
{
"key": "windows",
"doc_count": 2,
"avg_score": {
"value": 67
}
}
]
}
}
]
}
}
]
}
}