duanhu2414
2015-10-15 09:40
浏览 168
已采纳

多个指标Elasticsearch上的相同聚合

I have setup snowplow with Elasticsearch.

When I want to get the data out I just do normal queries and use aggregates to get them by day, country etc.

So I want to figure out clickthru rate for these aggregations, I have 2 kind of events: page views and clicks.

Currently I do 2 queries:

Page Views:

{
    "size": 0,
    "query": {
        "filtered": {
            "filter": {
                "bool": {
                    "must": [
                        {
                            "term": {
                                "event": "page_view"
                            }
                        }
                    ],
                    "must_not": {
                        "term": {
                            "br_family": "Robot"
                        }
                    }
                }
            }
        }
    },
    "aggs": {
        "dates": {
            "date_histogram": {
                "field": "collector_tstamp",
                "interval": "day"
            }
        }
    }
}

Clicks:

{
    "size": 0,
    "query": {
        "filtered": {
            "filter": {
                "bool": {
                    "must": [
                        {
                            "term": {
                                "event": "struct"
                            }
                        },
                        {
                            "term": {
                                "se_action": "click"
                            }
                        }
                    ],
                    "must_not": {
                        "term": {
                            "br_family": "Robot"
                        }
                    }
                }
            }
        }
    },
    "aggs": {
        "dates": {
            "date_histogram": {
                "field": "collector_tstamp",
                "interval": "day"
            }
        }
    }
}

I format the response to something easier to use and then merge them in PHP using something like this.

function merge_metrics($pv,$c){
    $r = array();

    if(count($pv) > 0){
        foreach ($pv as $key => $value) {
            $r[$value['name']]['page_views'] += $value['count']; 
        }
    }
    if(count($c) > 0){
        foreach ($c as $key => $value) {
            $r[$value['name']]['clicks'] += $value['count']; 
        }
    }

    $rf = array();

    foreach ($r as $key => $value) {
        $tmp_clicks = isset($value['clicks']) ? $value['clicks'] : 0;
        $tmp_page_views = isset($value['page_views']) ? isset($value['page_views']) : 0;
        $rf[] = array(
                'name' => $key,
                'page_views' => $tmp_page_views,
                'clicks' => $tmp_clicks,
                'ctr' => ctr($tmp_clicks,$tmp_page_views)
            ); 
    }

    return $rf;
}

Both $pv and $c are arrays that contain the aggregates that result from querying Elasticsearch and I do some formatting for ease of use.

My question is:

Is it possible get multiple metrics(in my case page views and clicks, these are specific filters) and perform same aggregations on both ? then returning the aggregations something like :

{
    "data": [
        {
            "day": "2015-10-13",
            "page_views": 61,
            "clicks": 0,
        },
        {
            "day": "2015-10-14",
            "page_views": 135,
            "clicks": 1,
        },
        {
            "day": "2015-10-15",
            "page_views": 39,
            "clicks": 0,
        }
    ]
}

But without me having to manually merge them ?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • duanjiong2021 2015-10-15 11:12
    已采纳

    Yes, it is definitely possible if you merge your aggregations into one single query. For instance, I suppose you have one query like this for page views:

    {
        "query": {...}
        "aggregations": {
            "by_day": {
                "date_histogram": {
                    "field": "day",
                    "interval": "day"
                },
                "aggs": {
                    "page_views_per_day": {
                        "sum": {
                            "field": "page_views"
                        }
                    }
                }
            }
        }
    }
    

    And another query like this for clicks:

    {
        "query": {...}
        "aggregations": {
            "by_day": {
                "date_histogram": {
                    "field": "day",
                    "interval": "day"
                },
                "aggs": {
                    "clicks_per_day": {
                        "sum": {
                            "field": "clicks"
                        }
                    }
                }
            }
        }
    }
    

    Provided you have the same constraints in your query, you can definitely merge them together at the date_histogram level, like this:

    {
        "query": {...}
        "aggregations": {
            "by_day": {
                "date_histogram": {
                    "field": "day",
                    "interval": "day"
                },
                "aggs": {
                    "page_views_per_day": {
                        "sum": {
                            "field": "page_views"
                        }
                    },
                    "clicks_per_day": {
                        "sum": {
                            "field": "clicks"
                        }
                    }
                }
            }
        }
    }
    

    UPDATE

    Since your queries are different for each of your aggregations, we need to do it slightly differently, i.e. by using an additional filters aggregation, like this:

    {
      "size": 0,
      "query": {
        "filtered": {
          "filter": {
            "bool": {
              "must": [
                {
                  "terms": {
                    "event": [
                      "page_view",
                      "struct"
                    ]
                  }
                }
              ],
              "should": {
                "term": {
                  "se_action": "click"
                }
              },
              "must_not": {
                "term": {
                  "br_family": "Robot"
                }
              }
            }
          }
        }
      },
      "aggs": {
        "dates": {
          "date_histogram": {
            "field": "collector_tstamp",
            "interval": "day"
          },
          "aggs": {
            "my_filters": {
              "filters": {
                "filters": {
                  "page_views_filter": {
                    "bool": {
                      "must": [
                        {
                          "term": {
                            "event": "page_view"
                          }
                        }
                      ],
                      "must_not": {
                        "term": {
                          "br_family": "Robot"
                        }
                      }
                    }
                  },
                  "clicks_filter": {
                    "bool": {
                      "must": [
                        {
                          "term": {
                            "event": "struct"
                          }
                        },
                        {
                          "term": {
                            "se_action": "click"
                          }
                        }
                      ],
                      "must_not": {
                        "term": {
                          "br_family": "Robot"
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
    

    Now for each daily bucket, you're going to end up with two sub-buckets, one for the count of page views and another for the count of clicks.

    已采纳该答案
    打赏 评论

相关推荐 更多相似问题