Elasticsearch到SQL等效

【问题描述】：

我对 文档有疑问。所以目标是我需要在指定日期内获取索引收盘的最新数据和收盘的最新数据。下面是一个等效的 SQL 示例：

SELECT (
    SELECT `close` FROM `stocks` WHERE date >= 2022-06-01 ORDER by date DESC LIMIT 1
) as last_close, 
(
    SELECT `close` FROM `stocks` ORDER by date ASC LIMIT 1
) as latest_close FROM stocks

这是我需要实现的，其余的我认为我不需要分享，因为瓶颈就在这个问题上。编辑：这是我在索引上的映射：

{
  "stocks": { - 
    "mappings": { - 
      "properties": { - 
        "avg": { - 
          "type": "double"
        },
        "board": { - 
          "type": "text"
        },
        "book": { - 
          "type": "double"
        },
        "change": { - 
          "type": "double"
        },
        "chg": { - 
          "type": "double"
        },

        "close": { - 
          "type": "double"
        },
        "date": { - 
          "type": "date",
          "format": "yyyy-MM-dd HH:mm:ss"
        },
        "der": { - 
          "type": "double"
        },
        "eps": { - 
          "type": "double"
        },
        "fve": { - 
          "type": "double"
        },
        "fvei": { - 
          "type": "double"
        },
        "group": { - 
          "type": "keyword"
        },
        "high": { - 
          "type": "double"
        },
        "low": { - 
          "type": "double"
        },
        "open": { - 

          "type": "double"
        },
        "paid_up_cap_shares": { - 
          "type": "text",
          "fields": { - 
            "keyword": { - 
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "pbv": { - 
          "type": "double"
        },
        "peg_analysis": { - 
          "type": "text"
        },
        "peg_ratio": { - 
          "type": "double"
        },
        "per": { - 
          "type": "double"
        },
        "prev": { - 
          "type": "double"
        },
        "roe": { - 
          "type": "double"
        },

        "stock": { - 
          "type": "keyword"
        },
        "trade_freq": { - 
          "type": "double"
        },
        "trade_val": { - 
          "type": "double"
        },
        "trade_vol": { - 
          "type": "double"
        }
      }
    }
  }
}

这是一个索引文档的示例：

{
    "_index": "stocks",
    "_id": "6odITIEBRQt2Zq4UUGu3",
    "_score": 1.0,
    "_source": {
        "date": "2022-06-10 13:23:36",
        "fvei": "112833.74",
        "pbv": "0.0",
        "prev": "97",
        "book": "-1.185716459E8",
        "roe": "-7.87",

        "der": "-6.87",
        "high": "91",
        "avg": "91.0",
        "fve": "91.0",
        "low": "91",
        "stock": "WINR",
        "per": "0.0",
        "close": "91",
        "trade_vol": "46283600",
        "group": "IDXPROPERT",
        "paid_up_cap_shares": "0.1",
        "trade_val": "4211807600",
        "chg": "-6.59",
        "change": "-6.0",
        "peg_ratio": "0.0",
        "eps": "9327072.3",
        "trade_freq": "1433",
        "peg_analysis": "negative growth",
        "board": "RG",
        "open": "91"
    }
},
{
    "_index": "stocks",
    "_id": "7IdITIEBRQt2Zq4UUGu3",
    "_score": 1.0,
    "_source": {
        "date": "2022-06-10 13:23:36",
        "fvei": "66215.12",

        "pbv": "0.0",
        "prev": "685",
        "book": "1946574.58",
        "roe": "22.08",
        "der": "3.62",
        "high": "685",
        "avg": "677.5",
        "fve": "680.0",
        "low": "670",
        "stock": "TLDN",
        "per": "0.0",
        "close": "680",
        "trade_vol": "577600",
        "group": "IDXNONCYC",
        "paid_up_cap_shares": "540.65",
        "trade_val": "393001500",
        "chg": "-0.73",
        "change": "-5.0",
        "peg_ratio": "0.0",
        "eps": "429847.27",
        "trade_freq": "117",
        "peg_analysis": "negative growth",
        "board": "RG",
        "open": "685"
    }
},

【讨论】：