Elasticsearchで期間ごとの集計値を出す

(2017-03-05)

Bucket(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で平均を出す。maxminsumも同様。

$ 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
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    ]
  }
}