Query score based on aggregated values


(Kevin Pearson) #1

I am wondering if there is a way to use aggregated values inside a query.

Example:
Say our data contains items and their price:
{
"id" : string
"name" : string
"price" : float
}

I want to do a query that returns the top items that have a price far from
the average price of items with the same name.

Example Data:
ID | Name | Price
1 | Chair | 5.99
2 | Chair | 5.99
3 | Chair | 59.99
4 | Desk | 61.00
5 | Desk | 60.00
6 | Desk | 59.99

The top response would be ID 3, since 59.99 is way higher than the average
price for a chair.

I believe I need to write a custom score script, but I am not sure how I
can get a reference to the average of items with the same name.

Thank you,
Kevin

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/8069763d-9ce2-4dfc-afc5-6293c2171828%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.


(Alexander Reelsen) #2

Hey,

you could execute a query, sorted by price and in the same request execute
a statistical facet for the price field and then check in your client for
each hit being returned, if it is above the average value returned by the
statistical facet.

You could also do this in two roundtrips, getting the statistical average
from the facet first and then executing a second query filtering only for
products with a price higher than the average.

--Alex

On Fri, Jan 17, 2014 at 12:43 AM, Kevin Pearson
kevin.pearson.x@gmail.comwrote:

I am wondering if there is a way to use aggregated values inside a query.

Example:
Say our data contains items and their price:
{
"id" : string
"name" : string
"price" : float
}

I want to do a query that returns the top items that have a price far from
the average price of items with the same name.

Example Data:
ID | Name | Price
1 | Chair | 5.99
2 | Chair | 5.99
3 | Chair | 59.99
4 | Desk | 61.00
5 | Desk | 60.00
6 | Desk | 59.99

The top response would be ID 3, since 59.99 is way higher than the average
price for a chair.

I believe I need to write a custom score script, but I am not sure how I
can get a reference to the average of items with the same name.

Thank you,
Kevin

--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/8069763d-9ce2-4dfc-afc5-6293c2171828%40googlegroups.com
.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAGCwEM8aj-g0sbxSTtbueAh8rG7FgaGOzovVEuK7DwLbzwXjbg%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.


(Kevin Pearson) #3

Thank you Alexander. The first possibility is what we ended up doing for
now. We plan on looking into the aggregations framework as a replacement
once we upgrade to elasticsearch 1.0 so we don't need to return thousands
of documents to the client.

Thank you.

On Monday, January 20, 2014 1:19:08 AM UTC-8, Alexander Reelsen wrote:

Hey,

you could execute a query, sorted by price and in the same request execute
a statistical facet for the price field and then check in your client for
each hit being returned, if it is above the average value returned by the
statistical facet.

You could also do this in two roundtrips, getting the statistical average
from the facet first and then executing a second query filtering only for
products with a price higher than the average.

--Alex

On Fri, Jan 17, 2014 at 12:43 AM, Kevin Pearson <kevin.p...@gmail.com<javascript:>

wrote:

I am wondering if there is a way to use aggregated values inside a query.

Example:
Say our data contains items and their price:
{
"id" : string
"name" : string
"price" : float
}

I want to do a query that returns the top items that have a price far
from the average price of items with the same name.

Example Data:
ID | Name | Price
1 | Chair | 5.99
2 | Chair | 5.99
3 | Chair | 59.99
4 | Desk | 61.00
5 | Desk | 60.00
6 | Desk | 59.99

The top response would be ID 3, since 59.99 is way higher than the
average price for a chair.

I believe I need to write a custom score script, but I am not sure how I
can get a reference to the average of items with the same name.

Thank you,
Kevin

--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to elasticsearc...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/8069763d-9ce2-4dfc-afc5-6293c2171828%40googlegroups.com
.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/769a6c3d-749d-4311-904d-7f51a37f648d%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.


(system) #4