# How would you query Elasticsearch to get ratios?

**URL:** <https://discuss.elastic.co/t/how-would-you-query-elasticsearch-to-get-ratios/1222>\
**Category:** Elasticsearch\
**Created:** [May 24, 2015, 6:06am UTC](https://discuss.elastic.co/t/how-would-you-query-elasticsearch-to-get-ratios/1222 "2015-05-24T06:06:00Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Asimov4](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/asimov4/32/44783_2.png) [@Asimov4](https://discuss.elastic.co/u/Asimov4)\
**Post date:** [May 24, 2015, 6:06am UTC](https://discuss.elastic.co/t/how-would-you-query-elasticsearch-to-get-ratios/1222/1 "2015-05-24T06:06:01Z")

</div>

My data in Elasticsearch looks like this:

1. I have documents that track the number of visits on our website for each page:  
{  
\_index: "visit\_index"  
\_type: "visit",  
ip: "192.168.1.1",  
page: "index.html"  
}

2. I have documents that track when a video on a page is played:  
{  
\_index: "video\_played\_index"  
\_type: "video\_played",  
ip: "192.168.1.1",  
page: "index.html",  
video\_title: "my\_video"  
}

I would like to perform a query that will get me top pages in terms of engagements. In other words, I want the top pages by ratio of the numbers of video played divided by the number of visits.

This means that I am more interested in a page that got 10 visits and 9 video plays than a page that got 1000 visits and 500 video plays.

How would you do that using Elasticsearch? Will I need to update my document and index setup?

---

<div class="post-metadata">

**Author:** ![loren](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/loren/32/44942_2.png) [@loren](https://discuss.elastic.co/u/loren)\
**Post date:** [May 26, 2015, 8:23pm UTC](https://discuss.elastic.co/t/how-would-you-query-elasticsearch-to-get-ratios/1222/2 "2015-05-26T20:23:31Z")

</div>

You can use a scripted metric aggregation. I do something similar for click-thru ratios (clicks/searches) where yours is plays/visits. Something like this maybe:

```auto
GET /visitindex,videoplayed_index/visit,videoplayed/_search?search_type=count
{
  "aggs": {
    "ratio": {
      "scripted_metric": {
        "init_script": "_agg['play'] = _agg['visit'] = 0",
        "map_script": "_agg[doc['type'].value] += 1",
        "reduce_script": "plays = visits = 0; for (agg in _aggs) { plays += agg.play ; visits += agg.visit ;}; return 100 * plays / visits"

      }
    },
    "type": {
      "terms": {
        "field": "_type"
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Asimov4](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/asimov4/32/44783_2.png) [@Asimov4](https://discuss.elastic.co/u/Asimov4)\
**Post date:** [May 27, 2015, 5:58am UTC](https://discuss.elastic.co/t/how-would-you-query-elasticsearch-to-get-ratios/1222/3 "2015-05-27T05:58:02Z")

</div>

This looks great! Thanks a lot!  
How well does the scripted metric aggregation scale to massive indices? Is it still efficient over hundreds of millions of such documents?

---

<div class="post-metadata">

**Author:** ![Sarwar](https://avatars.discourse-cdn.com/v4/letter/s/7c8e57/32.png) [@Sarwar](https://discuss.elastic.co/u/Sarwar)\
**Post date:** [May 27, 2015, 5:20pm UTC](https://discuss.elastic.co/t/how-would-you-query-elasticsearch-to-get-ratios/1222/4 "2015-05-27T17:20:37Z")

</div>

This might be of interest to you in case you got dynamic scripts disabled:

[https://www.elastic.co/blog/scripting-security](https://www.elastic.co/blog/scripting-security)

As for how it performs, check how much data comes back and see if you can add a cacheable filter which improves the query and then maybe the scripts won't be so expensive.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 6, 2017, 12:11am UTC](https://discuss.elastic.co/t/how-would-you-query-elasticsearch-to-get-ratios/1222/5 "2017-07-06T00:11:30Z")

</div>