elasticsearch - 如何在弹性搜索中按小时对文档进行分组？

Question

我有一个应用程序，用户每天都会询问一项调查，我希望获得平均回答时间。我尝试了一些请求，但我无法按小时对所有文档进行分组，它是按天按小时分组的。

我这样做：

{
 "aggs": {
      "byHour": {
          "date_histogram": {
              "field": "date",
              "interval": "hour",
              "format" : "H"

                }
            }
        }
    }
}

它按小时包装，但也按日期包装，我希望day被忽略。

 [
        {
          "key_as_string": "0",
          "key": 1533945600000,
          "doc_count": 40,
          "group_by_state": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": []
          }
        },
        {
          "key_as_string": "1",
          "key": 1533949200000,
          "doc_count": 345,
          "group_by_state": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": []
          }
        },
        {
          "key_as_string": "23",
          "key": 1534028400000,
          "doc_count": 15,
          "group_by_state": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": []
          }
        },
        {
          "key_as_string": "0",
          "key": 1534032000000,
          "doc_count": 0,
          "group_by_state": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": []
          }
        },
        {
          "key_as_string": "1",
          "key": 1534035600000,
          "doc_count": 2,
          "group_by_state": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": []
          }
        },
        {
          "key_as_string": "2",
          "key": 1534039200000,
          "doc_count": 3,
          "group_by_state": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": []
          }
        }
 ]

类型映射

{
  "myIndex": {
    "mappings": {
      "answer": {
        "properties": {
          "date": {
            "type": "date"
          },
          "lang": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "level": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "offset": {
            "type": "long"
          },
          "patientCaretrackId": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "protocolId": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "query": {
            "properties": {
              "constant_score": {
                "properties": {
                  "filter": {
                    "properties": {
                      "bool": {
                        "properties": {
                          "must": {
                            "properties": {
                              "term": {
                                "properties": {
                                  "questionId": {
                                    "type": "text",
                                    "fields": {
                                      "keyword": {
                                        "type": "keyword",
                                        "ignore_above": 256
                                      }
                                    }
                                  },
                                  "questionnaireId": {
                                    "type": "text",
                                    "fields": {
                                      "keyword": {
                                        "type": "keyword",
                                        "ignore_above": 256
                                      }
                                    }
                                  }
                                }
                              }
                            }
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          },
          "questionId": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "questionnaireId": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "surgeonId": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "value": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            },
            "fielddata": true
          }
        }
      }
    }
  }
}

文件示例：

[
{
          "date": "2018-09-11T00:00:00.000Z",
          "lang": "fr",
          "level": "red",
          "offset": 21,
          "patientCaretrackId": "5b894b10a9f7afec73762113",
          "protocolId": "ptg-koos-long-v1",
          "questionnaireId": "j21",
          "surgeonId": "699362de-f040-4799-b1ea-53f5b4a2fe03",
          "value": "permanentes",
          "questionId": "frequence-douleur-2"
},
{
          "date": "2018-09-11T00:00:00.000Z",
          "lang": "fr",
          "level": "red",
          "offset": 21,
          "patientCaretrackId": "5b894b10a9f7afec73762113",
          "protocolId": "ptg-koos-long-v1",
          "questionnaireId": "j21",
          "surgeonId": "699362de-f040-4799-b1ea-53f5b4a2fe03",
          "value": "permanentes",
          "questionId": "frequence-douleur-2"
        }
]

可以通过查询 Elasticsearch 来做到这一点吗？

谢谢，

标签： elasticsearchelasticsearch-aggregationdate-histogram

Answer 1

您可以将terms聚合与脚本一起使用：

https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-script

GET /_search
{
    "aggs" : {
        "hours" : {
            "terms" : {
                "script" : {
                    "source": "doc['date'].getHour()",
                    "lang": "painless"
                }
            }
        }
    }
}

（只是给你一个想法，完全不确定脚本本身..）