Question about the term_stats facet

Ben_2 · June 18, 2013, 7:47am

I am getting started with ES and have a hard time understanding how to
build a query in ES to get a result set that is similar to an SQL result
set, i.e. get more fields from my documents within the facet result, like a
grid, or table, which I can then loop over in the view.

My data is similar to this:

    { order_placed:   "2013-05-13 16:43",
      product_id:     100,
      sales_qty:      2,
      payment_method: "creditcard",
      country:        "US",
      retailer:       "ABC Store"
    },
    { order_placed:   "2013-05-14 11:24",
      product_id:     203,
      sales_qty:      1,
      payment_method: "cash",
      country:        "DE",
      retailer:       "XYZ Store"
    },
    { order_placed:   "2013-05-14 18:10",
      product_id:     138,
      sales_qty:      4,
      payment_method: "unknown",
      country:        "JP",
      retailer:       "NPN Store"
    } ... etc.

How do I do this in ES?

SELECT sum(sales_qty),country
FROM sales
WHERE product_id = 183 AND retailer = 'NPN Store'
GROUP BY country
ORDER BY sum(sales_qty) DESC;

The sum(sales_qty) should be limited to the product_id 183. I think what I
need is the term_stats facet, sort by reverse-max, and facet filter by
product_id = 183 and retailer = 'NPN Store' - I'm not sure how to put it
all together.

Basically I want to use ES like a data cube, if that makes any sense.

Pointers to documentation would be fine, as I am currently not sure what to
look for.

--
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.
For more options, visit https://groups.google.com/groups/opt_out.

dadoonet · June 18, 2013, 8:06am

Somehow, you want to compute on orders. So basically, you have to index orders in separate documents.
Then run a Query, using Query DSL, filtering by retailer and product_id using a BoolFilter (you will need not to analyze retailer field, set index to not_analyzed).

Add a Terms Stats facet on field country for the key_field and sales_qty for the value_field.

Does it help?

--
David Pilato | Technical Advocate | Elasticsearch.com
@dadoonet | @elasticsearchfr | @scrutmydocs

Le 18 juin 2013 à 09:47, Ben tonkatsufan@gmail.com a écrit :

I am getting started with ES and have a hard time understanding how to build a query in ES to get a result set that is similar to an SQL result set, i.e. get more fields from my documents within the facet result, like a grid, or table, which I can then loop over in the view.

My data is similar to this:
    { order_placed:   "2013-05-13 16:43",
      product_id:     100,
      sales_qty:      2,
      payment_method: "creditcard",
      country:        "US",
      retailer:       "ABC Store"
    },
    { order_placed:   "2013-05-14 11:24",
      product_id:     203,
      sales_qty:      1,
      payment_method: "cash",
      country:        "DE",
      retailer:       "XYZ Store"
    },
    { order_placed:   "2013-05-14 18:10",
      product_id:     138,
      sales_qty:      4,
      payment_method: "unknown",
      country:        "JP",
      retailer:       "NPN Store"
    } ... etc.
How do I do this in ES?

SELECT sum(sales_qty),country
FROM sales
WHERE product_id = 183 AND retailer = 'NPN Store'
GROUP BY country
ORDER BY sum(sales_qty) DESC;
The sum(sales_qty) should be limited to the product_id 183. I think what I need is the term_stats facet, sort by reverse-max, and facet filter by product_id = 183 and retailer = 'NPN Store' - I'm not sure how to put it all together.

Basically I want to use ES like a data cube, if that makes any sense.

Pointers to documentation would be fine, as I am currently not sure what to look for.

--
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.
For more options, visit https://groups.google.com/groups/opt_out.

Ben_2 · June 18, 2013, 8:12am

Hello David,

yes it should do it, but if I use query filters, it would not change the
SUM() calculation because the facet results are calculated from all
documents - at least that's how I understand the documentation.

Would I need to use facet filters to limit the statistical calculations
like SUM() to the filtered result set?

Thank you
Ben

On Tuesday, June 18, 2013 5:06:23 PM UTC+9, David Pilato wrote:

Somehow, you want to compute on orders. So basically, you have to index
orders in separate documents.
Then run a Query, using Query DSL, filtering by retailer and product_id
using a BoolFilterhttp://www.elasticsearch.org/guide/reference/query-dsl/bool-filter/ (you
will need not to analyze retailer field, set index to not_analyzedhttp://www.elasticsearch.org/guide/reference/mapping/core-types/
).

Add a Terms Stats facethttp://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet/ on
field country for the key_field and sales_qty for the value_field.

Does it help?

--
David Pilato | Technical Advocate | Elasticsearch.com
@dadoonet https://twitter.com/dadoonet | @elasticsearchfrhttps://twitter.com/elasticsearchfr
| @scrutmydocs https://twitter.com/scrutmydocs

Le 18 juin 2013 à 09:47, Ben <tonka...@gmail.com <javascript:>> a écrit :

I am getting started with ES and have a hard time understanding how to
build a query in ES to get a result set that is similar to an SQL result
set, i.e. get more fields from my documents within the facet result, like a
grid, or table, which I can then loop over in the view.

My data is similar to this:
    { order_placed:   "2013-05-13 16:43",
      product_id:     100,
      sales_qty:      2,
      payment_method: "creditcard",
      country:        "US",
      retailer:       "ABC Store"
    },
    { order_placed:   "2013-05-14 11:24",
      product_id:     203,
      sales_qty:      1,
      payment_method: "cash",
      country:        "DE",
      retailer:       "XYZ Store"
    },
    { order_placed:   "2013-05-14 18:10",
      product_id:     138,
      sales_qty:      4,
      payment_method: "unknown",
      country:        "JP",
      retailer:       "NPN Store"
    } ... etc.
How do I do this in ES?

SELECT sum(sales_qty),country
FROM sales
WHERE product_id = 183 AND retailer = 'NPN Store'
GROUP BY country
ORDER BY sum(sales_qty) DESC;

The sum(sales_qty) should be limited to the product_id 183. I think what I
need is the term_stats facet, sort by reverse-max, and facet filter by
product_id = 183 and retailer = 'NPN Store' - I'm not sure how to put it
all together.

Basically I want to use ES like a data cube, if that makes any sense.

Pointers to documentation would be fine, as I am currently not sure what
to look for.

--
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:>.
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.
For more options, visit https://groups.google.com/groups/opt_out.

dadoonet · June 18, 2013, 12:00pm

Yes!

Or use Elasticsearch Platform — Find real-time answers at scale | Elastic.
Should work.

--
David
Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs

Le 18 juin 2013 à 10:12, Ben tonkatsufan@gmail.com a écrit :

Hello David,

yes it should do it, but if I use query filters, it would not change the SUM() calculation because the facet results are calculated from all documents - at least that's how I understand the documentation.

Would I need to use facet filters to limit the statistical calculations like SUM() to the filtered result set?

Thank you
Ben

On Tuesday, June 18, 2013 5:06:23 PM UTC+9, David Pilato wrote:

Somehow, you want to compute on orders. So basically, you have to index orders in separate documents.
Then run a Query, using Query DSL, filtering by retailer and product_id using a BoolFilter (you will need not to analyze retailer field, set index to not_analyzed).

Add a Terms Stats facet on field country for the key_field and sales_qty for the value_field.

Does it help?

--
David Pilato | Technical Advocate | Elasticsearch.com
@dadoonet | @elasticsearchfr | @scrutmydocs

Le 18 juin 2013 à 09:47, Ben tonka...@gmail.com a écrit :
I am getting started with ES and have a hard time understanding how to build a query in ES to get a result set that is similar to an SQL result set, i.e. get more fields from my documents within the facet result, like a grid, or table, which I can then loop over in the view.

My data is similar to this:
    { order_placed:   "2013-05-13 16:43",
      product_id:     100,
      sales_qty:      2,
      payment_method: "creditcard",
      country:        "US",
      retailer:       "ABC Store"
    },
    { order_placed:   "2013-05-14 11:24",
      product_id:     203,
      sales_qty:      1,
      payment_method: "cash",
      country:        "DE",
      retailer:       "XYZ Store"
    },
    { order_placed:   "2013-05-14 18:10",
      product_id:     138,
      sales_qty:      4,
      payment_method: "unknown",
      country:        "JP",
      retailer:       "NPN Store"
    } ... etc.
How do I do this in ES?

SELECT sum(sales_qty),country
FROM sales
WHERE product_id = 183 AND retailer = 'NPN Store'
GROUP BY country
ORDER BY sum(sales_qty) DESC;
The sum(sales_qty) should be limited to the product_id 183. I think what I need is the term_stats facet, sort by reverse-max, and facet filter by product_id = 183 and retailer = 'NPN Store' - I'm not sure how to put it all together.

Basically I want to use ES like a data cube, if that makes any sense.

Pointers to documentation would be fine, as I am currently not sure what to look for.

--
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.
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.
For more options, visit https://groups.google.com/groups/opt_out.