{"action":"created","issue":{"url":"https://api.github.com/repos/elasticsearch/elasticsearch/issues/9791","labels_url":"https://api.github.com/repos/elasticsearch/elasticsearch/issues/9791/labels{/name}","comments_url":"https://api.github.com/repos/elasticsearch/elasticsearch/issues/9791/comments","events_url":"https://api.github.com/repos/elasticsearch/elasticsearch/issues/9791/events","html_url":"https://github.com/elasticsearch/elasticsearch/issues/9791","id":58373083,"number":9791,"title":"New type of aggregation","user":{"login":"zmathe","id":981448,"avatar_url":"https://avatars.githubusercontent.com/u/981448?v=3","gravatar_id":"","url":"https://api.github.com/users/zmathe","html_url":"https://github.com/zmathe","followers_url":"https://api.github.com/users/zmathe/followers","following_url":"https://api.github.com/users/zmathe/following{/other_user}","gists_url":"https://api.github.com/users/zmathe/gists{/gist_id}","starred_url":"https://api.github.com/users/zmathe/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zmathe/subscriptions","organizations_url":"https://api.github.com/users/zmathe/orgs","repos_url":"https://api.github.com/users/zmathe/repos","events_url":"https://api.github.com/users/zmathe/events{/privacy}","received_events_url":"https://api.github.com/users/zmathe/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/elasticsearch/elasticsearch/labels/:Aggs","name":":Aggs","color":"009800"},{"url":"https://api.github.com/repos/elasticsearch/elasticsearch/labels/feature","name":"feature","color":"328a58"}],"state":"closed","locked":false,"assignee":{"login":"colings86","id":236731,"avatar_url":"https://avatars.githubusercontent.com/u/236731?v=3","gravatar_id":"","url":"https://api.github.com/users/colings86","html_url":"https://github.com/colings86","followers_url":"https://api.github.com/users/colings86/followers","following_url":"https://api.github.com/users/colings86/following{/other_user}","gists_url":"https://api.github.com/users/colings86/gists{/gist_id}","starred_url":"https://api.github.com/users/colings86/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/colings86/subscriptions","organizations_url":"https://api.github.com/users/colings86/orgs","repos_url":"https://api.github.com/users/colings86/repos","events_url":"https://api.github.com/users/colings86/events{/privacy}","received_events_url":"https://api.github.com/users/colings86/received_events","type":"User","site_admin":false},"milestone":null,"comments":4,"created_at":"2015-02-20T16:08:49Z","updated_at":"2015-03-02T15:05:36Z","closed_at":"2015-03-02T15:05:36Z","body":"Hello,\r\nI am using ElasticSearch 1.4.2 and Kibana 4. I am not able to get the correct plots, because the following aggregation does not exists in Elasticsearch and also Kibana.\r\nGiven T1, T2, T3 ... Tn time periods. Each period contains the following element: {T1, d1:v1, d2:v2...dn:vn} where d1:v1 keys value pairs. For example: {\"09:00\",\"Product\":\"a\",\"Color\":\"red\",\"quantity\":1}, {\"09:01\",\"Product\":\"c\",\"Color\":\"blue\",\"quantity\":5}.\r\n\r\nI wanted to make a plot based on time(X axis) and quantity (Y axis).  The current elasticsearch and kibana return the \"wrong\" values in my case.\r\n\r\nThe system automatically splits the T1..Tn time interval into bucket[1],bucket[2],...,bucket[n] (bucket[1]>=T1 and bucket[n]<=T[n])\r\n\r\nbucket[1] = SUM(T1) + SUM(T2)\r\nbucket[2] = SUM(T1) + SUM(T2)+SUM(T3)\r\n...\r\nbucket[n] = SUM(Tn-1) + SUM(Tn)\r\n\r\nThis aggregation is wrong for me. I would like to have the following:\r\nbucket[1] = AVG(SUM(T1) + SUM(T2))\r\nbucket[2] = AVG(SUM(T1) + SUM(T2)+SUM(T3))\r\n...\r\nbucket[n] = AVG(SUM(Tn-1) + SUM(Tn))\r\n\r\nFor example:\r\n\r\n![screen shot 2015-02-18 at 18 13 53](https://cloud.githubusercontent.com/assets/981448/6252348/f15a41f4-b799-11e4-8b95-0234ca142dae.png)\r\n\r\nThe bucket length is 18 minute: we have 09:00,16:12, 16:30 buckets.\r\n09:00 SUM(T1) + SUM(t2) = 1 + 1 + 1 + 1 + 1= 5\r\n16:12  SUM(T3) = 1 + 2 + 2+ 2 = 7\r\n16:20 SUM(T4) = 1 \r\n\r\nWhat I want to have:\r\n09:00 = AVG(SUM(T1) + SUM(T2)) = (2 +  3)/2 = 2.5\r\n16:12  AVG(SUM(T3)) = 7/1 = 7\r\n16:20 AVG(SUM(T4)) = 1 \r\n\r\nI provide an example which can easily reproducible using kibana 4 and the latest elasticsearch:\r\n-Insert the following data to elasticsearch(I used sense from marvel):\r\nPOST /example/transactions/_bulk\r\n{ \"index\": {}}\r\n{\"Color\": \"red\", \"quantity\": 1, \"Product\": \"a\", \"time\": \"2015-02-09T09:00:00Z\"}\r\n{ \"index\": {}}\r\n{\"Color\": \"red\", \"quantity\": 1, \"Product\": \"b\", \"time\": \"2015-02-09T16:15:00Z\"}\r\n{ \"index\": {}}\r\n{\"Color\": \"red\", \"quantity\": 1, \"Product\": \"b\", \"time\": \"2015-02-09T16:30:00Z\"}\r\n{ \"index\": {}}\r\n{\"Color\": \"red\", \"quantity\": 1, \"Product\": \"a\", \"time\":\"2015-02-09T09:00:00Z\"}\r\n{ \"index\": {}}\r\n{\"Color\": \"red\", \"quantity\": 1, \"Product\": \"a\", \"time\": \"2015-02-09T09:15:00Z\"}\r\n{ \"index\": {}}\r\n{\"Color\": \"red\", \"quantity\": 2, \"Product\": \"b\", \"time\": \"2015-02-09T16:15:00Z\"}\r\n{ \"index\": {}}\r\n{\"Color\": \"red\", \"quantity\": 1, \"Product\": \"a\", \"time\":\"2015-02-09T09:15:00Z\"}\r\n{ \"index\": {}}\r\n{\"Color\": \"red\", \"quantity\": 2, \"Product\": \"b\", \"time\": \"2015-02-09T16:15:00Z\"}\r\n{ \"index\": {}}\r\n{\"Color\": \"red\", \"quantity\": 1, \"Product\": \"a\", \"time\": \"2015-02-09T09:15:00Z\"}\r\n{ \"index\": {}}\r\n{\"Color\": \"red\", \"quantity\": 2, \"Product\": \"b\", \"time\": \"2015-02-09T16:15:00Z\"}\r\n\r\n- make the query using kibana 4\r\n![screen shot 2015-02-18 at 17 31 43](https://cloud.githubusercontent.com/assets/981448/6251405/13cf497e-b794-11e4-8702-b18ad5960ba8.png)\r\n\r\nThe query made to the elasticsearch is:\r\n```json\r\n{\r\n    \"size\": 0,\r\n    \"query\": {\r\n        \"filtered\": {\r\n            \"query\": {\r\n                \"query_string\": {\r\n                    \"query\": \"*\"\r\n                }\r\n            },\r\n            \"filter\": {\r\n                \"bool\": {\r\n                    \"must\": [{\r\n                        \"range\": {\r\n                            \"time\": {\r\n                                \"gte\": 1423399451544,\r\n                                \"lte\": 1423631917911\r\n                            }\r\n                        }\r\n                    }],\r\n                    \"must_not\": []\r\n                }\r\n            }\r\n        }\r\n    },\r\n    \"aggs\": {\r\n        \"3\": {\r\n            \"date_histogram\": {\r\n                \"field\": \"time\",\r\n                \"interval\": \"3600000ms\",\r\n                \"min_doc_count\": 1,\r\n                \"extended_bounds\": {\r\n                    \"min\": 1423399451544,\r\n                    \"max\": 1423631917911\r\n                }\r\n            },\r\n            \"aggs\": {\r\n                \"4\": {\r\n                    \"terms\": {\r\n                        \"field\": \"Product\",\r\n                        \"size\": 0,\r\n                        \"order\": {\r\n                            \"1\": \"desc\"\r\n                        }\r\n                    },\r\n                    \"aggs\": {\r\n                        \"1\": {\r\n                            \"sum\": {\r\n                                \"field\": \"quantity\"\r\n                            }\r\n                        }\r\n                    }\r\n                }\r\n            }\r\n        }\r\n    },\r\n    \"fields\": [\"*\", \"_source\"],\r\n    \"script_fields\": {\r\n        \"time\": {\r\n            \"script\": \"if (doc[\\\"time\\\"].value == 0) { null } else { doc[\\\"time\\\"].value }\"\r\n        }\r\n    }\r\n}\r\n```\r\nI made a simple web page where I can make queries to ES and the result of the query is plotted. I changed the query as follow in order to make the \"correct\" plot.\r\n```json\r\n{\r\n    \"size\": 0,\r\n    \"query\": {\r\n        \"filtered\": {\r\n            \"query\": {\r\n                \"query_string\": {\r\n                    \"query\": \"*\"\r\n                }\r\n            },\r\n            \"filter\": {\r\n                \"bool\": {\r\n                    \"must\": [\r\n                        {\r\n                            \"range\": {\r\n                                \"time\": {\r\n                                   \"gte\": 1423147775940,\r\n                                    \"lte\": 1423752575940\r\n                                }\r\n                            }\r\n                        }\r\n                    ],\r\n                    \"must_not\": []\r\n                }\r\n            }\r\n        }\r\n    },\r\n    \"aggs\": {\r\n        \"2\": {\r\n            \"terms\": {\r\n                \"size\": 0,\r\n                \"field\": \"Product\"\r\n            },\r\n            \"aggs\": {\r\n                \"end_data\": {\r\n                    \"date_histogram\": {\r\n                        \"field\": \"time\",\r\n                        \"interval\": \"1080000ms\",\r\n                        \"min_doc_count\": 1,\r\n                        \"extended_bounds\": {\r\n                           \"min\":1423147775940,\r\n                           \"max\":1423752575940\r\n                        }\r\n                    },\r\n                    \"aggs\": {\r\n                        \"times\": {\r\n                            \"terms\": {\r\n                                \"field\": \"time\"\r\n                            },\r\n                            \"aggs\": {\r\n                                \"total_jobs\": {\r\n                                    \"sum\": {\r\n                                        \"field\": \"quantity\"\r\n                                    }\r\n                                }\r\n                            }\r\n                        }\r\n                    }\r\n                }\r\n            }\r\n        }\r\n    },\r\n    \"fields\": [\r\n        \"*\",\r\n        \"_source\"\r\n    ],\r\n    \"script_fields\": {\r\n        \"time\": {\r\n            \"script\": \"if (doc[\\\"time\\\"].value == 0) { null } else { doc[\\\"time\\\"].value }\"\r\n        }\r\n    }\r\n}\r\n```\r\nI made the averages in JS and I got the plot what I want:\r\n![screen shot 2015-02-18 at 17 36 35](https://cloud.githubusercontent.com/assets/981448/6251536/b7235e62-b794-11e4-84a7-ea7eaf6ebc80.png)\r\n\r\nIs it possible to support this kind of aggregation? \r\n\r\nThank you,\r\nZoltan"},"comment":{"url":"https://api.github.com/repos/elasticsearch/elasticsearch/issues/comments/76727128","html_url":"https://github.com/elasticsearch/elasticsearch/issues/9791#issuecomment-76727128","issue_url":"https://api.github.com/repos/elasticsearch/elasticsearch/issues/9791","id":76727128,"user":{"login":"colings86","id":236731,"avatar_url":"https://avatars.githubusercontent.com/u/236731?v=3","gravatar_id":"","url":"https://api.github.com/users/colings86","html_url":"https://github.com/colings86","followers_url":"https://api.github.com/users/colings86/followers","following_url":"https://api.github.com/users/colings86/following{/other_user}","gists_url":"https://api.github.com/users/colings86/gists{/gist_id}","starred_url":"https://api.github.com/users/colings86/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/colings86/subscriptions","organizations_url":"https://api.github.com/users/colings86/orgs","repos_url":"https://api.github.com/users/colings86/repos","events_url":"https://api.github.com/users/colings86/events{/privacy}","received_events_url":"https://api.github.com/users/colings86/received_events","type":"User","site_admin":false},"created_at":"2015-03-02T15:05:35Z","updated_at":"2015-03-02T15:05:35Z","body":"@clintongormley yep this would fit into the new agg features in https://github.com/elasticsearch/elasticsearch/issues/9876, although it would probably need to be done through the scriptable implementation.\r\n\r\nClosing in favour of that issue (#9876)"}}