Specific sorting order using windowfunction row_number


(Kris) #1

I have a very specific requirement in sorting the results from my elastic index.
I want to order by a date (new collection) but I want to distribute the results in such a way that
not all documents with the same brand end up in the firstt page of results because they are added the most recent
For this I think I need some kind of rank over partitioning function (row_number window function in SQL).

I hope to clarify this requirement with this small example

I want to be able to execute the following two steps during query execution:

step 1:

rank over brand order by date
{"id": 1, "brand": "sony", "sortingdate": "04-12-2015"} => rank 1
{"id": 2, "brand": "sony", "sortingdate": "03-12-2015"} => rank 2
{"id": 3, "brand": "sony", "sortingdate": "02-12-2015"} => rank 3
{"id": 4, "brand": "phillips", "sortingdate": "06-12-2015"} => rank 1
{"id": 5, "brand": "phillips", "sortingdate": "02-12-2015"} => rank 2
{"id": 6, "brand": "phillips", "sortingdate": "01-12-2015"} => rank 3 or 4
{"id": 7, "brand": "phillips", "sortingdate": "01-12-2015"} => rank 3 or 4
{"id": 8, "brand": "samsung", "sortingdate": "04-12-2015"} => rank 1
{"id": 9, "brand": "samsung", "sortingdate": "01-12-2015"} => rank 2

step 2:

order by ranknumber, brand, date
{"id": 4, "brand": "phillips", "sortingdate": "06-12-2015"} => rank 1
{"id": 8, "brand": "samsung", "sortingdate": "04-12-2015"} => rank 1
{"id": 1, "brand": "sony", "sortingdate": "04-12-2015"} => rank 1
{"id": 5, "brand": "phillips", "sortingdate": "02-12-2015"} => rank 2
{"id": 9, "brand": "samsung", "sortingdate": "01-12-2015"} => rank 2
{"id": 2, "brand": "sony", "sortingdate": "03-12-2015"} => rank 2
{"id": 6, "brand": "phillips", "sortingdate": "01-12-2015"} => rank 3 or 4
{"id": 3, "brand": "sony", "sortingdate": "02-12-2015"} => rank 3
{"id": 7, "brand": "phillips", "sortingdate": "01-12-2015"} => rank 3 or 4

Is this currently possible using existing functionality in elastic or is it possible to add this through a plugin or otherwise.

Thanks in advance

Kris


#2

Hi, were you able to come at a solution? As I find myself in a similar situation.


(Kris) #3

No solution. Not working on the problem anymore also. Still interested if a solution is available though


(system) #4