ElasticSearch聚合可以执行SQL的功能吗? [英] Can ElasticSearch aggregations do what SQL can do?

问题描述

在Elasticsearch中，我需要获得从最高到最低出现频率最频繁的频率和颜色数量.如果我有这样的数据:

In Elasticsearch I need get the frequency and the number of colors that occur the most frequent from the highest to lowest. If I have data like this:

id|name
----------
1|blue
----------
2|blue
----------
3|green
----------
4|yellow
----------
5|blue
----------
6|yellow
----------
7|purple
----------
8|purple
----------
9|purple

我需要获取每种颜色的计数，然后按计数进行分组.因此，最后，我希望将出现相同次数的所有颜色归为一组.这就是我在sql中的处理方式.

I need to get the count of each color and then group by the count. So in the end, I would like all the colors that occurs the same number of times to be inside one group. This is how I would do it in sql.

select 
  count(*) as 'Number of Colors', 
  i.c as 'Seen times' 
from 
    (
      select
        name as 'n', 
        count(*) as 'c'
      from
        colors
      group by name
   ) i 
group by i.c
order by i.c desc;

这将返回:

Number of Colors | Seen times
------------------------------
2                | 3
------------------------------
1                | 2
------------------------------
1                | 1

如何在Elasticsearch查询中编写它?我正在使用5.5版.

How would I write it in Elasticsearch query? I am using version 5.5.

推荐答案

默认情况下，弹性搜索不提供任何使用一些聚合结果可以输出新的聚合集，例如SQL所具有的.

By default elastic search do not provide any aggregation type which uses some aggregation results to give output of new set of aggregations like SQL has.

1. 一种方法是进行术语汇总，然后将其分组使用脚本
2. 如果要在单个查询中实现此目的，则可以使用脚本式指标聚合来实现，请使用下面的链接获取更多详细信息:

1. One way of doing it is to have terms aggregation and then group them using a script
2. If you want to achieve this in single query, you can use Scripted Metric Aggregation to achieve this, use below link to get more details :

https://www.elastic.co/guide/zh-CN/elasticsearch/reference/current/search-aggregations-metrics-scripted-metric-aggregation.html

这篇关于ElasticSearch聚合可以执行SQL的功能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！