Sub query using over order by in elasticsearch

Elastic Stack Elasticsearch
1

I need to transform the query to a query in Elasticsearch, but I didn't find a solution in my searches. Has anyone had this problem?

SELECT * FROM (
            SELECT
                coalesce(open.day, close.day) as day,                
                coalesce(open.total, 0) as open,
                coalesce(close.total, 0) as close,
                sum(coalesce(open.total, 0) - coalesce(close.total, 0)) over (order by coalesce(open.day, close.day)) as myProblemColumn
            FROM
            (
            SELECT  COUNT(1) as total,  day
            FROM  mytable
            WHERE
                ******
            GROUP BY day
            ) open
            full outer join
            (
            SELECT COUNT(1) as total, day
            FROM mytable
            WHERE
                ********
            GROUP BY day
            ) close
            ON open.day = close.day
            ORDER BY  coalesce(open.day, close.day) ASC
            ) blg WHERE day >= '2022-04-01'

My problem is the column "myProblemColumn", the others I managed to set up the search, where the result would work in the application.

2

This is quite the SQL statement. In order to get help someone needs to fully understand your data model, your SQL query and has to know Elasticsearch properly to translate.

Have you considered just showing some Elasticsearch sample documents and then explain what result you are after and go from there? Might have a much higher chance of getting help.

3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.