How do I access the value for numIngestedEvents using sql?

How do I convert the following working query to sql? I couldn’t figure out how to get the numIngestedEvents to work in sql.

{
“queryType”: “timeseries”,
“granularity”: {
“period”: “PT1M”,
“type”: “period”,
“timeZone”: “UTC”
},
“dataSource”: “explorer”,
“intervals”: “2018-02-21T00:00:00+00:00/2018-02-26T00:00:00+00:00”,
“aggregations”: [
{
“type”: “longSum”,
“fieldName”: “count”,
“name”: “numIngestedEvents”
},
{
“type”: “doubleSum”,
“fieldName”: “totalPercentage”,
“name”: “sum__totalPercentage”
}
],
“postAggregations”: [
{
“function”: “function(sum__totalPercentage,numIngestedEvents) { if (numIngestedEvents == 0) return 0; return sum__totalPercentage/numIngestedEvents; }”,
“type”: “javascript”,
“fieldNames”: [
“sum__totalPercentage”,
“numIngestedEvents”
],
“name”: “avg(totalPercentage)”
}
]
}

I tried the following but the query is returning the sum of totalPercentage instead of the average.

{“query”:"
SELECT __time, AVG(totalPercentage)
FROM explorer
WHERE __time >= TIMESTAMP ‘2018-02-21 00:00:00’ AND __time < TIMESTAMP ‘2018-02-26 00:00:00’
GROUP BY __time
"}

I think the SELECT statement should look something like this to work, but I don’t know how to get the value for numIngestedEvents

SELECT __time, (SUM(totalPercentage) / numIngestedEvents)

Any Ideas?

What’s the output if you add "EXPLAIN PLAN FOR " before the “SELECT” in the SQL query?

  • Jon

Hi Haiku,

The SQL equivalent of numIngestedEvents in that example is SUM(“count”). So the query should be:

SELECT FLOOR(__time TO MINUTE), SUM(totalPercentage) / SUM(“count”) FROM explorer WHERE __time >= TIMESTAMP ‘2018-02-21 00:00:00’ AND __time < TIMESTAMP ‘2018-02-26 00:00:00’ GROUP BY FLOOR(__time TO MINUTE)