Hi, You can nest aggregations, so in this case you'd first use Date Histogram aggregation with an interval of one hour: http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-aggregations-bucket-datehistogram-aggregation.html
Then you'd aggregate by "id" field: http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html Here is an example: http://www.solinea.com/blog/elasticsearch-aggs-save-the-day This should be very fast, even when running on a single machine. On Friday, January 31, 2014 3:36:20 AM UTC+2, Maxime Nay wrote: > > Hi, > > We are experimenting elasticsearch 1.0.0, and are particularly excited > about the new aggregation feature. > > Here is one of our use-case that we would like to optimize : > > Right now, to imitate a basic SQL group by query that would look like : > SELECT day, hour, id, SUM(views), SUM(clicks), SUM(video_plays) FROM > events GROUP BY day, hour, id > > we are issuing this kind of queries : > > { > "size" : 0, > "query":{"match_all":{}}, > "aggs" : { > "test_aggregation" : { > "terms" : { > "script" : "doc['day'].date + '-' + doc['hour'].value + > '-' + doc['id'].value", > "order" : { "_term" : "asc" }, > "size": > }, > "aggs" : { > "sum_click" : { "sum" : { "field" : "clicks" } }, > "sum_views" : { "sum" : { "field" : "views" } }, > "sum_video_plays" : { "sum" : { "field" : "video_plays" } } > } > } > } > } > > But the perfs for this kind of queries are kind of low. Thus, we would > like to know if there are a more optimized way to get what we want. > > Thanks ! > Maxime > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/bb2293a1-b83c-45a1-af42-e48b3fd9a0c9%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
