将SQL转换为DSL Elasticsearch查询

Question

我希望将这个SQL查询转换成Elastic DSL查询语言。

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

如何将此SQL查询转换为Elasticsearch

以下是我在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" 
            }
        }
  }

但这只是返回月份总和，而i.datetime<=t.datetime在其中不存在。

- reihaneh

---

你已经尝试过什么了吗？能给我们展示一下你目前的尝试吗？ - jarlh

请问，您是在计算“fk_c_c_id”的每日累加和吗？ - Evaldas Buinauskas

Answer 1

您可能需要的是累积汇总聚合：https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-cumulative-sum-aggregation.html 因此，您的查询应如下所示：

{
  "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"
              }
            }
          }
        }
      }
    }
  }
}

- Evaldas Buinauskas

---

那么这个条件在哪里呢？>>>> AND i.datetime <= t.datetime - reihaneh

cumulative_sum可以为您完成这项任务。 - Evaldas Buinauskas

Answer 2

我认为来参与这个讨论的人都在寻找这个解决方案。对于任何想要将他的SQL查询转换为DSL的人。

Elasticsearch提供了一个API来翻译您的SQL查询。

您只需发送一个POST请求，例如：

POST /_sql/translate
{
  "query": "SELECT * FROM library ORDER BY page_count DESC",
  "fetch_size": 10
}

这将把它翻译成本地的Elasticsearch查询。就像这样：

 {
  "size": 10,
  "_source": false,
  "fields": [
    {
      "field": "author"
    },
    {
      "field": "name"
    },
    {
      "field": "page_count"
    },
    {
      "field": "release_date",
      "format": "strict_date_optional_time_nanos"
    }
  ],
  "sort": [
    {
      "page_count": {
        "order": "desc",
        "missing": "_first",
        "unmapped_type": "short"
      }
    }
  ]
}

您可以查看此处的文档。

- seghair tarek

Answer 3

使用 ES 7，您可以使用 _xpack/format 将查询翻译为 dsl，如下所示：

curl -H "Content-Type:application/json" -XPOST 127.0.0.1:9200/_xpack/sql/translate?pretty -d '{"query" : "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"}'

- KayV