ElasticSearch聚合之按照某字段分组统计另一字段总和

劉亞坤

2022 年 09 月 05 日

139 次浏览

暂无评论

2342字数

实战代码

# 需求

将课程包按照学科分组，分别统计各学科下的课程小节数量

## 数据格式

```json
{
          "subject" : 1001, //学科
          "teacherIds" : "62c522aabb72127914f7bcd7",
          "type" : -1,
          "version" : -1,
          "imgUrl" : "",
          "hits" : 258,
          "createdAt" : "2022-07-07T16:41:31.9609245+08:00",
          "isDeleted" : false,
          "grade" : 1040,
          "intro" : "优秀特级教师",
          "name" : "高一语文上学期同步课（通用版）",
          "id" : "68652741-6b6c-4171-89c3-a5734a9c8ed2",
          "courseId" : 1000422852,
          "status" : 1,
          "sectionNum":25, //课程小节数量
          "updatedAt" : "2022-07-07T08:41:31.9605212+00:00"
        }
```

## ES代码

```shell
POST youzy_applib_aliyun_course/_search
{
  "size": 0, 
  "aggs": {
    "subjet": {
      "terms": {
        "field": "subject", //按照学科分组
        "size": 1000
      },
      "aggs": {
        "coun": {
          "sum": {
            "field": "sectionNum" //用sum去总数
          }
        }
      }
    }
  }
}

```

## c#翻译

```csharp
 public async Task<IList<CourseSubjectStatsViewDto>> GetSubjectStats()
        {
            var conditions = new List<Func<QueryContainerDescriptor<Course_ES>, QueryContainer>>()
            {
                x => x.Term(t => t.Field(f => f.IsDeleted).Value(false)),
                x => x.Term(t => t.Field(f => f.Status).Value(1))
            };

var query = await Client.SearchAsync<Course_ES>(x => x.Index(IndexName)
                .Query(q => q.Bool(b => b.Must(conditions)))
                .Size(0)
                .Aggregations(agg => agg.Terms("subject", sel => sel.Field(f => f.Subject)
                    .Aggregations(agg2 => agg2.Sum("sections", sel2 => sel2.Field(f => f.SectionNum)))
                )));

var result = new List<CourseSubjectStatsViewDto>();
            var subjectAgg = (BucketAggregate)query.Aggregations.First(x => x.Key == "subject").Value;
            var buckets = subjectAgg.Items.Select(x => (KeyedBucket<object>)x).ToList();
            foreach (var bucket in buckets)
            {
                result.Add(new CourseSubjectStatsViewDto()
                {
                    Subject = bucket.Key.ToInt(),
                    Count = ((ValueAggregate)bucket.Values.First()).Value.ToInt()
                });
            }
            result = result.OrderBy(x => x.Subject).ToList();
            return result;
        }

```

最后修改：2022 年 09 月 05 日