Ranking of a field among the sums of an other field in Kibana

[Deltaus]

Hello everyone,

I have a very specific problem. Let me simplify it.

We have an index on exam papers, where each row has fields "student-id","exam-date" and "exam-score".

What i want is to show each student their rank among all students by exam-date relative to their total exam-score in that month/year. As a single number in metric visualization.

For example:
sid:A, exam-date:Jan 2016, exam-score:50
sid:A, exam-date:Jan 2016, exam-score:60
sid:C, exam-date:feb 2016, exam-score:100
sid:B, exam-date:Jan 2016, exam-score:80
sid:C, exam-date:Jan 2016, exam-score:20
sid:C, exam-date:Jan 2016, exam-score:10

So for the query sid:C and date:jan 2016, we would get the result "3", because the total score for C in jan 2016 is 30, for B is 80 and for A is 110.

Now, filtering by the date is easy. But i just could not write this query in terms of kibana.
One good thing is that i can define any other index i want, with the condition that it should be easy to update it each time a new exam comes. So for example i can define the following index if it will make this easier:

Sid:A, date:jan 2016, total-score:110
Sid:A, date:2016, total-score:110
Sid:C, date:jan 2016, total-score:30
Sid:C, date:2016, total-score:130
Sid:B, date:jan 2016, total-score:80

Thanks in advance.

[cjcenizal]

Hi Deltaus,

This is tricky! One of your requirements is that this should be a Metric visualization, but that's a bit of a challenge because we can only specify certain types of aggregations for this type of visualization.

The second index you described makes our task easier, but we're still limited by the ways visualizations work. They're designed to show a range of values, as opposed to the rank of a specific value among the rest.

We could present a chart that orders each student within a specific time-range, but it seems like an important requirement that we only show one student's data, and not all of them at once. Is that correct?

CJ

[thomasneirynck]

@Deltaus this is tricky indeed

I agree with @cjcenizal. If you can show ALL the students (instead of just one), or say, the top N students, you can achieve this with the Data Table visualization. You can then rank the students on the cumulative sum of their scores, for a given month. You would have two sub-aggregations, one bucket for your months, the other for your student-ids. So this would even work on your first index-mapping that keeps track of the individual exams.

If we need to show the actual "rank"-value (1,2,3,4, etc....), that isn't possible now in Kibana. From the top of my head, I am not quite sure how that query to ES would look like either. I don't think Elasticsearch supports a rank-aggregation (?)

[Deltaus]

@thomasneirynck @cjcenizal

Thank you both for answers. And yes we had to show a student only his own result.

Currently we semi-solved this by creating an index with the very specific data we want. For example:

sid:C, date:Jan2016, month-rank: 3, year-rank:1 (where the union of sid and date fields is primary field in SQL jargon)

And we simply show the "sum of month-rank" with filtering for sid:C AND date:jan2016.

Tough the problem is this index is not easily updateable with each new data, and we went some not best practice ways to update it so the main problem remains unsolved.

One possible solution could have been, for example for the above result, 1)get the sum of exam-mark(denote totalc) for student C, then 2) get the count of students where sum of exam mark>=totalc. This can be further simplified with the second index in OP. But the main problem in this approach is it requires a script working between your front-end and kibana instance, which we did not choose to have.

One interesting point as raised by @thomasneirynck is this is also not a built-in feature in ES itself, which can only be implemented by listing ALL the buckets, ordering for a field(like sum), and than counting the rank of the bucket you are interested in(like sid:C) in the response. Funnily enough the problem traces back all the way to english language, where there is no specific way to ask "how manyth" something is, which is also the reason this problem is very hard to search by googling it.