记录常用的es 查询 聚合
GET _cat / indices
GET / p_ext_develop / _mapping / g
GET / p_ext_develop / _analyze {
"field": "other_name_en",
"text": "(3S)-N-Boc-2-azabicyclo[2.2.1]heptane-3-carboxylicacid;"
}
GET / hs2840 / _analyze {
"query": {
"term": {
"cas": {
"value": "29173500"
}
},
"range": {
"exportmonth": {
"from": "2018-01",
"to": "2018-12"
}
}
}
}
GET / p_ext_develop / g / _search {
"query": {
"constant_score": {
"filter": {
"term": {
"products_name_en.text": "Benzoic Acid"
}
},
"boost": 1.2
}
}
}
GET / p_ext_develop / g / _search {
"query": {
"filtered": {
"filter": {
"bool": {
"should": [{
"term": {
"other_name_en": "acid"
}
}]
}
}
}
}
}
#
DELETE / p_ext_develop
GET / p_ext_develop / g / _search {
"size": 300,
"query": {
"function_score": {
"query": {
"bool": {
"should": [{
"query_string": {
"fields": ["products_name_en", "other_name_en"],
"query": "Benzoic acid"
}
},
{
"term": {
"delflag": "0"
}
}
]
}
},
"functions": [{
"filter": {
"term": {
"products_name_en": "Benzoic acid"
}
},
"weight": 15
},
{
"filter": {
"regexp": {
"products_name_en": "[Bb]enzoic [Aa]cid"
}
},
"weight": 13
},
{
"filter": {
"regexp": {
"products_name_en": "[Bb]enzoic [Aa]cid.*"
}
},
"weight": 12
},
{
"filter": {
"term": {
"other_name_en": "Benzoic acid"
}
},
"weight": 10
},
{
"filter": {
"regexp": {
"other_name_en": "[Bb]enzoic [Aa]cid"
}
},
"weight": 8
},
{
"filter": {
"regexp": {
"other_name_en": "[Bb]enzoic [Aa]cid.*"
}
},
"weight": 7
},
{
"filter": {
"query_string": {
"fields": ["products_name_en"],
"query": "Benzoic acid"
}
},
"weight": 2
}
]
}
},
"sort": [{
"_score": {
"order": "desc"
}
},
{
"supplier_count": {
"order": "desc"
}
}
]
}
GET / p_ext_develop / g / _search {
"query": {
"function_score": {
"query": {
"match_all": {}
},
"functions": [{
"filter": {
"term": {
"products_name_en.text": "[Bb]enzoic [Aa]cid"
}
},
"weight": 30
},
{
"filter": {
"prefix": {
"products_name_en.text": "Benzoic Acid"
}
},
"weight": 29
},
{
"filter": {
"query": {
"wildcard": {
"products_name_en.text": {
"value": "Benzoic Acid"
}
}
}
},
"weight": 28
},
{
"filter": {
"term": {
"other_name_en.text": "Benzoic Acid"
}
},
"weight": 27
},
{
"filter": {
"prefix": {
"other_name_en.text": "Benzoic Acid"
}
},
"weight": 26
},
{
"filter": {
"query": {
"wildcard": {
"other_name_en.text": {
"value": "Benzoic Acid"
}
}
}
},
"weight": 25
}
]
}
}
}
GET / p_ext_develop / g / _search {
"size": 300,
"query": {
"function_score": {
"query": {
"bool": {
"must": [{
"query_string": {
"fields": ["products_name_en", "other_name_en"],
"query": "Benzoic acid"
}
},
{
"term": {
"delflag": "0"
}
}
]
}
},
"functions": [{
"filter": {
"term": {
"products_name_en.text": "Benzoic acid"
}
},
"weight": 15
},
{
"filter": {
"regexp": {
"products_name_en.text": "[Bb]enzoic [Aa]cid"
}
},
"weight": 13
},
{
"filter": {
"regexp": {
"products_name_en.text": "[Bb]enzoic [Aa]cid.*"
}
},
"weight": 12
},
{
"filter": {
"term": {
"other_name_en.text": "Benzoic acid"
}
},
"weight": 10
},
{
"filter": {
"regexp": {
"other_name_en.text": "[Bb]enzoic [Aa]cid"
}
},
"weight": 8
},
{
"filter": {
"regexp": {
"other_name_en.text": "[Bb]enzoic [Aa]cid.*"
}
},
"weight": 7
},
{
"filter": {
"query_string": {
"fields": ["products_name_en"],
"query": "Benzoic acid"
}
},
"weight": 2
}
]
}
},
"sort": [{
"_score": {
"order": "desc"
}
},
{
"supplier_count": {
"order": "desc"
}
}
]
}
PUT / p_ext_develop {
"mappings": {
"g": {
"properties": {
"application": {
"type": "string"
},
"cas_no": {
"type": "string",
"index": "not_analyzed"
},
"delflag": {
"type": "string"
},
"other_name_en": {
"type": "string",
"fields": {
"text": {
"type": "string",
"index": "not_analyzed"
}
}
},
"other_name_zh": {
"type": "string",
"fields": {
"text": {
"type": "string",
"index": "not_analyzed"
}
},
"analyzer": "ik"
},
"pid": {
"type": "string"
},
"products_name_en": {
"type": "string",
"fields": {
"text": {
"type": "string",
"index": "not_analyzed"
}
}
},
"products_name_zh": {
"type": "string",
"fields": {
"text": {
"type": "string",
"index": "not_analyzed"
}
},
"analyzer": "ik"
},
"supplier_count": {
"type": "string"
}
}
}
}
}
GET / p_ext_develop / g / _mapping
GET / p_ext_develop / g / _search {
"query": {
"match_all": {}
}
}
GET / p_ext_develop / g / _search {
"query": {
"match": {
"pid": "pd18051825563"
}
}
}
GET / p_ext_develop / g / _search {
"size": 300,
"query": {
"constant_score": {
"filter": {
"bool": {
"must": [{
"term": {
"delflag": "0"
}
},
{
"term": {
"cas_no": {
"value": "75-05-8"
}
}
}
]
}
}
}
},
"sort": [{
"supplier_count": {
"order": "desc"
}
}]
}
#
country_short country_name city_name city_short external post_code
DELETE / jerry_data
GET jerry_data / index / _mapping
#7261, 2231 3741 1425
GET /jerry_data/index/_search
{
"size": 10000,
"query": {
"constant_score": {
"filter": {
"bool": {
"must": [{
"match": {
"country_short": "CA"
}
},
{
"bool": {
"should": [{
"prefix": {
"city_name": {
"value": "AIR"
}
}
}, {
"prefix": {
"post_code": {
"value": "AIR"
}
}
}]
}
}
]
}
}
}
},
"sort": [{ "city_name": { "order": "desc" }},
{ "post_code": { "order": "asc" }}
]
}
GET /jerry_data/index/_search
{
"size": 10000,
"query": {
"constant_score": {
"filter": {
"bool": {
"should": [{
"regexp": {
"country_short": "AIR.*"
}
}, {
"regexp": {
"city_name": "AIR.*"
}
},
{
"regexp": {
"city_short": "AIR.*"
}
}
]
}
}
}
}
}
PUT /jerry_data
{
"mappings": {
"index":{
"properties": {
"country_short": {
"type": "string",
"index": "not_analyzed"
},
"country_name": {
"index": "not_analyzed",
"type": "string"
},
"city_name": {
"type": "string",
"index": "not_analyzed"
},
"city_short": {
"type": "string",
"index": "not_analyzed"
},
"external": {
"type": "string",
"index": "not_analyzed"
},
"post_code": {
"type": "string",
"index": "not_analyzed"
}
}
}
}
}
GET /hs2840/index/_search
{
"query": {
"term": {
"country_code": {
"value": "tt"
}
}
}
}
GET /hs2840/index/_search
{
"query": {
"match_all": {}
}
}
GET /cars/transactions/_search
{
"size": 0,
"query": {
"match_all": {}
},
"aggs": {
"colors": {
"terms": {
"field": "color"
}
}
}
}
# 查询统计
GET / cars / transactions / _search {
"query": {
"match": {
"make": "ford"
}
},
"aggs": {
"colors": {
"terms": {
"field": "color"
}
}
}
}
GET / cars / transactions / _search {
"size": 0,
"query": {
"match": {
"make": "ford"
}
},
"aggs": {
"single_avg_price": {
"avg": {
"field": "price"
}
},
"all": {
"global": {},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
}
}
}
}
}
GET / cars / transactions / _search {
"size": 0,
"query": {
"constant_score": {
"filter": {
"range": {
"price": {
"gte": 10000
}
}
}
}
},
"aggs": {
"single_avg_price": {
"avg": {
"field": "price"
}
}
}
}
GET / cars / transactions / _search {
"size": 0,
"query": {
"match": {
"make": "ford"
}
},
"aggs": {
"recent_sales": {
"filter": {
"range": {
"sold": {
"from": "now-1M"
}
}
},
"aggs": {
"average_price": {
"avg": {
"field": "price"
}
}
}
}
}
}
GET / cars / transactions / _search {
"size": 0,
"aggs": {
"distinct_colors": {
"cardinality": {
"field": "color"
}
}
}
}
GET / cars / transactions / _search {
"size": 0,
"aggs": {
"months": {
"date_histogram": {
"field": "sold",
"format": "yyyy-MM-dd",
"interval": "month",
"min_doc_count": 0,
"extended_bounds": {
"min": "2014-01-01",
"max": "2015-12-31"
}
},
"aggs": {
"distinct_colors": {
"cardinality": {
"field": "color"
}
}
}
}
}
}
GET / cars / transactions / _search {
"size": 0,
"query": {
"match": {
"make": "ford"
}
},
"post_filter": {
"term": {
"color": "green"
}
},
"aggs": {
"all_colors": {
"terms": {
"field": "color"
}
}
}
}
#
尝试尝试尝试juhe尝试尝试聚合
GET / cars / transactions / _search {
"size": 0,
"aggs": {
"popular_colors": {
"terms": {
"field": "color",
"size": 50
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
},
"make": {
"terms": {
"field": "make",
"size": 10
},
"aggs": {
"min_price": {
"min": {
"field": "price"
}
},
"max_price": {
"max": {
"field": "price"
}
}
}
}
}
}
}
}
#
图表图表图表
GET / cars / transactions / _search {
"size": 0,
"aggs": {
"price": {
"histogram": {
"field": "price",
"interval": 20000
},
"aggs": {
"revenue": {
"sum": {
"field": "price"
}
}
}
}
}
}
GET / cars / transactions / _search {
"size": 0,
"aggs": {
"sales": {
"date_histogram": {
"field": "sold",
"interval": "quarter",
"format": "yyyy-MM-dd",
"min_doc_count": 0,
"extended_bounds": {
"min": "2014-01-01",
"max": "2014-12-31"
}
},
"aggs": {
"per_make_sum": {
"terms": {
"field": "make"
},
"aggs": {
"sum_price": {
"sum": {
"field": "price"
}
}
}
},
"total_sum": {
"sum": {
"field": "price"
}
}
}
}
}
}
GET / hs2840 / index / _search {
"size": 0,
"aggs": {
"price_count": {
"terms": {
"size": 300,
"field": "month"
},
"aggs": {
"avg_qty": {
"avg": {
"field": "JSON_qty"
}
},
"country_zh": {
"terms": {
"size": 300,
"field": "country_zh"
},
"aggs": {
"max_qty": {
"min": {
"field": "JSON_qty"
}
},
"min_qty": {
"max": {
"field": "JSON_qty"
}
}
}
},
"sum_qty": {
"sum": {
"field": "JSON_qty"
}
}
}
}
}
}
GET / hs2840 / index / _search {
"query": {
"term": {
"cas": {
"value": "29053200"
}
}
}
}
GET / hs2840 / index / _search {
"query": {
"filtered": {
"query": {
"term": {
"cas": {
"value": "29053200"
}
}
},
"filter": {
"range": {
"exportmonth": {
"gte": "2018-01",
"lte": "2018-12"
}
}
}
}
}
}
#
2
GET / hs2840 / index / _search {
"query": {
"term": {
"country_code": {
"value": "ar"
}
}
}
}
#
月份的max and min
GET / hs2840 / index / _search {
"size": 0,
"aggs": {
"max_month": {
"max": {
"field": "exportmonth",
"format": "yyyy-MM"
}
},
"min_month": {
"min": {
"field": "exportmonth",
"format": "yyyy-MM"
}
}
}
}#
接口2
GET / hs2840 / index / _search {
"size": 10000,
"query": {
"term": {
"cas": {
"value": "29171400"
}
}
},
"aggs": {
"fromdata": {
"filter": {
"and": [{
"range": {
"exportmonth": {
"from": "2018-01",
"to": "2018-12"
}
}
}]
},
"aggs": {
"summonth": {
"date_histogram": {
"field": "exportmonth",
"interval": "month",
"format": "yyyy-MM",
"min_doc_count": 0,
"order": {
"_key": "desc"
}
}
}
}
}
}
}
GET / hs2840 / index / _search {
"query": {
"range": {
"exportmonth": {
"from": "2018-06",
"to": "2018-07"
}
}
}
}
GET / hs2840 / index / _search {
"size": 10000,
"query": {
"bool": {
"must": [{
"term": {
"cas": {
"value": "29053200"
}
}
}],
"filter": {
"range": {
"exportmonth": {
"gte": "2018-01",
"lte": "2018-12"
}
}
}
}
}
}
GET / hs2840 / index / _search {
"size": 5000,
"query": {
"term": {
"cas": {
"value": "31054000"
}
}
},
"aggs": {
"summonth": {
"date_histogram": {
"field": "exportmonth",
"interval": "month",
"format": "yyyy-MM",
"min_doc_count": 0,
"extended_bounds": {
"min": "2018-01",
"max": "2018-10"
},
"order": {
"_key": "desc"
}
},
"aggs": {
"datefrom": {
"filter": {
"range": {
"exportmonth": {
"from": "2018-01",
"to": "2018-12",
"format": "yyyy-MM"
}
}
},
"aggs": {
"sum_qty": {
"sum": {
"field": "JSON_qty"
}
},
"sum_price": {
"sum": {
"field": "JSON_price"
}
}
}
}
}
}
}
}
#
接口3验证
GET / hs2840 / index / _search {
"size": 0,
"aggs": {
"total_qty": {
"sum": {
"field": "JSON_qty"
}
},
"total_price": {
"sum": {
"field": "JSON_price"
}
}
}
}
#
接口3
GET / hs2840 / index / _search {
"size": 0,
"aggs": {
"all": {
"global": {},
"aggs": {
"total_qty": {
"sum": {
"field": "JSON_qty"
}
},
"total_price": {
"sum": {
"field": "JSON_price"
}
}
}
},
"bydate": {
"filter": {
"range": {
"exportmonth": {
"gte": "2018-01",
"lte": "2018-12"
}
}
},
"aggs": {
"bycoutry": {
"terms": {
"field": "country_code",
"size": 10,
"order": {
"sum_qty": "desc"
}
},
"aggs": {
"sum_qty": {
"sum": {
"field": "JSON_qty"
}
},
"sum_price": {
"sum": {
"field": "JSON_price"
}
}
}
}
}
}
}
}
GET / hs2840 / index / _search {
"size": 0,
"aggs": {
"bycountry": {
"terms": {
"field": "country_code",
"size": 300
},
"aggs": {
"bydate": {
"filter": {
"range": {
"exportmonth": {
"gte": "2018-01",
"lte": "2018-12"
}
}
},
"aggs": {
"bymnth": {
"date_histogram": {
"field": "exportmonth",
"interval": "month",
"format": "yyyy-MM",
"min_doc_count": 0,
"extended_bounds": {
"min": "2018-01",
"max": "2018-12"
},
"order": {
"sum_qty": "desc"
}
},
"aggs": {
"sum_qty": {
"sum": {
"field": "JSON_qty"
}
},
"sum_price": {
"sum": {
"field": "JSON_price"
}
}
}
}
}
}
}
}
}
}
GET / hs2840 / index / _search {
"size": 0,
"aggs": {
"bycountry": {
"terms": {
"field": "country_code",
"size": 300,
"order": {
"sum_qyt": "desc"
}
},
"aggs": {
"sum_qyt": {
"sum": {
"field": "JSON_qty"
}
},
"sum_price": {
"sum": {
"field": "JSON_price"
}
}
}
}
}
}
GET / hs2840 / index / _search {
"size": 10000,
"query": {
"term": {
"cas": {
"value": "29173500"
}
}
},
"aggs": {
"bymonth": {
"date_histogram": {
"field": "exportmonth",
"interval": "month",
"format": "yyyy-MM",
"min_doc_count": 0,
"extended_bounds": {
"min": "2019-01",
"max": "2019-06"
},
"order": {
"_key": "desc"
}
},
"aggs": {
"sum_qty": {
"sum": {
"field": "JSON_qty"
}
},
"sum_price": {
"sum": {
"field": "JSON_price"
}
}
}
}
}
}#
hscode 和时间
GET / hs2840 / index / _search {
"size": 10000,
"query": {
"term": {
"cas": "29173500"
},
"filtered": {
"filter": {
"range": {
"exportmonth": {
"gte": "2019-01",
"lte": "2019-12"
}
}
}
}
},
"aggs": {
"bymonth": {
"filter": {
"range": {
"exportmonth": {
"gte": "2019-01",
"lte": "2019-07"
}
}
},
"aggs": {
"datelist": {
"date_histogram": {
"field": "exportmonth",
"interval": "month",
"format": "yyyy-MM",
"min_doc_count": 0,
"extended_bounds": {
"min": "2019-01",
"max": "2019-12"
},
"order": {
"_key": "desc"
}
},
"aggs": {
"sum_qty": {
"sum": {
"field": "JSON_qty"
}
},
"sum_price": {
"sum": {
"field": "JSON_price"
}
}
}
}
}
}
}
}
#
接口1
GET / hs2840 / index / _search {
"size": 0,
"query": {
"term": {
"country_code": {
"value": "ar"
}
}
},
"aggs": {
"summonth": {
"filter": {
"range": {
"exportmonth": {
"gte": "2017-01",
"lte": "2017-12"
}
}
},
"aggs": {
"data_month": {
"date_histogram": {
"field": "exportmonth",
"interval": "month",
"format": "yyyy-MM",
"min_doc_count": 0,
"extended_bounds": {
"min": "2017-01",
"max": "2017-12"
},
"order": {
"_key": "desc"
}
},
"aggs": {
"sum_qty": {
"sum": {
"field": "JSON_qty"
}
},
"sum_price": {
"sum": {
"field": "JSON_price"
}
}
}
}
}
}
}
}
#
3
# Delete the `cars`
index
DELETE / cars
# Index example docs
POST / cars / transactions / _bulk {
"index": {}
} {
"price": 10000,
"color": "red",
"make": "honda",
"sold": "2014-10-28"
} {
"index": {}
} {
"price": 20000,
"color": "red",
"make": "honda",
"sold": "2014-11-05"
} {
"index": {}
} {
"price": 30000,
"color": "green",
"make": "ford",
"sold": "2014-05-18"
} {
"index": {}
} {
"price": 15000,
"color": "blue",
"make": "toyota",
"sold": "2014-07-02"
} {
"index": {}
} {
"price": 12000,
"color": "green",
"make": "toyota",
"sold": "2014-08-19"
} {
"index": {}
} {
"price": 20000,
"color": "red",
"make": "honda",
"sold": "2014-11-05"
} {
"index": {}
} {
"price": 80000,
"color": "red",
"make": "bmw",
"sold": "2014-01-01"
} {
"index": {}
} {
"price": 25000,
"color": "blue",
"make": "ford",
"sold": "2014-02-12"
}
#
Basic aggregation - most popular car colors
GET / cars / transactions / _search {
"size": 0,
"aggs": {
"popular_colors": {
"terms": {
"field": "color"
}
}
}
}
#
Find the average price of each car color
GET / cars / transactions / _search {
"size": 0,
"aggs": {
"colors": {
"terms": {
"field": "color"
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
}
}
}
}
}
#
Nest a second bucket to determine top makes per color
GET / cars / transactions / _search {
"size": 0,
"aggs": {
"colors": {
"terms": {
"field": "color"
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
},
"make": {
"terms": {
"field": "make"
}
}
}
}
}
}
GET / cars / transactions / _search {
"size": 0,
"query": {
"match": {
"make": "ford"
}
},
"aggs": {
"recent_sales": {
"filter": {
"range": {
"sold": {
"from": "now-60M"
}
}
},
"aggs": {
"average_price": {
"avg": {
"field": "price"
}
}
}
}
}
}
#
Finally, add some extra metrics to determine min / max price per - make, per - color
GET / cars / transactions / _search {
"size": 0,
"aggs": {
"colors": {
"terms": {
"field": "color"
},
"aggs": {
"avg_price": {
"avg": {
"field": "price"
}
},
"make": {
"terms": {
"field": "make"
},
"aggs": {
"min_price": {
"min": {
"field": "price"
}
},
"max_price": {
"max": {
"field": "price"
}
}
}
}
}
}
}
}