Elasticsearch division with statistical facets

Elastic Stack Elasticsearch
1

I need to take divisions of two field. I tried it with statistical facets
with scripting. But it gives me NaN values. The query is like,
{
"size": 0,
"query": {
"match_all": {}
},
"facets": {
"stat1": {
"statistical": {
"script": "(doc['COUNT_int'].value/ doc['TOTAL_COUNT_int'].value)"
}
}
}
}

and the response ;

{

  • took: 38
  • timed_out: false
  • _shards: {
    • total: 5
    • successful: 5
    • failed: 0
      }
  • hits: {
    • total: 331324
    • max_score: 1
    • hits: [ ]
      }
  • facets: {
    • stat1: {
      • _type: statistical
      • count: 331324
      • total: NaN
      • min: 0
      • max: Infinity
      • mean: NaN
      • sum_of_squares: NaN
      • variance: NaN
      • std_deviation: NaN
        }
        }

}
I tried it with doubleValue too but the result was same.

Note: TOTAL_COUNT_int greater than COUNT_int

Why didn't it take this divison ?

Thank You!

--

2

I would assume the issue is that you did a divide by zero. Is it possible
that TOTAL_COUNT was not indexed? Does it work using the source?

--
Ivan

On Tue, Jan 22, 2013 at 4:11 AM, Whispered ceahmetdal@gmail.com wrote:

I need to take divisions of two field. I tried it with statistical facets
with scripting. But it gives me NaN values. The query is like,
{
"size": 0,
"query": {
"match_all": {}
},
"facets": {
"stat1": {
"statistical": {
"script": "(doc['COUNT_int'].value/ doc['TOTAL_COUNT_int'].value)"
}
}
}
}

and the response ;

{

  • took: 38
  • timed_out: false
  • _shards: {
    • total: 5
    • successful: 5
    • failed: 0
      }
  • hits: {
    • total: 331324
    • max_score: 1
    • hits:
      }
  • facets: {
    • stat1: {
      • _type: statistical
      • count: 331324
      • total: NaN
      • min: 0
      • max: Infinity
      • mean: NaN
      • sum_of_squares: NaN
      • variance: NaN
      • std_deviation: NaN
        }
        }

}
I tried it with doubleValue too but the result was same.

Note: TOTAL_COUNT_int greater than COUNT_int

Why didn't it take this divison ?

Thank You!

--

--

3

On Tue, 2013-01-22 at 04:11 -0800, Whispered wrote:

I need to take divisions of two field. I tried it with statistical
facets with scripting. But it gives me NaN values. The query is like,

How are you sending your query? via curl?

In which case, make sure that your single quotes are not being messed up
by shell escaping.

Replace the ' in your script with \0027 (single quote in UTF8)

clint

{
"size": 0,
"query": {
"match_all": {}
},
"facets": {
"stat1": {
"statistical": {
"script": "(doc['COUNT_int'].value/
doc['TOTAL_COUNT_int'].value)"
}
}
}
}

and the response ;

{
* took: 38
* timed_out: false
* _shards: {
* total: 5
* successful: 5
* failed: 0
}
* hits: {
* total: 331324
* max_score: 1
* hits:
}
* facets: {
* stat1: {
* _type: statistical
* count: 331324
* total: NaN
* min: 0
* max: Infinity
* mean: NaN
* sum_of_squares: NaN
* variance: NaN
* std_deviation: NaN
}
}
}

I tried it with doubleValue too but the result was same.

Note: TOTAL_COUNT_int greater than COUNT_int

Why didn't it take this divison ?

Thank You!

--

--

4

I'm sending the query with _head plugin user interface.

22 Ocak 2013 Salı 18:28:50 UTC+2 tarihinde Clinton Gormley yazdı:

On Tue, 2013-01-22 at 04:11 -0800, Whispered wrote:

I need to take divisions of two field. I tried it with statistical
facets with scripting. But it gives me NaN values. The query is like,

How are you sending your query? via curl?

In which case, make sure that your single quotes are not being messed up
by shell escaping.

Replace the ' in your script with \0027 (single quote in UTF8)

clint

{
"size": 0,
"query": {
"match_all": {}
},
"facets": {
"stat1": {
"statistical": {
"script": "(doc['COUNT_int'].value/
doc['TOTAL_COUNT_int'].value)"
}
}
}
}

and the response ;

{
* took: 38
* timed_out: false
* _shards: {
* total: 5
* successful: 5
* failed: 0
}
* hits: {
* total: 331324
* max_score: 1
* hits:
}
* facets: {
* stat1: {
* _type: statistical
* count: 331324
* total: NaN
* min: 0
* max: Infinity
* mean: NaN
* sum_of_squares: NaN
* variance: NaN
* std_deviation: NaN
}
}
}

I tried it with doubleValue too but the result was same.

Note: TOTAL_COUNT_int greater than COUNT_int

Why didn't it take this divison ?

Thank You!

--

--

5

{
"size": 0,
"query": {
"match_all": {}
},
"facets": {
"stat1": {
"statistical": {
"script": "doc['OPC_int'].value"
}
}
}
}

gives;

{

  • took: 36
  • timed_out: false
  • _shards: {
    • total: 5
    • successful: 5
    • failed: 0
      }
  • hits: {
    • total: 240360
    • max_score: 1
    • hits:
      }
  • facets: {
    • stat1: {
      • _type: statistical
      • count: 240360
      • total: 5416673
      • min: 0
      • max: 96
      • mean: 22.53566733233483
      • sum_of_squares: 212503663
      • variance: 376.249468397237
      • std_deviation: 19.397151038161173
        }
        }

}

and the other query of the seperated version is

{
"size": 0,
"query": {
"match_all": {}
},
"facets": {
"stat1": {
"statistical": {
"script": "doc['TOTAL_COUNT_int'].value"
}
}
}
}

gives;

{

  • took: 19
  • timed_out: false
  • _shards: {
    • total: 5
    • successful: 5
    • failed: 0
      }
  • hits: {
    • total: 240360
    • max_score: 1
    • hits:
      }
  • facets: {
    • stat1: {
      • _type: statistical
      • count: 240360
      • total: 8672000
      • min: 0
      • max: 64
      • mean: 36.079214511565986
      • sum_of_squares: 410605440
      • variance: 406.58383988891586
      • std_deviation: 20.163924218487725
        }
        }

}

AS you can see they are not zero.

22 Ocak 2013 Salı 18:08:37 UTC+2 tarihinde Ivan Brusic yazdı:

I would assume the issue is that you did a divide by zero. Is it possible
that TOTAL_COUNT was not indexed? Does it work using the source?

--
Ivan

On Tue, Jan 22, 2013 at 4:11 AM, Whispered <ceahm...@gmail.com<javascript:>

wrote:

I need to take divisions of two field. I tried it with statistical facets
with scripting. But it gives me NaN values. The query is like,
{
"size": 0,
"query": {
"match_all": {}
},
"facets": {
"stat1": {
"statistical": {
"script": "(doc['COUNT_int'].value/
doc['TOTAL_COUNT_int'].value)"
}
}
}
}

and the response ;

{

  • took: 38
  • timed_out: false
  • _shards: {
    • total: 5
    • successful: 5
    • failed: 0
      }
  • hits: {
    • total: 331324
    • max_score: 1
    • hits:
      }
  • facets: {
    • stat1: {
      • _type: statistical
      • count: 331324
      • total: NaN
      • min: 0
      • max: Infinity
      • mean: NaN
      • sum_of_squares: NaN
      • variance: NaN
      • std_deviation: NaN
        }
        }

}
I tried it with doubleValue too but the result was same.

Note: TOTAL_COUNT_int greater than COUNT_int

Why didn't it take this divison ?

Thank You!

--

--

6

On Wed, Jan 23, 2013 at 3:22 AM, Whispered ceahmetdal@gmail.com wrote:

AS you can see they are not zero.

The second faceted query using TOTAL_COUNT shows that the minimum value is
in fact 0. For at least one document, there is a division by zero error.
Can you query for "TOTAL_COUNT_int = 0" and see what the results are?

--
Ivan

--