python - Elasticsearch，如何每天每小时获取订单

Question

我有包含ubereats订单数据的文件。我想知道每天每小时的平均订单。该文件包含52周内的订单数据。我设法获得了每个星期一，星期二等每小时的数据。但是还没有设法得到星​​期一，星期二等每小时的平均订单。

search_body = {
    "size": 0,
    "aggs": {
        "total_orders_per_day_of_week": {
            "terms": {
                "script": {
                    "lang": "painless",
                    "source": "doc['dateTime'].value.dayOfWeek"
                }
            },
        "aggs": {
            "countPerHour": {
                "terms": {
                    "size": 24,
                    "script": "return doc['dateTime'].value.getHour();"
                }
            }
         }
      }
    }
}

也许我需要做一些类似的事情来计算每天的平均订单量？

search_body = {
    "size" : 0,
    "aggs" : {
        "orders_per_day" : {
            "terms" : {
                "script" : {
                    "lang": "painless",
                    "source": "doc['dateTime'].value.dayOfWeek"
                }
            },
            "aggs" : {
                "numberOfOccurrences" : {
                    "cardinality": {
                        "script" : {
                            "lang": "painless",
                            "source": "doc['dateTime'].value.dayOfYear"
                        }
                    }
                }
            },
        }
    }
}

for day in result["aggregations"]["orders_per_day"]["buckets"]:
    print (day["key"], day["doc_count"], day["numberOfOccurrences"]["value"],
        day["doc_count"]/day["numberOfOccurrences"]["value"])

Answer 1

如果我这次没看错的话，您希望获得星期一，星期二每个小时的平均值...因此应该有24x7的结果。如果是这样，请执行以下操作:

{
  "size": 0,
  "aggs": {
    "orders_per_hour_of_day": {
      "terms": {
        "script" : {
            "lang": "painless",
            "source": "doc['dateTime'].value.getHour()"
        },
        "size": 24
      },
      "aggs": {
        "total_orders_per_day_of_week": {
          "terms": {
            "script": {
              "lang": "painless",
              "source": "doc['dateTime'].value.getDayOfWeekEnum()"
            }
          },
          "aggs": {
            "number_of_weeks": {
              "date_histogram": {
                "field": "dateTime",
                "calendar_interval": "week"
              }
            },
            "average_orders_per_hour_day_of_week": {
              "bucket_script": {
                "buckets_path": {
                  "doc_count": "_count",
                  "number_of_weeks": "number_of_weeks._bucket_count"
                },
                "script": "params.doc_count / params.number_of_weeks"
              }
            }
          }
        }
      }
    }
  }
}

从此处改编的代码: https://discuss.elastic.co/t/average-per-day-of-week-aggregation/124132

我不确定我是否清楚地理解了您的问题。听起来您想获得星期一，星期二，...星期日的平均订单。如果这是问题，您可以将每个存储分区分配24小时。

{
  "size": 0,
  "aggs": {
    "orders_per_day": {
      "terms": {
        "script": {
          "lang": "painless",
          "source": "doc['dateTime'].value.dayOfWeek"
        }
      },
      "aggs": {
        "avg_per_hour": {
          "bucket_script": {
            "buckets_path": {
              "count": "_count"
            },
            "script": "params.count / 24"
          }
        }
      }
    }
  }
}

存储桶聚合:
https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-script-aggregation.html
值区doc_count的路径:
https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline.html#_special_paths