# Facets and statistics with elasticsearch (on a subset)
**URL:** https://discuss.elastic.co/t/facets-and-statistics-with-elasticsearch-on-a-subset/11182
**Category:** Elasticsearch
**Created:** [March 17, 2013, 3:38pm UTC](https://discuss.elastic.co/t/facets-and-statistics-with-elasticsearch-on-a-subset/11182 "2013-03-17T15:38:30Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@Fabien\_Guiraud](https://discuss.elastic.co/u/Fabien_Guiraud)
#### Post date: [March 17, 2013, 3:38pm UTC](https://discuss.elastic.co/t/facets-and-statistics-with-elasticsearch-on-a-subset/11182/1 "2013-03-17T15:38:30Z")
Hi,
I have a problem with facets in elasticsearch. I have a table videos, a
table channels, 1 channel has many videos. I just want to show a donut with
% of views\_count per channel on the X lastest videos.
In SQL:
SELECT SUM(views\_count) FROM videos WHERE videos.channel\_id = X ORDER BY published\_at DESC LIMIT Y
I can do that on all videos but I don't arrive to do that with the LIMIT.
Any Idea??
Thanks
--
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](mailto:elasticsearch+unsubscribe@googlegroups.com).
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
---
### Author:  [@karmi](https://discuss.elastic.co/u/karmi)
#### Post date: [March 17, 2013, 5:07pm UTC](https://discuss.elastic.co/t/facets-and-statistics-with-elasticsearch-on-a-subset/11182/2 "2013-03-17T17:07:15Z")
> SELECT SUM(views\_count) FROM videos WHERE videos.channel\_id = X ORDER BY published\_at DESC LIMIT Y
It all depends on how you model your data in Elasticseach, that's the
hardest part. Assuming you'd have the `video` documents which have a
`channel_id` field and a `views_count` field, you can do a `filtered` [0](http://www.elasticsearch.org/guide/reference/query-dsl/filtered-query.html)
query with a `term` [1](http://www.elasticsearch.org/guide/reference/query-dsl/term-filter.html) filter on `channel_id`, and a `terms` facet [2](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet.html)
over `views_count`, and that will return a `count` (10, by default) of most
viewed videos. (In other scenarios, you could also use a `facet_filter` to
restrict facets to a certain `channel_id`, eg. when you'd like to get back
facets for multiple channels.)
Karel
--
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](mailto:elasticsearch+unsubscribe@googlegroups.com).
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
---
### Author:  [@Fabien\_Guiraud](https://discuss.elastic.co/u/Fabien_Guiraud)
#### Post date: [March 17, 2013, 5:10pm UTC](https://discuss.elastic.co/t/facets-and-statistics-with-elasticsearch-on-a-subset/11182/3 "2013-03-17T17:10:50Z")
Well quite simple (you're assuming well) :
videos:
channel\_id
views\_count
published\_at
When I do that query, the total / sum, etc... from statistical is on all
the data set. I would like have the sum of total views for the last 100
videos not the all dataset... Do you know what I mean?
I try size but it doesn't work...
On Sunday, March 17, 2013 6:07:15 PM UTC+1, Karel Minařík wrote:
> SELECT SUM(views\_count) FROM videos WHERE videos.channel\_id = X ORDER BY published\_at DESC LIMIT Y
>
> >
>
> It all depends on how you model your data in Elasticseach, that's the
> hardest part. Assuming you'd have the `video` documents which have a
> `channel_id` field and a `views_count` field, you can do a `filtered` [0](http://www.elasticsearch.org/guide/reference/query-dsl/filtered-query.html)
> query with a `term` [1](http://www.elasticsearch.org/guide/reference/query-dsl/term-filter.html) filter on `channel_id`, and a `terms` facet [2](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet.html)
> over `views_count`, and that will return a `count` (10, by default) of most
> viewed videos. (In other scenarios, you could also use a `facet_filter` to
> restrict facets to a certain `channel_id`, eg. when you'd like to get back
> facets for multiple channels.)
>
> Karel
--
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](mailto:elasticsearch+unsubscribe@googlegroups.com).
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
---
### Author:  [@karmi](https://discuss.elastic.co/u/karmi)
#### Post date: [March 17, 2013, 5:38pm UTC](https://discuss.elastic.co/t/facets-and-statistics-with-elasticsearch-on-a-subset/11182/4 "2013-03-17T17:38:24Z")
Ah, OK, then just add a range filter on published\_at to the query (to limit the facet computation), and use size to limit the number of results in the facet.
Karel
On Sunday, 17. March 2013 at 18:10, Fabien Guiraud wrote:
> Well quite simple (you're assuming well) :
>
> videos:
> channel\_id
> views\_count
> published\_at
>
> When I do that query, the total / sum, etc... from statistical is on all the data set. I would like have the sum of total views for the last 100 videos not the all dataset... Do you know what I mean?
> I try size but it doesn't work...
>
> On Sunday, March 17, 2013 6:07:15 PM UTC+1, Karel Minařík wrote:
>
> > > SELECT SUM(views\_count) FROM videos WHERE videos.channel\_id = X ORDER BY published\_at DESC LIMIT Y
> >
> > It all depends on how you model your data in Elasticseach, that's the hardest part. Assuming you'd have the `video` documents which have a `channel_id` field and a `views_count` field, you can do a `filtered` [0](http://www.elasticsearch.org/guide/reference/query-dsl/filtered-query.html) query with a `term` [1](http://www.elasticsearch.org/guide/reference/query-dsl/term-filter.html) filter on `channel_id`, and a `terms` facet [2](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet.html) over `views_count`, and that will return a `count` (10, by default) of most viewed videos. (In other scenarios, you could also use a `facet_filter` to restrict facets to a certain `channel_id`, eg. when you'd like to get back facets for multiple channels.)
> >
> > Karel
>
> --
> You received this message because you are subscribed to a topic in the Google Groups "elasticsearch" group.
> To unsubscribe from this topic, visit [https://groups.google.com/d/topic/elasticsearch/-lQsU4k0sNU/unsubscribe?hl=en-US](https://groups.google.com/d/topic/elasticsearch/-lQsU4k0sNU/unsubscribe?hl=en-US).
> To unsubscribe from this group and all its topics, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).
> For more options, visit [https://groups.google.com/groups/opt\_out](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](mailto:elasticsearch+unsubscribe@googlegroups.com).
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
---
### Author:  [@Fabien\_Guiraud](https://discuss.elastic.co/u/Fabien_Guiraud)
#### Post date: [March 17, 2013, 5:48pm UTC](https://discuss.elastic.co/t/facets-and-statistics-with-elasticsearch-on-a-subset/11182/5 "2013-03-17T17:48:03Z")
I put a sort DESC on published\_at, but size works because it returns only
10 results for example, but the facet calculate on the entire dataset... ☹
Look :
curl -X GET
'[http://localhost:9200/videos/video/\_search?from=0&size=20&pretty](http://localhost:9200/videos/video/_search?from=0&size=20&pretty)' -d
'{"query":{"bool":{"must":[{"query\_string":{"query":"\*","default\_operator":"AND"}}]}},"sort":[{"published\_at":"desc"}],"facets":{"views\_count":{"statistical":{"field":"views\_count"}}},"size":20,"from":0}'
Do you see any errors?
Thanks 🙂
On Sunday, March 17, 2013 6:38:24 PM UTC+1, Karel Minařík wrote:
> Ah, OK, then just add a range filter on published\_at to the query (to
> limit the facet computation), and use size to limit the number of results
> in the facet.
>
> Karel
>
> On Sunday, 17. March 2013 at 18:10, Fabien Guiraud wrote:
>
> Well quite simple (you're assuming well) :
>
> videos:
> channel\_id
> views\_count
> published\_at
>
> When I do that query, the total / sum, etc... from statistical is on all
> the data set. I would like have the sum of total views for the last 100
> videos not the all dataset... Do you know what I mean?
> I try size but it doesn't work...
>
> On Sunday, March 17, 2013 6:07:15 PM UTC+1, Karel Minařík wrote:
>
> SELECT SUM(views\_count) FROM videos WHERE videos.channel\_id = X ORDER BY published\_at DESC LIMIT Y
>
> It all depends on how you model your data in Elasticseach, that's the
> hardest part. Assuming you'd have the `video` documents which have a
> `channel_id` field and a `views_count` field, you can do a `filtered` [0](http://www.elasticsearch.org/guide/reference/query-dsl/filtered-query.html)
> query with a `term` [1](http://www.elasticsearch.org/guide/reference/query-dsl/term-filter.html) filter on `channel_id`, and a `terms` facet [2](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet.html)
> over `views_count`, and that will return a `count` (10, by default) of most
> viewed videos. (In other scenarios, you could also use a `facet_filter` to
> restrict facets to a certain `channel_id`, eg. when you'd like to get back
> facets for multiple channels.)
>
> Karel
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "elasticsearch" group.
> To unsubscribe from this topic, visit
> [https://groups.google.com/d/topic/elasticsearch/-lQsU4k0sNU/unsubscribe?hl=en-US](https://groups.google.com/d/topic/elasticsearch/-lQsU4k0sNU/unsubscribe?hl=en-US)
> .
> To unsubscribe from this group and all its topics, send an email to
> [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \.
> For more options, visit [https://groups.google.com/groups/opt\_out](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](mailto:elasticsearch+unsubscribe@googlegroups.com).
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
---
### Author:  [@karmi](https://discuss.elastic.co/u/karmi)
#### Post date: [March 18, 2013, 10:34am UTC](https://discuss.elastic.co/t/facets-and-statistics-with-elasticsearch-on-a-subset/11182/6 "2013-03-18T10:34:31Z")
The sort is completely useles here -- it only applies to results returned,
not facets.
In your example you're using a `statistical` facet, which IMHO does
something different then you asked for in the original question. For the
terms facet, you control the number of returned aggregations by the `size`
parameter, see
docs: [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet.html)
Your `bool` query with `string` is also very different from what you've
asked in the original question and what I suggested as an Elasticsearch way
of expressing `WHERE videos.channel_id = X`. The _query_ part in
Elasticsearch request restricts both search results and facets. If you only
care about facets, you should use `search_type=count`.
Karel
On Sunday, March 17, 2013 6:48:03 PM UTC+1, Fabien Guiraud wrote:
> I put a sort DESC on published\_at, but size works because it returns only
> 10 results for example, but the facet calculate on the entire dataset... ☹
> Look :
> curl -X GET '
> [http://localhost:9200/videos/video/\_search?from=0&size=20&pretty](http://localhost:9200/videos/video/_search?from=0&size=20&pretty)' -d
> '{"query":{"bool":{"must":[{"query\_string":{"query":"\*","default\_operator":"AND"}}]}},"sort":[{"published\_at":"desc"}],"facets":{"views\_count":{"statistical":{"field":"views\_count"}}},"size":20,"from":0}'
>
> Do you see any errors?
>
> Thanks 🙂
>
> On Sunday, March 17, 2013 6:38:24 PM UTC+1, Karel Minařík wrote:
>
> > Ah, OK, then just add a range filter on published\_at to the query (to
> > limit the facet computation), and use size to limit the number of results
> > in the facet.
> >
> > Karel
> >
> > On Sunday, 17. March 2013 at 18:10, Fabien Guiraud wrote:
> >
> > Well quite simple (you're assuming well) :
> >
> > videos:
> > channel\_id
> > views\_count
> > published\_at
> >
> > When I do that query, the total / sum, etc... from statistical is on all
> > the data set. I would like have the sum of total views for the last 100
> > videos not the all dataset... Do you know what I mean?
> > I try size but it doesn't work...
> >
> > On Sunday, March 17, 2013 6:07:15 PM UTC+1, Karel Minařík wrote:
> >
> > SELECT SUM(views\_count) FROM videos WHERE videos.channel\_id = X ORDER BY published\_at DESC LIMIT Y
> >
> > It all depends on how you model your data in Elasticseach, that's the
> > hardest part. Assuming you'd have the `video` documents which have a
> > `channel_id` field and a `views_count` field, you can do a `filtered` [0](http://www.elasticsearch.org/guide/reference/query-dsl/filtered-query.html)
> > query with a `term` [1](http://www.elasticsearch.org/guide/reference/query-dsl/term-filter.html) filter on `channel_id`, and a `terms` facet [2](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet.html)
> > over `views_count`, and that will return a `count` (10, by default) of most
> > viewed videos. (In other scenarios, you could also use a `facet_filter` to
> > restrict facets to a certain `channel_id`, eg. when you'd like to get back
> > facets for multiple channels.)
> >
> > Karel
> >
> > --
> > You received this message because you are subscribed to a topic in the
> > Google Groups "elasticsearch" group.
> > To unsubscribe from this topic, visit
> > [https://groups.google.com/d/topic/elasticsearch/-lQsU4k0sNU/unsubscribe?hl=en-US](https://groups.google.com/d/topic/elasticsearch/-lQsU4k0sNU/unsubscribe?hl=en-US)
> > .
> > To unsubscribe from this group and all its topics, send an email to
> > [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com).
> > For more options, visit [https://groups.google.com/groups/opt\_out](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](mailto:elasticsearch+unsubscribe@googlegroups.com).
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
---
### Author:  [@Fabien\_Guiraud](https://discuss.elastic.co/u/Fabien_Guiraud)
#### Post date: [March 18, 2013, 6:20pm UTC](https://discuss.elastic.co/t/facets-and-statistics-with-elasticsearch-on-a-subset/11182/7 "2013-03-18T18:20:14Z")
Okay nice, I understand a little more, I will try that, thanks !
On Mon, Mar 18, 2013 at 11:34 AM, Karel Minařík [karel.minarik@gmail.com](mailto:karel.minarik@gmail.com)wrote:
> The sort is completely useles here -- it only applies to results returned,
> not facets.
>
> In your example you're using a `statistical` facet, which IMHO does
> something different then you asked for in the original question. For the
> terms facet, you control the number of returned aggregations by the `size`
> parameter, see docs:
> [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet.html)
>
> Your `bool` query with `string` is also very different from what you've
> asked in the original question and what I suggested as an Elasticsearch way
> of expressing `WHERE videos.channel_id = X`. The _query_ part in
> Elasticsearch request restricts both search results and facets. If you only
> care about facets, you should use `search_type=count`.
>
> Karel
>
> On Sunday, March 17, 2013 6:48:03 PM UTC+1, Fabien Guiraud wrote:
>
> > I put a sort DESC on published\_at, but size works because it returns only
> > 10 results for example, but the facet calculate on the entire dataset... ☹
> > Look :
> > curl -X GET '[http://localhost:9200/videos/\*\*video/\_search?from=0&size=20&](http://localhost:9200/videos/**video/_search?from=0&size=20&)
> > \*\*pretty[http://localhost:9200/videos/video/\_search?from=0&size=20&pretty](http://localhost:9200/videos/video/_search?from=0&size=20&pretty)'
> > -d '{"query":{"bool":{"must":[{" **query\_string":{"query":"\*","**
> > default\_operator":"AND"}}]}},"**sort":[{"published\_at":"desc"}**
> > ],"facets":{"views\_count":{" **statistical":{"field":"views\_**
> > count"}}},"size":20,"from":0}'
> >
> > Do you see any errors?
> >
> > Thanks 🙂
> >
> > On Sunday, March 17, 2013 6:38:24 PM UTC+1, Karel Minařík wrote:
> >
> > > Ah, OK, then just add a range filter on published\_at to the query (to
> > > limit the facet computation), and use size to limit the number of results
> > > in the facet.
> > >
> > > Karel
> > >
> > > On Sunday, 17. March 2013 at 18:10, Fabien Guiraud wrote:
> > >
> > > Well quite simple (you're assuming well) :
> > >
> > > videos:
> > > channel\_id
> > > views\_count
> > > published\_at
> > >
> > > When I do that query, the total / sum, etc... from statistical is on all
> > > the data set. I would like have the sum of total views for the last 100
> > > videos not the all dataset... Do you know what I mean?
> > > I try size but it doesn't work...
> > >
> > > On Sunday, March 17, 2013 6:07:15 PM UTC+1, Karel Minařík wrote:
> > >
> > > SELECT SUM(views\_count) FROM videos WHERE videos.channel\_id = X ORDER BY published\_at DESC LIMIT Y
> > >
> > > It all depends on how you model your data in Elasticseach, that's the
> > > hardest part. Assuming you'd have the `video` documents which have a
> > > `channel_id` field and a `views_count` field, you can do a `filtered` [0](http://www.elasticsearch. **org/guide/reference/query-dsl/** )
> > > query with a `term` [1] filter on `channel_id`, and a `terms` facet [2]
> > > over `views_count`, and that will return a `count` (10, by default) of most
> > > viewed videos. (In other scenarios, you could also use a `facet_filter` to
> > > restrict facets to a certain `channel_id`, eg. when you'd like to get back
> > > facets for multiple channels.)
> > >
> > > Karel
> > >
> > > filtered-query.html[http://www.elasticsearch.org/guide/reference/query-dsl/filtered-query.html](http://www.elasticsearch.org/guide/reference/query-dsl/filtered-query.html)
> > > [1]: [http://www.elasticsearch](http://www.elasticsearch). **org/guide/reference/query-dsl/**
> > > term-filter.html[http://www.elasticsearch.org/guide/reference/query-dsl/term-filter.html](http://www.elasticsearch.org/guide/reference/query-dsl/term-filter.html)
> > > [2]: [http://www.elasticsearch](http://www.elasticsearch). **org/guide/reference/api/**
> > > search/facets/terms-facet.html[http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet.html](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet.html)
> > >
> > > --
> > > You received this message because you are subscribed to a topic in the
> > > Google Groups "elasticsearch" group.
> > > To unsubscribe from this topic, visit [https://groups.google.com/d/](https://groups.google.com/d/)\*\*
> > > topic/elasticsearch/-\*\*lQsU4k0sNU/unsubscribe?hl=en-\*\*US[https://groups.google.com/d/topic/elasticsearch/-lQsU4k0sNU/unsubscribe?hl=en-US](https://groups.google.com/d/topic/elasticsearch/-lQsU4k0sNU/unsubscribe?hl=en-US)
> > > .
> > > To unsubscribe from this group and all its topics, send an email to
> > > elasticsearc...@googlegroups.\*\*com.
> > > For more options, visit [https://groups.google.com/\*\*groups/opt\_out](https://groups.google.com/**groups/opt_out)[https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out)
> > > .
> > >
> > > --
> > > You received this message because you are subscribed to a topic in the
> > > Google Groups "elasticsearch" group.
> > > To unsubscribe from this topic, visit
> > > [https://groups.google.com/d/topic/elasticsearch/-lQsU4k0sNU/unsubscribe?hl=en-US](https://groups.google.com/d/topic/elasticsearch/-lQsU4k0sNU/unsubscribe?hl=en-US)
> > > .
> > > To unsubscribe from this group and all its topics, send an email to
> > > [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).
> > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
--
Fabien
--
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](mailto:elasticsearch+unsubscribe@googlegroups.com).
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 2:46am UTC](https://discuss.elastic.co/t/facets-and-statistics-with-elasticsearch-on-a-subset/11182/8 "2017-07-06T02:46:01Z")