技术

Elasticsearch sum和group by(aggregation)的集成

Elasticsearch, Go语言, 实用教程

Elasticsearch的API是RESTful风格的，传入json风格的DSL就可以进行很多种操作。然而DSL的语法和格式实在是有些太过复杂，对于新手来说难以快速掌握，所以有时候会用ES的SQL接口写一些SQL来进行查询。然而ES对SQL的支持并不是很完备，所以学习一点DSL的语法还是有必要的。
简单的一些查询和插入方法在网上很容易搜到，但是一些比较高级的操作和他们的组合操作就有可能搜不到了。比如SQL里sum和group by，在SQL里很容易写并且组合，但是对于DSL来说就有点绕。
这里以一个例子来说明怎么用DSL进行sum和group by的组合操作。假设有一个花店销售管理的数据库表flower_sale_detail，数据库的逻辑结构差不多是这样：

flower_id flower_name sales reserves sale_date
… … … … …
1501 Rose 21 56 20201210
1502 Lily 19 26 20201210
1501 Rose 32 47 20201211
1502 Lily 25 33 20201211
1503 Carnation 17 29 20201211
1501 Rose 38 41 20201212
1502 Lily 31 42 20201212
1503 Carnation 27 31 20201212
1504 Violet 19 35 20201212
… … … … …

比如说要得到在三天销量前三的花，以及它们的库存量，按销量由高到低排序，用SQL很好写：

1
2
3
4
5
6
SELECT sum(sales) as total_sales, sum(reserves) as total_reserves
FROM flower_sale_detail
WHERE sale_date BETWEEN '20201210' AND '20201212'
GROUP BY flower_name
ORDER BY total_sales
LIMIT 3

如果在ES里存在类似的索引，那用DSL写就是这样：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
GET /flower_sale_detail/_search
{
    "query": {
        "bool": {
            "must": [
                {
                    "range": {
                        "sale_date": {
                            "gte": "20201210",
                            "lte": "20201212"
                        }
                    }
                }
            ]
        }
    },
    "aggs": {
        "by_flower_name": {
            "terms": {
                "field": "flower_name",
                "order": {
                    "total_sales": "desc"
                },
                "size": 10
            },
            "aggs": {
                "total_sales": {
                    "sum": {
                        "field": "sales"
                    }
                },
                "total_reserves": {
                    "sum": {
                        "field": "reserves"
                    }
                }
            }
        }
    },
    "size": 0
}

至于在代码里怎么写出来，这里以GO语言为例，大概是这么写的：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
rangeQuery := elastic.NewRangeQuery("sale_date").
    From("20201210").
    To("20201212")
boolQuery := elastic.NewBoolQuery().Must(rangeQuery)
searchService := elastic.NewSearchService(client).
    Size(0).
    Query(boolQuery).
    Index("flower_sale_detail")
termAgg := elastic.NewTermsAggregation().Field("flower_name")
termAgg = termAgg.SubAggregation("total_sales", 
    elastic.NewSumAggregation().Field("sales"))
termAgg = termAgg.SubAggregation("total_reserves", 
    elastic.NewSumAggregation().Field("reserve"))
termAgg = termAgg.Order("total_sales", false).Size(3)
searchService = searchService.Aggregation("flower_name", termAgg)
res, err := searchService.Do(ctx)

值得注意的一个小细节是，上面DSL的最后一个size限定是针对query的，如果没有这个size限定，ES的返回结果会带一堆（默认是10个）根据query条件查询出来的结果。

以上这个小例子基本上涵盖了SQL里的select, sum, group by, order by，不得不说，DSL写起来真的麻烦。如果SQL不是很复杂的时候，完全可以这样写：

1
2
3
4
5
6
POST _sql?format=txt
{
    "query":"""
    YOUR SQL
    """
}

值得一提的是，DSL目前并不直接支持聚合结果的分页（当然常规query的分页还是支持的，只需要写好”from”和”size”就行），而SQL很容易用 LIMIT start, size 做分页查询，并且ES的SQL接口不支持LIMIT传入两个参数，这样有时候查询的数据量很大的时候就很麻烦。为了做分页，有时只能做一些骚操作，比如在aggs的terms里设置size的值为start+limit，读到内存后再去做分页。网上有些大神也用了很多更骚的操作来对聚合结果进行分页，感兴趣的可以取Google一下。

分享到