This is the 12th day of my participation in the August More Text Challenge. For details, see: August More Text Challenge

If ❤️ my article is helpful, welcome to like, follow. This is the greatest encouragement for me to continue my technical creation. More past articles in my personal column

Elasticsearch aggs polymerization

Barrel and indicators

SELECT COUNT(color) 
FROM table
GROUP BY color 
Copy the code

• COUNT(color) is an indicator.
• GROUP BY color is equivalent to bucket.

A bucket is simply a collection of documents that meet certain criteria:

• An employee belongs to either a male bucket or a female bucket
• Albany belongs to the New York barrel
• The date 2014-10-28 belongs to October barrel

Buckets allow us to divide documents into meaningful collections, but what makes sense is to calculate some metric for the documents in those buckets.

Bucking is a means to an end: it provides a way to group documents so that we can calculate indicators of interest.

Most of the metrics are simple math (such as minimum, average, maximum, and summary)

Try to aggregate

With eachThe labelAs abarrel, such as:

{ “size”: 0, “aggs”: { “tag”: { “terms”: { “field”: “tag_id” } } } }

addindicatorseachThe labelThe total consumption

{
  "size": 0."aggs": {
    "tag": {
      "terms": {
        "field": "tag_id"
      },
      "aggs": {
        "total_cost": {
          "sum": {
            "field": "cost"
          }
        }
      }
    }
  }
}
Copy the code

The labelConsumption,Nested barrelsEvery designer has the material

{
  "size": 0."aggs": {
    "tag": {
      "terms": {
        "field": "tag_id"
      },
      "aggs": {
        "total_cost": {
          "sum": {
            "field": "cost"}},"designer": {
          "terms": {
            "field": "designer_id"
          }
        }
      }
    }
  }
}
Copy the code

The labelConsumption,Nested barrelsMaximum and minimum consumption per designer

{
  "size": 0."aggs": {
    "tag": {
      "terms": {
        "field": "tag_id"
      },
      "aggs": {
        "total_cost": {
          "sum": {
            "field": "cost"}},"designer": {
          "terms": {
            "field": "designer_id"
          },
          "aggs": {
            "avg_cost": {
              "avg": { "field": "cost"}},"max_cost": {
              "max": { "field": "cost" }
            }
          }
        }
      }
    }
  }
}
Copy the code

The bar chart

The date_histogram (like histogram) returns buckets only if the number of documents is non-zero by default

Basic bar chart

• Histogram buckets require two parameters: a numeric field and an interval to define the bucket size.
• The SUM measure is nested within each price range and is used to show total revenue within each range.

Example: show & click relationship

{
   "size" : 0."aggs": {"show": {"histogram": {"field": "show"."interval": 1000
         },
         "aggs": {"total_click": {
               "sum": { 
                 "field" : "click"
               }
             }
         }
      }
   }
}
Copy the code

Statistics by time

How much material to show per month?

{
   "size" : 0."aggs": {
      "show": {
         "date_histogram": {
            "field": "show"."interval": "month"."format": "yyyy-MM-dd"}}}}Copy the code

No document is returned in Buckets, and two parameters need to be set to achieve the effect:

{
   "size" : 0."aggs": {
      "show": {
         "date_histogram": {
            "field": "show"."interval": "month"."format": "yyyy-MM-dd"."min_doc_count" : 0."extended_bounds" : { 
                "min" : "2019-01-01"."max" : "2019-12-31"
            }
         }
      }
   }
}
Copy the code

• min_doc_countThis parameter forces the return of empty buckets.
• extended_boundsThis parameter forces a full year to be returned. / / in doubt

// As an example, we built the aggregation to show the total sales of all car brands by quarter. Total sales are also calculated by quarter, by car brand, to find out which brands make the most money:

{
   "size" : 0."aggs": {
      "sales": {
         "date_histogram": {
            "field": "sold"."interval": "quarter"."format": "yyyy-MM-dd"."min_doc_count" : 0
         },
         "aggs": {
            "per_make_sum": {
               "terms": { "field": "make" },
               "aggs": {
                  "sum_price": { "sum": { "field": "price"}}}},"total_sum": {
               "sum": { "field": "price" } 
            }
         }
      }
   }
}
Copy the code

Scoped aggregation editing

Id = 259 designer material display & consumption

{
    "query" : {
        "match" : { "designer_id" : "259"}},"size":0."aggs" : {
        "avg_show": { "avg": { "field": "show"}},"avg_cost": { "avg": { "field": "cost"}}}}Copy the code

Id = 259 designer material display & consumption compared to all materials

{
    "query" : {
        "match" : {
            "designer_id" : "259"}},"size":0."aggs" : {
    	"avg_show": {
           "avg": { "field": "show"}},"avg_cost": {
           "avg": { "field": "cost"}},"all": {
            "global" : {}, 
            "aggs" : {
			   "all_avg_show": {
                  "avg": { "field": "show"}},"all_avg_cost": {
                  "avg": { "field": "cost" } 
               }
            }
        }
    }
}
Copy the code

Polymer filtration

The average consumption of material is greater than 1000

{
    "size" : 0."query" : {
        "constant_score": {
            "filter": {
                "range": {
                    "cost": { "gte": 1000}}}}},"aggs" : {
        "single_avg_cost": { "avg" : { "field" : "cost"}},"per_designer_sum": {
           "terms": { "field": "designer_id" },
           "aggs": {"single_avg_cost": { "avg" : { "field" : "cost"}},"single_avg_show": { "avg" : { "field" : "show" } }
           }
       }
    }
}
Copy the code

Aggregate calculation

{
  "size": 0."aggs": {
    "tag_aggs": {
      "terms": {
        "field": "tag_id"
      },
      "aggs": {
        "sum_cost": {
          "sum": {
            "field": "cost"}},"sum_show": {
          "sum": {
            "field": "show"}},"cpm": {
          "bucket_script": {
            "buckets_path": {
              "total_cost": "sum_cost"."total_show": "sum_show"
            },
            "script": "( params.total_cost / params.total_show ) * 1000"
          }
        }
      }
    }
  }
}
Copy the code