Hi,
I have an index "inputs", which consists of 2 columns: sessionId (string), timestamp (date).
I want to calculate average session duration and try to add visualization to Canvas.
SQL formula:
SELECT AVG(duration)
FROM
(SELECT MAX(timestamp)-MIN(timestamp) AS duration
FROM inputs
GROUP BY sessionId)
As I see, it would be also needed first to cast timestamp to numeric (BIGINT).
[MAX(timestamp) - MIN(timestamp)] must be [numeric], found value [MAX(timestamp)] type [datetime]
So I changed SQL query to:
SELECT
MAX(time) - MIN(time) as duration
FROM
(SELECT sessionId, CAST(timestamp as BIGINT) AS time
FROM "inputs")
GROUP BY sessionId
Now I'm getting error:
[math] > Failed to execute math expression. Check your column names
As I assume it's not supported within Elasticsearch:
Using sub-selects ( SELECT X FROM (SELECT Y) ) is supported to a small degree : any sub-select that can be "flattened" into a single SELECT is possible with Elasticsearch SQL.
Elasticsearch SQL limitations
Could I get confirmation that it's not possible within Elasticsearch to manipulate index in such a way to visualize average session duration using Kibana Canvas or Kibana Dashboard panel (I'm expected to do it through Kibana Dashboard panel)?
More general question:
As I understand Elasticsearch is not a tool that supports complex queries like average session duration?
Is the nature of Elasticsearch to do calculations based on a single row of index, not based on the whole index (GROUP BY) and that's incorrect use of Elasticsearch (average session duration) query?
I'd be extremely thankful for help,
Kuba
