elasticsearch - 在 Elasticsearch 中生成数据表

Question

我正在尝试制作包含一些计算的数据表

******************************************************
** Bidder * Request * CPM * Revenue * Response Time **
******************************************************

我创建了一个包含所有数据的索引，因此我的数据以以下格式存储:

{
  "data": {
    "took": 1,
    "timed_out": false,
    "_shards": {
      "total": 5,
      "successful": 5,
      "skipped": 0,
      "failed": 0
    },
    "hits": {
      "total": {
        "value": 78,
        "relation": "eq"
      },
      "max_score": 1,
      "hits": [
        {
          "_index": "nits_media_bid_won",
          "_type": "nits_media_data_collection",
          "_id": "MIyt6m8BWa2IbVphmPUh",
          "_score": 1,
          "_source": {
            "bidderCode": "appnexus",
            "width": 300,
            "height": 600,
            "statusMessage": "Bid available",
            "adId": "43d59b34fd61b5",
            "requestId": "2c6d19dcc536c3",
            "mediaType": "banner",
            "source": "client",
            "cpm": 0.5,
            "creativeId": 98493581,
            "currency": "USD",
            "netRevenue": true,
            "ttl": 300,
            "adUnitCode": "/19968336/header-bid-tag-0",
            "appnexus": {
              "buyerMemberId": 9325
            },
            "meta": {
              "advertiserId": 2529885
            },
            "originalCpm": 0.5,
            "originalCurrency": "USD",
            "auctionId": "a628c0c0-bd4d-4f2a-9011-82fab780910e",
            "responseTimestamp": 1580190231422,
            "requestTimestamp": 1580190231022,
            "bidder": "appnexus",
            "timeToRespond": 400,
            "pbLg": "0.50",
            "pbMg": "0.50",
            "pbHg": "0.50",
            "pbAg": "0.50",
            "pbDg": "0.50",
            "pbCg": null,
            "size": "300x600",
            "adserverTargeting": {
              "hb_bidder": "appnexus",
              "hb_adid": "43d59b34fd61b5",
              "hb_pb": "0.50",
              "hb_size": "300x600",
              "hb_source": "client",
              "hb_format": "banner"
            },
            "status": "rendered",
            "params": [
              {
                "placementId": 13144370
              }
            ],
            "nits_account": "asjdfagsd2384vasgd19",
            "nits_url": "http://nitsmedia.local/run-ad",
            "session_id": "YTGpETKSk2nHwLRB6GbP",
            "timestamp": "2020-01-28T05:43:51.702Z",
            "geo_data": {
              "continent": "North America",
              "address_format": "{{recipient}}\n{{street}}\n{{city}} {{region_short}} {{postalcode}}\n{{country}}",
              "alpha2": "US",
              "alpha3": "USA",
              "country_code": "1",
              "international_prefix": "011",
              "ioc": "USA",
              "gec": "US",
              "name": "United States of America",
              "national_destination_code_lengths": [
                3
              ],
              "national_number_lengths": [
                10
              ],
              "national_prefix": "1",
              "number": "840",
              "region": "Americas",
              "subregion": "Northern America",
              "world_region": "AMER",
              "un_locode": "US",
              "nationality": "American",
              "postal_code": true,
              "unofficial_names": [
                "United States",
                "Vereinigte Staaten von Amerika",
                "États-Unis",
                "Estados Unidos",
                "アメリカ合衆国",
                "Verenigde Staten"
              ],
              "languages_official": [
                "en"
              ],
              "languages_spoken": [
                "en"
              ],
              "geo": {
                "latitude": 37.09024000000000143018041853792965412139892578125,
                "latitude_dec": "39.44325637817383",
                "longitude": -95.7128909999999990532160154543817043304443359375,
                "longitude_dec": "-98.95733642578125",
                "max_latitude": 71.5388001000000031126546673476696014404296875,
                "max_longitude": -66.8854170000000038953658076934516429901123046875,
                "min_latitude": 18.77629999999999910187398199923336505889892578125,
                "min_longitude": 170.595699999999993679011822678148746490478515625,
                "bounds": {
                  "northeast": {
                    "lat": 71.5388001000000031126546673476696014404296875,
                    "lng": -66.8854170000000038953658076934516429901123046875
                  },
                  "southwest": {
                    "lat": 18.77629999999999910187398199923336505889892578125,
                    "lng": 170.595699999999993679011822678148746490478515625
                  }
                }
              },
              "currency_code": "USD",
              "start_of_week": "sunday"
            }
          }
        },

          //Remaining data set....
      ]
    },
  }
}

因此，根据我的数据集，我想获取所有唯一的bidderCode(将在表格中以Bidder表示)，并通过相应的计算来制作数据。例如

请求-这将是聚合

中的文档总数

CPM-CPM将是所有CPM的总和除以1000

收入-CPM总数乘以1000

响应时间-(responseTimestamp-requestTimestamp)

的平均值

我怎么能做到这一点，我对此有些困惑。我尝试通过以下方式构建块:

return $this->elasticsearch->search([
    'index' => 'nits_media_bid_won',
    'body' => [
        'query' => $query,
        'aggs' => [
            'unique_bidders' => [
                'terms' => ['field' => 'bidderCode.keyword']
            ],
            'aggs' => [
                'sum' => [
                    'cpm' => [
                        'field' => 'cpm',
                        'script' => '_value / 1000'
                    ]
                ]
            ],
        ]
    ]
]);

但这向我显示了错误:

{
   "error":{
      "root_cause":[
         {
            "type":"x_content_parse_exception",
            "reason":"[1:112] [sum] unknown field [cpm], parser not found"
         }
      ],
      "type":"x_content_parse_exception",
      "reason":"[1:112] [sum] unknown field [cpm], parser not found"
   },
   "status":400
}

我是新手，请帮忙。谢谢。

Answer 1

ElasticSearch没错-您已经用其类型交换了聚合名称。它不能parse agg类型cpm。

这是更正后的查询:

GET nits_media_bid_won/_search
{
  "size": 0,
  "aggs": {
    "unique_bidders": {
      "terms": {
        "field": "bidderCode.keyword",
        "size": 10
      },
      "aggs": {
        "cpm": {            <----------
          "sum": {          <----------
            "field": "cpm",
            "script": "_value / 1000"
          }
        }
      }
    }
  }
}