我希望将这个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在其中不存在。