Elasticsearch 如何实现 SQL 语句中 Group By 和 Limit 的功能-低调大师

Elasticsearch 如何实现 SQL 语句中 Group By 和 Limit 的功能

2020-07-27 619

作者介绍

魏彬，普翔科技 CTO，开源软件爱好者，中国第一位 Elastic 认证工程师，《Elastic日报》和《ElasticTalk》社区项目发起人，被 elastic 中国公司授予 2019 年度合作伙伴架构师特别贡献奖。对 Elasticsearch、Kibana、Beats、Logstash、Grafana 等开源软件有丰富的实践经验，为零售、金融、保险、证券、科技等众多行业的客户提供过咨询和培训服务，帮助客户在实际业务中找准开源软件的定位，实现从 0 到 1 的落地、从 1 到 N 的拓展，产生实际的业务价值。

有 SQL 背景的同学在学习 Elasticsearch 时，面对一个查询需求，不由自主地会先思考如何用 SQL 来实现，然后再去想 Elasticsearch 的 Query DSL 如何实现。那么本篇就给大家讲一条常见的 SQL 语句如何用 Elasticsearch 的查询语言实现。

一、SQL语句

假设我们有一个汽车的数据集，每个汽车都有车型、颜色等字段，我希望获取颜色种类大于1个的前2车型。假设汽车的数据模型如下：

{
    "model":"modelA",
    "color":"red"
}

假设我们有一个 cars 表，通过如下语句创建测试数据。

INSERT INTO cars (model,color) VALUES ('A','red'); 
INSERT INTO cars (model,color) VALUES ('A','white'); 
INSERT INTO cars (model,color) VALUES ('A','black'); 
INSERT INTO cars (model,color) VALUES ('A','yellow'); 
INSERT INTO cars (model,color) VALUES ('B','red'); 
INSERT INTO cars (model,color) VALUES ('B','white'); 
INSERT INTO cars (model,color) VALUES ('C','black'); 
INSERT INTO cars (model,color) VALUES ('C','red'); 
INSERT INTO cars (model,color) VALUES ('C','white'); 
INSERT INTO cars (model,color) VALUES ('C','yellow'); 
INSERT INTO cars (model,color) VALUES ('C','blue'); 
INSERT INTO cars (model,color) VALUES ('D','red');
INSERT INTO cars (model,color) VALUES ('A','red');

那么实现我们需求的 SQL 语句也比较简单，实现如下：

SELECT model,COUNT(DISTINCT color) color_count FROM cars GROUP BY model HAVING color_count > 1 ORDER BY color_count desc LIMIT 2;

这条查询语句中 Group By 是按照 model 做分组， Having color_count>1 限定了车型颜色种类大于1，ORDER BY color_count desc 限定结果按照颜色种类倒序排列，而 LIMIT 2 限定只返回前3条数据。
那么在 Elasticsearch 中如何实现这个需求呢？

二、在 Elasticsearch 模拟测试数据

首先我们需要先在 elasticsearch 中插入测试的数据，这里我们使用 bulk 接口，如下所示：

POST _bulk
{"index":{"_index":"cars","_type":"doc","_id":"1"}}
{"model":"A","color":"red"}
{"index":{"_index":"cars","_type":"doc","_id":"2"}}
{"model":"A","color":"white"}
{"index":{"_index":"cars","_type":"doc","_id":"3"}}
{"model":"A","color":"black"}
{"index":{"_index":"cars","_type":"doc","_id":"4"}}
{"model":"A","color":"yellow"}
{"index":{"_index":"cars","_type":"doc","_id":"5"}}
{"model":"B","color":"red"}
{"index":{"_index":"cars","_type":"doc","_id":"6"}}
{"model":"B","color":"white"}
{"index":{"_index":"cars","_type":"doc","_id":"7"}}
{"model":"C","color":"black"}
{"index":{"_index":"cars","_type":"doc","_id":"8"}}
{"model":"C","color":"red"}
{"index":{"_index":"cars","_type":"doc","_id":"9"}}
{"model":"C","color":"white"}
{"index":{"_index":"cars","_type":"doc","_id":"10"}}
{"model":"C","color":"yellow"}
{"index":{"_index":"cars","_type":"doc","_id":"11"}}
{"model":"C","color":"blue"}
{"index":{"_index":"cars","_type":"doc","_id":"12"}}
{"model":"D","color":"red"}
{"index":{"_index":"cars","_type":"doc","_id":"13"}}
{"model":"A","color":"red"}

其中 index 为 cars，type 为 doc，所有数据与mysql 数据保持一致。大家可以在 Kibana 的 Dev Tools 中执行上面的命令，然后执行下面的查询语句验证数据是否已经成功存入。

GET cars/_search

三、Group By VS Terms/Metric Aggregation

SQL 中 Group By 语句在 Elasticsearch 中对应的是 Terms Aggregation，即分桶聚合，对应 Group By color 的语句如下所示：

GET cars/_search
{
  "size":0,
  "aggs":{
    "models":{
      "terms":{
        "field":"model.keyword"
      }
    }
  }
}

结果如下：

{
  "took": 161,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 13,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "models": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "A",
          "doc_count": 5
        },
        {
          "key": "C",
          "doc_count": 5
        },
        {
          "key": "B",
          "doc_count": 2
        },
        {
          "key": "D",
          "doc_count": 1
        }
      ]
    }
  }
}

我们看 aggregations 这个 key 下面的即为返回结果。
SQL 语句中还有一项是 COUNT(DISTINCT color) color_count 用于计算每个 model 的颜色数，在 Elasticsearch 中我们需要使用一个指标类聚合 Cardinality ，进行不同值计数。语句如下：

GET cars/_search
{
  "size": 0,
  "aggs": {
    "models": {
      "terms": {
        "field": "model.keyword"
      },
      "aggs": {
        "color_count": {
          "cardinality": {
            "field": "color.keyword"
          }
        }
      }
    }
  }
}

其返回结果如下：

{
  "took": 74,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 13,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "models": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "A",
          "doc_count": 5,
          "color_count": {
            "value": 4
          }
        },
        {
          "key": "C",
          "doc_count": 5,
          "color_count": {
            "value": 5
          }
        },
        {
          "key": "B",
          "doc_count": 2,
          "color_count": {
            "value": 2
          }
        },
        {
          "key": "D",
          "doc_count": 1,
          "color_count": {
            "value": 1
          }
        }
      ]
    }
  }
}

结果中 color_count 即为每个 model 的颜色数，但这里所有的模型都返回了，我们只想要颜色数大于1的模型，因此这里还要加一个过滤条件。

四、Having Condition VS Bucket Filter Aggregation

Having color_count > 1 在 Elasticsearch 中对应的是 Bucket Filter 聚合，语句如下所示：

GET cars/_search
{
  "size": 0,
  "aggs": {
    "models": {
      "terms": {
        "field": "model.keyword"
      },
      "aggs": {
        "color_count": {
          "cardinality": {
            "field": "color.keyword"
          }
        },
        "color_count_filter": {
          "bucket_selector": {
            "buckets_path": {
              "colorCount": "color_count"
            },
            "script": "params.colorCount>1"
          }
        }
      }
    }
  }
}

返回结果如下：

{
  "took": 39,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 13,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "models": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "A",
          "doc_count": 5,
          "color_count": {
            "value": 4
          }
        },
        {
          "key": "C",
          "doc_count": 5,
          "color_count": {
            "value": 5
          }
        },
        {
          "key": "B",
          "doc_count": 2,
          "color_count": {
            "value": 2
          }
        }
      ]
    }
  }
}

此时返回结果只包含颜色数大于1的模型，但大家会发现颜色数多的 C 不是在第一个位置，我们还需要做排序处理。

五、Order By Limit VS Bucket Sort Aggregation

ORDER BY color_count desc LIMIT 3 在 Elasticsearch 中可以使用 Bucket Sort 聚合实现，语句如下所示：

GET cars/_search
{
  "size": 0,
  "aggs": {
    "models": {
      "terms": {
        "field": "model.keyword"
      },
      "aggs": {
        "color_count": {
          "cardinality": {
            "field": "color.keyword"
          }
        },
        "color_count_filter": {
          "bucket_selector": {
            "buckets_path": {
              "colorCount": "color_count"
            },
            "script": "params.colorCount>1"
          }
        },
        "color_count_sort": {
          "bucket_sort": {
            "sort": {
              "color_count": "desc"
            },
            "size": 2
          }
        }
      }
    }
  }
}

返回结果如下：

{
  "took": 32,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 13,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "models": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "C",
          "doc_count": 5,
          "color_count": {
            "value": 5
          }
        },
        {
          "key": "A",
          "doc_count": 5,
          "color_count": {
            "value": 4
          }
        }
      ]
    }
  }
}

至此我们便将 SQL 语句实现的功能用 Elasticsearch 查询语句实现了。对比 SQL 语句与 Elasticsearch 的查询语句，大家会发现后者复杂了很多，但并非无章可循，随着大家对常见语法越来越熟悉，相信一定会越写越得心应手！

声明：本文由原文《Elasticsearch如何实现 SQL语句中 Group By 和 Limit 的功能》作者“魏彬”授权转载，对未经许可擅自使用者，保留追究其法律责任的权利。

【阿里云Elastic Stack】100%兼容开源ES，独有9大能力，提供免费X-pack服务（单节点价值$6000）

MaxCompute资源管理—— 包年包月资源分时

背景概述在数据开发过程中，普遍有这样的场景：不同项目（开发、生产）对资源使用的时段不同，如数仓团队有离线生产project在夜间是计算高峰期，开发project和业务分析团队的project计算高峰在白天。即不同project在不同的时段对CU的消耗需求不同，日常峰谷时间段比较固定。 MaxCompute管家之前提供的自定义配额组，只能对配额进行二级切分，但无法进行时段切分，2020年7月23日开始，MaxCompute 管家陆续对各个区域进行升级新版本，新版本支持的功能之一——分时配额，将支持对预留计算资源（预留CU）进行按时段切分，满足时段的差异化资源需求，满足对资源隔离（生产/开发/自助分析）不同工作负载的能力，避免相互干扰，同时更大化提高资源使用率。本文将介绍如何进行分时配额，协助您更快上手使用分时配额功能，提高资源使用率。注意事项基本原则：只有包年包月的预留计算资源（预留CU）支持分时配置，非预留计算资源不支持分时。开启分时配置，是对当前region下所有配额组都开启，但默认quota组不支持编辑。配额组开启分时后，默认是一个时段（00:00:00—23:59:...

2020-07-27

620

作者 | 李劲松，Apache Flink Committer，阿里巴巴技术专家 7 月 6 日，Apache Flink 1.11 正式发布。从 3 月初进行功能规划到 7 月初正式发版，1.11 用将近 4 个月的时间重点优化了 Flink 的易用性问题，提升用户的生产使用体验。 SQL 作为 Flink 中公认的核心模块之一，对推动 Flink 流批一体功能的完善至关重要。在 1.11 中，Flink SQL 也进行了大量的增强与完善，开发大功能 10 余项，不仅扩大了应用场景，还简化了流程，上手操作更简单。其中，值得注意的改动包括：默认 Planner 已经切到 Blink planner 上。引入了对 CDC（Change Data Capture，变动数据捕获）的支持，用户仅用几句简单的 SQL 即可对接 Debezium 和 Canal 的数据源。离线数仓实时化，用户可方便地使用 SQL 将流式数据从 Kafka 写入 Hive 等。 Flink SQL 演变随着流计算的发展，挑战不再仅限于数据量和计算量，业务变得越来越复杂，开发者可能是资深的大数据从业者、初学...

2020-07-28

448

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。