How to divide two fields and get an average The Next CEO of Stack Overflowdoes elasticsearch have compound indexes?Elastic Search deleteByQuery multiple termselasticsearch copy_to field not behaving as expected with aggregationsHow do I write this in a query in Elastic?elasticsearch averaging a field on a bucketelastic agfregations get uniq value where clauseHow to get the average count of missing field per document with Elasticsearch?Elastic search date histogram averagesAverage value of array subfield in elasticsearchQuery to match content of two fields in Kibana
Flying from Cape Town to England and return to another province
Running a General Election and the European Elections together
What did we know about the Kessel run before the prequels?
Where do students learn to solve polynomial equations these days?
Why don't programming languages automatically manage the synchronous/asynchronous problem?
How did people program for Consoles with multiple CPUs?
Why, when going from special to general relativity, do we just replace partial derivatives with covariant derivatives?
If the heap is zero-initialized for security, then why is the stack merely uninitialized?
How to get from Geneva Airport to Metabief, Doubs, France by public transport?
Writing differences on a blackboard
When you upcast Blindness/Deafness, do all targets suffer the same effect?
A Man With a Stainless Steel Endoskeleton (like The Terminator) Fighting Cloaked Aliens Only He Can See
What was the first Unix version to run on a microcomputer?
0-rank tensor vs vector in 1D
Is there a difference between "Fahrstuhl" and "Aufzug"
Can we say or write : "No, it'sn't"?
Why is the US ranked as #45 in Press Freedom ratings, despite its extremely permissive free speech laws?
Would this house-rule that treats advantage as a +1 to the roll instead (and disadvantage as -1) and allows them to stack be balanced?
RigExpert AA-35 - Interpreting The Information
Easy to read palindrome checker
Help understanding this unsettling image of Titan, Epimetheus, and Saturn's rings?
Chain wire methods together in Lightning Web Components
Is a distribution that is normal, but highly skewed considered Gaussian?
What is meant by "large scale tonal organization?"
How to divide two fields and get an average
The Next CEO of Stack Overflowdoes elasticsearch have compound indexes?Elastic Search deleteByQuery multiple termselasticsearch copy_to field not behaving as expected with aggregationsHow do I write this in a query in Elastic?elasticsearch averaging a field on a bucketelastic agfregations get uniq value where clauseHow to get the average count of missing field per document with Elasticsearch?Elastic search date histogram averagesAverage value of array subfield in elasticsearchQuery to match content of two fields in Kibana
I am using Elastic 6.5
I need to divide two fields together and then get an average value.
Example mysql that I am trying to recreate:
SELECT avg(field1/field2)
FROM table
WHERE field1 > 0 && field2 > 0
elasticsearch
add a comment |
I am using Elastic 6.5
I need to divide two fields together and then get an average value.
Example mysql that I am trying to recreate:
SELECT avg(field1/field2)
FROM table
WHERE field1 > 0 && field2 > 0
elasticsearch
add a comment |
I am using Elastic 6.5
I need to divide two fields together and then get an average value.
Example mysql that I am trying to recreate:
SELECT avg(field1/field2)
FROM table
WHERE field1 > 0 && field2 > 0
elasticsearch
I am using Elastic 6.5
I need to divide two fields together and then get an average value.
Example mysql that I am trying to recreate:
SELECT avg(field1/field2)
FROM table
WHERE field1 > 0 && field2 > 0
elasticsearch
elasticsearch
edited Mar 21 at 18:19
Crixus
asked Mar 21 at 18:13
CrixusCrixus
164
164
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
I have solved my task with the query below:
"size": 0,
"query":
"match_all":
,
"aggs":
"average":
"avg":
"script":
add a comment |
You can achieve what you are looking for using Script Fields.
I've created sample mapping, documents, the query and the response for this use case which are mentioned below:
Mapping
PUT myindex
"mappings":
"mydocs":
"properties":
"field1":
"type":"long"
,
"field2":
"type":"long"
Sample Documents:
POST myindex/mydocs/1
"field1": 0,
"field2": 0
POST myindex/mydocs/2
"field1": 20,
"field2": 20
POST myindex/mydocs/3
"field1": 40,
"field2": 20
POST myindex/mydocs/4
"field1": 40,
"field2": 0
Query Request
POST <your_index_name>/_search
"_source":"*",
"query":
"match_all":
,
"script_fields":
"average":
"script":"long a1 = 0l;long a2 = 0l;if(params._source.field1==null)a1 = 01; else a1 = params._source.field1if(params._source.field2==null
Note that you can modify the above logic accordingly to fit your use case of averaging only if fields > 0
Response
"took": 12,
"timed_out": false,
"_shards":
"total": 5,
"successful": 5,
"failed": 0
,
"hits":
"total": 4,
"max_score": 1,
"hits": [
"_index": "myindex",
"_type": "mydocs",
"_id": "2",
"_score": 1,
"_source":
"field1": 20,
"field2": 20
,
"fields":
"average": [
1
]
,
"_index": "myindex",
"_type": "mydocs",
"_id": "4",
"_score": 1,
"_source":
"field1": 40,
"field2": 0
,
"fields":
"average": [
0
]
,
"_index": "myindex",
"_type": "mydocs",
"_id": "1",
"_score": 1,
"_source":
"field1": 0,
"field2": 0
,
"fields":
"average": [
0
]
,
"_index": "myindex",
"_type": "mydocs",
"_id": "3",
"_score": 1,
"_source":
"field1": 40,
"field2": 20
,
"fields":
"average": [
2
]
]
What you are looking for in the above response is the average
field.
Hope it helps!
1
Thanks for the answer. I kept having a problem with the formatting of the script when running it in Kibana, so I didn't get to see the result.
– Crixus
Mar 22 at 10:34
Hey @Crixus, not a problem. Next time do mention in question that you'd want to run query in kibana. Would've created query accordingly. Your answer has a condition missing. Other than that looks good. Go ahead and accept your answer.
– Kamal
Mar 22 at 10:36
which condition am I missing?
– Crixus
Mar 22 at 12:30
1
@KamalI I see where you are coming from. The reason why I didn't have this extra condition is because 0/1=0. Is there a performance benefit to having this extra check?
– Crixus
Mar 22 at 14:07
1
I mentioned that because in your question, you have mentioned it. And I assume you only have positive integers and not negatives. Even so, I amuse myself at times. I think it's great what you have. As for performance benefit, I don't think that makes a difference, but I'd write that logic because we should code for humans to understand. Some of us are still humans ;)
– Kamal
Mar 22 at 14:24
|
show 1 more comment
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55286791%2fhow-to-divide-two-fields-and-get-an-average%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I have solved my task with the query below:
"size": 0,
"query":
"match_all":
,
"aggs":
"average":
"avg":
"script":
add a comment |
I have solved my task with the query below:
"size": 0,
"query":
"match_all":
,
"aggs":
"average":
"avg":
"script":
add a comment |
I have solved my task with the query below:
"size": 0,
"query":
"match_all":
,
"aggs":
"average":
"avg":
"script":
I have solved my task with the query below:
"size": 0,
"query":
"match_all":
,
"aggs":
"average":
"avg":
"script":
answered Mar 22 at 10:33
CrixusCrixus
164
164
add a comment |
add a comment |
You can achieve what you are looking for using Script Fields.
I've created sample mapping, documents, the query and the response for this use case which are mentioned below:
Mapping
PUT myindex
"mappings":
"mydocs":
"properties":
"field1":
"type":"long"
,
"field2":
"type":"long"
Sample Documents:
POST myindex/mydocs/1
"field1": 0,
"field2": 0
POST myindex/mydocs/2
"field1": 20,
"field2": 20
POST myindex/mydocs/3
"field1": 40,
"field2": 20
POST myindex/mydocs/4
"field1": 40,
"field2": 0
Query Request
POST <your_index_name>/_search
"_source":"*",
"query":
"match_all":
,
"script_fields":
"average":
"script":"long a1 = 0l;long a2 = 0l;if(params._source.field1==null)a1 = 01; else a1 = params._source.field1if(params._source.field2==null
Note that you can modify the above logic accordingly to fit your use case of averaging only if fields > 0
Response
"took": 12,
"timed_out": false,
"_shards":
"total": 5,
"successful": 5,
"failed": 0
,
"hits":
"total": 4,
"max_score": 1,
"hits": [
"_index": "myindex",
"_type": "mydocs",
"_id": "2",
"_score": 1,
"_source":
"field1": 20,
"field2": 20
,
"fields":
"average": [
1
]
,
"_index": "myindex",
"_type": "mydocs",
"_id": "4",
"_score": 1,
"_source":
"field1": 40,
"field2": 0
,
"fields":
"average": [
0
]
,
"_index": "myindex",
"_type": "mydocs",
"_id": "1",
"_score": 1,
"_source":
"field1": 0,
"field2": 0
,
"fields":
"average": [
0
]
,
"_index": "myindex",
"_type": "mydocs",
"_id": "3",
"_score": 1,
"_source":
"field1": 40,
"field2": 20
,
"fields":
"average": [
2
]
]
What you are looking for in the above response is the average
field.
Hope it helps!
1
Thanks for the answer. I kept having a problem with the formatting of the script when running it in Kibana, so I didn't get to see the result.
– Crixus
Mar 22 at 10:34
Hey @Crixus, not a problem. Next time do mention in question that you'd want to run query in kibana. Would've created query accordingly. Your answer has a condition missing. Other than that looks good. Go ahead and accept your answer.
– Kamal
Mar 22 at 10:36
which condition am I missing?
– Crixus
Mar 22 at 12:30
1
@KamalI I see where you are coming from. The reason why I didn't have this extra condition is because 0/1=0. Is there a performance benefit to having this extra check?
– Crixus
Mar 22 at 14:07
1
I mentioned that because in your question, you have mentioned it. And I assume you only have positive integers and not negatives. Even so, I amuse myself at times. I think it's great what you have. As for performance benefit, I don't think that makes a difference, but I'd write that logic because we should code for humans to understand. Some of us are still humans ;)
– Kamal
Mar 22 at 14:24
|
show 1 more comment
You can achieve what you are looking for using Script Fields.
I've created sample mapping, documents, the query and the response for this use case which are mentioned below:
Mapping
PUT myindex
"mappings":
"mydocs":
"properties":
"field1":
"type":"long"
,
"field2":
"type":"long"
Sample Documents:
POST myindex/mydocs/1
"field1": 0,
"field2": 0
POST myindex/mydocs/2
"field1": 20,
"field2": 20
POST myindex/mydocs/3
"field1": 40,
"field2": 20
POST myindex/mydocs/4
"field1": 40,
"field2": 0
Query Request
POST <your_index_name>/_search
"_source":"*",
"query":
"match_all":
,
"script_fields":
"average":
"script":"long a1 = 0l;long a2 = 0l;if(params._source.field1==null)a1 = 01; else a1 = params._source.field1if(params._source.field2==null
Note that you can modify the above logic accordingly to fit your use case of averaging only if fields > 0
Response
"took": 12,
"timed_out": false,
"_shards":
"total": 5,
"successful": 5,
"failed": 0
,
"hits":
"total": 4,
"max_score": 1,
"hits": [
"_index": "myindex",
"_type": "mydocs",
"_id": "2",
"_score": 1,
"_source":
"field1": 20,
"field2": 20
,
"fields":
"average": [
1
]
,
"_index": "myindex",
"_type": "mydocs",
"_id": "4",
"_score": 1,
"_source":
"field1": 40,
"field2": 0
,
"fields":
"average": [
0
]
,
"_index": "myindex",
"_type": "mydocs",
"_id": "1",
"_score": 1,
"_source":
"field1": 0,
"field2": 0
,
"fields":
"average": [
0
]
,
"_index": "myindex",
"_type": "mydocs",
"_id": "3",
"_score": 1,
"_source":
"field1": 40,
"field2": 20
,
"fields":
"average": [
2
]
]
What you are looking for in the above response is the average
field.
Hope it helps!
1
Thanks for the answer. I kept having a problem with the formatting of the script when running it in Kibana, so I didn't get to see the result.
– Crixus
Mar 22 at 10:34
Hey @Crixus, not a problem. Next time do mention in question that you'd want to run query in kibana. Would've created query accordingly. Your answer has a condition missing. Other than that looks good. Go ahead and accept your answer.
– Kamal
Mar 22 at 10:36
which condition am I missing?
– Crixus
Mar 22 at 12:30
1
@KamalI I see where you are coming from. The reason why I didn't have this extra condition is because 0/1=0. Is there a performance benefit to having this extra check?
– Crixus
Mar 22 at 14:07
1
I mentioned that because in your question, you have mentioned it. And I assume you only have positive integers and not negatives. Even so, I amuse myself at times. I think it's great what you have. As for performance benefit, I don't think that makes a difference, but I'd write that logic because we should code for humans to understand. Some of us are still humans ;)
– Kamal
Mar 22 at 14:24
|
show 1 more comment
You can achieve what you are looking for using Script Fields.
I've created sample mapping, documents, the query and the response for this use case which are mentioned below:
Mapping
PUT myindex
"mappings":
"mydocs":
"properties":
"field1":
"type":"long"
,
"field2":
"type":"long"
Sample Documents:
POST myindex/mydocs/1
"field1": 0,
"field2": 0
POST myindex/mydocs/2
"field1": 20,
"field2": 20
POST myindex/mydocs/3
"field1": 40,
"field2": 20
POST myindex/mydocs/4
"field1": 40,
"field2": 0
Query Request
POST <your_index_name>/_search
"_source":"*",
"query":
"match_all":
,
"script_fields":
"average":
"script":"long a1 = 0l;long a2 = 0l;if(params._source.field1==null)a1 = 01; else a1 = params._source.field1if(params._source.field2==null
Note that you can modify the above logic accordingly to fit your use case of averaging only if fields > 0
Response
"took": 12,
"timed_out": false,
"_shards":
"total": 5,
"successful": 5,
"failed": 0
,
"hits":
"total": 4,
"max_score": 1,
"hits": [
"_index": "myindex",
"_type": "mydocs",
"_id": "2",
"_score": 1,
"_source":
"field1": 20,
"field2": 20
,
"fields":
"average": [
1
]
,
"_index": "myindex",
"_type": "mydocs",
"_id": "4",
"_score": 1,
"_source":
"field1": 40,
"field2": 0
,
"fields":
"average": [
0
]
,
"_index": "myindex",
"_type": "mydocs",
"_id": "1",
"_score": 1,
"_source":
"field1": 0,
"field2": 0
,
"fields":
"average": [
0
]
,
"_index": "myindex",
"_type": "mydocs",
"_id": "3",
"_score": 1,
"_source":
"field1": 40,
"field2": 20
,
"fields":
"average": [
2
]
]
What you are looking for in the above response is the average
field.
Hope it helps!
You can achieve what you are looking for using Script Fields.
I've created sample mapping, documents, the query and the response for this use case which are mentioned below:
Mapping
PUT myindex
"mappings":
"mydocs":
"properties":
"field1":
"type":"long"
,
"field2":
"type":"long"
Sample Documents:
POST myindex/mydocs/1
"field1": 0,
"field2": 0
POST myindex/mydocs/2
"field1": 20,
"field2": 20
POST myindex/mydocs/3
"field1": 40,
"field2": 20
POST myindex/mydocs/4
"field1": 40,
"field2": 0
Query Request
POST <your_index_name>/_search
"_source":"*",
"query":
"match_all":
,
"script_fields":
"average":
"script":"long a1 = 0l;long a2 = 0l;if(params._source.field1==null)a1 = 01; else a1 = params._source.field1if(params._source.field2==null
Note that you can modify the above logic accordingly to fit your use case of averaging only if fields > 0
Response
"took": 12,
"timed_out": false,
"_shards":
"total": 5,
"successful": 5,
"failed": 0
,
"hits":
"total": 4,
"max_score": 1,
"hits": [
"_index": "myindex",
"_type": "mydocs",
"_id": "2",
"_score": 1,
"_source":
"field1": 20,
"field2": 20
,
"fields":
"average": [
1
]
,
"_index": "myindex",
"_type": "mydocs",
"_id": "4",
"_score": 1,
"_source":
"field1": 40,
"field2": 0
,
"fields":
"average": [
0
]
,
"_index": "myindex",
"_type": "mydocs",
"_id": "1",
"_score": 1,
"_source":
"field1": 0,
"field2": 0
,
"fields":
"average": [
0
]
,
"_index": "myindex",
"_type": "mydocs",
"_id": "3",
"_score": 1,
"_source":
"field1": 40,
"field2": 20
,
"fields":
"average": [
2
]
]
What you are looking for in the above response is the average
field.
Hope it helps!
edited Mar 22 at 14:03
answered Mar 22 at 4:57
KamalKamal
2,46211022
2,46211022
1
Thanks for the answer. I kept having a problem with the formatting of the script when running it in Kibana, so I didn't get to see the result.
– Crixus
Mar 22 at 10:34
Hey @Crixus, not a problem. Next time do mention in question that you'd want to run query in kibana. Would've created query accordingly. Your answer has a condition missing. Other than that looks good. Go ahead and accept your answer.
– Kamal
Mar 22 at 10:36
which condition am I missing?
– Crixus
Mar 22 at 12:30
1
@KamalI I see where you are coming from. The reason why I didn't have this extra condition is because 0/1=0. Is there a performance benefit to having this extra check?
– Crixus
Mar 22 at 14:07
1
I mentioned that because in your question, you have mentioned it. And I assume you only have positive integers and not negatives. Even so, I amuse myself at times. I think it's great what you have. As for performance benefit, I don't think that makes a difference, but I'd write that logic because we should code for humans to understand. Some of us are still humans ;)
– Kamal
Mar 22 at 14:24
|
show 1 more comment
1
Thanks for the answer. I kept having a problem with the formatting of the script when running it in Kibana, so I didn't get to see the result.
– Crixus
Mar 22 at 10:34
Hey @Crixus, not a problem. Next time do mention in question that you'd want to run query in kibana. Would've created query accordingly. Your answer has a condition missing. Other than that looks good. Go ahead and accept your answer.
– Kamal
Mar 22 at 10:36
which condition am I missing?
– Crixus
Mar 22 at 12:30
1
@KamalI I see where you are coming from. The reason why I didn't have this extra condition is because 0/1=0. Is there a performance benefit to having this extra check?
– Crixus
Mar 22 at 14:07
1
I mentioned that because in your question, you have mentioned it. And I assume you only have positive integers and not negatives. Even so, I amuse myself at times. I think it's great what you have. As for performance benefit, I don't think that makes a difference, but I'd write that logic because we should code for humans to understand. Some of us are still humans ;)
– Kamal
Mar 22 at 14:24
1
1
Thanks for the answer. I kept having a problem with the formatting of the script when running it in Kibana, so I didn't get to see the result.
– Crixus
Mar 22 at 10:34
Thanks for the answer. I kept having a problem with the formatting of the script when running it in Kibana, so I didn't get to see the result.
– Crixus
Mar 22 at 10:34
Hey @Crixus, not a problem. Next time do mention in question that you'd want to run query in kibana. Would've created query accordingly. Your answer has a condition missing. Other than that looks good. Go ahead and accept your answer.
– Kamal
Mar 22 at 10:36
Hey @Crixus, not a problem. Next time do mention in question that you'd want to run query in kibana. Would've created query accordingly. Your answer has a condition missing. Other than that looks good. Go ahead and accept your answer.
– Kamal
Mar 22 at 10:36
which condition am I missing?
– Crixus
Mar 22 at 12:30
which condition am I missing?
– Crixus
Mar 22 at 12:30
1
1
@KamalI I see where you are coming from. The reason why I didn't have this extra condition is because 0/1=0. Is there a performance benefit to having this extra check?
– Crixus
Mar 22 at 14:07
@KamalI I see where you are coming from. The reason why I didn't have this extra condition is because 0/1=0. Is there a performance benefit to having this extra check?
– Crixus
Mar 22 at 14:07
1
1
I mentioned that because in your question, you have mentioned it. And I assume you only have positive integers and not negatives. Even so, I amuse myself at times. I think it's great what you have. As for performance benefit, I don't think that makes a difference, but I'd write that logic because we should code for humans to understand. Some of us are still humans ;)
– Kamal
Mar 22 at 14:24
I mentioned that because in your question, you have mentioned it. And I assume you only have positive integers and not negatives. Even so, I amuse myself at times. I think it's great what you have. As for performance benefit, I don't think that makes a difference, but I'd write that logic because we should code for humans to understand. Some of us are still humans ;)
– Kamal
Mar 22 at 14:24
|
show 1 more comment
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55286791%2fhow-to-divide-two-fields-and-get-an-average%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown