复杂SQL转ES
MySQL查询转ES查询
⽬录
前⾔
MySQL查询遇到了性能瓶颈，⽆奈改为⽤ES查询，但sql语句转为es查询没那么简单，⽹上的资料浩繁杂乱，错多对少，终于在官⽅⽂档中到了⾃⼰的答案，故记录下来⽅便⽇后使⽤。
ES版本：7.13.2
es⾃带的sql转换
POST 127.0.0.1:9200/_xpack/sql/translate
请求内容：
{
"query":"SELECT * FROM goods_stock WHERE store_id=10"
}
这⾥goods_stock是es的索引，where条件必须是该索引中存储的字段
返回内容：
{
"size": 1000,
"query":{
"term":{
"store_id":{
"value": 10,
"boost": 1.0
}
}
},
"_source":{
"includes":[
"store_id",
...
],
"excludes":[]
},
"sort":[
{
"_doc":{
"order":"asc"
}
}
]
}
mysql操作官方文档返回的内容就是sql转换为es查询语句的样⼦。不过呢，这个转换的api只能转换简单的sql，聚合的操作就转换不了，⽽且啊，这个转换的查询有挺多没⽤的冗余信息。
复杂sql
如果需求是复杂的sql该咋办，那么只能⾃⼰参考官⽅⽂档，⾃⼰写es查询了呀
查询⽅法
上⾯的es查询中，有个term的查询，term是个什么玩意⼉？
es查询中，有层层递进的关系，你先告诉它，你要做什么查询，是精确的呢，还是模糊的呢
TermQuery
term表⽰的就是精确查询
{
"query":{
"term":{
"name":"spring"
}
}
}
⽐如这⾥搜索的就是name的精确值为spring的⽂档
TermsQuery
terms其实跟term差不多，都是精确搜索
"query":{
"terms":{
"title":["blog","first"]
}
}
只不过terms⽀持搜索多个值
BoolQuery
像sql中有⼀堆and、 or之类的关系，es中当然也有啦。
bool就是为了将多个查询⽤and / or这样的关系连起来的查询⽅法
⽐如这样⼀个查询：
SELECT * FROM student WHERE name='spring' and age <> 18;
"bool":{
"must":[
{
"term":{
"name":"spring"
}
}
],
"must_not":[
{
"term":{
"age": 18
}
}
]
}
}
}
bool查询过滤的⼦句有四种
must        返回的⽂档必须满⾜must⼦句的条件，并且参与计算分值。
must_not  返回的⽂档必须不满⾜must_not定义的条件。
filter          先判断是否满⾜查询条件，如果不满⾜，会缓存查询过程（记录该⽂档不满⾜结果）；满⾜的话，就直接缓存结果。另外不进⾏分值计算
should      返回的⽂档可能满⾜should⼦句的条件。
MatchQuery
match query 和term query的区别是match query在搜索前先将搜索关键字分词，再拿词语去索引中搜索，term是精确查询，match是模糊查询。
我暂时不记录了，因为暂时没⽤到，这个模糊查询功能是做检索⼯具的⼀个核⼼功能。
范围查询
⽐如 age > 10 and age < 18
在es的查询中等于
{
"range":{
"age":{
"gt":10,
"lt":18
}
}
}
range表⽰范围查询，age是具体的字段
gt  表⽰⼤于
gte 表⽰⼤于等于
lt    表⽰⼩于
lte  表⽰⼩于等于
多个范围查询
"age":{
"gt":10,
"lt":18
},
"score":{
"gte":90,
"lte":95
}
}
}
时间范围查询
⽐如
birthday >= '2000-05-01 00:00:00' and birthday <= '2020-07-01 23:59:59'
{
"range":{
"birthday":{
"gte":"2021-05-01 00:00:00",
"lte":"2021-07-01 23:59:59",
"format":"yyyy-MM-dd HH:mm:ss"
}
}
}
format 表⽰标准化⽇期
yyyy    表⽰年
MM    表⽰⽉
dd      表⽰⽇
HH      表⽰24⼩时制的⼩时
mm    表⽰分
ss      表⽰秒
模糊查询
类似于mysql的like查询要怎么做呢
SELECT * FROM student WHERE name LIKE "张%"；
对于没有做分词的模糊查询，使⽤wildcardQuery就⾏了
{
"query":{
"wildcard":{
"name":"张*"
}
}
}
通配符*跟sql的%⼀样
聚合统计
count
原本我以为统计⽂档数量是需要调⽤ES的count API的
localhost:9200/index_name/_count
实际上查询⽂档的时候呢，是可以返回符合条件的⽂档数量的
"hits":{
"total":{
"value": 5986,
"relation":"eq"
},
...
}
这⾥的value就是⽂档总数。不过需要注意⼀点，ES查询时，默认只返回9999条，这意味着当超过1万条的数据时，这个统计值只有9999。
需要在查询时加⼀个参数：
"track_total_hits":true  // 与query平级
这个参数表⽰返回所有符合条件的⽂档
count + distinct
{
"query":{
...
},
"aggs":{
"count":{
"cardinality":{
"field":"goods_id"
}
}
}
}
aggs与query平级，field填写需要做distinct操作的列。
SUM
{
"query":{
...
},
"my_lock_total":{
"sum":{
"field":"lock_num"
}
}
}
复杂的SUM
⽐如⼀家店的仓库⾥有很多商品，现在要统计⼀下总库存的售价是多少
sql : SELECT SUM(stock_num * tag_price) FROM store_stock;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。