# Percentile\_ranks for values in database

**URL:** <https://discuss.elastic.co/t/percentile-ranks-for-values-in-database/128900>\
**Category:** Elasticsearch\
**Created:** [April 20, 2018, 2:42pm UTC](https://discuss.elastic.co/t/percentile-ranks-for-values-in-database/128900 "2018-04-20T14:42:45Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![debuggio](https://avatars.discourse-cdn.com/v4/letter/d/85f322/32.png) [@debuggio](https://discuss.elastic.co/u/debuggio)\
**Post date:** [April 20, 2018, 2:42pm UTC](https://discuss.elastic.co/t/percentile-ranks-for-values-in-database/128900/1 "2018-04-20T14:42:46Z")

</div>

Hi guys!

I have data that looks like this:  
{  
"user" : {  
"id: 1,  
"money": 100,  
"points": 50  
},  
.....................  
}

I'm trying to build a query with ranks for each of these columns (money and points). So I would love to have results like this:

UserId | percentile\_rank\_money | percentile\_rank\_points  
or at least  
percentile\_rank\_money | percentile\_rank\_points

In SQL world I can archive this using: " PERCENT\_RANK() over (Partition By "  
But for elastic I have to specify list of values for 'percentile\_ranks' and because I have more that 2 columns to calculate rank sending request for every single one is not a good idea.

Is it possible to archive such result?  
Thanks is advance

---

<div class="post-metadata">

**Author:** ![polyfractal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/polyfractal/32/48162_2.png) [@polyfractal](https://discuss.elastic.co/u/polyfractal)\
**Post date:** [April 27, 2018, 1:53pm UTC](https://discuss.elastic.co/t/percentile-ranks-for-values-in-database/128900/2 "2018-04-27T13:53:30Z")

</div>

I'm not sure I understand the issue. You can include both percentile ranks in the same query:

```auto
{
  "aggs": {
    "users": {
      "field": "id"
    },
    "aggs": {
      "ranks_points": {
        "percentile_ranks": {
          "field": "points"
        }
      },
      "ranks_money": {
        "percentile_ranks": {
          "field": "money"
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![debuggio](https://avatars.discourse-cdn.com/v4/letter/d/85f322/32.png) [@debuggio](https://discuss.elastic.co/u/debuggio)\
**Post date:** [April 28, 2018, 8:39am UTC](https://discuss.elastic.co/t/percentile-ranks-for-values-in-database/128900/3 "2018-04-28T08:39:09Z")

</div>

Hi Zachary,  
Thanks for your reply.  
The problem is, that if I use `percentile_ranks` I get an error: `Required [values]`, so AFAIK I have to specify list of values, that I don't know without reading all of them from elastic at first

---

<div class="post-metadata">

**Author:** ![polyfractal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/polyfractal/32/48162_2.png) [@polyfractal](https://discuss.elastic.co/u/polyfractal)\
**Post date:** [April 30, 2018, 3:01pm UTC](https://discuss.elastic.co/t/percentile-ranks-for-values-in-database/128900/4 "2018-04-30T15:01:17Z")

</div>

Sorry, I'm still a bit confused.

You have to specify `values` because that's what the `percentile_ranks` aggregation does: it tells you the rank of specific values that you care about.

If you just want to know the `90th` percentile and find out what the value is at that point, you should use the `percentile` aggregation instead.

Basically, `percentile_ranks` is for asking what the rank of specific, known values is. `percentiles` is for asking what value is greater than `n` percent of the data.

---

<div class="post-metadata">

**Author:** ![debuggio](https://avatars.discourse-cdn.com/v4/letter/d/85f322/32.png) [@debuggio](https://discuss.elastic.co/u/debuggio)\
**Post date:** [May 3, 2018, 11:44am UTC](https://discuss.elastic.co/t/percentile-ranks-for-values-in-database/128900/5 "2018-05-03T11:44:57Z")

</div>

What can I do if I don't know the value?  
I'm trying to find something similar to SQL query:  
`SELECT UserId, (PERCENT_RANK() OVER (Partition By UserTypeId ORDER BY money desc) AS MoneyRank`  
`FROM blablabla`  
`WHERE blablabla`

So, I'm interested in percentile\_ranks for all users that satisfies some filter expression and I have no idea about their values.  
For now I found only 1 workaround for Elastic is to read all users that satisfies filter expression, then grab all their `Money` field values and use them to get `percentile_ranks` and then match these ranks with what I got previously. It' a bit complicated on my point of view in comparison to 1 SQL query, but for now I don't have better solution

---

<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:** [May 31, 2018, 11:45am UTC](https://discuss.elastic.co/t/percentile-ranks-for-values-in-database/128900/6 "2018-05-31T11:45:00Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.