常见 SQL 语句在 ES 中如何使用？

车辙cz
452 阅读8分钟

「这是我参与2022首次更文挑战的第13天，活动详情查看：2022首次更文挑战」。

1 前言

ES 在对比关系型数据库中，检索数据方面有着极大的优势，但是常常我们还是喜欢先思考 SQL 是如何实现的，再思考 ElasticSearch 是怎么实现的，所以本篇文章将会介绍一下常见的 SQL ，ElasticSearch 要怎么实现这些常见 SQL。（这里检索方式由于 SQL 的是 Like，对应 ElasticSearch 检索方式很多，过段时间另起一篇介绍检索方式，下面主要介绍 SQL 的 group by 等基本操作）。

1.1 基本语法

由于下面涉及到主要是 ElasticSearch 聚合的语法。首先回顾一下语法

"aggregations":{   ## 和 Query 同级的关键词
	"aggregation_name_1":{  ## 自定义聚合查询名字
		"aggregation_type":{	## 聚合类型，Metric或者其他
			## aggregation_body 聚合需要的查询体
		},
		"meta" :{  ## 子聚合，不是必须
			## meta_data_body
		},
		"aggregations": { ## 子聚合，不是必须
		    ## sub_aggregation
		}
	},
	## 可以包含多个同级聚合，不是必须
	"aggregation_name_2":{  ## 自定义聚合查询名字
		## ......
	}
}

注意 aggregations 是和我们平常 ES query 查询是同级的，并且如果是聚合，建议分页的 size 设置为 0。

ElasticSearch 聚合有三种主要分类

image.png

2 实战

2.1 准备

以一张积木表为例，如下：

CREATE TABLE `ji_mu` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '积木名称',
  `color` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '积木颜色',
  `size` int DEFAULT NULL COMMENT '积木大小',
  `shape` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '积木形状',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

相应的数据

INSERT INTO ji_mu (`name`,color,size,shape) VALUES ('red-01','red','5',"正方形"); 
INSERT INTO ji_mu (`name`,color,size,shape) VALUES ('white-02','white','6',"正方形"); 
INSERT INTO ji_mu (`name`,color,size,shape) VALUES ('black-03','black','8',"圆形"); 
INSERT INTO ji_mu (`name`,color,size,shape) VALUES ('yellow-04','yellow','1',"圆形"); 
INSERT INTO ji_mu (`name`,color,size,shape) VALUES ('red-05','red','3',"圆形"); 
INSERT INTO ji_mu (`name`,color,size,shape) VALUES ('white-06','white','6',"三角形"); 
INSERT INTO ji_mu (`name`,color,size,shape) VALUES ('black-07','black','8',"三角形"); 
INSERT INTO ji_mu (`name`,color,size,shape) VALUES ('red-08','red','5',"正方形"); 
INSERT INTO ji_mu (`name`,color,size,shape) VALUES ('white-09','white','6',"正方形"); 
INSERT INTO ji_mu (`name`,color,size,shape) VALUES ('yellow-10','yellow','8',"圆形"); 
INSERT INTO ji_mu (`name`,color,size,shape) VALUES ('blue-11','blue','8',"长方形"); 
INSERT INTO ji_mu (`name`,color,size,shape) VALUES ('red-12','red','8',"三角形");
INSERT INTO ji_mu (`name`,color,size,shape) VALUES ('red-13','red','5',"三角形"); 
INSERT INTO ji_mu (`name`,color,size,shape) VALUES ('black-14','black','1',"椭圆");

同时建立 ElasticSearch 中的 Mapping 结构

PUT ji_mu
{
  "mappings": {
    "properties": {
      "shape": {
        "type": "keyword"
      },
      "color": {
        "type": "keyword"
      },
      "size": {
        "type": "integer"
      },
      "name": {
        "type": "keyword"
      }
    }
  }
}

数据和 MySQL 一样

POST ji_mu/_bulk
{"index":{"_id":1}}
{"size":5, "name":"red-01", "shape":"正方形", "color":"red"}
{"index":{"_id":2}}
{"size":6, "name":"white-02","shape":"正方形", "color":"white"}
{"index":{"_id":3}}
{"size":8, "name":"black-03","shape":"圆形", "color":"black"}
{"index":{"_id":4}}
{"size":1, "name":"yellow-04","shape":"圆形", "color":"yellow"}
{"index":{"_id":5}}
{"size":3, "name":"red-05","shape":"圆形", "color":"red"}
{"index":{"_id":6}}
{"size":6, "name":"white-06","shape":"三角形", "color":"white"}
{"index":{"_id":7}}
{"size":8, "name":"black-07","shape":"三角形", "color":"black"}
{"index":{"_id":8}}
{"size":5, "name":"red-08","shape":"正方形", "color":"red"}
{"index":{"_id":9}}
{"size":6, "name":"white-09","shape":"正方形", "color":"white"}
{"index":{"_id":10}}
{"size":8, "name":"yellow-10","shape":"圆形", "color":"yellow"}
{"index":{"_id":11}}
{"size":8, "name":"blue-11","shape":"长方形", "color":"blue"}
{"index":{"_id":12}}
{"size":8, "name":"red-12","shape":"三角形", "color":"red"}
{"index":{"_id":13}}
{"size":5, "name":"red-13","shape":"三角形", "color":"red"}
{"index":{"_id":14}}
{"size":1, "name":"black-13","shape":"椭圆", "color":"black"}

2.2 测试

2.2.1 案例一

需求：查询积木表中，按照形状分类，并且统计每个形状颜色数量且数量大于 1，最后取 top 2 个。SQL 语句如下

SELECT shape,COUNT(DISTINCT color) color_count 
FROM ji_mu 
GROUP BY shape 
HAVING color_count > 1 
ORDER BY color_count DESC LIMIT 2;

这条 SQL 很简单，首先使用 GROUP BY shape 进行形状分组，同时计算每个形状的颜色数量，由于同一形状颜色有些一样，需要去重，所以 COUNT(DISTINCT color) color_count，然后使用 HAVING color_count > 1 方式来过滤，要求颜色数量大于 1 ，最后使用 ORDER BY color_count DESC LIMIT 倒序并取出前 2 名。

那么怎么用 ElasticSearch 如何完成这个需求呢？

2.2.1.1 Group By

首先看 group by， ElasticSearch 对应的就是 Buckets 聚合的 terms，意思是分桶聚合

POST ji_mu/_search
{
  "size": 0,
  "aggs": {
    "shape_terms": {   //类似 group by 分组
      "terms": {       // 分桶聚合
        "field": "shape",  // 字段
        "size": 10
      }
    }
  }
}

结果大概如下，aggregations 下就是各个桶中的结果

{
  ......
  "aggregations" : {
    "shape_terms" : {  // 自定义的聚合的名称
      ......
      "buckets" : [
        {
          "key" : "三角形",   // 类似 group by 的字段
          "doc_count" : 4   // 桶中的数量，类似 count
        },
        {
          "key" : "圆形",
          "doc_count" : 4
        },
        {
          "key" : "正方形",
          "doc_count" : 4
        },
        {
          "key" : "椭圆",
          "doc_count" : 1
        },
        {
          "key" : "长方形",
          "doc_count" : 1
        }
      ]
    }
  }
}

2.2.1.2 DISTINCT

由于需要对颜色去重，在 ElasticSearch 可以使用指标类聚合 cardinality。它可以达到数据去重效果并统计。

DSL 语句：

POST ji_mu/_search
{
  "size": 0,
  "aggs": {
    "shape_terms": {  // 分桶聚合，类似 group by
      "terms": {
        "field": "shape",
        "size": 10
      },
      "aggs": {
        "color_count": {  // 在分桶聚合基础上，使用指标聚合求出颜色数量
          "cardinality": {
            "field": "color"
          }
        }
      }
    }
  }
}

结果如下

{
  ......
  "aggregations" : {
    ......
    "shape_terms" : { // 自定义的聚合名称
      "buckets" : [
        {
          "key" : "三角形",
          "doc_count" : 4,
          "color_count" : { // 自定义的聚合名称
            "value" : 3     // 每个桶颜色的数量
          }
        },
        {
          "key" : "圆形",
          "doc_count" : 4,
          "color_count" : {
            "value" : 3
          }
        },
        {
          "key" : "正方形",
          "doc_count" : 4,
          "color_count" : {
            "value" : 2
          }
        },
        {
          "key" : "椭圆",
          "doc_count" : 1,
          "color_count" : {
            "value" : 1
          }
        },
        {
          "key" : "长方形",
          "doc_count" : 1,
          "color_count" : {
            "value" : 1
          }
        }
      ]
    }
  }
}

2.2.1.3 Having & Order by

由于我们需要二次的进行过滤，只取颜色数量大于 1 的，在 ElasticSearch 中可以使用管道聚合类型的 bucket_selector 来完成，管道聚合是可以从其他聚合类型当作输入来源，然后二次过滤

POST ji_mu/_search
{
  "size": 0,
  "aggs": {
    "shape_terms": {  // 按照形状分组
      "terms": {   // 桶聚合类型
        "field": "shape", // 字段
        "size": 10
      },
      "aggs": {
        "color_count": {  // 获取颜色数量
          "cardinality": {  // 指标聚合类型
            "field": "color"  // 指定字段
          }
        },
        "color_count_filter":{   // having 过滤
          "bucket_selector": {  // 管道聚合类型
            "buckets_path": {
              "count":"color_count"  // 指定管道路径为上面的 color_count 指标聚合，并赋值给 count
            },
            "script": "params.count > 1"  // 取路径赋值的 count 字段，过滤只有 count 大于 1 的
          }
        }
      }
    }
  }
}

结果如下，可以看到结果中，椭圆和长方形不见了。

{
    ......
  "aggregations" : {
    "shape_terms" : {  // 自定义的聚合名称
      ......
      "buckets" : [ 
        {
          "key" : "三角形",  
          "doc_count" : 4, // 相当于 count
          "color_count" : { // 自定义的聚合名称
            "value" : 3
          }
        },
        {
          "key" : "圆形",
          "doc_count" : 4,
          "color_count" : {
            "value" : 3
          }
        },
        {
          "key" : "正方形",
          "doc_count" : 4,
          "color_count" : {
            "value" : 2
          }
        }
      ]
    }
  }
}

最后为了将结果倒排和取前 2个。在 ElasticSearch 中可以继续采用管道聚合类型的 bucket_sort。进行倒排和 limit 2。

POST ji_mu/_search
{
  "size": 0,
  "aggs": {
    "shape_terms": {  // 按照形状分组
      "terms": {   // 桶聚合类型
        "field": "shape", // 字段
        "size": 10
      },
      "aggs": {
        "color_count": {  // 获取颜色数量
          "cardinality": {  // 指标聚合类型
            "field": "color"  // 指定字段
          }
        },
        "color_count_filter":{   // having 过滤
          "bucket_selector": {  // 管道聚合类型
            "buckets_path": {
              "count":"color_count"  // 指定管道路径为上面的 color_count 指标聚合，并赋值给 count
            },
            "script": "params.count > 1"  // 取路径赋值的 count 字段，过滤只有 count 大于 1 的
          }
        },
        "color_count_sort":{  
          "bucket_sort": {  // 管道聚合类型
            "sort": {"color_count":"desc"}, // 指定排序的字段为上面 color_count 的指标聚合
            "from": 0,  // 相当于 limit
            "size": 2
          }
        }
      }
    }
  }
}

结果如下：正方形就不见了

{
    ......
  "aggregations" : {
    "shape_terms" : {  // 自定义的聚合名称
      ......
      "buckets" : [ 
        {
          "key" : "三角形",  
          "doc_count" : 4, // 相当于 count
          "color_count" : { // 相当于 DISTINCT
            "value" : 3
          }
        },
        {
          "key" : "圆形",
          "doc_count" : 4,
          "color_count" : {
            "value" : 3
          }
        }
      ]
    }
  }
}

2.2.2 案例二

需求：求出每个 color 颜色中 size 最大的一条记录。

SELECT * FROM ji_mu
WHERE (color,size) IN (
    SELECT color,MAX(size) size
    FROM ji_mu 
    GROUP BY color 
)

首先从子查询中分析，通过 GROUP BY color 分组，区别出每种颜色，而后计算出每组颜色取 size 的最大值 MAX(size) ,而后当初一个子查询，进行 in 操作，如果某条记录同时满足 color 和 size，则返回详细信息。

那么如何用 ElasticSearch 如何完成这个需求呢？

2.2.2.1 Group By

依然是 group by，和前面案例类似分桶聚合

POST ji_mu/_search
{
  "size": 0,
  "aggs": {
    "shape_terms": {
      "terms": {
        "field": "shape",
        "size": 10
      }
    }
  }
}

2.2.2.2 MAX

Max 函数，对应的 ElasticSearch 中对应的是指标聚合 Max，同样求出每个的最大值。

POST ji_mu/_search
{
  "size": 0,
  "aggs": {
    "shape_terms": {  // 分桶聚合
      "terms": {
        "field": "shape",
        "size": 10
      },
      "aggs": {
        "max_info": {  // 在分桶聚合的基础上，求每个桶最大值
          "max": {
            "field": "size"
          }
        }
      }
    }
  }
}

结果如下：

{
  ......
  "aggregations" : {
    "shape_terms" : {   // 自定义聚合名称
      ......
      "buckets" : [
        {
          "key" : "三角形",
          "doc_count" : 4,
          "max_info" : {    // 自定义聚合名称
            "value" : 8.0    // 每个桶中的最大，相当于 Max()
          }
        },
        {
          "key" : "圆形",
          "doc_count" : 4,
          "max_info" : {
            "value" : 8.0
          }
        },
        {
          "key" : "正方形",
          "doc_count" : 4,
          "max_info" : {
            "value" : 6.0
          }
        },
        {
          "key" : "椭圆",
          "doc_count" : 1,
          "max_info" : {
            "value" : 1.0
          }
        },
        {
          "key" : "长方形",
          "doc_count" : 1,
          "max_info" : {
            "value" : 8.0
          }
        }
      ]
    }
  }
}

到了这一步，in 要怎么操作呢？在 SQL 中是为了获取详细信息所以采用了 in 这种操作，然而在 ElasticSearch 可以另辟路径，使用分桶聚合类型中的 top_hits，该聚合可以返回聚合结果前几条，对结果进行排序，这样 Max 聚合就不需要了。

POST ji_mu/_search
{
  "size": 0,
  "aggs": {
    "color_terms": {   // 分桶聚合
      "terms": {
        "field": "color",
        "size": 10
      },
      "aggs": {
        "top_info": {   
          "top_hits": {
            "size": 1,  // 取每个桶的 top 1 数据
            "sort": [
              {
                "size": "desc" // 对每个桶中的数据进行倒序
              }
            ]
          }
        }
      }
    }
  }
}

结果如下：

{
  ......
  "aggregations" : {
    "color_terms" : {   // 根据 color 进行了分桶
      ......
      "buckets" : [
        {
          "key" : "red",
          "doc_count" : 5,
          "top_info" : {
            "hits" : {
              "total" : {
                "value" : 5,
                "relation" : "eq"
              },
              "max_score" : null,
              "hits" : [   // 桶中第一条的详细数据
                {
                  "_index" : "ji_mu",
                  "_type" : "_doc",
                  "_id" : "12",
                  "_score" : null,
                  "_source" : {
                    "size" : 8,
                    "name" : "red-12",
                    "shape" : "三角形",
                    "color" : "red"
                  },
                  "sort" : [
                    8
                  ]
                }
              ]
            }
          }
        },
        {
          "key" : "black",
          "doc_count" : 3,
          "top_info" : {
            "hits" : {
              "total" : {
                "value" : 3,
                "relation" : "eq"
              },
              "max_score" : null,
              "hits" : [   // 桶中第一条的详细数据
                {
                  "_index" : "ji_mu",
                  "_type" : "_doc",
                  "_id" : "3",
                  "_score" : null,
                  "_source" : {
                    "size" : 8,
                    "name" : "black-03",
                    "shape" : "圆形",
                    "color" : "black"
                  },
                  "sort" : [
                    8
                  ]
                }
              ]
            }
          }
        },
        ......
      ]
    }
  }
}

结尾

至此我们使用 ElasticSearch 完成了文中提到的两个案例，可以看到越来越复杂，并且有些地方和 SQL 方式是不大一样的，但是也并不是不能解决。所以得对 ElasticSearch 多加熟悉，才能对这些需求游刃有余。都看到这里了，喜欢点个赞呗 ^V^。

avatar
文章
阅读
粉丝