convert sql to dsl elasticsearch query

Question

i want to convert this sql query to elastic DSL query language

SELECT t.pk_c_c_s,
       t.fk_c_c_id,
       t.s_b_a,
       t.datetime,
       SUBSTR(t.datetime, 0, 7) m,
       (
         SELECT SUM(i.s_b_a) sarpu
         FROM TBL_C_C_S i
         WHERE substr(i.datetime, 0, 7) = substr(t.datetime, 0, 7)
           AND i.datetime <= t.datetime
           AND i.fk_c_c_id = t.fk_c_c_id
         GROUP BY SUBSTR(i.datetime, 0, 7)
        ) s
FROM TBL_C_C_S t

how can i convert this sql query to elasticsearch

this is my way in elasticsearch

POST /c_c_s_index_test/_search
{ "size":0,
  "aggs": {
    "customer": {
      "terms": {
        "field": "fk_c_c_id",
        "size": 5
      },
      "aggs": {
        "sumscore": {
          "sum": {
            "field": "s_b_a"
          }
        },
        "month": {
          "date_histogram": {
            "field": "datetime",
            "interval": "1M",
            "min_doc_count": 1
          },
          "aggs": {
            "customer": {
              "sum": {
                "field": "s_b_a"
              }
            }
          }
        }
      }
    }    ,
        "stats_monthly_sales": {
            "extended_stats_bucket": {
                "buckets_path": "customer>sumscore" 
            }
        }
  }

but this just return sum of month and i.datetime<=t.datetime does not exists in this

Answer 1

What you probably need is cumulative sum aggregation: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-cumulative-sum-aggregation.html

So your query should look as follows:

{
  "size": 0,
  "aggs": {
    "customer": {
      "terms": {
        "field": "fk_c_c_id",
        "size": 5
      },
      "aggs": {
        "sales_per_month": {
          "date_histogram": {
            "field": "datetime",
            "interval": "month"
          },
          "aggs": {
            "sales": {
              "sum": {
                "field": "s_b_a"
              }
            },
            "cumulative_sales": {
              "cumulative_sum": {
                "buckets_path": "sales"
              }
            }
          }
        }
      }
    }
  }
}