一天中从文档中计算出的不同小时数

Question

我有时间戳字段(：mm:ss).我想数一下每个设备id在给定的一天中有多少个不同的时间。在基巴纳怎么做？

这是我的真实档案。

例如，https://drive.google.com/open?id=1JRZj8myVu1UHJ3jxZzzb8LSKKMicY0Qi。

 Doc1=
 {
   name = "heart_beat"
   date= "2019-06-05 T 12:10:00"
   "device_id" = "abc"
 }
 Doc2=
 {
    name="heart_beat"
    date="2019-06-05 T 09:10:00"
    "device_id" = "xyz"
  }
  Doc3=
  {
    name="heart_beat"
    date="2019-06-05 T 12:15:00"
    "device_id" = "pqr"
  }
  Doc4=
  {
     name="heart_beat"
     date="2019-06-05 T 07:10:00"
     "device_id" = "def"
  }
  Doc5=
 {
    name="heart_beat"
    date="2019-06-07 T 09:10:00"
    "device_id" = "xyz"
  }
  Doc6=
  {
    name="heart_beat"
    date="2019-06-07 T 12:15:00"
    "device_id" = "pqr"
  }

答案应该是

1. "2019-06-05“装置-”abc“小时-1
2. "2019-06-05“装置-”pqr“小时-1
3. "2019-06-05“装置-”xyz“小时-1
4. "2019-06-05“装置-”def“小时-1
5. "2019-06-07“装置-”xyz“小时-1
6. "2019-06-07“装置-”pqr“小时-1

Answer 1

我正在使用脚本来获得不同的时间。查询有2个聚合。术语聚合--给出所有不同的小时和其中文档的计数。2.基数聚合--提供不同小时的计数。

您可以根据您的要求保留其中之一或两者。

制图：

PUT testindex4/_mapping
{
  "properties": {
    "name":{
      "type":"text"
    }, 
    "date":
    {
      "type":"date",
      "format":"YYYY-MM-DD'T'HH:mm:ss"
    }
  }
}

查询：

GET testindex4/_search
{
  "size":0,
  "aggs": {
    "byHours": {    ---> if you need doc count in distinct hours
      "terms": {
        "script": {
          "source": "doc[params.date_field].value.hourOfDay;",
          "params": {
            "date_field": "date"
          }
        }
      }
    },
    "distinct_hours": {      ---> count of distinct hours
      "cardinality": {
        "script": {
          "lang": "painless",
          "source": "doc[params.date_field].value.hourOfDay;",
          "params": {
            "date_field": "date"
          }
        }
      }
    }
  }
}

全数据

    "hits" : [
      {
        "_index" : "testindex4",
        "_type" : "_doc",
        "_id" : "ZjyHc2sB36IvcvW_wPNu",
        "_score" : 1.0,
        "_source" : {
          "name" : "heart_beat",
          "date" : "2019-06-05T12:10:00"
        }
      },
      {
        "_index" : "testindex4",
        "_type" : "_doc",
        "_id" : "ZzyIc2sB36IvcvW_DPMn",
        "_score" : 1.0,
        "_source" : {
          "name" : "heart_beat",
          "date" : "2019-06-05T12:15:00"
        }
      },
      {
        "_index" : "testindex4",
        "_type" : "_doc",
        "_id" : "ajyPc2sB36IvcvW_0fOc",
        "_score" : 1.0,
        "_source" : {
          "name" : "heart_beat",
          "date" : "2019-06-05T01:15:00"
        }
      }
    ]

结果：

"aggregations" : {
    "distinct_hours" : {
      "value" : 2
    },
    "byHours" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "12",
          "doc_count" : 2
        },
        {
          "key" : "1",
          "doc_count" : 1
        }
      ]
    }
  }

编辑:用于获取每个设备映射的记录

PUT testindex4/_mapping
{
  "properties": {
    "device": {
      "properties": {
        "make": {
           "type": "text"
        },
        "model":{
           "type": "keyword"
        }
      }
    },
    "date": {
      "type": "date",
      "format": "yyyy-MM-dd HH:mm:ss"
    }
  }
}

查询

GET testindex4/_search
{
  "size": 0,
  "aggs": {
    "byDevice": {
      "terms": {
        "field": "device.model"
      },
      "aggs": {
        "byDate": {
          "terms": {
            "script": {
              "source": "doc[params.date_field].value.toString('yyyy-MM-dd')",
              "params": {
                "date_field": "date"
              }
            }
          },
          "aggs": {
            "byHours": {
              "terms": {
                "script": {
                  "source": "doc[params.date_field].value.getHour();",
                  "params": {
                    "date_field": "date"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

结果

  "aggregations" : {
    "byDevice" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "VFD 720",
          "doc_count" : 3,
          "byDate" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "2019-06-06",
                "doc_count" : 2,
                "byHours" : {
                  "doc_count_error_upper_bound" : 0,
                  "sum_other_doc_count" : 0,
                  "buckets" : [
                    {
                      "key" : "12",
                      "doc_count" : 1
                    },
                    {
                      "key" : "15",
                      "doc_count" : 1
                    }
                  ]
                }
              },
              {
                "key" : "2019-06-05",
                "doc_count" : 1,
                "byHours" : {
                  "doc_count_error_upper_bound" : 0,
                  "sum_other_doc_count" : 0,
                  "buckets" : [
                    {
                      "key" : "12",
                      "doc_count" : 1
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "key" : "VFD 721",
          "doc_count" : 2,
          "byDate" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "2019-06-05",
                "doc_count" : 1,
                "byHours" : {
                  "doc_count_error_upper_bound" : 0,
                  "sum_other_doc_count" : 0,
                  "buckets" : [
                    {
                      "key" : "12",
                      "doc_count" : 1
                    }
                  ]
                }
              },
              {
                "key" : "2019-06-06",
                "doc_count" : 1,
                "byHours" : {
                  "doc_count_error_upper_bound" : 0,
                  "sum_other_doc_count" : 0,
                  "buckets" : [
                    {
                      "key" : "12",
                      "doc_count" : 1
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }