ElasticSearch高级查询

1、sum查询

请求示例:

GET _search
{
    "query": {
    "bool": {
     "must": [
       {
         "match": {
            "Product": "test"
         }
       }
     ]
    }
  },
  "aggs": {
    "sum_qty":{
      "sum":{
        "field":"Qty"
      }
    }
  }
}

返回结果示例:

{
   "took" : 186,
   "timed_out" : false,
   "_shards" : {
     "total" : 5,
     "successful" : 5,
     "skipped" : 0,
     "failed" : 0
   },
   "hits" : {
     "total" : 1,
     "max_score" : 17.619776,
     "hits" : [
       {
         "_index" : "stock",
         "_type" : "stockproduct",
         "_id" : "149551",
         "_score" : 17.619776,
         "_source" : {
           "ID" : 149551,
           "Product" : "test",
           "SupplierName" : "科技有限公司",
           "Qty" : 30000,
           "CreateTime" : 1562235634,
           "UpdateTime" : 1562236525
         }
       }
       }
     ]
   },
   "aggregations" : {
     "sum_qty" : {
       "value" : 30000.0
     }
   }
 }

ElasticClient代码调用示例:

var mustQuerys = new List<Func<QueryContainerDescriptor<Stock>, QueryContainer>>()
            {
                t=>t.Match(f => f.Analyzer("ik_smart").Field(fd => fd.Product).Query("test")),
            };

            var valueAgg = client.Search<Stock>(r => r.Query(q => q.Bool(b => b.Must(mustQuerys)))
             .Aggregations(agg => agg.Sum("sum_qty", c => c.Field(f => f.Qty))))
            .Aggregations.Sum("sum_qty");
            if (valueAgg?.Value > 0)
            {
                return (int)valueAgg.Value;
            }

2、count(distinct 某个字段)查询:

请求示例:

GET _search
{
    "query": {
    "bool": {
     "must": [
       {
         "match": {
            "Product": "test"
         }
       }
     ]
    }
  },
  "aggs": {
    "count": {
      "cardinality": {
        "field": "SupplierName"
      }
     }
  }
}

返回结果示例:

{
   "took" : 186,
   "timed_out" : false,
   "_shards" : {
     "total" : 5,
     "successful" : 5,
     "skipped" : 0,
     "failed" : 0
   },
   "hits" : {
     "total" : 1,
     "max_score" : 17.619776,
     "hits" : [
       {
         "_index" : "stock",
         "_type" : "stockproduct",
         "_id" : "149551",
         "_score" : 17.619776,
         "_source" : {
           "ID" : 149551,
           "Product" : "test",
           "SupplierName" : "科技有限公司",
           "Qty" : 30000,
           "CreateTime" : 1562235634,
           "UpdateTime" : 1562236525
         }
       }
       }
     ]
   },
   "aggregations" : {
     "count" : {
      "value" : 1
    }
   }
 } 

ElasticClient代码调用示例:

var mustQuerys = new List<Func<QueryContainerDescriptor<Stock>, QueryContainer>>()
            {
                t=>t.Match(f => f.Analyzer("ik_smart").Field(fd => fd.Product).Query("test")),
            };

            var valueAgg = client.Search<Stock>(r => r.Query(q => q.Bool(b => b.Must(mustQuerys)))
             .Aggregations(agg => agg.Cardinality("distinct_companyName", c => c.Field(f => f.SupplierName))))
            .Aggregations.ValueCount("distinct_companyName");
            if (valueAgg?.Value > 0)
            {
                return (int)valueAgg.Value;
            }

3、distinct多个字段查询结果集:

请求示例:

GET _search
{
    "query": {
    "bool": {
     "must": [
       {
         "match": {
            "Product": "test"
         }
       }
     ]
    }
  },
  "aggs": {
    "unique_ids": {
            "terms": {
                "script": "doc['SupplierName'].value + ',' + doc['Product'].value"
            }
    }
  }
}

返回结果示例:

{
   "took" : 186,
   "timed_out" : false,
   "_shards" : {
     "total" : 5,
     "successful" : 5,
     "skipped" : 0,
     "failed" : 0
   },
   "hits" : {
     "total" : 1,
     "max_score" : 17.619776,
     "hits" : [
       {
         "_index" : "stock",
         "_type" : "stockproduct",
         "_id" : "149551",
         "_score" : 17.619776,
         "_source" : {
           "ID" : 149551,
           "Product" : "test",
           "SupplierName" : "科技有限公司",
           "Qty" : 30000,
           "CreateTime" : 1562235634,
           "UpdateTime" : 1562236525
         }
       }
       }
     ]
   },
   "aggregations" : {
     "unique_ids" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "科技有限公司,test",
          "doc_count" : 1
        }
      ]
    }
   }
 } 

ElasticClient代码调用示例:

var mustQuerys = new List<Func<QueryContainerDescriptor<Stock>, QueryContainer>>();
mustQuerys.Add(t => t.Match(f => f.Analyzer("ik_smart").Field(fd => fd.Product).Query("test")));
var valueAgg = client.Search<Stock>(r => r.Query(q => q.Bool(b => b.Must(mustQuerys)))
             .Aggregations(agg => agg.Terms("supplier_product",
             c => c.Script("doc['SupplierName'].value + ',' + doc['Product'].value"))))
            .Aggregations.Values;
            if (valueAgg?.Count() > 0)
            {
                foreach (var agg in valueAgg)
                {
                    if (agg is BucketAggregate)
                    {
                        var items = ((BucketAggregate)agg).Items;
                        foreach (var item in items)
                        {
                            if (item is KeyedBucket<object>)
                            {
                                var key = ((KeyedBucket<object>)item).Key;
                                var keys = key.ToString().Split(',');
                                var supplier = keys[0];
                                var product = keys[1];
                            }
                        }
                    }
                }
            }

发表评论

电子邮件地址不会被公开。 必填项已用*标注